首页 > 代码库 > .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批量增加数据