Calling SQL Server Query from C#

Does anyone know how I can call a query saved as an MSSQL Server query from C#?
kim,
When you say "saved" do you mean as an ASCII text file with a .sql
extension?
There are many ways in ADO.NET using C#, here's an example using the
SQLClient:
CS\executingacommand.cs
http://tinyurl.com/5re88
There's much material to read in the MSDN documentation.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
kim d wrote:
> Does anyone know how I can call a query saved as an MSSQL Server query from C#?
|||Yes as a.sql file. I know how to query from within my code using CommandText
but how do I call the .sql file previously created. The code sample in the
link below doesn't seem to refer to location for the query it just sets the
CommandText
"Mark Allison" wrote:

> kim,
> When you say "saved" do you mean as an ASCII text file with a .sql
> extension?
> There are many ways in ADO.NET using C#, here's an example using the
> SQLClient:
> CS\executingacommand.cs
> http://tinyurl.com/5re88
> There's much material to read in the MSDN documentation.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> kim d wrote:
>
|||kim,
I'm not an expert at C#, but maybe you could read the file into a
variable and then pass that to CommandText?
Alternatively you could call osql.exe and give it the .sql file as a
parameter.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
kim d wrote:[vbcol=seagreen]
> Yes as a.sql file. I know how to query from within my code using CommandText
> but how do I call the .sql file previously created. The code sample in the
> link below doesn't seem to refer to location for the query it just sets the
> CommandText
> "Mark Allison" wrote:
>
|||Kim,
Here's a start on reading/writing text files.
http://www.csharpfriends.com/Article...?articleID=132
hth
Hirantha
(Please reply to newsgroups)
"kim d" <kimd@.discussions.microsoft.com> wrote in message
news:7FAA3B11-049D-4172-8680-6F9C4C23316F@.microsoft.com...[vbcol=seagreen]
> Yes as a.sql file. I know how to query from within my code using
> CommandText
> but how do I call the .sql file previously created. The code sample in the
> link below doesn't seem to refer to location for the query it just sets
> the
> CommandText
> "Mark Allison" wrote:
|||Thanks for your help. I don't have a problem writing the SQL statements
directly into my application but I wasn't sure what the convention was
(regardless of language). I would have thought it was customary to reference
the queries directly since they have already been constructed.
"Hirantha S. Hettiarachchi" wrote:

> Kim,
> Here's a start on reading/writing text files.
> http://www.csharpfriends.com/Article...?articleID=132
> --
> hth
> Hirantha
> (Please reply to newsgroups)
> "kim d" <kimd@.discussions.microsoft.com> wrote in message
> news:7FAA3B11-049D-4172-8680-6F9C4C23316F@.microsoft.com...
>
>
|||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.
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
"kim d" <kimd@.discussions.microsoft.com> wrote in message
news:9DFFD0A7-1794-4375-B777-91A8981F9C72@.microsoft.com...[vbcol=seagreen]
> Thanks for your help. I don't have a problem writing the SQL statements
> directly into my application but I wasn't sure what the convention was
> (regardless of language). I would have thought it was customary to
> reference
> the queries directly since they have already been constructed.
> "Hirantha S. Hettiarachchi" wrote:
|||Thanks Dan, that looks like it'll do the trick.
-Kim
"Dan Guzman" wrote:

> 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.
> 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
> "kim d" <kimd@.discussions.microsoft.com> wrote in message
> news:9DFFD0A7-1794-4375-B777-91A8981F9C72@.microsoft.com...
>
>
|||Nice!
Dan Guzman wrote:
> 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.
>