/// <summary> /// Generic method for execute any store procedure /// whether the sp for insert, update , select or delete /// it will work genericlly for all type of store procedure /// </summary> /// <param name="procedureName"></param> /// <param name="parameters"></param> /// <returns>return DataTable object</returns> public static DataTable ExecuteDTByProcedure(string procedureName, SqlParameter[] parameters) { using (SqlConnection con = connectDB()) { SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandText = procedureName; if (parameters != null) { cmd.Parameters.AddRange(parameters); } cmd.CommandType = CommandType.StoredProcedure; DataTable dt = new DataTable(); try { SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); }catch (Exception ex) { DataBaseExceptionLogger.Log(ex); return(new DataTable()); } return(dt); } }
/// <summary> /// Established Connecttion between ASP.NET and SQL Server /// </summary> /// <returns>SqlConnection object</returns> public static SqlConnection connectDB() { try { string connectionString = ConfigurationManager.ConnectionStrings["FCDB"].ConnectionString; SqlConnection con = new SqlConnection(connectionString); con.Open(); return(con); } catch (Exception ex) { DataBaseExceptionLogger.Log(ex); return(new SqlConnection()); } }
/// <summary> /// Generic method for creating SqlParameter /// </summary> /// <param name="parameterName">sql paramter name</param> /// <param name="value">sql parameter value</param> /// <returns>return SqlParameter object</returns> public static SqlParameter AddParameter(string parameterName, object value) { value = value == null ? DBNull.Value : value; try { SqlParameter parameter = new SqlParameter(parameterName, value); return(parameter); }catch (Exception ex) { DataBaseExceptionLogger.Log(ex); return(null); } }