using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace UserDocMapping
{
public class DAL
{
string ConString = ConfigurationSettings.AppSettings["DBCon"].ToString();
#region return Dataset Methods With Params
public DataSet DSWithParams(string SPName, SqlParameter[] param)
{
SqlConnection con = new SqlConnection(ConString);
SqlCommand cmd = new SqlCommand(SPName, con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
con.Open();
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
SqlDataAdapter ad = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
ad.Fill(ds);
return ds;
}
catch (Exception Ex)
{
throw;
}
finally
{
cmd.Dispose();
con.Close();
con.Dispose();
}
}
#endregion
#region return Dataset Methods Without Params
public DataSet DSWithOutParams(string SPName)
{
SqlConnection con = new SqlConnection(ConString);
SqlCommand cmd = new SqlCommand(SPName, con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
con.Open();
SqlDataAdapter ad = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
ad.Fill(ds);
return ds;
}
catch (Exception Ex)
{
throw;
}
finally
{
cmd.Dispose();
con.Close();
con.Dispose();
}
}
#endregion
#region return DataTable Methods With Params
public DataTable DTWithParams(string SPName, SqlParameter[] param)
{
DataSet dsLogin = new DataSet();
SqlConnection con = new SqlConnection(ConString);
SqlCommand cmd = new SqlCommand(SPName, con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
con.Open();
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
SqlDataAdapter ad = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
ad.Fill(ds);
return ds.Tables[0];
}
catch (Exception Ex)
{
throw;
}
finally
{
cmd.Dispose();
con.Close();
con.Dispose();
}
}
#endregion
#region return DataTable Methods Without Params
public DataTable DTWithOutParams(string SPName)
{
SqlConnection con = new SqlConnection(ConString);
SqlCommand cmd = new SqlCommand(SPName, con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
con.Open();
SqlDataAdapter ad = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
ad.Fill(ds);
return ds.Tables[0];
}
catch (Exception Ex)
{
throw;
}
finally
{
cmd.Dispose();
con.Close();
con.Dispose();
}
}
#endregion
#region ExecuteNonQuery
public void ExecuteNonQuery(string SPName, SqlParameter[] param)
{
SqlConnection con = new SqlConnection(ConString);
SqlCommand cmd = new SqlCommand(SPName, con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
con.Open();
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
cmd.ExecuteNonQuery();
}
catch (Exception Ex)
{
throw;
}
finally
{
cmd.Dispose();
con.Close();
con.Dispose();
}
}
#endregion
#region ExecuteScalar
public object ExecuteScalar(string SPName, SqlParameter[] param)
{
SqlConnection con = new SqlConnection(ConString);
SqlCommand cmd = new SqlCommand(SPName, con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
con.Open();
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
object val = cmd.ExecuteScalar();
return val;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
con.Close();
con.Dispose();
}
}
#endregion
}
}
