Posted in C#.Net

DataAccessLayer.cs Class in .Net For SQL Server.

This C# class contains the three methods which are useful to perform DML(Insert,Update and Delete) and Select operations against Sql Server database.
Methods:- 1)GetConnectionString, 2)ExecuteNonQuery and 3)ExecuteDataSet.
1) GetConnectionString:  This method reads the connection string from web.config file and returns it.
  Connection String Tag in web.config file.
    <connectionStrings>
      <  add name=” ConnectionString”
                          connectionString=”User Id=sa; Password=123;
                           Database=DatabaseName; Data Source=ServerName; “/>
    </connectionStrings>

2) ExecuteNonQuery: 

      It takes four arguments such as connectionString(connection string of sql server database), commandType(command type nothing but whether it is command text or stored procedure), commandText(if command type is command text then pass query or if it is stored procedure then pass name of stored procedureand parameters(if command type is stored procedure then pass params of that stored procedure as a array).

It returns int value i.e., 1 if query or stored procedure executed successfully other wise it returns 0 value.

It is useful for insert, update, delete operations.

3) ExecuteDataSet:

This method also takes same four arguments such as connectionString(connection string of sql server database), commandType(command type nothing but whether it is command text or stored procedure), commandText(if command type is command text then pass query or if it is stored procedure then pass name of stored procedureand parameters(if command type is stored procedure then pass params of that stored procedure as a array).It returns dataset. It is useful for select operation.DataAccessLayer.csusing System;

using System.Data;
using System.Configuration;
using System.Data.SqlClient;
public class DataAccessLayer
{
    public static string GetConnectionString()
    {
       return  ConfigurationManager.ConnectionStrings[“ConnectionString”].
                                               ConnectionString.ToString();
    }
    static SqlConnection con;
    public static int ExecuteNonQuery
        (string connectionString, CommandType commandType, string commandText,
                                                    SqlParameter[] parameters)
    {
        try
        {
            con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(commandText, con);
            cmd.CommandType = commandType;
            foreach (SqlParameter p in parameters)
            { cmd.Parameters.Add(p); }
            con.Open();
            return cmd.ExecuteNonQuery();
        }
        catch (SqlException ex)
        { throw new ArgumentException(ex.Message); }
        finally { con.Close(); }
    }
    public static DataSet ExecuteDataSet
        (string connectionString, CommandType commandType, string commandText,
                                                    SqlParameter[] parameters)
    {
        try
        {
            SqlConnection sqlcon = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(commandText, sqlcon);
            cmd.CommandType = commandType;
            foreach (SqlParameter p in parameters)
            { cmd.Parameters.Add(p); }
            DataSet ds;
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                ds = new DataSet();
                da.Fill(ds);
            }
            return ds;
        }
        catch (SqlException ex)
        { throw new ArgumentException(ex.Message); }
    }
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s