/// <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); }