1、我需要从c# 的datatable插入数据到oracle数据库。
2、datatable里面数据量比较大,总共27万条记录。
3、插入前需要先清空数据库的表。
4、要求插入效率比较高。
5、如果插入失败,回滚到插入前的表。求高人,给完整代码。最好通过以下代码改造,下面的代码可以运行,但效率很低。
public static int BatchInsert(string table, DataTable dt)
{
OracleConnection connOracle = new OracleConnection(connectionString);
connOracle.Open();
//Oracle.DataAccess.Client.OracleConnection connOracle = new Oracle.DataAccess.Client.OracleConnection(connectionString);
try
{
string strfields = "";
string strvalues = "";
string filed = "";
string strvalue = "";
int count = 0; OracleDataAdapter da = new OracleDataAdapter(); OracleCommandBuilder ob = new OracleCommandBuilder(da); for (int i = 0; i < dt.Columns.Count; i++)
{
string strfiled = dt.Columns[i].ColumnName;
strfields += strfiled + ",";
strvalues += ":" + strfiled + ",";
} if (strfields != "")
{
strfields = strfields.Substring(0, strfields.LastIndexOf(","));
strvalues = strvalues.Substring(0, strvalues.LastIndexOf(","));
} da.InsertCommand = new OracleCommand();
da.InsertCommand.Connection = connOracle;
da.InsertCommand.CommandText = "insert into " + table + "(" + strfields + ") values (" + strvalues + ")"; for (int i = 0; i < dt.Columns.Count; i++)
{
filed = dt.Columns[i].ColumnName;
strvalue = ":" + filed;
OracleParameter oparam = new OracleParameter();
oparam.ParameterName = strvalue;
oparam.SourceVersion = DataRowVersion.Current;
oparam.SourceColumn = filed;
da.InsertCommand.Parameters.Add(oparam);
} count = da.Update(dt); connOracle.Close(); return count;
}
catch (Exception ex)
{
string aa = ex.Message;
return 0;
}
}
2、datatable里面数据量比较大,总共27万条记录。
3、插入前需要先清空数据库的表。
4、要求插入效率比较高。
5、如果插入失败,回滚到插入前的表。求高人,给完整代码。最好通过以下代码改造,下面的代码可以运行,但效率很低。
public static int BatchInsert(string table, DataTable dt)
{
OracleConnection connOracle = new OracleConnection(connectionString);
connOracle.Open();
//Oracle.DataAccess.Client.OracleConnection connOracle = new Oracle.DataAccess.Client.OracleConnection(connectionString);
try
{
string strfields = "";
string strvalues = "";
string filed = "";
string strvalue = "";
int count = 0; OracleDataAdapter da = new OracleDataAdapter(); OracleCommandBuilder ob = new OracleCommandBuilder(da); for (int i = 0; i < dt.Columns.Count; i++)
{
string strfiled = dt.Columns[i].ColumnName;
strfields += strfiled + ",";
strvalues += ":" + strfiled + ",";
} if (strfields != "")
{
strfields = strfields.Substring(0, strfields.LastIndexOf(","));
strvalues = strvalues.Substring(0, strvalues.LastIndexOf(","));
} da.InsertCommand = new OracleCommand();
da.InsertCommand.Connection = connOracle;
da.InsertCommand.CommandText = "insert into " + table + "(" + strfields + ") values (" + strvalues + ")"; for (int i = 0; i < dt.Columns.Count; i++)
{
filed = dt.Columns[i].ColumnName;
strvalue = ":" + filed;
OracleParameter oparam = new OracleParameter();
oparam.ParameterName = strvalue;
oparam.SourceVersion = DataRowVersion.Current;
oparam.SourceColumn = filed;
da.InsertCommand.Parameters.Add(oparam);
} count = da.Update(dt); connOracle.Close(); return count;
}
catch (Exception ex)
{
string aa = ex.Message;
return 0;
}
}
解决方案 »
- 提一个关于Control的高难度问题,四个裤衩以上才能进.
- vs2003 asp.net 网站开发中怎样实现要求用户登陆后自动返回他刚才访问的那个页面?
- 求助 winform 中右键菜单的一个问题
- get,”必须声明主体,因为它未标记为 abstract 或 extern
- 水晶报表怎么添加一个可输入的text框?
- 怪问题???郁闷!散分!
- 调用VC的DLL参数类型问题
- 在VS2008中用C#生成基于windows的窗体控件库出现的问题,另外哪位大侠给点分吧,快支持不住了。多谢了
- C#窗体编程,菜单合并
- "无法创建的myRelation的子列表"?怎样实现当在datagrid1中选第i条记录时,datagrid2中也同时选中第i条记录?
- 新手请教NHibernate查询问题
- C# 关于BCD码的问题
SqlConnection sqlCnn = new SqlConnection(Class_mssql_conn.ConnStr);
sqlCnn.Open();
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = sqlCnn;
SqlTransaction st = sqlCnn.BeginTransaction(IsolationLevel.ReadCommitted);
sqlCmd.Transaction = st;
try
{
foreach (int i = 0; i < dt.Columns.Count; i++)
{
sqlCmd.CommandText = attendance.delhourSql(dt.Rows[0][0].Tostring());//此处为插入数据表的SQL语句
sqlCmd.ExecuteNonQuery();
}
st.Commit();
MessageBox.Show("保存成功", "信息", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
catch (Exception ex)
{
MessageBox.Show("保存失败", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally { sqlCnn.Close(); }
comm.Parameters.Add("@BatchNo", SqlDbType.NVarChar, 50).Value = v_BatchNO;
try
{
IDictionary<string, string> Mapping = new Dictionary<string, string>();
//datatable中有多少属性严格按序添加
Mapping.Add("ID", "ID");
…………
Mapping.Add("~~","~~");
int i = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteSqlBulkCopy(Mapping, "表名", 连接字符串, dataTable);
if (i == 0)
{
return false;
}
return true;
}
catch (SqlException ex)
{
//throw new Exception(ex.Message);
return false;
}
finally
{
conn.Close();
}
private void DoInsert()
{
DataTable dtSource = 你获取的DataTable//
ArrayList SQLStringList = new ArrayList(); SQLStringList.Add("Delete删除语句"); if (dtSource != null)
{
for (int i = 0; i < dtSource.Rows.Count; i++)
{
SQLStringList.Add("根据要求拼接的插入数据库预计");
}
} ExecuteSqlTran(SQLStringList);
}
/// <summary>
/// 执行多条SQL语句,实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
using (OracleConnection conn = new OracleConnection(connectionString))
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.OracleClient.OracleException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
}
可以 还是建议用储存过程。SQL2008是可以用DataTable做参数传入的。