SIMPLE : How to insert and retrieve data in Microsoft SQL Server database using ASP.net

This is a very basic and easy way to insert the data in database, this method suggest a proper way to insert the data in sql database.

Create the store procedure as follows:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[CD_Insert_CDdata]
(
     @CD_Number nvarchar(50)= null
    ,@CD_Name nvarchar(300)= null

    ,@CD_Descp nvarchar(max)= null
    ,@mode nvarchar (5)= '1'
    ,@CDID nvarchar(50)=null OUTPUT    -- imp note : output keyword is needed to make the parameter act like output (it can act as output as well as input too)
)

 AS
BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

        IF (@mode ='1')

            BEGIN TRY
               BEGIN TRANSACTION    -- Start the transaction

                    -- insert Data In Cd table
                       INSERT INTO CD (CD_Number,CD_Name, CD_Descp)
                                 VALUES (@CD_Number,@CD_Name,@CD_Descp)                  
                       
                   SET @CDID = Scope_Identity()  -- get the last inserted record ID (primary key)

                    --SET @CDID = (Select Scope_Identity() as CDID)   -- (one more way)

               select 'success'  -- to send success as return value

               -- If we reach here, success!
               COMMIT

            END TRY

            BEGIN CATCH

              -- OMG, there was an Error/Exception….

              IF @@TRANSCOUNT > 0

                 ROLLBACK  --rollback the insert query above

              -- Raise an Exception /error with the details of the exception

              DECLARE @ErrorMsg nvarchar(4000), @ErrorSeverity int

              SELECT @ErrorMsg = ERROR_MESSAGE(),
                     @ErrorSeverity = ERROR_SEVERITY()

              RAISERROR(@ErrorMsg, @ErrorSeverity, 1)

                --SET @CDID = @ErrorMsg + ' failed'  -- send the error message by parameter

                    select 'failure'   -- send a Failure response in return string

            END CATCH   

        End

Create a Class file in Asp.net project

Create and initialize the variables as the data needs to be instered in database.Initialize the SQL Objects to insert the data, create a function (or get /set method) to assign the data to variables. Assign the data to variables. Then create a function to insert the data named as “InsertCDData()”

public string InsertCDData()
    {   
        string strResult = null;   // used to send success or  failure values are return
        string strCDID = null;   // used to get the record number of inserted record!!!!

//initialize the Sqlconnection object
        using (mSqlCon = new SqlConnection(this.strConnection))
        {

            mSqlCon.Open();      // open the connection

           //initialize the SQLCommand object
            using (mSqlcmd = new SqlCommand())
            {

              mSqlcmd.Connection = mSqlCon;
              mSqlcmd.CommandText = "CD_Insert_CDdata";   //store procedure name
              mSqlcmd.CommandType = CommandType.StoredProcedure;

              mSqlcmd.Parameters.Add("@CD_Number", SqlDbType.NVarChar, 10).Value = this._CD_Number;

              mSqlcmd.Parameters.Add("@CD_Name", SqlDbType.NVarChar, 10).Value = this._CD_Name;

              mSqlcmd.Parameters.Add("@CD_Descp", SqlDbType.NVarChar, 10).Value = this._CDDescption;

              mSqlcmd.Parameters.Add("@mode", SqlDbType.NVarChar, 10).Value = "1";

              System.Data.SqlClient.SqlParameter CDID_param = mSqlcmd.Parameters.Add("@CDID", SqlDbType.NVarChar, 50);   // this parameter is  used to get the record number inserted

              CDID_param.Direction = ParameterDirection.Output;  

 
              //CDID_param.Direction = ParameterDirection.InputOutput;

                try
                {
                    mSqlDr = mSqlcmd.ExecuteReader();  // execute the store procedure

                    string strOutput = "";

                    while (mSqlDr.Read())
                    {
                        strOutput = Convert.ToString(mSqlDr[0]);  //to get the result generated from store procedure
                    }

                    mSqlDr.Close();

                    // to get The record ID just inserted
                    strCDID =  Convert.ToString(CDID_param.Value);

                    // OR this way  to ---  get The record ID just inserted
                   // strCDID = Convert.ToString(mSqlcmd.Parameters["@CDID"].Value);

                    if (strOutput.Contains("success"))
                    { // result is successful

                        strResult = "CD data Inserted Successfully!!!!!" + " Record No : " + strCDID;

                    }
                    else
                    {
                        if (strOutput == string.Empty)
                        {   

                            // result is Failure
                            strResult = "CD data Inserted Failure!!!!! ";
                        }
                        else
                        {    

                            // result is send out
                            strResult = strOutput + " Record No : " + strCDID + " Record Inserted !!";
                        }
                    }
                }
                catch (Exception ex)
                {  

                       // save the Exceptions created in Class files in database
                    StoreExceptions(ex);
                }
                finally
                {

                           // close all connection and null the objects created to release memory
                    mSqlCon.Close();

                    if (mSqlCon != null) //If con object is not null then Free memory occupied by msqlcon by Dispose it
                        mSqlCon.Dispose();
                    if (mSqlcmd != null)
                        mSqlcmd.Dispose();
                    if (mSqlDa != null)
                        mSqlDa.Dispose();

                    mSqlCon = null;   //free memory occupied by MsqlCon and msqlCmd and mSqlDa
                    mSqlcmd = null;
                    mSqlDa = null;

                }

                return strResult;  // return the result

            }
        }
    }