Popular Posts

There was an error in this gadget

Jul 25, 2013

Select data using Stored Procedures with Parameters




StoreProcedure to select data:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetEmployeeDetailsByID]
      @EmployeeID int = 0
AS
BEGIN
      SET NOCOUNT ON;
      SELECT FirstName, LastName, BirthDate, City, Country
      FROM Employees WHERE EmployeeID=@EmployeeID
END



 Code Behind:
String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetEmployeeDetailsByID";
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = txtID.Text.Trim();       
cmd.Connection = con;
try
{
    con.Open();
    GridView1.EmptyDataText = "No Records Found";
    GridView1.DataSource = cmd.ExecuteReader();
    GridView1.DataBind();
}
catch (Exception ex)
{
    throw ex;
}
finally
{
    con.Close();
    con.Dispose();
}

 you can also make some changes:

 using (SqlDataReader dr = cmd.ExecuteReader())
            {
                if (dr.Read())
                {
                    Label1.Text = dr["FirstName"].ToString();
                    Label2.Text = dr["LastName"].ToString();
                    Label3.Text = dr[3].ToString();
                    Label4.Text = dr["Email"].ToString();
                }
            }


You can also try:

SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow);



No comments:

Post a Comment