Calling Stored Procedure from C# & Display the records

Let's create a sample stored procedure first,

Create procedure GetSchoolName (@ID int)

as

select SchoolName, Date from School where ID like @ID;

return;

The code includes @ID parameter which is an input parameter that obtains the search string to perform a "like" search in school table.

In our c# code, we are going to pass variable ID (at run time) to this procedure and getting back the records that matches with that ID

Hence, the c# code:

try

{

SqlDataReader rdr = null;

//Create a connection to the SQL Server

SqlConnection conn = new SqlConnection(DataAccess.GetConnectionString());

//Create a command object & then set to the connection

SqlCommand cmd = new SqlCommand("dbo. GetSchoolName", conn);

//Set the command type as storedProcedure

cmd.CommandType = CommandType.StoredProcedure;

//get the variable ID from textbox control

int id = Convert.ToInt32(id.Text);

//Create & add a parameter to parameters collection for stored procedure

cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;

//Open the connection

conn.Open();

//execute command & read the data using SqlDataReader

rdr = cmd.ExecuteReader();

//display records into listbox

while (rdr.Read())

{

listBox1.Items.Add(rdr.GetValue(0).ToString());

}

Or

//Records display in a table

TableRow tr;

TableCell tc;

while (rdr.Read())

{

tr = new TableRow();

tc = new TableCell();

tc.Text = rdr["SchoolName"].ToString() + " " + rdr["Date"].ToString();

tr.Cells.Add(tc);

table1.Rows.Add(tr);

}

conn.Close(); //close connection

rdr.Close(); // close SqlDataReader

}

Happy Coding !!!