Sql Helper Class in asp.net

Labels: Asp.net, Methods in .net
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.IO;
using System.Web.UI.WebControls;
using System.Web.UI;


///
/// Summary description for Dal
///
public class Dal
{

string ConnectionString=string.Empty;
static SqlConnection con;

public Dal()
{
ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
con = new SqlConnection(ConnectionString);
}

public void SetConnection()
{
if (ConnectionString == string.Empty)
{
ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
}
con = new SqlConnection(ConnectionString);
}

public DataSet ExecuteProcudere(string procName, Hashtable parms)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
if (con == null)
{
SetConnection();
}
cmd.Connection = con;

if (parms.Count > 0)
{
foreach (DictionaryEntry de in parms)
{
cmd.Parameters.AddWithValue(de.Key.ToString(), de.Value);
}

}
da.SelectCommand = cmd;

da.Fill(ds);
return ds;
}

public int ExecuteQuery(string procName, Hashtable parms)
{
SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
if (parms.Count > 0)
{
foreach (DictionaryEntry de in parms)
{
cmd.Parameters.AddWithValue(de.Key.ToString(), de.Value);
}

}
if (con == null)
{
SetConnection();
}
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
con.Open();

int result=cmd.ExecuteNonQuery();

return result;

}


public int ExecuteQuerywithOutputparams(SqlCommand cmd)
{
if (con == null)
{
SetConnection();
}
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
con.Open();

int result = cmd.ExecuteNonQuery();

return result;

}

public int ExecuteQueryWithOutParam(string procName, Hashtable parms)
{
SqlCommand cmd = new SqlCommand();
SqlParameter sqlparam = new SqlParameter();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
if (parms.Count > 0)
{
foreach (DictionaryEntry de in parms)
{
if (de.Key.ToString().Contains("_out"))
{
sqlparam = new SqlParameter(de.Key.ToString(),de.Value);
sqlparam.DbType = DbType.Int32;
sqlparam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(sqlparam);
}
else
{
cmd.Parameters.AddWithValue(de.Key.ToString(), de.Value);
}
}

}
if (con == null)
{
SetConnection();
}
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
con.Open();

int result = cmd.ExecuteNonQuery();

if (sqlparam != null)
result = Convert.ToInt32(sqlparam.SqlValue.ToString());
return result;
}
}