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
}
}
}
