Inserting Data rows with Parameters and Stored procedure in C# and MS SQL Server

In C# you can do your SQL things in many ways, one of the safest and fastest method in execute SQL commands from SQL server itself. Suppose you want to new row to Product table,

  1. First you need to setup SqlDataAdapter
  2. Create Parameter Objects
  3. Create SQL Procedure with Parameters
Lets do one by one


con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings
 ProductTableAdapter = new SqlDataAdapter("select * from productMaster", con);
We are now accessed the Product table with SqlDataAdapter and ready to create Parameter sets.

Setting up Parameters with values

ProductTableAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
 ProductTableAdapter.SelectCommand.CommandText = "CRUDS_PRODUCTMASTER";
 ProductTableAdapter.SelectCommand.Parameters.Add(new SqlParameter("@Item", cmb_item.Text.ToString().ToUpper()));
 ProductTableAdapter.SelectCommand.Parameters.Add(new SqlParameter("@mfr", cmb_mfr.Text.ToString().ToUpper())); Common.ProductTableAdapter.SelectCommand.ExecuteScalar();
with the SqlParameter  we can pass values to the SQL Procedure that we are going to create in next step. The commandType of stored procedure indicate the it is StoredProcedure. Now all we need is a Procedure.

Design your  SQL Stored Procedure

Open you SQL Server Explorer or SQL Server Itself and expand Procedure node and drop the following line of code.
 @item nchar(10),
 @mfr nchar(10),
 insert into productmaster  (item,mfr) values(@item,@mfr,);
The parameters in C# and SQL should match otherwise it will cause errors.

Published by


A developer,teacher and a blogger obsessed with Python,Dart and open source world from India

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.