/// <summary> /// 执行SQL返回DataTable /// </summary> public static DataTable ExecuteTable(this KilyContext db, string sql, SqlParameter[] sqlParams) { try { DbConnection connection = db.Database.GetDbConnection(); SqlCommand cmd = connection.CreateCommand() as SqlCommand; db.Database.OpenConnection(); cmd.CommandText = sql; if (sqlParams != null) { cmd.Parameters.AddRange(sqlParams); } DataTable dt = new DataTable(); using (SqlDataAdapter adapter = new SqlDataAdapter(cmd)) { adapter.Fill(dt); } db.Database.CloseConnection(); return(dt); } catch (Exception) { return(null); } }
/// <summary> /// 执行存储过程返回DataSet数据集 /// </summary> public static DataSet Execute(this KilyContext db, string sql, SqlParameter[] sqlParams, IList <String> PropertyNames = null) { DbConnection connection = db.Database.GetDbConnection(); SqlCommand cmd = connection.CreateCommand() as SqlCommand; db.Database.OpenConnection(); cmd.CommandText = sql; cmd.CommandType = CommandType.StoredProcedure; if (sqlParams != null) { cmd.Parameters.AddRange(sqlParams); } DataSet ds = new DataSet(); using (SqlDataAdapter adapter = new SqlDataAdapter(cmd)) { if (PropertyNames != null) { string Table = "Table"; for (int i = 0; i < PropertyNames.Count; i++) { if (i == 0) { adapter.TableMappings.Add(Table, PropertyNames[i]); } else { adapter.TableMappings.Add(Table + i, PropertyNames[i]); } } } adapter.Fill(ds); } db.Database.CloseConnection(); return(ds); }