Posted By:
Test
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
/// <summary>
/// Summary description for DBAccess
/// </summary>
public class DBAccess
{
private SqlConnection dbcon;
public static string connectionString
{
get { return ConfigurationManager.ConnectionStrings["PSCPConStr"].ConnectionString; }
}
public DBAccess()
{
this.dbcon = new SqlConnection(connectionString);
}
public DataSet GetDataSet(string sql, SqlParameter[] parameters)
{
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand(sql, this.dbcon);
foreach (SqlParameter p in parameters)
{
cmd.Parameters.Add(p);
}
adapter.SelectCommand = cmd;
adapter.Fill(dataset);
return dataset;
}
public DataSet GetDataSet(string sql)
{
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand(sql, this.dbcon);
adapter.SelectCommand = cmd;
adapter.Fill(dataset);
return dataset;
}
public int ExecuteCommand(string sql, SqlParameter[] parameters)
{
SqlCommand cmd = new SqlCommand(sql, this.dbcon);
foreach (SqlParameter p in parameters)
{
cmd.Parameters.Add(p);
}
cmd.Connection.Open();
int ret = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return ret;
}
public int ExecuteCommand(string sql)
{
SqlCommand cmd = new SqlCommand(sql, this.dbcon);
cmd.Connection.Open();
int ret = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return ret;
}
public object GetScalar(string sql, SqlParameter[] parameters)
{
try
{
DataSet ds = this.GetDataSet(sql, parameters);
return ds.Tables[0].Rows[0][0];
}
catch
{
return null;
}
}
public object GetScalar(string sql, SqlParameter[] parameters, string columnName)
{
try
{
DataSet ds = this.GetDataSet(sql, parameters);
return ds.Tables[0].Rows[0][columnName];
}
catch
{
return null;
}
}
public object GetScalar(string sql, string columnName)
{
try
{
DataSet ds = this.GetDataSet(sql);
return ds.Tables[0].Rows[0][columnName];
}
catch
{
return null;
}
}
public object GetScalar(string sql)
{
try
{
DataSet ds = this.GetDataSet(sql);
return ds.Tables[0].Rows[0][0];
}
catch
{
return null;
}
}
public object GetFromStoredProc(string storeProcName, SqlParameter[] collection, string output)
{
object toRet;
SqlCommand cmd = new SqlCommand(storeProcName, this.dbcon);
cmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter param in collection)
{
cmd.Parameters.Add(param);
}
cmd.Parameters[output].Direction = ParameterDirection.Output;
this.dbcon.Open();
cmd.ExecuteNonQuery();
toRet = cmd.Parameters[output].Value;
this.dbcon.Close();
return toRet;
}
public void RunStoredProc(string storeProcName, SqlParameter[] collection)
{
SqlCommand cmd = new SqlCommand(storeProcName, this.dbcon);
cmd.CommandTimeout = 40000;
cmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter param in collection)
{
cmd.Parameters.Add(param);
}
this.dbcon.Open();
cmd.ExecuteNonQuery();
this.dbcon.Close();
}
public DataRow GetDataRow(string sql)
{
return this.GetDataSet(sql).Tables[0].Rows[0];
}
public DataRow GetDataRow(string sql, SqlParameter[] parameters)
{
return this.GetDataSet(sql, parameters).Tables[0].Rows[0];
}
}
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
/// <summary>
/// Summary description for DBAccess
/// </summary>
public class DBAccess
{
private SqlConnection dbcon;
public static string connectionString
{
get { return ConfigurationManager.ConnectionStrings["PSCPConStr"].ConnectionString; }
}
public DBAccess()
{
this.dbcon = new SqlConnection(connectionString);
}
public DataSet GetDataSet(string sql, SqlParameter[] parameters)
{
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand(sql, this.dbcon);
foreach (SqlParameter p in parameters)
{
cmd.Parameters.Add(p);
}
adapter.SelectCommand = cmd;
adapter.Fill(dataset);
return dataset;
}
public DataSet GetDataSet(string sql)
{
DataSet dataset = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand(sql, this.dbcon);
adapter.SelectCommand = cmd;
adapter.Fill(dataset);
return dataset;
}
public int ExecuteCommand(string sql, SqlParameter[] parameters)
{
SqlCommand cmd = new SqlCommand(sql, this.dbcon);
foreach (SqlParameter p in parameters)
{
cmd.Parameters.Add(p);
}
cmd.Connection.Open();
int ret = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return ret;
}
public int ExecuteCommand(string sql)
{
SqlCommand cmd = new SqlCommand(sql, this.dbcon);
cmd.Connection.Open();
int ret = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return ret;
}
public object GetScalar(string sql, SqlParameter[] parameters)
{
try
{
DataSet ds = this.GetDataSet(sql, parameters);
return ds.Tables[0].Rows[0][0];
}
catch
{
return null;
}
}
public object GetScalar(string sql, SqlParameter[] parameters, string columnName)
{
try
{
DataSet ds = this.GetDataSet(sql, parameters);
return ds.Tables[0].Rows[0][columnName];
}
catch
{
return null;
}
}
public object GetScalar(string sql, string columnName)
{
try
{
DataSet ds = this.GetDataSet(sql);
return ds.Tables[0].Rows[0][columnName];
}
catch
{
return null;
}
}
public object GetScalar(string sql)
{
try
{
DataSet ds = this.GetDataSet(sql);
return ds.Tables[0].Rows[0][0];
}
catch
{
return null;
}
}
public object GetFromStoredProc(string storeProcName, SqlParameter[] collection, string output)
{
object toRet;
SqlCommand cmd = new SqlCommand(storeProcName, this.dbcon);
cmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter param in collection)
{
cmd.Parameters.Add(param);
}
cmd.Parameters[output].Direction = ParameterDirection.Output;
this.dbcon.Open();
cmd.ExecuteNonQuery();
toRet = cmd.Parameters[output].Value;
this.dbcon.Close();
return toRet;
}
public void RunStoredProc(string storeProcName, SqlParameter[] collection)
{
SqlCommand cmd = new SqlCommand(storeProcName, this.dbcon);
cmd.CommandTimeout = 40000;
cmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter param in collection)
{
cmd.Parameters.Add(param);
}
this.dbcon.Open();
cmd.ExecuteNonQuery();
this.dbcon.Close();
}
public DataRow GetDataRow(string sql)
{
return this.GetDataSet(sql).Tables[0].Rows[0];
}
public DataRow GetDataRow(string sql, SqlParameter[] parameters)
{
return this.GetDataSet(sql, parameters).Tables[0].Rows[0];
}
}
