public DataSet GetDataSetByQuery(string query)
{
DataSet retVal = new DataSet();
SqlConnection conn = null;
SqlCommand cmd;
SqlDataAdapter da;
try
{
conn = SqlUtil.CreateConnection(ConnectionString);
cmd = SqlUtil.CreateCommand(conn);
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
da = new SqlDataAdapter(cmd);
SqlUtil.StartTimer();
da.Fill(retVal);
SqlUtil.StopTimer(cmd);
return(retVal);
}
finally
{
SqlUtil.ReleaseConnection(conn);
}
}
public DataSet GetDataSetWithPaging(string procName, int recNumToStart, int recNumToShow, params object[] allParams)
{
DataSet retVal = new DataSet();
SqlConnection conn = null;
SqlCommand cmd;
SqlDataAdapter da;
DataTable dt = new DataTable();
try
{
conn = SqlUtil.CreateConnection(ConnectionString);
cmd = SqlUtil.CreateCommand(conn);
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
if (allParams != null)
{
foreach (object obj in allParams)
{
if (obj is IList)
{
foreach (DBParameter db in (IList)obj)
{
AddDBParam(cmd, (DBParameter)db);
}
}
else
{
AddDBParam(cmd, (DBParameter)obj);
}
}
}
da = new SqlDataAdapter(cmd);
//da.Fill(retVal);
SqlUtil.StartTimer();
da.Fill(recNumToStart, recNumToShow, dt);
SqlUtil.StopTimer(cmd);
retVal.Tables.Add(dt);
if (ParameterNames.Count != 0)
{
foreach (string s in ParameterNames)
{
/****set output parameter*****/
AddOutputValue(s, (int)cmd.Parameters[s].Value);
}
}
return(retVal);
}
finally
{
SqlUtil.ReleaseConnection(conn);
}
}
public DataSet GetDataSet(string procName, params object[] allParams)
{
DataSet retVal = new DataSet();
SqlConnection conn = null;
SqlCommand cmd;
SqlDataAdapter da;
try
{
conn = SqlUtil.CreateConnection(ConnectionString);
cmd = SqlUtil.CreateCommand(conn);
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
if (allParams != null)
{
foreach (object obj in allParams)
{
if (obj is IList)
{
foreach (DBParameter db in (IList)obj)
{
AddDBParam(cmd, (DBParameter)db);
}
}
else
{
AddDBParam(cmd, (DBParameter)obj);
}
}
}
da = new SqlDataAdapter(cmd);
SqlUtil.StartTimer();
da.Fill(retVal);
SqlUtil.StopTimer(cmd);
#region Output parameters
SetOutputParameter(cmd);
#endregion
return(retVal);
}
finally
{
SqlUtil.ReleaseConnection(conn);
}
}
public int GetNoOfRowsAffected(string procName, params object[] allParams)
{
SqlConnection conn = null;
SqlCommand cmd;
int rtn;
try
{
conn = SqlUtil.CreateConnection(ConnectionString);
cmd = SqlUtil.CreateCommand(conn);
cmd.CommandText = procName.Trim();
cmd.CommandType = CommandType.StoredProcedure;
if (allParams != null)
{
foreach (object obj in allParams)
{
if (obj is IList)
{
foreach (DBParameter db in (IList)obj)
{
AddDBParam(cmd, (DBParameter)db);
}
}
else
{
AddDBParam(cmd, (DBParameter)obj);
}
}
}
rtn = SqlUtil.GetNoOfRowsAffected(cmd, ConnectionString);
#region Output parameters
SetOutputParameter(cmd);
#endregion
}
catch (Exception ex)
{
throw ex;
}
finally
{
SqlUtil.ReleaseConnection(conn);
}
return(rtn);
}
public DataSet GetDataSetByQuery(string query, params object[] allParams)
{
DataSet retVal = new DataSet();
SqlConnection conn = null;
SqlCommand cmd;
SqlDataAdapter da;
try
{
conn = SqlUtil.CreateConnection(ConnectionString);
cmd = SqlUtil.CreateCommand(conn);
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
if (allParams != null)
{
foreach (object obj in allParams)
{
if (obj is IList)
{
foreach (DBParameter db in (IList)obj)
{
AddDBParam(cmd, (DBParameter)db);
}
}
else
{
AddDBParam(cmd, (DBParameter)obj);
}
}
}
da = new SqlDataAdapter(cmd);
SqlUtil.StartTimer();
da.Fill(retVal);
SqlUtil.StopTimer(cmd);
return(retVal);
}
finally
{
SqlUtil.ReleaseConnection(conn);
}
}
public IDataReader GetReaderByQuery(string query)
{
SqlConnection conn = null;
SqlCommand cmd;
try
{
conn = SqlUtil.CreateConnection(ConnectionString);
cmd = SqlUtil.CreateCommand(conn);
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
return(SqlUtil.GetDataReader(cmd, ConnectionString));
}
finally
{
SqlUtil.ReleaseConnection(conn);
}
}
public IDataReader GetReader(string procName, params object[] allParams)
{
SqlConnection conn = null;
SqlCommand cmd;
try
{
conn = SqlUtil.CreateConnection(ConnectionString);
cmd = SqlUtil.CreateCommand(conn);
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
if (allParams != null)
{
foreach (object obj in allParams)
{
if (obj is IList)
{
foreach (DBParameter db in (IList)obj)
{
AddDBParam(cmd, (DBParameter)db);
}
}
else
{
AddDBParam(cmd, (DBParameter)obj);
}
}
}
return(SqlUtil.GetDataReader(cmd, ConnectionString));
}
finally
{
SqlUtil.ReleaseConnection(conn);
}
}
public void UpdateDataSet(string updateStatement, string deleteStatement, DataSet ds, bool queryTypeUpdateCommand, bool queryTypeDeleteCommand, params object[] allParams)
{
SqlConnection conn = null;
SqlCommand updCmd;
SqlCommand delCmd;
SqlCommand cmd = null;
SqlDataAdapter da;
try
{
conn = SqlUtil.CreateConnection(ConnectionString);
updCmd = SqlUtil.CreateCommand(conn);
updCmd.CommandText = updateStatement;
if (queryTypeUpdateCommand)
{
updCmd.CommandType = CommandType.Text;
}
else
{
updCmd.CommandType = CommandType.StoredProcedure;
}
delCmd = SqlUtil.CreateCommand(conn);
delCmd.CommandText = deleteStatement;
if (queryTypeDeleteCommand)
{
delCmd.CommandType = CommandType.Text;
}
else
{
delCmd.CommandType = CommandType.StoredProcedure;
}
if (allParams != null)
{
for (int iCount = 0; iCount < allParams.Length; iCount++)
{
if (iCount == 0)
{
cmd = updCmd;
}
else if (iCount == 1)
{
cmd = delCmd;
}
object obj = allParams[iCount];
if (obj == null)
{
continue;
}
if (obj is IList)
{
foreach (DBParameter db in (IList)obj)
{
if (db.ParamValue == null)
{
AddDBParamNoValue(cmd, (DBParameter)db);
}
else
{
AddDBParam(cmd, (DBParameter)db);
}
}
}
else
if (((DBParameter)obj).ParamValue == null)
{
AddDBParamNoValue(cmd, (DBParameter)obj);
}
else
{
AddDBParam(cmd, (DBParameter)obj);
}
}
}
da = new SqlDataAdapter();
da.UpdateCommand = updCmd;
da.InsertCommand = updCmd;
da.DeleteCommand = delCmd;
//da.UpdateBatchSize = 10;
da.Update(ds);
}
finally
{
SqlUtil.ReleaseConnection(conn);
}
}