Reader Level:

Inserting records in a database using stored procedure and ADO.NET technology.

By Puran Mehra on Aug 13, 2009
In this article I will explain you how to use CommandType stored procedure to insert records in a database in a disconnected manner.

Command objects use parameters to pass values to stored procedures, which facilitates type checking and validation.


Begin your application by launching VS.NET and creating a new project using File - > Project and choose C# Windows Application template as shown in figure below:


Add a new item application configuration file as shown below from Project -> Add New Item


The code for the application configuration file:


<?xml version="1.0" encoding="utf-8" ?>



    <add name="constr" connectionString="initial catalog=puran; data source=MCN002; integrated security=sspi"/>




In the form design and button, textboxes and labels as follow:


Add the necessary reference file from the solution explorer, Reference as below:


Code using stored procedure for adding records in a database through textbox using parameterized query in disconnected model.


using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

using System.Data.Common;

using System.Configuration;


namespace insert_record_SP


    public partial class Form1 : Form


        public Form1()





        SqlConnection con;

        SqlCommand cmd;


        private void button1_Click(object sender, EventArgs e)


            con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);

            cmd = new SqlCommand();


            cmd.Connection = con;


            cmd.Parameters.Add(new SqlParameter("@RollNo", SqlDbType.Int));

            cmd.Parameters["@RollNo"].Value = textBox1.Text;


            cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar));

            cmd.Parameters["@Name"].Value = textBox2.Text;


            cmd.Parameters.Add(new SqlParameter("@Fees", SqlDbType.Float));

            cmd.Parameters["@Fees"].Value = textBox3.Text;


            cmd.CommandType = CommandType.StoredProcedure;


            cmd.CommandText = "insertData";





            MessageBox.Show("Record inserted");





There are three types of command in .NET 

  1. Text
  2. Table Direct
  3. Stored Procedure





As SQL text command (Default)




cmd.CommandType = CommandType.TableDirect


The name of a table. Works with OleDb only.






The name of a stored procedure


Note: You can read my previous article:


to get better understanding of this article as it using parameters commands, DbParameter Object and parameters data types which I have explained in my previous article.


Why I have used stored procedure as CommandType?


Every time you write a query it is parsed in database. If you have written a stored procedure for it, it will be parsed once and can be executed N number of times.

Stored procedures can also improve performance. All the conditional logic and is written into a stored procedure which is a single execution block on the database server.


Stored procedures provide: 

  • Modular programming i.e. stored procedures are precompiled code.
  • Performance in terms of faster execution and reduced network traffic.
  • Security. As commands are not directly executed. They are executed as stored procedure. 



Hope the article would have helped you in understanding stored procedure as CommandType.


Your feedback and constructive contributions are welcome.  Please feel free to contact me for feedback or comments you may have about this article.

Puran Mehra
Puran Mehra

Working as a Software professional.