Tuesday, April 3, 2012

Simple Database Wrapper Class

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
///
/// Summary description for db
///
public class db
{
public db()
{
//
// TODO: Add constructor logic here
//
}
string _conn = ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString;
public DataSet GetDsBySpArr(String spName, string[] key, object[] value)
{
DbConnection connection = new SqlConnection(_conn);
SqlCommand cmd = (SqlCommand)connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spName.ToString();
DbParameter parm;
for (int i = 0; i < key.Length; i++)
{
// db.AddInParameter(dbCommand, (string)key.GetValue(i), GetDBType(value1.GetValue(i).GetType().Name), value1.GetValue(i));
parm = cmd.CreateParameter();
parm.ParameterName = (string)key.GetValue(i);
parm.Value = value.GetValue(i);
cmd.Parameters.Add(parm);
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet pubsDataSet = new DataSet("Pubs");
da.Fill(pubsDataSet);
connection.Close();
return pubsDataSet;
}
public DataSet GetDsBySp(String spName)
{
 
DbConnection connection = new SqlConnection(_conn);
SqlCommand cmd = (SqlCommand)connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spName.ToString();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet pubsDataSet = new DataSet("Pubs");
da.Fill(pubsDataSet, "publishers");
connection.Close();
return pubsDataSet;
}
public void ExecuteSp(String sp_name, string[] key, object[] value)
{
DbConnection connection = new SqlConnection(_conn);
connection.Open();
SqlCommand cmd = (SqlCommand)connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
if (sp_name != null) cmd.CommandText = sp_name;
DbParameter parm;
for (int i = 0; i <= key.Length - 1; i++)
{
parm = cmd.CreateParameter();
parm.ParameterName = (string)key.GetValue(i);
parm.Value = value.GetValue(i);
cmd.Parameters.Add(parm);
}
cmd.ExecuteNonQuery();
connection.Close();
}
public object ExecuteScaler(String spName, string[] key, object[] value)
{
DbConnection connection = new SqlConnection(_conn);
connection.Open();
SqlCommand cmd = (SqlCommand)connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
if (spName != null) cmd.CommandText = spName;
DbParameter parm;
for (int i = 0; i <= key.Length - 1; i++)
{
parm = cmd.CreateParameter();
parm.ParameterName = (string)key.GetValue(i);
parm.Value = value.GetValue(i);
cmd.Parameters.Add(parm);
}
object scalerresult = cmd.ExecuteScalar();
connection.Close();
return scalerresult;
}
 
 
 
}