Format of a stored procedure

Question

I searched a lot on Google but I couldn't find a format following which I can make a connection to SQL Server and get data from a stored procedure, which doesn't need any parameters, and getting data from two tables.

What I want is to get the data from the stored procedure and store in something that I can retrieve the data easily.

My stored procedure is,

ALTER PROCEDURE [dbo].[GetTablesData]
AS
   SELECT TableA.Column1, TableB.Column2
   FROM TableA TableA
   INNER JOIN TableB TableB 
   ON TableA.ID = TableB.ID AND TableA.ID2 = TableB .ID2

As I will get two columns, I need to check

If (Column1 < 7)
   Then Get Column2 and calculate a email address (by stripping off things from that column and get the data I want...) and send an email.

Now I don't know how I will call the stored procedure and do use it in IF else statement, I looked at old code, where they are using XSD (Dataset) but I didn't get the concept of that either online.

I can do all the IF/else statement code just dunno how to get data through stored procedure and where to store it, and how to retrieve it. Cheers — Muhammad Raja, Mar 19 '12 at 09:26
related: http://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-from-c-sharp-program — Filip De Vos, Mar 19 '12 at 09:27

NaveenBhat · Answer 1 · 2012-03-19T12:03:33.687

You can use ExecuteReader() method of SqlCommand like this:

SqlConnection sqlConnection1 = new SqlConnection("Your Connection String");
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;

cmd.CommandText = "GetTablesData"; //StoredProcedureName
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection1;

sqlConnection1.Open();

reader = cmd.ExecuteReader();

DataTable myData = new DataTable();
myData.Load(reader);

sqlConnection1.Close();

You can retrieve the values from DataTable by using its indexers. like,

foreach(var dr in myData)
{
    Debug.WriteLine("By Column Name:" + dr["ColumnName"]);
    Debug.WriteLine("By Column Index:" + dr[0]);
    Debug.WriteLine("------Next Row-------");
}

More specifically...

if(Convert.ToInt32(dr["Column1"]) < 7)
{
    //Do required operation
}

How i will be able to use If statement with this DataTable then ?, cheers — Muhammad Raja, Mar 19 '12 at 09:42

score 1 · Answer 2 · answered Mar 19 '12 at 09:52

There are a few ways you can get back data from stored procs I have included 3 different ways to do it, the example using Reader by Knvn is also good, I just didnt include it here because there is no need to duplicate his answer.

I know these are in VB and you are using C# but they are easy to convert and work much the same way.

This will bring your data back and put it into a datatable.

  Dim dt As New DataTable

  Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("Blueprint").ToString())

  Dim cmd As New SqlCommand
  cmd.CommandType = CommandType.StoredProcedure
  cmd.CommandText = "spNameHere"

  cmd.Connection = conn

  Using da As New SqlDataAdapter(cmd)
     conn.Open()
     da.Fill(dt)
     conn.Close()
  End Using

If you have a procedure that will only ever return one result you might want to execute it as a scalar and assign the result streight to a variable like so:

  Dim names As String = String.Empty

  Dim ds As New DataTable

  Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("Blueprint").ToString())

  Dim cmd As New SqlCommand
  cmd.CommandType = CommandType.StoredProcedure
  cmd.CommandText = "spNameHere"

  cmd.Connection = conn

  conn.Open()

  names = cmd.ExecuteScalar()

  conn.Close()

Then the 3rd example is if you want to execute some SQL that does not return anything. In this situation there is no need to use a reader etc you can just do ExecuteNonQuery()

  Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("Blueprint").ToString())

     Dim cmd As New SqlCommand

     cmd.CommandType = CommandType.StoredProcedure
     cmd.CommandText = "spNameHere"

     cmd.Connection = conn

     conn.Open()
     cmd.ExecuteNonQuery()
     conn.Close()

score 1 · Accepted Answer · answered Mar 19 '12 at 11:55

You can use ExecuteReader to retrieve a reader. The reader iterates over all rows in the result set. To retrieve the first row, call Read() once.

If Read() returns true, you can access the columns like read["column1"]. The type of the column can be SqlTypes.SqlInt32, which you can cast to int using as.

So you'd end up with something like:

using (var con = new SqlConnection("...connection string here..."))
{
    var com = con.CreateCommand();
    com.CommandText = "GetTablesData"; 
    com.CommandType = CommandType.StoredProcedure;
    con.Open():
    using (var read = cmd.ExecuteReader())
    {
        if (!read.Read())
            throw new Exception("No rows returned!")l

        if (read["Column1"] as int < 7)
        {
            var col2 = read["Column2"] as string;
            // Do stuff with col2
        }
    }
}

Format of a stored procedure

3 Answers3