public static DataTable GetDataByQuery(string Query)
{
DataTable dt = new DataTable();
SqlConnection cn = new SqlConnection(ConnectionString());
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = Query;
SqlDataAdapter da = new SqlDataAdapter(cmd);
try
{
da.Fill(dt);
}
catch (Exception ex)
{
DALUtility.ErrorLog(ex.Message, "DALCommon.vb, GetDataUsingDataTableByQuery");
}
return(dt);
}
public static int ExecuteNonQuery(string spName, SqlParameter[] paramtr)
{
int recCount = 0;
SqlConnection cn = new SqlConnection(ConnectionString());
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spName;
for (int i = 0; i <= paramtr.Length - 1; i++)
{
cmd.Parameters.Add(paramtr[i]);
}
try
{
if ((cn.State == ConnectionState.Closed))
{
cn.Open();
}
recCount = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
DALUtility.ErrorLog(ex.Message, "DALCommon.vb, ExecuteNonQuery");
}
finally
{
cmd = null;
if ((cn.State == ConnectionState.Open))
{
cn.Close();
}
}
return(recCount);
}
public static DataTable GetDataByParameter(string spName, string ParameterName, object ParameterValue)
{
DataTable dt = new DataTable();
SqlConnection cn = new SqlConnection(ConnectionString());
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spName;
SqlDataAdapter da = new SqlDataAdapter(cmd);
cmd.Parameters.AddWithValue(ParameterName, ParameterValue);
try
{
da.Fill(dt);
}
catch (Exception ex)
{
DALUtility.ErrorLog(ex.Message, "DALCommon.vb, GetDataUsingDataTableByParameter");
}
return(dt);
}
public static int ExecuteTransaction(string spName, SqlCommand cmd, SqlParameter[] paramtr)
{
int intCount = 0;
cmd.CommandText = spName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
for (int i = 0; i <= paramtr.Length - 1; i++)
{
cmd.Parameters.Add(paramtr[i]);
}
try
{
intCount = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
DALUtility.ErrorLog(ex.Message, "DALCommon.vb, ExecuteTransaction");
}
return(intCount);
}