public static int ExecuteTransactionReturnIdentity(string spName, SqlCommand cmd, SqlParameter[] paramtr) { int intCount = 0; int identity = 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]); } //For returning Value SqlParameter returnValue = default(SqlParameter); returnValue = cmd.Parameters.Add("@num", System.Data.SqlDbType.Real); returnValue.Direction = System.Data.ParameterDirection.ReturnValue; try { cmd.ExecuteNonQuery(); identity = Convert.ToInt32(cmd.Parameters["@num"].Value); } catch (Exception ex) { DALUtility.ErrorLog(ex.Message, "DALCommon.vb, ExecuteTransaction"); } return(identity); }
public static DataTable GetDataByQuery(string Query, SqlParameter[] paramtr) { 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); for (int i = 0; i <= paramtr.Length - 1; i++) { cmd.Parameters.Add(paramtr[i]); } try { da.Fill(dt); } catch (Exception ex) { DALUtility.ErrorLog(ex.Message, "DALCommon.vb, GetDataUsingDataTableByQuery"); } return(dt); }
public static decimal GetIDByStoredProcedure(string spName, SqlParameter[] paramtr) { SqlDataReader dr = null; SqlConnection cn = new SqlConnection(ConnectionString()); SqlCommand cmd = new SqlCommand(); decimal j = 0; 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(); } dr = cmd.ExecuteReader(); if ((dr.HasRows)) { while (dr.Read()) { j = Convert.ToDecimal(dr.GetValue(0)); } } else { j = 0; } } catch (Exception ex) { DALUtility.ErrorLog(ex.Message, "DALCommon.vb, DataExistDblByStoredProcedure"); } finally { if ((cn.State == ConnectionState.Open)) { cn.Close(); } dr = null; cmd = null; } return(j); }
public static bool DataExistsByStoredProcedure(string spName, SqlParameter[] paramtr) { SqlDataReader dr = null; 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(); } dr = cmd.ExecuteReader(); } catch (Exception ex) { DALUtility.ErrorLog(ex.Message, "DALCommon.vb, DataExistsByStoredProcedure"); } if ((dr.HasRows)) { dr = null; cmd = null; if ((cn.State == ConnectionState.Open)) { cn.Close(); } return(true); } else { dr = null; cmd = null; if ((cn.State == ConnectionState.Open)) { cn.Close(); } return(false); } }
public static int GetCountByProcedure(string spName, SqlParameter[] paramtr) { SqlDataReader dr = null; SqlConnection cn = new SqlConnection(ConnectionString()); SqlCommand cmd = new SqlCommand(); int result = 0; 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(); } dr = cmd.ExecuteReader(); if ((dr.HasRows)) { while (dr.Read()) { result = Convert.ToInt32(dr.GetValue(0)); } } } catch (Exception ex) { DALUtility.ErrorLog(ex.Message, "DALCommon.vb, GetCountByProcedure"); } finally { cmd = null; dr = null; if ((cn.State == ConnectionState.Open)) { cn.Close(); } } return(result); }
public static decimal ExecuteNonQueryReturnIdentity(string spName, SqlParameter[] paramtr) { decimal identity = 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]); } //For returning Value SqlParameter returnValue = default(SqlParameter); returnValue = cmd.Parameters.Add("@num", System.Data.SqlDbType.Real); returnValue.Direction = System.Data.ParameterDirection.ReturnValue; try { if ((cn.State == ConnectionState.Closed)) { cn.Open(); } cmd.ExecuteNonQuery(); identity = Convert.ToDecimal(cmd.Parameters["@num"].Value); } catch (Exception ex) { DALUtility.ErrorLog(ex.Message, "DALCommon.vb, ExecuteNonQueryReturnIdentity"); } finally { cmd = null; if ((cn.State == ConnectionState.Open)) { cn.Close(); } } return(identity); }
public static DataTable GetDataByStoredProcedure(string spName) { 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); try { da.Fill(dt); } catch (Exception ex) { DALUtility.ErrorLog(ex.Message, "DALCommon.vb, GetDataUsingDataTable"); } return(dt); }
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 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); }