/// <summary>
/// 获取绑定参数
/// </summary>
/// <param name="sql"></param>
/// <param name="loop"></param>
/// <returns></returns>
private string GetParam(string sql, int loop)
{
int intPara = 1;
for (int idx = sql.IndexOf("?"); idx > 0; idx = sql.IndexOf("?"))
{
sql = sql.Substring(0, idx) + ":" + loop.ToString() + (intPara++) + sql.Substring(idx + 1, sql.Length - idx - 1);
}
SqlLog.OutPutSql(sql);
return(sql);
}
/// <summary>
/// 获取Schema表
/// </summary>
/// <param name="sql"></param>
/// <param name="objValuesArr"></param>
/// <param name="objDbTypes"></param>
/// <returns></returns>
private DataTable GetTableSchema(string sql, object[][] objValuesArr, params DbType[] objDbTypes)
{
sql = sql.ToLower();
DataTable dtSchema = new DataTable();
System.Type[] enmType = ConvertDbTypeToSysType(objDbTypes);
int pos1 = sql.IndexOf("(");
int pos2 = sql.IndexOf(")");
string strTableName = sql.Substring(0, pos1).Replace("insert into", "").Trim();
dtSchema.TableName = strTableName;
string strCol = sql.Substring(pos1 + 1, pos2 - pos1 - 1);
string[] strCols = strCol.Split(',');
for (int i = 0; i < strCols.Length; i++)
{
dtSchema.Columns.Add(strCols[i], enmType[i]);
}
dtSchema.BeginLoadData();
StringBuilder sb = new StringBuilder();
for (int i = 0; i < objValuesArr[0].Length; i++)
{
object[] objCol = new object[objValuesArr.Length];
sb.Append("---> " + Convert.ToString(i + 1) + Environment.NewLine);
for (int j = 0; j < objValuesArr.Length; j++)
{
if (objValuesArr[j][i] == null)
{
objValuesArr[j][i] = System.DBNull.Value;
}
sb.Append(Convert.ToString(j + 1) + ":= " + objValuesArr[j][i] + "; ");
objCol[j] = objValuesArr[j][i];
}
dtSchema.LoadDataRow(objCol, true);
sb.AppendLine();
}
dtSchema.EndLoadData();
GetParm(sql, 0, true);
SqlLog.OutPutSql("values: " + sb.ToString());
return(dtSchema);
}
/// <summary>
/// ExecSQLForBatch
/// </summary>
/// <param name="cmd"></param>
/// <param name="sql"></param>
/// <param name="objValuesArr"></param>
/// <param name="objDbTypes"></param>
/// <returns></returns>
public int ExecSqlForBatch(OracleCommand cmd, string sql, ref int step, object[][] objValuesArr, params DbType[] objDbTypes)
{
int intAffectedRows = 0;
try
{
cmd.ArrayBindCount = objValuesArr[0].Length;
cmd.CommandText = GetParam(sql);
OracleDbType[] enmOracleDbType = ConvertDbTypeToOracleDbType(objDbTypes);
OracleParameter param = null;
for (int i = 0; i < enmOracleDbType.Length; i++)
{
param = new OracleParameter(Convert.ToString(i + 1 * step * 10), enmOracleDbType[i]);
param.Direction = ParameterDirection.Input;
param.Value = objValuesArr[i];
cmd.Parameters.Add(param);
}
StringBuilder sb = new StringBuilder();
for (int i = 0; i < objValuesArr[0].Length; i++)
{
sb.Append("---> " + Convert.ToString(i + 1) + Environment.NewLine);
for (int j = 0; j < objValuesArr.Length; j++)
{
if (objValuesArr[j][i] == null)
{
objValuesArr[j][i] = System.DBNull.Value;
}
sb.Append(Convert.ToString(j + 1) + ":= " + objValuesArr[j][i] + "; ");
}
sb.AppendLine();
}
SqlLog.OutPutSql("Values: " + sb.ToString());
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
intAffectedRows = cmd.ExecuteNonQuery();
}
catch (System.Exception objEx)
{
intAffectedRows = -1;
throw objEx;
}
return(intAffectedRows);
}
/// <summary>
/// 获取绑定参数
/// </summary>
/// <param name="sql"></param>
/// <param name="loop"></param>
/// <param name="isLog"></param>
/// <returns></returns>
private string GetParm(string sql, int loop, bool isLog)
{
int intPara = 1;
int i = -1;
for (int idx = sql.IndexOf("?"); idx > 0; idx = sql.IndexOf("?"))
{
if (loop == 0)
{
sql = sql.Substring(0, idx) + "@" + (intPara++) + sql.Substring(idx + 1, sql.Length - idx - 1);
}
else
{
sql = sql.Substring(0, idx) + "@" + ((++i) + 1 + loop * 10).ToString() + sql.Substring(idx + 1, sql.Length - idx - 1);
}
}
if (isLog)
{
SqlLog.OutPutSql(sql);
}
return(sql);
}
/// <summary>
/// ExecSQLForBatch
/// </summary>
/// <param name="cmd"></param>
/// <param name="sql"></param>
/// <param name="objValuesArr"></param>
/// <param name="objDbTypes"></param>
/// <returns></returns>
public int ExecSqlForBatch(string conn, EnumExecType execType, string sql, ref int step, object[][] objValuesArr, params DbType[] objDbTypes)
{
//EnumExecType execType = EnumExecType.ExecSql;
//if (typeStr == "1")
// execType = EnumExecType.ExecSql;
//else if (typeStr == "2")
// execType = EnumExecType.ExecSqlForBatch;
//else if (typeStr == "3")
// execType = EnumExecType.ExecSqlForBatchSimpleInsert;
//else if (typeStr == "4")
// execType = EnumExecType.ExecProc;
// 2100参数限制
int divNum = 2100;
SqlCommand[] sqlCommandArr = null;
if (objValuesArr[0].Length * objDbTypes.Length > 2100)
{
sqlCommandArr = new SqlCommand[((objValuesArr[0].Length * objDbTypes.Length) / 2100) + 1];
for (int m = 0; m < sqlCommandArr.Length; m++)
{
sqlCommandArr[m] = GetSqlCommand(conn, execType);
}
}
else
{
sqlCommandArr = new SqlCommand[1];
sqlCommandArr[0] = GetSqlCommand(conn, execType);
}
int intAffectedRows = 0;
int num = 0;
try
{
SqlDbType[] enmSqlDbType = ConvertDbTypeToSqlDbType(objDbTypes);
SqlParameter param = null;
StringBuilder sb = new StringBuilder();
for (int i = 0; i < objValuesArr[0].Length; i++)
{
sb.Append("---> " + Convert.ToString(i + 1) + Environment.NewLine);
for (int j = 0; j < objValuesArr.Length; j++)
{
if (objValuesArr[j][i] == null)
{
objValuesArr[j][i] = System.DBNull.Value;
}
sb.Append(Convert.ToString(j + 1) + ":= " + objValuesArr[j][i] + "; ");
}
sb.AppendLine();
}
int div = 0;
for (int i = 0; i < objValuesArr[0].Length; i++)
{
for (int j = 0; j < enmSqlDbType.Length; j++)
{
div = ((i + 1) * enmSqlDbType.Length) / divNum;
if (j + 1 < 10)
{
param = new SqlParameter(Convert.ToString((i + step) * 10) + Convert.ToString(j + 1), enmSqlDbType[j]);
}
else
{
param = new SqlParameter(Convert.ToString(i + step) + Convert.ToString(j + 1), enmSqlDbType[j]);
}
//param = new SqlParameter(Convert.ToString((i + step) * 10) + Convert.ToString(j + 1), enmSqlDbType[j]);
param.Direction = ParameterDirection.Input;
param.Value = objValuesArr[j][i];
sqlCommandArr[div].Parameters.Add(param);
}
sqlCommandArr[div].CommandText = GetParm(sql, (i + step) * 10, (i == 0));
if (i == 0)
{
SqlLog.OutPutSql("values: " + sb.ToString());
}
if (sqlCommandArr[div].Connection.State == ConnectionState.Closed)
{
sqlCommandArr[div].Connection.Open();
}
intAffectedRows += sqlCommandArr[div].ExecuteNonQuery();
num = i + step;
}
}
catch (System.Exception objEx)
{
intAffectedRows = -1;
throw objEx;
}
finally
{
step = num + 1;
}
return(intAffectedRows);
}
/// <summary>
/// 批量操作
/// </summary>
/// <param name="conn"></param>
/// <param name="sql"></param>
/// <param name="objValuesArr"></param>
/// <param name="objDbTypes"></param>
/// <returns></returns>
public int ExecSqlForBatch(string conn, string sql, ref int step, object[][] objValuesArr, params DbType[] objDbTypes)
{
SqlCommand cmd = GetSqlCommand(conn, EnumExecType.ExecSql);
int intAffectedRows = 0;
int num = 0;
try
{
SqlDbType[] enmSqlDbType = ConvertDbTypeToSqlDbType(objDbTypes);
SqlParameter param = null;
StringBuilder sb = new StringBuilder();
for (int i = 0; i < objValuesArr[0].Length; i++)
{
sb.Append("---> " + Convert.ToString(i + 1) + Environment.NewLine);
for (int j = 0; j < objValuesArr.Length; j++)
{
if (objValuesArr[j][i] == null)
{
objValuesArr[j][i] = System.DBNull.Value;
}
sb.Append(Convert.ToString(j + 1) + ":= " + objValuesArr[j][i] + "; ");
}
sb.AppendLine();
}
for (int i = 0; i < objValuesArr[0].Length; i++)
{
for (int j = 0; j < enmSqlDbType.Length; j++)
{
param = new SqlParameter(Convert.ToString((i + step) * 10) + Convert.ToString(j + 1), enmSqlDbType[j]);
param.Direction = ParameterDirection.Input;
param.Value = objValuesArr[j][i];
cmd.Parameters.Add(param);
}
cmd.CommandText = GetParm(sql, (i + step) * 10, (i == 0));
if (i == 0)
{
SqlLog.OutPutSql("values: " + sb.ToString());
}
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
intAffectedRows += cmd.ExecuteNonQuery();
num = i + step;
}
}
catch (System.Exception objEx)
{
intAffectedRows = -1;
throw objEx;
}
finally
{
step = num + 1;
if (cmd != null)
{
cmd.Connection.Close();
cmd.Dispose();
}
}
return(intAffectedRows);
}