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:
> > Does anyone know how I can call a query saved as an MSSQL Server query from C#?
>|||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:
> 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:
>>Does anyone know how I can call a query saved as an MSSQL Server query from C#?|||Kim,
Here's a start on reading/writing text files.
http://www.csharpfriends.com/Articles/getArticle.aspx?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...
> 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:
>> > Does anyone know how I can call a query saved as an MSSQL Server query
>> > from C#?|||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/Articles/getArticle.aspx?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...
> > 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:
> >> > Does anyone know how I can call a query saved as an MSSQL Server query
> >> > from C#?
> >>
>
>|||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\\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
"kim d" <kimd@.discussions.microsoft.com> wrote in message
news:9DFFD0A7-1794-4375-B777-91A8981F9C72@.microsoft.com...
> 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/Articles/getArticle.aspx?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...
>> > 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:
>> >> > Does anyone know how I can call a query saved as an MSSQL Server
>> >> > query
>> >> > from C#?
>> >>
>>|||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\\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
> "kim d" <kimd@.discussions.microsoft.com> wrote in message
> news:9DFFD0A7-1794-4375-B777-91A8981F9C72@.microsoft.com...
> > 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/Articles/getArticle.aspx?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...
> >> > 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:
> >> >> > Does anyone know how I can call a query saved as an MSSQL Server
> >> >> > query
> >> >> > from C#?
> >> >>
> >>
> >>
> >>
>
>|||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.
>