因工作的原因,要使用winform来处理大量的数据,但是c#自带的System.data.OracleClient效率不是很高,在网上找了很久,找到了ODP.NET,是oracle为c#提供的。貌似从vs2010开始,微软开始推荐使用ODP.NET。效率的话,在没有索引的情况下,100万数据,不到10秒。 1.从官网上下载ODAC,如果你是32位的机器,那下载32的;64位的,就下载64的。我的win7, 64位,所以我下载的是ODAC1120320_x64,具体地址: 64位:http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html 32位:http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html 2.解压,然后点击 setup.exe 安装,然后在这个地址:D:\app\12\product\11.2.0\client_1\odp.net\bin\2.x ![](https://i-blog.csdnimg.cn/blog_migrate/c52bba448374a5aa6873e7c7ca0ec69d.png) 双击 OraProvCfg.exe,会自动配置环境 ![](https://i-blog.csdnimg.cn/blog_migrate/1a6cac3dcd48837964b51cba9f806265.png) 3.在安装的目录下,依次找到以下dll: oci.dll ociw32.dll Oracle.DataAccess.dll orannzsbb11.dll oraocci11.dll oraociicus11.dll OraOps11w.dll 然后将这些dll放到bin\debug目录下(这里是c/s项目,b/s的话貌似放在bin目录下) 4.在项目中,添加引用,就可以使用了,用法跟自带的System.data.OracleClient差不多 ![](https://i-blog.csdnimg.cn/blog_migrate/89ed28c71d724e84c73106fbfe57fd1b.png) 5.批量插入:
-
- string connectStr = "User Id=scott;Password=tiger;Data Source=";
- OracleConnection conn = new OracleConnection(connectStr);
- OracleCommand command = new OracleCommand();
- command.Connection = conn;
-
- command.ArrayBindCount = recc;
-
-
- command.CommandText = "insert into dept values(:deptno, :deptname, :loc)";
- conn.Open();
-
- int[] deptNo = new int[recc];
- string[] dname = new string[recc];
- string[] loc = new string[recc];
-
-
- OracleParameter deptNoParam = new OracleParameter("deptno",
- OracleDbType.Int32);
- deptNoParam.Direction = ParameterDirection.Input;
- deptNoParam.Value = deptNo; command.Parameters.Add(deptNoParam);
- OracleParameter deptNameParam = new OracleParameter("deptname",
- OracleDbType.Varchar2);
- deptNameParam.Direction = ParameterDirection.Input;
- deptNameParam.Value = dname;
- command.Parameters.Add(deptNameParam);
- OracleParameter deptLocParam = new OracleParameter("loc", OracleDbType.Varchar2);
- deptLocParam.Direction = ParameterDirection.Input;
- deptLocParam.Value = loc;
- command.Parameters.Add(deptLocParam);
- Stopwatch sw = new Stopwatch();
- sw.Start();
-
- for (int i = 0; i < recc; i++)
- {
- deptNo[i] = i;
- dname[i] = i.ToString();
- loc[i] = i.ToString();
- }
-
- command.ExecuteNonQuery();
- sw.Stop();
- System.Diagnostics.Debug.WriteLine("批量插入:" + recc.ToString()
- + "所占时间:" +sw.ElapsedMilliseconds.ToString());
6.上面的代码太乱,给一个已经封装好的批量插入的方法:
-
-
-
-
-
-
-
- public static int BatchInsert(string tableName, Dictionary<string, object> columnRowData, string conStr, int len)
- {
- if (string.IsNullOrEmpty(tableName))
- {
- throw new ArgumentException("必须指定批量插入的表名称", "tableName");
- }
-
- if (columnRowData == null || columnRowData.Count < 1)
- {
- throw new ArgumentException("必须指定批量插入的字段名称", "columnRowData");
- }
-
- int iResult = 0;
- string[] dbColumns = columnRowData.Keys.ToArray();
- StringBuilder sbCmdText = new StringBuilder();
- if (columnRowData.Count > 0)
- {
-
- sbCmdText.AppendFormat("INSERT INTO {0}(", tableName);
- sbCmdText.Append(string.Join(",", dbColumns));
- sbCmdText.Append(") VALUES (");
- sbCmdText.Append(":" + string.Join(",:", dbColumns));
- sbCmdText.Append(")");
-
- using (OracleConnection conn = new OracleConnection(conStr))
- {
- using (OracleCommand cmd = conn.CreateCommand())
- {
-
- cmd.ArrayBindCount = len;
- cmd.BindByName = true;
- cmd.CommandType = CommandType.Text;
- cmd.CommandText = sbCmdText.ToString();
- cmd.CommandTimeout = 600;
-
-
- OracleParameter oraParam;
- List<IDbDataParameter> cacher = new List<IDbDataParameter>();
- OracleDbType dbType = OracleDbType.Object;
- foreach (string colName in dbColumns)
- {
- dbType = GetOracleDbType(columnRowData[colName]);
- oraParam = new OracleParameter(colName, dbType);
- oraParam.Direction = ParameterDirection.Input;
- oraParam.OracleDbTypeEx = dbType;
-
- oraParam.Value = columnRowData[colName];
- cmd.Parameters.Add(oraParam);
- }
-
- conn.Open();
-
-
- var trans = conn.BeginTransaction();
- try
- {
- cmd.Transaction = trans;
- iResult = cmd.ExecuteNonQuery();
- trans.Commit();
- }
- catch (Exception ex)
- {
- trans.Rollback();
- throw ex;
- }
- finally
- {
- if (conn != null) conn.Close();
- }
-
- }
- }
- }
- return iResult;
- }
-
-
-
-
- private static OracleDbType GetOracleDbType(object value)
- {
- OracleDbType dataType = OracleDbType.Object;
- if (value is string[])
- {
- dataType = OracleDbType.Varchar2;
- }
- else if (value is DateTime[])
- {
- dataType = OracleDbType.TimeStamp;
- }
- else if (value is int[] || value is short[])
- {
- dataType = OracleDbType.Int32;
- }
- else if (value is long[])
- {
- dataType = OracleDbType.Int64;
- }
- else if (value is decimal[] || value is double[] || value is float[])
- {
- dataType = OracleDbType.Decimal;
- }
- else if (value is Guid[])
- {
- dataType = OracleDbType.Varchar2;
- }
- else if (value is bool[] || value is Boolean[])
- {
- dataType = OracleDbType.Byte;
- }
- else if (value is byte[])
- {
- dataType = OracleDbType.Blob;
- }
- else if (value is char[])
- {
- dataType = OracleDbType.Char;
- }
- return dataType;
- }
7.调用封装的方法: ![](https://i-blog.csdnimg.cn/blog_migrate/4448e10d1d3fd1988aa1e74d5406c025.png) 8.完成。 对于服务器上的oracle版本问题,我们的是10g,但是我用的ODP是11g的,还是可以插入数据,没什么问题,貌似可以向下兼容 |