/// <summary> /// 获取表的约束条件 /// constraint_name - 约束名称 /// column_name - 约束的字段 /// constraint_type - 约束类型,PK: 主键,U: 唯一键,R: 外键 /// </summary> /// <param name="tableName">表名</param> /// <returns></returns> public ConstraintCollection GetSqlTableConstraint(string tableName) { tableName = tableName.ToUpper(); //此处取SqlServer2008中表的主键列信息,注意建表时主键列排第一列,此处通过列ID来检索 string sql = "select col.name as column_name,con.name as constraint_name,con.type as constraint_type" + " from sys.columns col, sys.key_constraints con " + " where col.object_id = con.parent_object_id and col.column_id = 1 and col.is_nullable = 0" + " and col.object_id in (select object_id from sys.tables where name = @table_name)"; try { DbParameter[] dbParams = { DbFactory <SqlServerConn, SqlServerFactory <SqlServerConn> > .MakeInParam("@table_name", SqlDbType.VarChar, 30, tableName), }; IDbOperation db = new SqlServerExecute(); DataSet dataSet = db.FillDataset(sql, dbParams); DataSet dsEmtpy = GetEmptyTable(tableName); string prevType = string.Empty; string prevColumnName = string.Empty; List <DataColumn> cols = null; foreach (DataRow dr in dataSet.Tables[0].Rows) { string constraintName = dr["constraint_name"].ToString(); string columnName = dr["column_name"].ToString(); string constraintType = dr["constraint_type"].ToString(); if (constraintType != prevType || constraintName != prevColumnName) { if (prevType.Length > 0) { UniqueConstraint uc = new UniqueConstraint(prevColumnName, cols.ToArray(), prevType == "PK"); dsEmtpy.Tables[0].Constraints.Add(uc); } cols = new List <DataColumn>(); } cols.Add(dsEmtpy.Tables[0].Columns[columnName]); prevType = constraintType; prevColumnName = constraintName; } if (prevType.Length > 0) { UniqueConstraint uc = new UniqueConstraint(prevColumnName, cols.ToArray(), prevType == "PK"); dsEmtpy.Tables[0].Constraints.Add(uc); } return(dsEmtpy.Tables[0].Constraints); } catch (CustomDataException exdb) { throw exdb; } catch (System.Exception ex) { throw new CustomDataException(ex, sql); } }
private void AutoProcess(DbTransaction transaction, DataTable dataTable, ExistingItemProcessType processType) { if (string.IsNullOrEmpty(dataTable.TableName)) { throw new CustomDataException("表名为空"); } if (dataTable.Rows.Count == 0) { return; } string sql = string.Empty; bool owerConnection = transaction == null; DbConnection conn = null; DbTransaction tran = null; try { ConstraintCollection ucList = GetSqlTableConstraint(dataTable.TableName); if (ucList.Count == 0) { AutoInsert(null, dataTable); return; } if (owerConnection) { conn = CreateConnection(); conn.Open(); tran = conn.BeginTransaction(); } else { conn = transaction.Connection; tran = transaction; } //sql = "select * from " + dt.TableName; //DataSet ds = GetDataSet(tran, sql); sql = "select count(*) from " + dataTable.TableName + " where "; Dictionary <string, bool> keyColumnList = new Dictionary <string, bool>(); foreach (UniqueConstraint uc in ucList) { if (uc == null || uc.Columns.Length == 0) { continue; } foreach (DataColumn dc in uc.Columns) { keyColumnList[dc.ColumnName] = true; } } int idx = 0; foreach (string strColumnName in keyColumnList.Keys) { if (idx > 0) { sql += " and "; } sql += strColumnName + "= @" + strColumnName; ++idx; } DataSet dsTable = GetSqlTableInfo(dataTable.TableName); DataTable dtInsert = dataTable.Clone(); DataTable dtExist = dataTable.Clone(); foreach (DataRow dr in dataTable.Rows) { DbParameter[] pList = new DbParameter[keyColumnList.Keys.Count]; idx = 0; foreach (DataRow dr2 in dsTable.Tables[0].Rows) { if (keyColumnList.ContainsKey(dr2["cname"].ToString())) { pList[idx] = DbFactory <SqlServerConn, SqlServerFactory <SqlServerConn> > .MakeInParam("@" + dr2["cname"].ToString(), SqlServerDbTypeWrapper.WrapSqlDbType(dr2["coltype"].ToString()), Convert.ToInt32(dr2["width"]), dr[dr2["cname"].ToString()]); ++idx; } } bool bContain = Convert.ToInt32(ExecuteScalar(tran, CommandType.Text, sql, pList)) > 0; if (!bContain) { dtInsert.ImportRow(dr); } else { dtExist.ImportRow(dr); } } if (dtInsert.Rows.Count > 0) { AutoInsert(tran, dtInsert); } if (processType == ExistingItemProcessType.Update && dtExist.Rows.Count > 0) { AutoUpdate(tran, dtExist); } if (owerConnection) { tran.Commit(); } } catch (System.Exception ex) { if (owerConnection) { tran.Rollback(); } throw new CustomDataException(ex, sql); } finally { if (owerConnection && conn != null) { conn.Close(); } } }
/// <summary> /// 自动更新表 /// </summary> /// <param name="transaction"></param> /// <param name="dataTable"></param> public void AutoUpdate(DbTransaction transaction, DataTable dataTable) { DataSet dsTable = GetSqlTableInfo(dataTable.TableName); if (null == dsTable || dsTable.Tables.Count == 0 || dsTable.Tables[0].Rows.Count == 0) { throw new CustomDataException("未找到 " + dataTable.TableName + " 表的信息"); } ConstraintCollection ucList = GetSqlTableConstraint(dataTable.TableName); if (ucList.Count == 0) { throw new CustomDataException("表 " + dataTable.TableName + " 没有主键,无法自动更新"); } Dictionary <string, bool> keyTable = new Dictionary <string, bool>(); foreach (UniqueConstraint uc in ucList) { if (uc == null || uc.Columns.Length == 0) { continue; } foreach (DataColumn dc in uc.Columns) { keyTable[dc.ColumnName] = true; } } StringBuilder sb = new StringBuilder(); sb.Append("update "); sb.Append(dataTable.TableName); int idx = 0; foreach (DataRow drTable1 in dsTable.Tables[0].Rows) { string cName = drTable1["cname"].ToString(); if (dataTable.Columns.Contains(cName) && !keyTable.ContainsKey(cName)) { if (idx == 0) { sb.Append(" set "); } else { sb.Append(", "); } sb.Append(cName); sb.Append(" = @"); sb.Append(cName); ++idx; } } if (idx == 0) { return; } idx = 0; foreach (UniqueConstraint uc in ucList) { if (uc == null || uc.Columns.Length == 0) { continue; } foreach (DataColumn dc in uc.Columns) { if (idx == 0) { sb.Append(" where "); } else { sb.Append(" and "); } sb.Append(dc.ColumnName); sb.Append(" = @"); sb.Append(dc.ColumnName); ++idx; } } string sql = sb.ToString(); DbParameter[] parameters = null; try { foreach (DataRow dr in dataTable.Rows) { List <DbParameter> paramList = new List <DbParameter>(); foreach (DataRow drTable in dsTable.Tables[0].Rows) { string cName2 = drTable["cname"].ToString(); if (!dataTable.Columns.Contains(cName2)) { continue; } DbType dbt = SqlServerDbTypeWrapper.WrapSqlDbType(drTable["coltype"].ToString()); object objValue = dr.IsNull(cName2) ? null : dr[cName2]; paramList.Add(DbFactory <SqlServerConn, SqlServerFactory <SqlServerConn> > .MakeInParam("@" + cName2, dbt, Convert.ToInt32(drTable["width"]), objValue)); } parameters = paramList.ToArray(); IDbOperation db = new SqlServerExecute(); if (transaction == null) { db.ExecuteNonQuery(sql, parameters); } else { db.ExecuteNonQuery(transaction, sql, parameters); } } } catch (System.Exception ex) { throw new CustomDataException(ex, sql, parameters); } }
/// <summary> /// 将源表中的数据插入到表中(含事务) /// </summary> /// <param name="transaction">事务</param> /// <param name="dt">表名</param> public void AutoInsert(DbTransaction transaction, DataTable dt) { DataSet dataSet = GetSqlTableInfo(dt.TableName); if (null == dataSet || dataSet.Tables.Count == 0 || dataSet.Tables[0].Rows.Count == 0) { throw new CustomDataException("未找到 " + dt.TableName + " 表的信息"); } StringBuilder sb1 = new StringBuilder(); StringBuilder sb2 = new StringBuilder(); foreach (DataRow drTable1 in dataSet.Tables[0].Rows) { string cName = drTable1["cname"].ToString(); if (dt.Columns.Contains(cName)) { if (sb1.Length > 0) { sb1.Append(", "); sb2.Append(", "); } sb1.Append(cName); sb2.Append("@" + cName); } } if (sb1.Capacity == 0) { return; } string sql = "insert into " + dt.TableName + " (" + sb1.ToString() + ") values (" + sb2.ToString() + ")"; DbParameter[] parameters = null; try { foreach (DataRow dr in dt.Rows) { List <DbParameter> paramList = new List <DbParameter>(); foreach (DataRow drTable in dataSet.Tables[0].Rows) { string cName2 = drTable["cname"].ToString(); if (!dt.Columns.Contains(cName2)) { continue; } DbType dbt = SqlServerDbTypeWrapper.WrapSqlDbType(drTable["coltype"].ToString()); object objValue = dr.IsNull(cName2) ? null : dr[cName2]; paramList.Add(DbFactory <SqlServerConn, SqlServerFactory <SqlServerConn> > .MakeInParam("@" + cName2, dbt, Convert.ToInt32(drTable["width"]), objValue)); } parameters = paramList.ToArray(); IDbOperation db = new SqlServerExecute(); if (transaction == null) { db.ExecuteNonQuery(sql, parameters); } else { db.ExecuteNonQuery(transaction, sql, parameters); } } } catch (System.Exception ex) { throw new CustomDataException(ex, sql, parameters); } }