469,579 Members | 1,161 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

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?

TFD

Jul 23 '05 #1
1 5946
Below 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\\SqlScriptF ile.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" <tr****************@yahoo.com> wrote in message
news:11**********************@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

Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by lawrence | last post: by
2 posts views Thread by Michael Schmitt | last post: by
4 posts views Thread by Alex Page | last post: by
4 posts views Thread by Nick Sinclair | last post: by
8 posts views Thread by Ravi | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.