public void AutoProcess(DbTransaction transaction, DataTable dataTable, ExistingItemProcessType processType) { if (dataTable.TableName.Length == 0) { throw new Exception("表名为空"); } if (dataTable.Rows.Count == 0) { return; } string sql = ""; bool owerConnection = transaction == null; DbConnection conn = null; DbTransaction tran = null; try { ConstraintCollection ucList = GetTableConstraint(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 = ExecuteDataset(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 columnName in keyColumnList.Keys) { if (idx > 0) { sql += " and "; } sql += columnName + "=:" + columnName; ++idx; } DataSet dsTable = GetTableInfo(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 <OracleConn, OracleFactory <OracleConn> > .MakeInParam(":" + dr2["cname"].ToString(), OracleDbTypeWrapper.WrapDbType(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(); } } }
public void AutoUpdate(DbTransaction transaction, DataTable dataTable) { DataSet dsTable = GetTableInfo(dataTable.TableName); if (null == dsTable || dsTable.Tables.Count == 0 || dsTable.Tables[0].Rows.Count == 0) { throw new Exception("未找到 " + dataTable.TableName + " 表的信息"); } ConstraintCollection ucList = GetTableConstraint(dataTable.TableName); if (ucList.Count == 0) { throw new Exception("表 " + 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 dataRow in dsTable.Tables[0].Rows) { string cName = dataRow["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 dataRow 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 = OracleDbTypeWrapper.WrapDbType(drTable["coltype"].ToString()); object o = dataRow.IsNull(cName2) ? null : dataRow[cName2]; paramList.Add(DbFactory <OracleConn, OracleFactory <OracleConn> > .MakeInParam(":" + cName2, dbt, Convert.ToInt32(drTable["WIDTH"]), o)); } parameters = paramList.ToArray(); IDbOperation db = new OracleExecute(); if (transaction == null) { db.ExecuteNonQuery(sql, parameters); } else { db.ExecuteNonQuery(transaction, sql, parameters); } } } catch (System.Exception ex) { throw new CustomDataException(ex, sql, parameters); } }
public void AutoInsert(DbTransaction transaction, DataTable dataTable) { DataSet dsTable = GetTableInfo(dataTable.TableName); if (null == dsTable || dsTable.Tables.Count == 0 || dsTable.Tables[0].Rows.Count == 0) { throw new Exception("未找到 " + dataTable.TableName + " 表的信息"); } StringBuilder sb1 = new StringBuilder(); StringBuilder sb2 = new StringBuilder(); foreach (DataRow drTable1 in dsTable.Tables[0].Rows) { string cName = drTable1["CNAME"].ToString(); if (dataTable.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 " + dataTable.TableName + " (" + sb1.ToString() + ") values (" + sb2.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 = OracleDbTypeWrapper.WrapDbType(drTable["coltype"].ToString()); object o = dr.IsNull(cName2) ? null : dr[cName2]; paramList.Add(DbFactory <OracleConn, OracleFactory <OracleConn> > .MakeInParam(":" + cName2, dbt, Convert.ToInt32(drTable["width"]), o)); } parameters = paramList.ToArray(); IDbOperation db = new OracleExecute(); if (transaction == null) { db.ExecuteNonQuery(sql, parameters); } else { db.ExecuteNonQuery(transaction, sql, parameters); } } } catch (System.Exception ex) { throw new CustomDataException(ex, sql, parameters); } }