public static DataTable GetData(DataBase dataBaseType, CommandType commandType, string query, SqlParameter[] parameters = null) { SqlConnection conn = new SqlConnection(GetConnectionStaring(dataBaseType)); SqlCommand cmd = conn.CreateCommand(); SqlDataReader reader = null; try { cmd.CommandType = commandType; cmd.CommandText = query; if (parameters != null) parameters.ToList().ForEach(s => { cmd.Parameters.Add(s); }); DataTable dt = new DataTable(); conn.Open(); reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); dt.Load(reader); return dt; } catch (Exception ex) { throw; } finally { if (reader != null && !reader.IsClosed) { reader.Close(); } if (cmd != null) { if (cmd.Parameters != null) { cmd.Parameters.Clear(); } cmd.Dispose(); } if (conn != null && conn.State == ConnectionState.Open) { conn.Close(); conn.Dispose(); } } }
private List<string> GetValuesList(SqlParameter[] args) { var values = new List<string>(); args.ToList().ForEach(arg => { var param = arg; var value = param.Value; if (value == null) value = "NULL"; else if (value.GetType() != typeof(int)) value = "'" + value + "'"; values.Add(Left(value.ToString())); }); return values; }
public static int StoreData_ExecuteNonQuery(DataBase dataBaseType, CommandType commandType, string query, SqlParameter[] parameters = null, List<TVParameter> TVParameters = null) { SqlConnection conn = new SqlConnection(GetConnectionStaring(dataBaseType)); SqlCommand cmd = conn.CreateCommand(); try { cmd.CommandType = commandType; cmd.CommandText = query; if (parameters != null) parameters.ToList().ForEach(s => { cmd.Parameters.Add(s); }); if (TVParameters != null) TVParameters.ToList().ForEach(s => { SqlParameter tvpParam = cmd.Parameters.AddWithValue(s.ParameterName, s.ParameterValue); tvpParam.SqlDbType = s.SqlDbType; tvpParam.TypeName = s.ParameterTypeName; }); conn.Open(); return cmd.ExecuteNonQuery(); } catch (Exception ex) { throw; } finally { if (cmd != null) { if (cmd.Parameters != null) { cmd.Parameters.Clear(); } cmd.Dispose(); } if (conn != null && conn.State == ConnectionState.Open) { conn.Close(); conn.Dispose(); } } }