Monday 23 September 2013

Simple method in C#.NET to Select Count from SQL Server using SqlCommand.ExecuteScalar() method and with SqlParameters.

Here i implemented a simple method to Select Count value from SQL Server. I am implementing this method by using SqlCommand.ExecuteScalar() 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 PerformSQLSelectCountOperation(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());
        connection.Open();
        int num = Convert.ToInt32(selectCommand.ExecuteScalar());
        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 count(*) from employee where Location=@Location";
List<SqlParameter> sqlParams = new List<SqlParameter>();
sqlParams.Add(new SqlParameter("@Location", "Hyderabad"));
int result = PerformSQLSelectCountOperation(sqlQuery, sqlParams, sqlConnectionString);

No comments: