Beispiel #1
0
        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();
                }
            }
        }
Beispiel #2
0
        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);
            }
        }
Beispiel #3
0
        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);
            }
        }