Best way to execute sql command in c#

Question

I need to do some sql query against sql-server 2008 R2. The command is:

C:> sqlcmd –S
.\APPSDB
1> use
Example_database
2> go
1> Select SiteName, SiteID, Platform, Description, AdminUser from dbo.ExampleInfo
2> go

I want to develop a console application in C# to run this query. My question is: What is the appropriate way to do it? Should I put the above command into a SQL script and use a process to execute this script, or should I use stand C# SQL API such as SqlConnection to do it? Something like:

string cmdStr = @"....." //the above command
using (var connection = new SqlConnection(ConnectionString))
{
    using (var command = connection.CreateCommand())
    {
        connection.Open();
        command.CommandText = cmdStr;
        using (var reader = command.ExecuteReader())
        {
            do
            {
                while (reader.Read())
                {             
                    Console.WriteLine(reader["SiteName"].ToString());
                    Console.WriteLine(reader["SiteName"].ToString());
                    // the rest
                }
            Console.WriteLine("--next command--");
        } while (reader.NextResult());
    }
}

That is a choice you need to make. If this app begins to grow (as we all know most apps do) you should use an sql connection object and call a sproc. Otherwise a script is fine. — JonH, Feb 01 '13 at 18:13
why downvote my qustion? I have several databases under one sql server instance APPSDB. — ericyoung, Feb 01 '13 at 18:23
@JonH meh; sprocs can be over-rated ; parameterized SQL is perfectly appropriate in most cases — Marc Gravell, Feb 01 '13 at 18:34
@eric the only thing you need in the CommandText is the "select ..." — Marc Gravell, Feb 01 '13 at 18:41

score 1 · Answer 1 · answered Feb 01 '13 at 18:13

1

There is no need for sqlcmd for this.

The CommandText property should contain the query only like this:

string cmdStr = @"Select SiteName, SiteID, Platform, Description, AdminUser 
                  from dbo.ExampleInfo";

answered Feb 01 '13 at 18:13

Mahmoud Gamal

78,257
17
139
164

I have several databases under one sql server instance. I think I need to use "Use Example_Database", or "Use_Example_Database1", etc. single cmdStr may not work. – ericyoung Feb 01 '13 at 18:25
@ericyoung This should be in the connection string, provided in the connection string property. – Mahmoud Gamal Feb 01 '13 at 18:31
@eric you shouldn't need that at all: that belogs in the connection string – Marc Gravell Feb 01 '13 at 18:35

Best way to execute sql command in c#

1 Answers1