Advanced Ado.net

Example of basic way of calling stored procedure

private void button1_Click(object sender, EventArgs e) {
  using (SqlConnection con = new SqlConnection(dc.Con)) {
    using (SqlCommand cmd = new SqlCommand("sp_Add_contact", con)) {
      cmd.CommandType = CommandType.StoredProcedure;

      cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text;
      cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text;

      con.Open();
      cmd.ExecuteNonQuery();
    }
  }
}

The Other way of calling sp
using (SqlConnection con = new SqlConnection(dc.Con))
            {
                using (SqlCommand cmd = new SqlCommand("SP_ADD", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FirstName", txtfirstname);
                    cmd.Parameters.AddWithValue("@LastName", txtlastname);
                    con.Open();
                    cmd.ExecuteNonQuery();
                }

            }


Advanced Program to call OutPut Parameter value from sp
using( SqlConnection cn = new SqlConnection("server=(local);Database=Northwind;user id=sa;password=;")) 
    { 
        SqlCommand cmd = new SqlCommand("CustOrderOne", cn); 
        cmd.CommandType=CommandType.StoredProcedure ; 

        SqlParameter parm= new SqlParameter("@CustomerID",SqlDbType.NChar) ; 
        parm.Value="ALFKI"; 
        parm.Direction =ParameterDirection.Input ; 
        cmd.Parameters.Add(parm); 

        SqlParameter parm2= new SqlParameter("@ProductName",SqlDbType.VarChar); 
        parm2.Size=50; 
        parm2.Direction=ParameterDirection.Output; 
        cmd.Parameters.Add(parm2); 

        SqlParameter parm3=new SqlParameter("@Quantity",SqlDbType.Int); 
        parm3.Direction=ParameterDirection.Output; 
        cmd.Parameters.Add(parm3);

        cn.Open(); 
        cmd.ExecuteNonQuery(); 
        cn.Close(); 

        Console.WriteLine(cmd.Parameters["@ProductName"].Value); 
        Console.WriteLine(cmd.Parameters["@Quantity"].Value.ToString());
        Console.ReadLine(); 
    } 

Sp with Output Parameter
Create  PROCEDURE GetstudentnameInOutputVariable
(

@studentid INT,                       
@studentname VARCHAR(200)  OUT    
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid
END

Getting Value from the above sp using the following Query
Declare @Studentname as nvarchar(200)   
Declare @Studentemail as nvarchar(50)    
Execute GetstudentnameInOutputVariable 1 , @Studentname output
select @Studentname
Advertisements

About kakani santosh kumar

Working as Software Engineer in Prithvi Information Solutions having 3 years of Experience in Dotnet Framework ranging from 1.1 to 3.5
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s