Monday 23 September 2013

Simple method in C#.NET to Insert Update or Delete data in SQL Server using SqlCommand.ExecuteNonQuery() method and SqlParameters.

Here i implemented a simple method to perform the Insert Update of Delete actions in SQL Server. I am implementing this method by using SqlCommand.ExecuteNonQuery() method and SqlParameter's. This method is better is secure way also.

Namespaces:

using System.Data.SqlClient;
using System.Collections.Generic;

Method: 

public static int PerformSQLInsertOrUpdateOrDeleteOperation(string sqlQuery, List<SqlParameter> sqlParameters, string sqlConnectionString)
    {
        SqlConnection connection = new SqlConnection(sqlConnectionString);
        SqlCommand selectCommand = new SqlCommand(sqlQuery, connection);
        if (sqlParameters.Count > 0)
            selectCommand.Parameters.AddRange(sqlParameters.ToArray());
        using (connection)
        {
            connection.Open();
            int num = selectCommand.ExecuteNonQuery();
            connection.Close();
            return num;
        }
    }

How can we use this method?

string sqlConnectionString = "Data Source=ADMIN-PC;Initial Catalog=dvtech;User ID=sa;Password=sql@121";
string sqlQuery = "Select * from employee where Location=@Location";
List<SqlParameter> sqlParams = new List<SqlParameter>();
sqlParams.Add(new SqlParameter("@Location", "Hyderabad"));
int noOfEffectedRows = PerformSQLInsertOrUpdateOrDeleteOperation(sqlQuery, sqlParams, sqlConnectionString);

No comments: