//记录数
int recc = models.Count;
string[] PHONE = new string[recc];
string[] STATUS = new string[recc];
string[] PROVINCE = new string[recc];
DateTime[] ORDERTIME = new DateTime[recc];
// 为了传递参数,不可避免的要使用参数,下面会连续定义三个
// 从名称可以直接看出每个参数的含义,不在每个解释了
Oracle.DataAccess.Client.OracleParameter[] param = new Oracle.DataAccess.Client.OracleParameter[4];
Oracle.DataAccess.Client.OracleParameter phoneParam =new Oracle.DataAccess.Client.OracleParameter("PHONE" ,Oracle.DataAccess.Client.OracleDbType.Varchar2);
phoneParam.Direction = ParameterDirection.Input;
phoneParam.Value = PHONE;
param[0] = phoneParam;
Oracle.DataAccess.Client.OracleParameter statusParam =new Oracle.DataAccess.Client.OracleParameter("STATUS" , Oracle.DataAccess.Client.OracleDbType.Varchar2);
statusParam.Direction = ParameterDirection.Input;
statusParam.Value = STATUS;
param[1] = statusParam;
Oracle.DataAccess.Client.OracleParameter provinceParam =
new Oracle.DataAccess.Client.OracleParameter("PROVINCE" , Oracle.DataAccess.Client.OracleDbType.Varchar2);
provinceParam.Direction = ParameterDirection.Input;
provinceParam.Value = STATUS;
param[2] = provinceParam;
Oracle.DataAccess.Client.OracleParameter ordertimeParam =
new Oracle.DataAccess.Client.OracleParameter("ORDERTIME" , Oracle.DataAccess.Client.OracleDbType.Date);
ordertimeParam.Direction = ParameterDirection.Input;
ordertimeParam.Value = ORDERTIME;
param[3] = ordertimeParam;
//在下面的循环中,先把数组定义好,而不是直接生成SQL
for(int i = 0 ; i < recc ; i++)
{
PHONE[i] = models[i].PHONE;
STATUS[i] = models[i].STATUS;
PROVINCE[i] = models[i].PROVINCE;
ORDERTIME[i] =DateTime.Parse(models[i].ORDERTIME);
}
DbHelperOra.ExecuteSqlBat(recc , strSql.ToString() , param);
/// <summary>
/// 批量插入数据
/// </summary>
/// <param name="recordCount">记录数</param>
/// <param name="sql">sql</param>
/// <param name="param">参数</param>
public static void ExecuteSqlBat(int recordCount , string sql , params Oracle.DataAccess.Client.OracleParameter[] param)
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connectionString);
Oracle.DataAccess.Client.OracleCommand command = new Oracle.DataAccess.Client.OracleCommand();
command.Connection = conn;
conn.Open();
Oracle.DataAccess.Client.OracleTransaction tx = conn.BeginTransaction();
try
{
command.Transaction = tx;
//这个参数需要指定每次批插入的记录数
command.ArrayBindCount = recordCount;
//用到的是数组,而不是单个的值,这就是它独特的地方
command.CommandText = sql;
for(int i = 0 ; i < param.Length ; i++)
{
command.Parameters.Add(param[i]);
}
//这个调用将把参数数组传进SQL,同时写入数据库
command.ExecuteNonQuery();
tx.Commit();
}
catch(Exception ex)
{
tx.Rollback();
throw ex;
}
}
Oracle批量数据导入
最新推荐文章于 2023-12-04 13:21:42 发布