Monday, 23 September 2013

Simple method in C#.NET to Select Single cell value from SQL Server using SqlCommand.ExecuteScalar() method and SqlParameters.

Here i implemented a simple method to Select single cell 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 string PerformSQLSelectSingleCellOperation(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();
        string str = Convert.ToString(selectCommand.ExecuteScalar());
        connection.Close();
        if (!string.IsNullOrEmpty(str))
            return str;
        else
            return string.Empty;
    }

How can we use this method?
string sqlConnectionString = "Data Source=ADMIN-PC;Initial Catalog=dvtech;User ID=sa;Password=sql@121";
string sqlQuery = "Select empname from employee where Location=@Location";
List<SqlParameter> sqlParams = new List<SqlParameter>();
sqlParams.Add(new SqlParameter("@Location", "Hyderabad"));
string result = PerformSQLSelectSingleCellOperation(sqlQuery, sqlParams, sqlConnectionString);

No comments: