首页 > 代码库 > .net 调用Oracle.DataAccess.Client批量增加数据

.net 调用Oracle.DataAccess.Client批量增加数据

  

1、添加引用

using Oracle.DataAccess.Client;

using System.Configuration;

 2、代码 增加方法

//DestinationTableName 表名

//connectionString 连接

string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString;

config配置文件中 

<add name="DefaultConnectionString" connectionString="Data Source=库名;User ID=用户名;Password=密码;" providerName="System.Data.OracleClient"/>

//dataTable 数据

 public void Insert(string DestinationTableName, string connectionString, DataTable dataTable)
        {
            if (dataTable.Rows.Count == 0)
            {
                return;
            }

        //string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString;

         string strMainTableName = "表名";
         // 锁表防止并发产生重复数据 删除数据表已经存在的同一批数据
        string sSQLLock = "lock table  " + strMainTableName + " in share row exclusive mode";
        string strDeleSqlTemp = string.Format("delete from " + strMainTableName + " where ny=‘{0}‘ and dz in ({1})", strDate, strAdd);

        //连接

        using (OracleConnection conn = new OracleConnection(connectionString))
                        {
                            int recordCount = dtTemp.Rows.Count;//数据条数
                            conn.Open();//打开连接
                            OracleTransaction trans = conn.BeginTransaction();//连接打开事务

          try
                            {
                                //锁表
                                OracleCommand cmdLock = new OracleCommand(sSQLLock, conn);
                                cmdLock.ExecuteNonQuery();
                                //删除原有数据
                                OracleCommand cmdDele = new OracleCommand(strDeleSqlTemp, conn);
                                cmdDele.ExecuteNonQuery();

            //获取目标表表结构
                                DataTable DestDataTable = GetOracleTableSchema(strMainTableName);

           //获取insert 语句
                                string strSql = common.GenerateInserSql(strMainTableName, dtTemp, "");
                                OracleCommand cmd = new OracleCommand(strSql, conn);

           //参数赋值
                                common.GenerateParameter(DestDataTable, dtTemp, cmd);
                                cmd.Transaction = trans;
                                cmd.ArrayBindCount = recordCount;
                                cmd.BindByName = true;
                                cmd.ExecuteNonQuery();
                                trans.Commit(); //执行

          }
                            catch (Exception ex)
                            {
                                trans.Rollback();
                                strRetVal = common.CreateXml("0007", "错误信息:" + ex.Message);
                            }
                            finally
                            {
                                conn.Close();
                            }
                        }

 }

获取表列的方法GetOracleTableSchema,参数赋值方法GenerateInserSql在博客中详细说明http://www.cnblogs.com/xuxin-1989/p/4157697.html

.net 调用Oracle.DataAccess.Client批量增加数据