Passing sql statements as strings to mssql with C#?

Question

This is a really, really stupid question but I am so accustomed to using linq / other methods for connecting and querying a database that I never stopped to learn how to do it from the ground up.

Question: How do I establish a manual connection to a database and pass it a string param in C#? (yes, I know.. pure ignorance).

Thanks

Answer 1

One uses the SqlCommand class to execute commands (either stored procedures or sql) on SQL Server using ado.net. Tutorials abound.

Answer 2

Here's an example from http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx

public void RunStoredProcParams()
    {
     SqlConnection conn = null;
     SqlDataReader rdr  = null;

     // typically obtained from user
     // input, but we take a short cut
     string custId = "FURIB";

     Console.WriteLine("\nCustomer Order History:\n");

     try
     {
      // create and open a connection object
      conn = new 
       SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
      conn.Open();

      // 1.  create a command object identifying
      //     the stored procedure
      SqlCommand cmd  = new SqlCommand(
       "CustOrderHist", conn);

      // 2. set the command object so it knows
      //    to execute a stored procedure
      cmd.CommandType = CommandType.StoredProcedure;

      // 3. add parameter to command, which
      //    will be passed to the stored procedure
      cmd.Parameters.Add(
       new SqlParameter("@CustomerID", custId));

      // execute the command
      rdr = cmd.ExecuteReader();

      // iterate through results, printing each to console
      while (rdr.Read())
      {
       Console.WriteLine(
        "Product: {0,-35} Total: {1,2}",
        rdr["ProductName"],
        rdr["Total"]);
      }
     }
     finally
     {
      if (conn != null)
      {
       conn.Close();
      }
      if (rdr != null)
      {
       rdr.Close();
      }
     } 
    }

Answer 3

using (SqlConnection conn = new SqlConnection(databaseConnectionString))
{
    using (SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = "StoredProcedureName";
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@ID", fileID);

        conn.Open();
        using (SqlDataReader rdr =
                   cmd.ExecuteReader(CommandBehavior.CloseConnection))
        {
            if (rdr.Read())
            {
                // process row from resultset;
            }
        }
    }
}

Answer 4

3 things no one else has shown you yet:

• "Stacking" using statements
• Setting an explicit parameter type rather than letting .Net try to pick one for you
• "var" keyword

.

string sql = "MyProcedureName";

using (var cn = new SqlConnection(databaseConnectionString))
using (var cmd = new SqlCommand(sql, cn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@ParameterName", SqlDbType.VarChar, 50)
        .Value = "MyParameterValue";

    conn.Open();
    using (SqlDataReader rdr =
               cmd.ExecuteReader(CommandBehavior.CloseConnection))
    {
        if (rdr.Read())
        {
            // process row from resultset;
        }
    }
}