.net 调用Oracle.Data.Access 组件提供的用于批量操作的方法—获取数据库表结构方法和跟参数赋值方法

1、/// <summary>        

/// 获取当前目标表结构       

/// </summary>        

/// <param name="tableName">目标表</param>        

/// <returns></returns>

        public static DataTable GetOracleTableSchema(string tableName)      

   {             BS.EAP.DBAccess.IDataBase oDB = BS.EAP.DBAccess.DBFactory.GetDBInstance();    

         string sql = string.Format(@"             select t.COLUMN_NAME as name, t.DATA_LENGTH as ColLength,t.DATA_TYPE as Typeid, t.DATA_SCALE as Scale,decode(nvl(tp.column_name, ''),'',0,1) as isPrimary,                    '{0}' as tableName, '{0}' as viewName, 0 as viewField,                    (case t.NULLABLE when 'Y' then 1 else 0 end) as isNullable, tp.column_name             from USER_TAB_COLS t             left join (select col.table_name, col.column_name                  from user_constraints con,  user_cons_columns col                  where con.constraint_name = col.constraint_name and con.constraint_type='P'                  ) tp on tp.table_name = t.TABLE_NAME and tp.column_name = t.column_name             where  t.HIDDEN_COLUMN ='NO' and t.TABLE_NAME = '{0}'", tableName.ToUpper());

            return oDB.GetDataTable(sql);      

   }

 

 2、/// <summary>
        /// 生成批量插入Sql语句
        /// </summary>
        /// <param name="DestinationTableName">目标表</param>
        /// <param name="table">数据源</param>
        /// <param name="cmd">操作命令</param>
        /// <param name="DicInitData">初始数据</param>
        /// <returns></returns>
        public static string GenerateInserSql(string DestinationTableName, DataTable table, string strDsid)
        {
            BS.EAP.DBAccess.IDataBase oDB = DBFactory.GetDBInstance();
            DataTable dt = new DataTable();
            string column_name = string.Empty;
            string param = string.Empty;
            //查询表机构字段
            dt = oDB.GetDataTable(@"select column_name EnKey from 
                                        user_tab_columns   where  lower(table_name)=   '" + DestinationTableName.ToLower() + "' order by COLUMN_ID");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                column_name += dt.Rows[i][0].ToString() + ",";
                param += ":" + dt.Rows[i][0].ToString() + ",";
            }
            column_name = column_name.Substring(0, column_name.Length - 1);
            param = param.Substring(0, param.Length - 1);
            return string.Format("insert into " + DestinationTableName + "(" + column_name + ") values(" + param + ")");
        }

 

3、/// <summary>
        /// 构造参数
        /// </summary>
        /// <param name="DestDataTable"></param>
        /// <param name="table"></param>
        /// <param name="cmd"></param>
        public static void GenerateParameter(DataTable DestDataTable, DataTable table, OracleCommand cmd, string Dsid, string TableName, DataTable dbwell)
        {
            string strTypID = string.Empty;
            string strColumName = string.Empty;
            string IsRep = string.Empty;
            int colCount = table.Columns.Count;
            for (int i = 0; i < colCount; i++)
            {
                    strColumName = table.Columns[i].ColumnName;
                    OracleParameter parameter = new OracleParameter();
                    parameter.ParameterName = strColumName;
                    parameter.Direction = ParameterDirection.Input;
                    int iScale = 0;
                    strTypID = GetColumnType(strColumName, DestDataTable, out iScale);
                    DbType oDbType = GetDataFieldType(strTypID, iScale);
                    parameter.DbType = oDbType;
                     parameter.Value = GetObjectArray(table, strColumName, oDbType, dbwell);
                    cmd.Parameters.Add(parameter);
            }
        }

 

4、GetColumnType方法

  /// <summary>        

/// 获取字段类型         /// </summary>   

/// <param name="columnName">字段名称</param>    

/// <param name="dt">数据表</param>        

/// <returns></returns>        

public static string GetColumnType(string columnName, DataTable dt, out int iScale)        

{            

string strType = string.Empty;            

iScale = 0;            

for (int iRow = 0; iRow < dt.Rows.Count; iRow++)            

{                

string strColName = dt.Rows[iRow]["Name"].ToString();                

if (strColName.ToUpper() == columnName.ToUpper())                

{                    

strType = dt.Rows[iRow]["TypeID"].ToString();                    

if (dt.Rows[iRow]["SCALE"].ToString() == "" || dt.Rows[iRow]["SCALE"].ToString() == "0")                        

iScale = 0;                    

else                        

iScale = Convert.ToInt32(dt.Rows[iRow]["SCALE"].ToString());           

          break;                

}            

}

   return strType;       

}

 

5、// <summary>
        /// 获取字段类型
        /// </summary>
        /// <param name="strType">字段类型</param>
        /// <param name="iScale">小数位数</param>
        /// <returns></returns>
        public static DbType GetDataFieldType(string strType, int iScale)
        {
            DbType oDbType = DbType.String;
            switch (strType.ToUpper())
            {
                case "VARCHAR2":
                    oDbType = DbType.String;
                    break;
                case "DATE":
                    oDbType = DbType.DateTime;
                    break;
                case "NVARCHAR2":
                    oDbType = DbType.String;
                    break;
                case "NUMBER":
                    if (iScale == 0)
                        oDbType = DbType.Int32;
                    else
                        oDbType = DbType.Decimal;
                    break;
                case "FLOAT":
                    oDbType = DbType.Decimal;
                    break;
                case "LONG":
                    oDbType = DbType.Decimal;
                    break;
                case "BINARY_FLOAT":
                    oDbType = DbType.Decimal;
                    break;
                case "BINARY_DOUBLE":
                    oDbType = DbType.Decimal;
                    break;
                default:
                    break;
            }
            return oDbType;
        }

 

6、/// <summary>
        ///  构造值数组,供赋值使用
        /// </summary>
        /// <param name="dt">数据源表</param>
        /// <param name="colname">列名</param>
        /// <returns></returns>
        public static object[] GetObjectArray(DataTable dt, string colname, DbType oDbType, DataTable dbwell)
        {
            object[] objArray = new object[dt.Rows.Count];
            int iCol = 0;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (dt.Columns[i].ColumnName == colname)
                {
                    iCol = i;
                    break;
                }
            }
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                    if (string.IsNullOrEmpty(dt.Rows[j][iCol].ToString()))
                        objArray[j] = DBNull.Value;
                    else
                        objArray[j] = GetObjValue(dt.Rows[j][iCol], oDbType);
            }
            return objArray;
        }