Monday 23 September 2013

Simple method in C#.NET to Select data from SQL Server using SqlDataAdapter.Fill() method and SqlParameters.

Here i implemented a simple method to perform the Select operation in SQL Server. I am implementing this method by using SqlDataAdapter.Fill() method and SqlParameter's. This method is better is secure way also.

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

Method: 
public static DataTable PerformSQLSelectOperation(string sqlQuery, List<SqlParameter> sqlParameters)
    {
        DataTable dataTable = new DataTable();
        SqlConnection connection = new SqlConnection(sqlConnectionString);
        SqlCommand selectCommand = new SqlCommand(sqlQuery, connection);
        if (sqlParameters.Count > 0)
            selectCommand.Parameters.AddRange(sqlParameters.ToArray());
        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectCommand);
        sqlDataAdapter.SelectCommand = selectCommand;
        sqlDataAdapter.Fill(dataTable);
        return dataTable;
    }

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"));
DataTable noOfEffectedRows = PerformSQLSelectOperation(sqlQuery, sqlParams, sqlConnectionString);

No comments: