call store procedure and action for insert and get data

here i create call for store procedure and action for insert and get data,check the code

create web confing connection string for db

add key="ConnectionString" value="Data Source=###;Initial Catalog=db;User ID=sa;Password=#pwd#"

Create class for sql operations

using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;
using System.Data.Common;

public class SqlHelper
    {
        private static string SetConnection()
        {
            return Convert.ToString(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]);
        }

        public static int ExecuteNonQuery( CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            using (SqlConnection connection = new SqlConnection(SetConnection()))
            {
               
                //create a command and prepare it for execution
                SqlCommand cmd = new SqlCommand();
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }

                //associate the connection with the command
                cmd.Connection = connection;

                cmd.CommandText = commandText;

                //set the command type
                cmd.CommandType = commandType;
                //attach the command parameters if they are provided
                if (commandParameters != null)
                {
                    foreach (SqlParameter p in commandParameters)
                    {
                        if (p != null)
                        {
                            if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
                            {
                                p.Value = DBNull.Value;
                            }
                            cmd.Parameters.Add(p);
                        }
                    }
                }

                //finally, execute the command.
                int retval = cmd.ExecuteNonQuery();

                // detach the SqlParameters from the command object, so they can be used again.
                cmd.Parameters.Clear();
                return retval;
            }
        }


        public static DataTable ExecuteDataTable(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            //create & open a SqlConnection, and dispose of it after we are done.
            using (SqlConnection cn = new SqlConnection(SetConnection()))
            {
                cn.Open();
                //call the overload that takes a connection in place of the connection string
                return ExecuteDataset(cn, commandType, commandText, commandParameters).Tables[0];
            }
        }

        public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            //create a command and prepare it for execution
            SqlCommand cmd = new SqlCommand();

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            //associate the connection with the command
            cmd.Connection = connection;

            cmd.CommandText = commandText;

            //set the command type
            cmd.CommandType = commandType;
            //attach the command parameters if they are provided
            if (commandParameters != null)
            {
                foreach (SqlParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        cmd.Parameters.Add(p);
                    }
                }
            }

           

            //create the DataAdapter & DataSet
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            cmd.CommandTimeout = 400;
           
            //fill the DataSet using default values for DataTable names, etc.
            da.Fill(ds);

            // detach the SqlParameters from the command object, so they can be used again.           
            cmd.Parameters.Clear();

            //return the dataset
            return ds;
        }
    }


Create code for call sql store procedure

using System.Data.SqlClient;
using System.Data;

public void get_data()
    {
        SqlParameter[] Params;
        int id=1;
        int index = 0;
        Params = new SqlParameter[2];
        Params[index] = new SqlParameter("@iD", id);

        DataTable dt = new DataTable();
        dt=SqlHelper.ExecuteDataTable(CommandType.StoredProcedure, "user_GetByPK", Params);
    }


    public void insert()
    {
        SqlParameter[] Params;

        int index = 0;
        Params = new SqlParameter[2];
        int id = 0;
        string email="email@email.com";
        string name="name";

        Params[index] = new SqlParameter("@iD", id);
        Params[index].Direction = ParameterDirection.Output;
        index++;
        Params[index] = new SqlParameter("@email", email);
        index++;
        Params[index] = new SqlParameter("@name", name);
        index++;

        SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "Insert_user", Params);

        if (!Convert.IsDBNull(Params[0]))
            Response.Write( Convert.ToInt64(Params[0].Value));
        else
            Response.Write(0);
    }