private bool insertDataTable(DataTable dataTable, List <Structure> structList, string tableName)
{
allcount += dataTable.Rows.Count;
List <string> columnNameList = new List <string>();
foreach (DataColumn column in dataTable.Columns)
{
columnNameList.Add(column.ColumnName);
}
List <OracleParameter> oParams = new List <OracleParameter>();
Dictionary <string, object[]> oValues = new Dictionary <string, object[]>();
Dictionary <string, Structure> structures = new Dictionary <string, Structure>();
string sql_columns = "";
string sql_values = "";
foreach (string tablecolumnname in columnMap.Keys)
{
Structure structure = structList.FirstOrDefault(it => it.ColumnName == tablecolumnname);
if (structure == null)
{
continue;
}
string sourcecolumname = columnMap[tablecolumnname];
if (!columnNameList.Contains(sourcecolumname)) // 导入数据中，没有这个字段
{
continue;
}
// 搞出来SQL
// 搞Parameter
object[] oValue = new object[dataTable.Rows.Count];
OracleParameter oParam = new OracleParameter(tablecolumnname, getOracleDbType(structure.DataType));
oParam.Direction = ParameterDirection.Input;
oParam.Value = oValue;
oParams.Add(oParam);
oValues[sourcecolumname] = oValue;
structures[sourcecolumname] = structure;
sql_columns += tablecolumnname + ",";
sql_values += ":" + tablecolumnname + ",";
}
OracleParameter oParamCreateBy = new OracleParameter("CREATED_BY", OracleDbType.Varchar2);
oParamCreateBy.Direction = ParameterDirection.Input;
object[] CreateByValues = new object[dataTable.Rows.Count];
oParamCreateBy.Value = CreateByValues;
oParams.Add(oParamCreateBy);
OracleParameter oParamCreationDate = new OracleParameter("CREATION_DATE", OracleDbType.Varchar2);
oParamCreationDate.Direction = ParameterDirection.Input;
object[] CreationDateValues = new object[dataTable.Rows.Count];
oParamCreationDate.Value = CreationDateValues;
oParams.Add(oParamCreationDate);
OracleParameter oParamLastUpdateBy = new OracleParameter("LAST_UPDATED_BY", OracleDbType.Varchar2);
oParamLastUpdateBy.Direction = ParameterDirection.Input;
object[] ParamLastUpdateByValues = new object[dataTable.Rows.Count];
oParamLastUpdateBy.Value = ParamLastUpdateByValues;
oParams.Add(oParamLastUpdateBy);
OracleParameter oParamLastUpdateDate = new OracleParameter("LAST_UPDATE_DATE", OracleDbType.Varchar2);
oParamLastUpdateDate.Direction = ParameterDirection.Input;
object[] LastUpdateDateValues = new object[dataTable.Rows.Count];
oParamLastUpdateDate.Value = LastUpdateDateValues;
oParams.Add(oParamLastUpdateDate);
OracleParameter oParamProjectID = new OracleParameter("PROJECTID", OracleDbType.Varchar2);
oParamProjectID.Direction = ParameterDirection.Input;
object[] ProjectIDValues = new object[dataTable.Rows.Count];
oParamProjectID.Value = ProjectIDValues;
oParams.Add(oParamProjectID);
OracleParameter oParamID = new OracleParameter("TASKTIMES", OracleDbType.Int32);
oParamID.Direction = ParameterDirection.Input;
int[] TaskTimesValues = new int[dataTable.Rows.Count];
oParamID.Value = TaskTimesValues;
oParams.Add(oParamID);
string sql = string.Format("INSERT INTO {0}({1}CREATED_BY,LAST_UPDATED_BY,CREATION_DATE, LAST_UPDATE_DATE, PROJECTID,TASKTIMES) VALUES({2}:CREATED_BY,:LAST_UPDATED_BY,:CREATION_DATE,:LAST_UPDATE_DATE,:PROJECTID,:TASKTIMES)",
tableName, sql_columns, sql_values);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
foreach (string txtColumnName in structures.Keys)
{
var list = oValues[txtColumnName];
var structure = structures[txtColumnName];
list[i] = getRowValue(txtColumnName, structure, dataTable.Rows[i]);
}
CreateByValues[i] = this.userID;
CreationDateValues[i] = DateTime.Now;
ParamLastUpdateByValues[i] = this.userID;
LastUpdateDateValues[i] = DateTime.Now;
ProjectIDValues[i] = this.taskInfo.id;
TaskTimesValues[i] = this.times;
}
try
{
if (OracleAccess.ExecuteSqlBat(dataTable.Rows.Count, sql, oParams.ToArray()))
{
successCount += dataTable.Rows.Count;
}
//TableDAL.DropTable(tableName);
}
catch (System.Exception ex)
{
log.Error(string.Format("BetchLogic > run > insertDataTable > {0}", ex));
SendMessageEvent(false, "遇到异常：" + ex.ToString());
}
baseline += dataTable.Rows.Count;
int btcount = baseline;
return(true);
}