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"))
{
cnn.Open();
// 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)
{
cmd.Parameters.Add(param);
if (firstOutputParameter == null &&
param.Direction==ParameterDirection.Output &&
param.SqlDbType == SqlDbType.Int)
firstOutputParameter = param;
}
}
// Execute the stored procedure
cmd.ExecuteNonQuery();
// Return the first output parameter value
if (firstOutputParameter != null)
retVal = (int)firstOutputParameter.Value;
}
}
return retVal;
}
In this function we pass two paramaters.
1)StoredProcedureName
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!!
Regards,
S.Rajeswari
Steadfast Technology