C#: Running DDL to Create Functions.

Greetings All, I was hoping that someone out there has seen a problem
similar to the one I am seeing. I have one file with several "create
function" statements in it. When I try to run it through C# I get
errors because it does not like the "GO" statements.

Second, when I break them up into individual files and then call them
from C# and try to execute them I get funky results if there is a
variable in an update statement. C# gets back to me that the variable
must be declared?

TFDBelow is a C# example that runs a SQL script file and parses for 'GO' batch
delimiters. It uses OleDb but can be modified to use SqlClient, if needed.

I'm not sure what the issue is in your second question. Is the variable
declared in the script? If not, it must be passed as a command parameter.

static void main()
{
string connectionString;

connectionString = "Provider=SQLOLEDB;" +
";Data Source=MyServer" +
";Initial Catalog=MyDatabase" +
";Integrated Security=SSPI;";
System.Data.OleDb.OleDbConnection oleDbConnection =
new System.Data.OleDb.OleDbConnection(connectionString );
oleDbConnection.Open();

executeSqlScriptFile("C:\\MySqlScripts\\SqlScriptFile.sql",
oleDbConnection);
}

static void executeSqlScriptFile(string sqlScriptFileName,
System.Data.OleDb.OleDbConnection oleDbConnection)
{
System.IO.StringWriter sqlBatchWriter;
System.IO.StreamReader sqlScriptFile =
new System.IO.StreamReader(sqlScriptFileName);
sqlBatchWriter = new System.IO.StringWriter();
string sqlScriptLine;

while(sqlScriptFile.Peek() > -1)
{
sqlScriptLine = sqlScriptFile.ReadLine();
if (string.Compare(sqlScriptLine.Trim(), "GO", true) == 0)
{
executeSqlScriptBatch(sqlBatchWriter.ToString(),
oleDbConnection);
sqlBatchWriter.Close();
sqlBatchWriter = new System.IO.StringWriter();
}
else
{
sqlBatchWriter.WriteLine(sqlScriptLine);
}
}

executeSqlScriptBatch(sqlBatchWriter.ToString(),
oleDbConnection);

sqlBatchWriter.Close();
}

static void executeSqlScriptBatch(string sqlBatch,
System.Data.OleDb.OleDbConnection oleDbConnection)
{
if (string.Compare(sqlBatch.Trim(), "", true) == 0)
return;
System.Data.OleDb.OleDbCommand oleDbCommand =
new System.Data.OleDb.OleDbCommand(sqlBatch,
oleDbConnection);
oleDbCommand.ExecuteNonQuery();
}

--
Hope this helps.

Dan Guzman
SQL Server MVP

"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1111452007.234077.258920@.z14g2000cwz.googlegr oups.com...
> Greetings All, I was hoping that someone out there has seen a problem
> similar to the one I am seeing. I have one file with several "create
> function" statements in it. When I try to run it through C# I get
> errors because it does not like the "GO" statements.
> Second, when I break them up into individual files and then call them
> from C# and try to execute them I get funky results if there is a
> variable in an update statement. C# gets back to me that the variable
> must be declared?
> TFD