Bind Results in C# using SQL prepared statements

Question

Using this:

SqlConnection myConnection = new SqlConnection("Data Source=.\\SERVER;Initial Catalog=DB;Integrated Security=True;TrustServerCertificate=True;User Instance=False");
myConnection.Open();

SqlCommand myCommand = new SqlCommand("SELECT BusinessName FROM Businessess WHERE BusinessID = @Param2", myConnection);

SqlParameter myParam2 = new SqlParameter("@Param2", SqlDbType.Int, 4);
myParam2.Value = 1;
myCommand.Parameters.Add(myParam2);

MessageBox.Show(myCommand); //How do I bind results to show as string?

How do I bind the results of a prepared statement to a variable so that I may manipulate them?

Why don't you use Stored Procedure? – Thit Lwin Oo Feb 11 '12 at 15:19 — Thit Lwin Oo, Feb 11 '12 at 15:19

Darin Dimitrov · Accepted Answer · 2012-02-11T15:33:09.713

6

Try like this:

using (SqlConnection myConnection = new SqlConnection("Data Source=.\\SERVER;Initial Catalog=DB;Integrated Security=True;TrustServerCertificate=True;User Instance=False"))
using (SqlCommand myCommand = myConnection.CreateCommand())
{
    myConnection.Open();
    myCommand.CommandText = "SELECT BusinessName FROM Businessess WHERE BusinessID = @Param2";
    myCommand.Parameters.AddWithValue("@Param2", myParam2);
    using (SqlDataReader reader = myCommand.ExecuteReader())
    {
        if (reader.Read())
        {
            string businessName = reader.GetString(reader.GetOrdinal("BusinessName"));
            MessageBox.Show(businessName);
        }
        else
        {
            MessageBox.Show(string.Format("Sorry, no business found with id = {0}", myParam2));
        }
    }
}

Things to notice:

disposable resources are wrapped in using statements to ensure proper disposal even in case of exceptions
simplification of the parameter passed to the sql command
call the ExecuteReader on the command in order to retrieve an object allowing you to read the returned resultset.

edited Feb 11 '12 at 15:33

answered Feb 11 '12 at 15:19

Darin Dimitrov

1,023,142
271
3,287
2,928

not a fan of .AddWithValue(), but otherwise nice work suggesting using blocks, .CreateCommand(), and of course actually calling an Execute__() function. – Joel Coehoorn Feb 11 '12 at 15:23
How do I use DbDataReader reader? Is there a namespace I need to include to use that? – Dan Kanze Feb 11 '12 at 15:28
@DanKanze, of course, it's the `System.Data.Common` namespace. – Darin Dimitrov Feb 11 '12 at 15:29
I included it, but I keep getting "No mapping exists from object type System.Data.SqlClient.SqlParameter to a known managed provider native type." hung up on using (DbDataReader reader = myCommand.ExecuteReader()) – Dan Kanze Feb 11 '12 at 15:31
1

@DanKanze, try using a `SqlDataReader` instead of a `DbDataReader`. I have updated my answer. – Darin Dimitrov Feb 11 '12 at 15:33
@DarinDimitrov You are a golden god. – Dan Kanze Feb 11 '12 at 15:38

Bind Results in C# using SQL prepared statements

1 Answers1

Linked