Common Function to insert datas into Database

This resource is a common function which can be used while using 3-Tier Architecture to insert datas into database. This function can be used in common for all Insert StoredProcedures to pass values from c# code to database.

This is the common function for all Stored procedures to insert datas into Database from c# code.

public static int ExecuteNonQuery(string storedProcedureName,
params SqlParameter[] arrParam)
int retVal=0;
SqlParameter firstOutputParameter = null;

// Open the connection
using (SqlConnection cnn = new SqlConnection(
"Data Source=EnterYourDatasourceName;Initial Catalog=Enter your DataBaseName; Integrated Security=True"))

// Define the command
using (SqlCommand cmd= new SqlCommand())
cmd.Connection = cnn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcedureName;

// Handle the parameters
if (arrParam != null)
foreach (SqlParameter param in arrParam)
if (firstOutputParameter == null &&
param.Direction==ParameterDirection.Output &&
param.SqlDbType == SqlDbType.Int)
firstOutputParameter = param;

// Execute the stored procedure

// Return the first output parameter value
if (firstOutputParameter != null)
retVal = (int)firstOutputParameter.Value;
return retVal;

In this function we pass two paramaters.

2)Parameters for StoredProcedure

The function performs as follows:

First connects to the Sql connection
Then checks if the function has any Parameters for SP.
If the Parameters are not null then we add the parameters to the SQL Command.
Then the function executes the SQl Command using Execute.NonQuery() which returns the inserted row id as OutPut.
Then the function returns the inserted row id(integer value).

Hope this helps!!

Steadfast Technology


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: