新增
/// <summary> /// 通用新增方法 /// </summary> /// <param name="arr">一行数据封装的集合</param> /// <param name="tableName">表名</param> /// <returns>结果 为true成功</returns> public bool Insert(Dictionary<string, object> arr, string tableName) { SqlConnection con = new SqlConnection(ConnectionString); try { con.Open(); string sql = "insert into " + tableName + "("; string sql2 = ")values("; SqlCommand com = new SqlCommand(sql, con); foreach (KeyValuePair<string, object> ar in arr) { //跳过未赋值的属性 if (ar.Value == null) { continue; } sql += ar.Key + ","; sql2 += "@" + ar.Key + ","; SqlParameter sp = new SqlParameter("@" + ar.Key, ar.Value.GetType()); sp.Value = ar.Value; com.Parameters.Add(sp); } com.CommandText = sql.Substring(0, sql.Length - 1) + sql2.Substring(0, sql2.Length - 1) + ")"; return Convert.ToBoolean(com.ExecuteNonQuery()); } catch (Exception) { return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } } /// <summary> /// 通用新增方法(批量) /// </summary> /// <param name="arr">多行数据封装的集合</param> /// <param name="tableName">表名</param> /// <returns>结果 为true 成功</returns> public bool Inserts(List<Dictionary<string, object>> arr, string tableName) { SqlConnection con = new SqlConnection(ConnectionString); SqlTransaction tr = con.BeginTransaction(); try { con.Open(); foreach (Dictionary<string, object> ar in arr) { string sql = "insert into " + tableName + "("; string sql2 = ")values("; SqlCommand com = new SqlCommand(sql, con); com.Transaction = tr; foreach (KeyValuePair<string, object> ak in ar) { //跳过未赋值的属性 if (ak.Value == null) { continue; } sql += ak.Key + ","; sql2 += "@" + ak.Key + ","; SqlParameter sp = new SqlParameter("@" + ak.Key, ak.Value.GetType()); sp.Value = ak.Value; com.Parameters.Add(sp); } com.CommandText = sql.Substring(0, sql.Length - 1) + sql2.Substring(0, sql2.Length - 1) + ")"; com.ExecuteNonQuery(); } tr.Commit(); return true; } catch (Exception) { tr.Rollback(); return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } } /// <summary> /// 通用新增方法 /// </summary> /// <param name="entity">实体</param> /// <returns></returns> public bool Insert(object entity) { if (entity == null) { return false; } SqlConnection con = new SqlConnection(ConnectionString); try { con.Open(); Type t = entity.GetType(); string tableName = entity.GetType().Name; System.Reflection.PropertyInfo[] pars = t.GetProperties(); string sql = "insert into " + tableName + "("; string sql2 = ")values("; SqlCommand com = new SqlCommand(sql, con); foreach (System.Reflection.PropertyInfo p in pars) { //跳过未赋值的属性 if (p.GetValue(entity, null) == null) { continue; } sql += p.Name + ","; sql2 += "@" + p.Name + ","; SqlParameter sp = new SqlParameter("@" + p.Name, p.PropertyType); sp.Value = p.GetValue(entity, null); com.Parameters.Add(sp); } com.CommandText = sql.Substring(0, sql.Length - 1) + sql2.Substring(0, sql2.Length - 1) + ")"; return Convert.ToBoolean(com.ExecuteNonQuery()); } catch (Exception) { return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } } /// <summary> /// 通用新增方法 /// </summary> /// <param name="entity">实体</param> /// <param name="tableName">表名</param> /// <returns></returns> public bool Insert(object entity, string tableName) { if (entity == null) { return false; } SqlConnection con = new SqlConnection(ConnectionString); try { con.Open(); Type t = entity.GetType(); System.Reflection.PropertyInfo[] pars = t.GetProperties(); string sql = "insert into " + tableName + "("; string sql2 = ")values("; SqlCommand com = new SqlCommand(sql, con); foreach (System.Reflection.PropertyInfo p in pars) { //跳过未赋值的属性 if (p.GetValue(entity, null) == null) { continue; } sql += p.Name + ","; sql2 += "@" + p.Name + ","; SqlParameter sp = new SqlParameter("@" + p.Name, p.PropertyType); sp.Value = p.GetValue(entity, null); com.Parameters.Add(sp); } com.CommandText = sql.Substring(0, sql.Length - 1) + sql2.Substring(0, sql2.Length - 1) + ")"; return Convert.ToBoolean(com.ExecuteNonQuery()); } catch (Exception) { return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } } /// <summary> /// 通用新增方法(批量) /// </summary> /// <param name="entitys">批量实体集合</param> /// <returns></returns> public bool Inserts(List<object> entitys) { SqlConnection con = new SqlConnection(ConnectionString); SqlTransaction tr = con.BeginTransaction(); try { con.Open(); foreach (object entity in entitys) { if (entity == null) { continue; } Type t = entity.GetType(); string tableName = entity.GetType().Name; System.Reflection.PropertyInfo[] pars = t.GetProperties(); string sql = "insert into " + tableName + "("; string sql2 = ")values("; SqlCommand com = new SqlCommand(sql, con); com.Transaction = tr; foreach (System.Reflection.PropertyInfo p in pars) { //跳过未赋值的属性 if (p.GetValue(entity, null) == null) { continue; } sql += p.Name + ","; sql2 += "@" + p.Name + ","; SqlParameter sp = new SqlParameter("@" + p.Name, p.PropertyType); sp.Value = p.GetValue(entity, null); com.Parameters.Add(sp); } com.CommandText = sql.Substring(0, sql.Length - 1) + sql2.Substring(0, sql2.Length - 1) + ")"; } tr.Commit(); return true; } catch (Exception) { tr.Rollback(); return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } } /// <summary> /// 通用新增方法(批量) /// </summary> /// <param name="entitys">批量实体集合</param> /// <param name="tableName">表名</param> /// <returns></returns> public bool Inserts(List<object> entitys, string tableName) { SqlConnection con = new SqlConnection(ConnectionString); ; SqlTransaction tr = con.BeginTransaction(); try { con.Open(); foreach (object entity in entitys) { if (entity == null) { continue; } Type t = entity.GetType(); System.Reflection.PropertyInfo[] pars = t.GetProperties(); string sql = "insert into " + tableName + "("; string sql2 = ")values("; SqlCommand com = new SqlCommand(sql, con); com.Transaction = tr; foreach (System.Reflection.PropertyInfo p in pars) { //跳过未赋值的属性 if (p.GetValue(entity, null) == null) { continue; } sql += p.Name + ","; sql2 += "@" + p.Name + ","; SqlParameter sp = new SqlParameter("@" + p.Name, p.PropertyType); sp.Value = p.GetValue(entity, null); com.Parameters.Add(sp); } com.CommandText = sql.Substring(0, sql.Length - 1) + sql2.Substring(0, sql2.Length - 1) + ")"; } tr.Commit(); return true; } catch (Exception) { tr.Rollback(); return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } }
修改
/// <summary> /// 通用更新方法 /// </summary> /// <param name="arr">要更新的数据集合(主键值必须存在于数据表中)</param> /// <param name="tableName">表名</param> /// <returns></returns> public bool Update(Dictionary<string, object> arr, string tableName) { SqlConnection con = new SqlConnection(ConnectionString); try { con.Open(); string sql = "UPDATE " + tableName + " SET "; SqlCommand com = new SqlCommand(sql, con); foreach (KeyValuePair<string, object> ar in arr) { //跳过未赋值的属性 if (ar.Value == null) { continue; } SqlParameter sp = new SqlParameter("@" + ar.Key, ar.Value.GetType()); sp.Value = ar.Value; com.Parameters.Add(sp); //判断是否是主键 if (IsPrimaryKey(ar.Key, tableName)) { continue; } sql += ar.Key + "=@" + ar.Key + ","; } sql = sql.Substring(0, sql.Length - 1) + " WHERE "; List<Dictionary<string, object>> keys = Select("sp_pkeys " + tableName); foreach (Dictionary<string, object> a in keys) { sql += a["COLUMN_NAME"] + "=@" + a["COLUMN_NAME"] + " and "; } com.CommandText = sql.Substring(0, sql.Length - 4); return Convert.ToBoolean(com.ExecuteNonQuery()); } catch (Exception) { return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } } /// <summary> /// 通用更新方法(批量) /// </summary> /// <param name="arr">批量数据集合</param> /// <param name="tableName">表名</param> /// <returns></returns> public bool Updates(List<Dictionary<string, object>> arr, string tableName) { SqlConnection con = new SqlConnection(ConnectionString); SqlTransaction tr = con.BeginTransaction(); try { con.Open(); List<Dictionary<string, object>> keys = Select("sp_pkeys " + tableName); foreach (Dictionary<string, object> ar in arr) { string sql = "UPDATE " + tableName + " SET "; SqlCommand com = new SqlCommand(sql, con); com.Transaction = tr; foreach (KeyValuePair<string, object> a in ar) { //跳过未赋值的属性 if (a.Value == null) { continue; } SqlParameter sp = new SqlParameter("@" + a.Key, a.Value.GetType()); sp.Value = a.Value; com.Parameters.Add(sp); //判断是否是主键 if (IsPrimaryKey(a.Key, tableName)) { continue; } sql += a.Key + "=@" + a.Key + ","; } sql = sql.Substring(0, sql.Length - 1) + " WHERE "; foreach (Dictionary<string, object> a in keys) { sql += a["COLUMN_NAME"] + "=@" + a["COLUMN_NAME"] + " and "; } com.CommandText = sql.Substring(0, sql.Length - 4); com.ExecuteNonQuery(); } tr.Commit(); return true; } catch (Exception) { tr.Rollback(); return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } } /// <summary> /// 通用新增（实体） /// </summary> /// <param name="entity">封装一行数据的实体</param> /// <param name="tableName">表名</param> /// <returns></returns> public bool Update(object entity, string tableName) { if (entity == null) { return false; } SqlConnection con = new SqlConnection(ConnectionString); try { con.Open(); Type t = entity.GetType(); System.Reflection.PropertyInfo[] pt = t.GetProperties(); string sql = "UPDATE " + tableName + " SET "; SqlCommand com = new SqlCommand(sql, con); foreach (System.Reflection.PropertyInfo p in pt) { //跳过未赋值的属性 if (p.GetValue(entity, null) == null) { continue; } SqlParameter sp = new SqlParameter("@" + p.Name, p.PropertyType); sp.Value = p.GetValue(entity, null); com.Parameters.Add(sp); //判断是否是主键 if (IsPrimaryKey(p.Name, tableName)) { continue; } sql += p.Name + "=@" + p.Name + ","; } sql = sql.Substring(0, sql.Length - 1) + " WHERE "; List<Dictionary<string, object>> keys = Select("sp_pkeys " + tableName); foreach (Dictionary<string, object> a in keys) { sql += a["COLUMN_NAME"] + "=@" + a["COLUMN_NAME"] + " and "; } com.CommandText = sql.Substring(0, sql.Length - 4); return Convert.ToBoolean(com.ExecuteNonQuery()); } catch (Exception) { return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } } /// <summary> /// 通用修改（实体） /// </summary> /// <param name="entity">封装一行数据的实体</param> /// <returns></returns> public bool Update(object entity) { if (entity == null) { return false; } SqlConnection con = new SqlConnection(ConnectionString); try { con.Open(); Type t = entity.GetType(); string tableName = entity.GetType().Name; System.Reflection.PropertyInfo[] pt = t.GetProperties(); string sql = "UPDATE " + tableName + " SET "; SqlCommand com = new SqlCommand(sql, con); foreach (System.Reflection.PropertyInfo p in pt) { //跳过未赋值的属性 if (p.GetValue(entity, null) == null) { continue; } SqlParameter sp = new SqlParameter("@" + p.Name, p.PropertyType); sp.Value = p.GetValue(entity, null); com.Parameters.Add(sp); //判断是否是主键 if (IsPrimaryKey(p.Name, tableName)) { continue; } sql += p.Name + "=@" + p.Name + ","; } sql = sql.Substring(0, sql.Length - 1) + " WHERE "; List<Dictionary<string, object>> keys = Select("sp_pkeys " + tableName); foreach (Dictionary<string, object> a in keys) { sql += a["COLUMN_NAME"] + "=@" + a["COLUMN_NAME"] + " and "; } com.CommandText = sql.Substring(0, sql.Length - 4); return Convert.ToBoolean(com.ExecuteNonQuery()); } catch (Exception) { return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } } /// <summary> /// 通用更新方法(批量实体) /// </summary> /// <param name="entitys">批量数据行实体</param> /// <returns></returns> public bool Updates(List<object> entitys) { SqlConnection con = new SqlConnection(ConnectionString); SqlTransaction tr = con.BeginTransaction(); try { con.Open(); foreach (object entity in entitys) { if (entity == null) { continue; } Type t = entity.GetType(); string tableName = entity.GetType().Name; System.Reflection.PropertyInfo[] pt = t.GetProperties(); string sql = "UPDATE " + tableName + " SET "; SqlCommand com = new SqlCommand(sql, con); com.Transaction = tr; foreach (System.Reflection.PropertyInfo p in pt) { //跳过未赋值的属性 if (p.GetValue(entity, null) == null) { continue; } SqlParameter sp = new SqlParameter("@" + p.Name, p.PropertyType); sp.Value = p.GetValue(entity, null); com.Parameters.Add(sp); //判断是否是主键 if (IsPrimaryKey(p.Name, tableName)) { continue; } sql += p.Name + "=@" + p.Name + ","; } sql = sql.Substring(0, sql.Length - 1) + " WHERE "; List<Dictionary<string, object>> keys = Select("sp_pkeys " + tableName); foreach (Dictionary<string, object> a in keys) { sql += a["COLUMN_NAME"] + "=@" + a["COLUMN_NAME"] + " and "; } com.CommandText = sql.Substring(0, sql.Length - 4); com.ExecuteNonQuery(); } tr.Commit(); return true; } catch (Exception) { tr.Rollback(); return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } } /// <summary> /// 通用更新方法(批量实体) /// </summary> /// <param name="entitys">批量数据行实体</param> /// <param name="tableName">表名</param> /// <returns></returns> public bool Updates(List<object> entitys, string tableName) { SqlConnection con = new SqlConnection(ConnectionString); SqlTransaction tr = con.BeginTransaction(); try { con.Open(); foreach (object entity in entitys) { if (entity == null) { continue; } Type t = entity.GetType(); System.Reflection.PropertyInfo[] pt = t.GetProperties(); string sql = "UPDATE " + tableName + " SET "; SqlCommand com = new SqlCommand(sql, con); com.Transaction = tr; foreach (System.Reflection.PropertyInfo p in pt) { //跳过未赋值的属性 if (p.GetValue(entity, null) == null) { continue; } SqlParameter sp = new SqlParameter("@" + p.Name, p.PropertyType); sp.Value = p.GetValue(entity, null); com.Parameters.Add(sp); //判断是否是主键 if (IsPrimaryKey(p.Name, tableName)) { continue; } sql += p.Name + "=@" + p.Name + ","; } sql = sql.Substring(0, sql.Length - 1) + " WHERE "; List<Dictionary<string, object>> keys = Select("sp_pkeys " + tableName); foreach (Dictionary<string, object> a in keys) { sql += a["COLUMN_NAME"] + "=@" + a["COLUMN_NAME"] + " and "; } com.CommandText = sql.Substring(0, sql.Length - 4); com.ExecuteNonQuery(); } tr.Commit(); return true; } catch (Exception) { tr.Rollback(); return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } } /// <summary> /// 通用新增（实体） /// </summary> /// <param name="entity">封装一行数据的实体</param> /// <returns></returns> public bool UpdateWhereKeyIn(object entity, string ids) { if (entity == null) { return false; } SqlConnection con = new SqlConnection(ConnectionString); try { con.Open(); Type t = entity.GetType(); string tableName = entity.GetType().Name; System.Reflection.PropertyInfo[] pt = t.GetProperties(); string sql = "UPDATE " + tableName + " SET "; SqlCommand com = new SqlCommand(sql, con); foreach (System.Reflection.PropertyInfo p in pt) { //跳过未赋值的属性 if (p.GetValue(entity, null) == null) { continue; } //判断是否是主键 if (IsPrimaryKey(p.Name, tableName)) { continue; } sql += p.Name + "=@" + p.Name + ","; SqlParameter sp = new SqlParameter("@" + p.Name, p.PropertyType); sp.Value = p.GetValue(entity, null); com.Parameters.Add(sp); } sql = sql.Substring(0, sql.Length - 1) + " WHERE "; List<Dictionary<string, object>> keys = Select("sp_pkeys " + tableName); foreach (Dictionary<string, object> a in keys) { sql += a["COLUMN_NAME"] + " in " + ids + " and "; } com.CommandText = sql.Substring(0, sql.Length - 4); return Convert.ToBoolean(com.ExecuteNonQuery()); } catch (Exception) { return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } } /// <summary> /// 通用新增（实体） /// </summary> /// <param name="entity">封装一行数据的实体</param> /// <param name="tableName">表名</param> /// <returns></returns> public bool UpdateWhereKeyIn(object entity, string ids, string tableName) { if (entity == null) { return false; } SqlConnection con = new SqlConnection(ConnectionString); try { con.Open(); Type t = entity.GetType(); System.Reflection.PropertyInfo[] pt = t.GetProperties(); string sql = "UPDATE " + tableName + " SET "; SqlCommand com = new SqlCommand(sql, con); foreach (System.Reflection.PropertyInfo p in pt) { //跳过未赋值的属性 if (p.GetValue(entity, null) == null) { continue; } //判断是否是主键 if (IsPrimaryKey(p.Name, tableName)) { continue; } sql += p.Name + "=@" + p.Name + ","; SqlParameter sp = new SqlParameter("@" + p.Name, p.PropertyType); sp.Value = p.GetValue(entity, null); com.Parameters.Add(sp); } sql = sql.Substring(0, sql.Length - 1) + " WHERE "; List<Dictionary<string, object>> keys = Select("sp_pkeys " + tableName); foreach (Dictionary<string, object> a in keys) { sql += a["COLUMN_NAME"] + " in " + ids + " and "; } com.CommandText = sql.Substring(0, sql.Length - 4); return Convert.ToBoolean(com.ExecuteNonQuery()); } catch (Exception) { return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } }
删除
/// <summary> /// 删除通用方法(单条数据) /// </summary> /// <param name="arr">要删除的数据行的主键</param> /// <param name="tableName">要删除的表</param> /// <returns></returns> public bool Delete(Dictionary<string, object> arr, string tableName) { SqlConnection con = new SqlConnection(ConnectionString); try { con.Open(); string sql = "DELETE FROM " + tableName + " WHERE "; SqlCommand com = new SqlCommand(sql, con); foreach (KeyValuePair<string, object> ar in arr) { sql += ar.Key + "=@" + ar.Key + " and "; SqlParameter sp = new SqlParameter("@" + ar.Key, ar.Value.GetType()); sp.Value = ar.Value; com.Parameters.Add(sp); } com.CommandText = sql.Substring(0, sql.Length - 4); return Convert.ToBoolean(com.ExecuteNonQuery()); } catch (Exception) { return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } } /// <summary> /// 通用删除方法(批量) /// </summary> /// <param name="arr">要删除的多条数据的主键</param> /// <param name="tableName">表名</param> /// <returns></returns> public bool Deletes(List<Dictionary<string, object>> arr, string tableName) { SqlConnection con = new SqlConnection(ConnectionString); SqlTransaction tr = con.BeginTransaction(); try { con.Open(); foreach (Dictionary<string, object> ar in arr) { string sql = "DELETE FROM " + tableName + " WHERE "; SqlCommand com = new SqlCommand(sql, con); com.Transaction = tr; foreach (KeyValuePair<string, object> a in ar) { sql += a.Key + "=@" + a.Key + " and "; SqlParameter sp = new SqlParameter("@" + a.Key, a.Value.GetType()); sp.Value = a.Value; com.Parameters.Add(sp); } com.CommandText = sql.Substring(0, sql.Length - 4); com.ExecuteNonQuery(); } tr.Commit(); return true; } catch (Exception) { tr.Rollback(); return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } } /// <summary> /// 通用删除(实体) /// </summary> /// <param name="entity">用实体封装的一行数据</param> /// <returns></returns> public bool Delete(object entity) { if (entity == null) { return false; } SqlConnection con = new SqlConnection(ConnectionString); try { con.Open(); Type t = entity.GetType(); string tableName = entity.GetType().Name; System.Reflection.PropertyInfo[] pr = t.GetProperties(); string sql = "DELETE FROM " + tableName + " WHERE "; SqlCommand com = new SqlCommand(sql, con); List<Dictionary<string, object>> keys = Select("sp_pkeys " + tableName); foreach (Dictionary<string, object> ar in keys) { sql += ar["COLUMN_NAME"] + "=@" + ar["COLUMN_NAME"] + " and "; foreach (System.Reflection.PropertyInfo p in pr) { if (p.Name.Trim() == ar["COLUMN_NAME"].ToString().Trim()) { SqlParameter sp = new SqlParameter("@" + ar["COLUMN_NAME"].ToString().Trim(), p.PropertyType); sp.Value = p.GetValue(entity, null); com.Parameters.Add(sp); break; } } } com.CommandText = sql.Substring(0, sql.Length - 4); return Convert.ToBoolean(com.ExecuteNonQuery()); } catch (Exception) { return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } } /// <summary> /// 通用删除(实体) /// </summary> /// <param name="entity">用实体封装的一行数据</param> /// <param name="tableName">表名</param> /// <returns></returns> public bool Delete(object entity, string tableName) { if (entity == null) { return false; } SqlConnection con = new SqlConnection(ConnectionString); try { con.Open(); Type t = entity.GetType(); System.Reflection.PropertyInfo[] pr = t.GetProperties(); string sql = "DELETE FROM " + tableName + " WHERE "; SqlCommand com = new SqlCommand(sql, con); List<Dictionary<string, object>> keys = Select("sp_pkeys " + tableName); foreach (Dictionary<string, object> ar in keys) { sql += ar["COLUMN_NAME"] + "=@" + ar["COLUMN_NAME"] + " and "; foreach (System.Reflection.PropertyInfo p in pr) { if (p.Name.Trim() == ar["COLUMN_NAME"].ToString().Trim()) { SqlParameter sp = new SqlParameter("@" + ar["COLUMN_NAME"].ToString().Trim(), p.PropertyType); sp.Value = p.GetValue(entity, null); com.Parameters.Add(sp); break; } } } com.CommandText = sql.Substring(0, sql.Length - 4); return Convert.ToBoolean(com.ExecuteNonQuery()); } catch (Exception) { return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } } /// <summary> /// 通用删除批量(实体) /// </summary> /// <param name="entitys">多个实体封装的集合</param> /// <returns></returns> public bool Deletes(List<object> entitys) { SqlConnection con = new SqlConnection(ConnectionString); SqlTransaction tr = null; try { con.Open(); tr = con.BeginTransaction(); foreach (object entity in entitys) { if (entity == null) { continue; } Type t = entity.GetType(); string tableName = entity.GetType().Name; System.Reflection.PropertyInfo[] pt = t.GetProperties(); string sql = "DELETE FROM " + tableName + " WHERE "; SqlCommand com = new SqlCommand(sql, con); com.Transaction = tr; List<Dictionary<string, object>> keys = Select("sp_pkeys " + tableName); foreach (Dictionary<string, object> ar in keys) { sql += ar["COLUMN_NAME"] + "=@" + ar["COLUMN_NAME"] + " and "; foreach (System.Reflection.PropertyInfo p in pt) { if (p.Name.Trim() == ar["COLUMN_NAME"].ToString().Trim()) { SqlParameter sp = new SqlParameter("@" + p.Name, p.PropertyType); sp.Value = p.GetValue(entity, null); com.Parameters.Add(sp); break; } } } com.CommandText = sql.Substring(0, sql.Length - 4); com.ExecuteNonQuery(); } tr.Commit(); return true; } catch (Exception) { tr.Rollback(); return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } } /// <summary> /// 通用删除批量(实体) /// </summary> /// <param name="entitys">多个实体封装的集合</param> /// <param name="tableName">表名</param> /// <returns></returns> public bool Deletes(List<object> entitys, string tableName) { SqlConnection con = new SqlConnection(ConnectionString); SqlTransaction tr = null; try { con.Open(); tr = con.BeginTransaction(); foreach (object entity in entitys) { if (entity == null) { continue; } Type t = entity.GetType(); System.Reflection.PropertyInfo[] pt = t.GetProperties(); string sql = "DELETE FROM " + tableName + " WHERE "; SqlCommand com = new SqlCommand(sql, con); com.Transaction = tr; List<Dictionary<string, object>> keys = Select("sp_pkeys " + tableName); foreach (Dictionary<string, object> ar in keys) { sql += ar["COLUMN_NAME"] + "=@" + ar["COLUMN_NAME"] + " and "; foreach (System.Reflection.PropertyInfo p in pt) { if (p.Name.Trim() == ar["COLUMN_NAME"].ToString().Trim()) { SqlParameter sp = new SqlParameter("@" + p.Name, p.PropertyType); sp.Value = p.GetValue(entity, null); com.Parameters.Add(sp); break; } } } com.CommandText = sql.Substring(0, sql.Length - 4); com.ExecuteNonQuery(); } tr.Commit(); return true; } catch (Exception) { tr.Rollback(); return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } } /// <summary> /// 通用删除(实体) /// </summary> /// <param name="entity">用实体封装的一行数据</param> /// <param name="tableName">表名</param> /// <returns></returns> public bool DeleteWhereKeyIn(object entity, string ids) { if (entity == null) { return false; } SqlConnection con = new SqlConnection(ConnectionString); try { con.Open(); Type t = entity.GetType(); string tableName = entity.GetType().Name; System.Reflection.PropertyInfo[] pr = t.GetProperties(); string sql = "DELETE FROM " + tableName + " WHERE "; SqlCommand com = new SqlCommand(sql, con); List<Dictionary<string, object>> keys = Select("sp_pkeys " + tableName); foreach (Dictionary<string, object> ar in keys) { sql += ar["COLUMN_NAME"] + " in " + ids + " and "; } com.CommandText = sql.Substring(0, sql.Length - 4); return Convert.ToBoolean(com.ExecuteNonQuery()); } catch (Exception) { return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } } /// <summary> /// 通用删除(实体) /// </summary> /// <param name="entity">用实体封装的一行数据</param> /// <param name="tableName">表名</param> /// <returns></returns> public bool DeleteWhereKeyIn(object entity, string ids, string tableName) { if (entity == null) { return false; } SqlConnection con = new SqlConnection(ConnectionString); try { con.Open(); Type t = entity.GetType(); System.Reflection.PropertyInfo[] pr = t.GetProperties(); string sql = "DELETE FROM " + tableName + " WHERE "; SqlCommand com = new SqlCommand(sql, con); List<Dictionary<string, object>> keys = Select("sp_pkeys " + tableName); foreach (Dictionary<string, object> ar in keys) { sql += ar["COLUMN_NAME"] + " in " + ids + " and "; } com.CommandText = sql.Substring(0, sql.Length - 4); return Convert.ToBoolean(com.ExecuteNonQuery()); } catch (Exception) { return false; } finally { if (con.State == ConnectionState.Open) con.Close(); } }