public TableTypeParameter CreateParameter(string p_paramName, object p_paramValue) { TableTypeParameter param = new TableTypeParameter(); { param.ParameterName = p_paramName; param.ParameterValue = p_paramValue; }; return param; }
private void BatchImportDatabase(DataSet ds) { try { if (ds != null && ds.Tables != null && ds.Tables.Count > 0 ) { string Qes1PutTime = "1"; string Qes2PutTime = "5"; Guid guid = Guid.NewGuid(); string strGuid = guid.ToString(); string strUserName = string.Empty; DLL.User loginUser = Session["dsUser"] as DLL.User; if (loginUser == null) { strUserName = loginUser.Name; } string sql = "insert into hbh_t_importData (GUID,aboutAgeBegin,aboutAgeEnd,message_Title,message_content,messDate)values(?,?,?,?,?,now());"; // 每个表导入一次 foreach (DataTable table in ds.Tables) { if (table != null // Sheet名不可为空 && !PubClass.IsNull(table.TableName) // Sheet名不可包含忽略字符 && !table.TableName.Contains("#") // 有行 && table.Rows != null && table.Rows.Count > 0 && table.Columns != null // 多余两列 && table.Columns.Count >= 2 ) { int totalCount = table.Rows.Count; string strTableName = table.TableName; int BatchSize = 2000; int loopNumber = PubClass.GetInt(Math.Ceiling(((decimal)totalCount / BatchSize))); for (int j = 1; j <= loopNumber; j++) { int curCount = Math.Min(totalCount - BatchSize * (j - 1), BatchSize); int preBatchIndex = BatchSize * (j - 1); //TableTypeParameter[][] sqlParamArray = new TableTypeParameter[table.Rows.Count][]; TableTypeParameter[][] sqlParamArray = new TableTypeParameter[curCount][]; //foreach (DataRow row in table.Rows) //for (int i = 0; i < table.Rows.Count; i++) string month = "";//记录上条记录的月份 string LastMonth = ""; //记录上条记录的月份 int QestingSequence = 1;//记录当前记录是本月的第几周推送 int inQty = 2;//存储几周推送一镒 for (int i = 0; i < curCount; i++) { DataRow row = table.Rows[preBatchIndex + i]; TableTypeParameter[] curParamArr = new TableTypeParameter[5]; //当本记录的月分为空时表明单元格合并了取不到数据。那本记录的月分=上条记录的月份。 if (!string.IsNullOrEmpty(row[0].ToString().Trim())) { month = row[0].ToString().Trim(); } if (!string.IsNullOrEmpty(LastMonth)) { if (LastMonth.Equals(month)) { QestingSequence += inQty; } else { QestingSequence = 1; LastMonth = month; } } else LastMonth = month; //string sql = "insert into hbh_t_importMessageData (GUID,aboutAgeBegin,aboutAgeEnd,message_Title,message_content,messDate)values(?,?,?,?,?,now());"; int endDay = QestingSequence + 1;//时间段结束 curParamArr[0] = new TableTypeParameter("GUID", strGuid); curParamArr[1] = new TableTypeParameter("aboutAgeBegin", strTableName + month + QestingSequence + "周"); curParamArr[2] = new TableTypeParameter("aboutAgeEnd", strTableName + month + endDay + "周"); string row_title = ""; if (string.IsNullOrEmpty(row[1].ToString().Trim())) row_title = ""; else row_title = row[1].ToString().Trim(); string row_content = ""; if (string.IsNullOrEmpty(row[2].ToString().Trim())) row_content = ""; else row_content = row[2].ToString().Trim(); curParamArr[3] = new TableTypeParameter("message_Title", row_title); curParamArr[4] = new TableTypeParameter("message_content", row_content); sqlParamArray[i] = curParamArr; } DatabaseAdapter sqlHelper = new DatabaseAdapter(this); sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, sql, sqlParamArray); } } } // ,concat(Content,'(',AgeGroupName,')') // ,(length(Sequence) - length(replace(Sequence,'.',''))) ,length(Sequence),length(replace(Sequence,'.','')) /* // 效率有问题,所以换成增临时表来处理 -- 目录-更新 update T_Menu,T_ImportData imp2 set T_Menu.Code = imp2.Content ,T_Menu.Name = concat(imp2.Sequence,' ',imp2.Content) ,T_Menu.AgeGroupName = imp2.AgeGroupName where 1=1 and T_Menu.Sequence = imp2.Sequence and imp2.GUID = '{0}' and (length(imp2.Sequence) - length(replace(imp2.Sequence,'.',''))) < 3 ; */ string strProcName = "hbh_proc_ImportSubQuestionData"; // 为了效率,改为存储过程 //string updateText = string.Format(updateSql, strGuid); string updateText = string.Format("call {0}('{1}');", strProcName, strGuid); DatabaseAdapter sqlHelper2 = new DatabaseAdapter(this); sqlHelper2.DbHelper.ExecuteNonQuery(CommandType.Text, updateText); } } catch (Exception ex) { Response.Write(string.Format("<script>alert('{0}')</script> ",ex.Message)); } }
private void BatchImportDatabase(DataSet ds) { try { if (ds != null && ds.Tables != null && ds.Tables.Count > 0 ) { string Qes1PutTime = "1"; string Qes2PutTime = "5"; Guid guid = Guid.NewGuid(); string strGuid = guid.ToString(); string strUserName = string.Empty; DLL.User loginUser = Session["dsUser"] as DLL.User; if (loginUser == null) { strUserName = loginUser.Name; } string sql = "insert into hbh_t_importData (GUID,aboutAgeBegin,aboutAgeEnd,message_Title,message_content,messDate)values(?,?,?,?,?,now());"; // 每个表导入一次 foreach (DataTable table in ds.Tables) { if (table != null // Sheet名不可为空 && !PubClass.IsNull(table.TableName) // Sheet名不可包含忽略字符 && !table.TableName.Contains("#") // 有行 && table.Rows != null && table.Rows.Count > 0 && table.Columns != null // 多余两列 && table.Columns.Count >= 2 ) { int totalCount = table.Rows.Count; string strTableName = table.TableName; int BatchSize = 2000; int loopNumber = PubClass.GetInt(Math.Ceiling(((decimal)totalCount / BatchSize))); for (int j = 1; j <= loopNumber; j++) { int curCount = Math.Min(totalCount - BatchSize * (j - 1), BatchSize); int preBatchIndex = BatchSize * (j - 1); //TableTypeParameter[][] sqlParamArray = new TableTypeParameter[table.Rows.Count][]; TableTypeParameter[][] sqlParamArray = new TableTypeParameter[curCount][]; //foreach (DataRow row in table.Rows) //for (int i = 0; i < table.Rows.Count; i++) string month = "";//记录上条记录的月份 string week = "";//记录上条记录的周 string MW = ""; //记录上条记录的月份+周 int QestingSequence = 1;//记录当前记录是本周的第几天推送消息 int inDay = 4;//存储天数 for (int i = 0; i < curCount; i++) { DataRow row = table.Rows[preBatchIndex + i]; TableTypeParameter[] curParamArr = new TableTypeParameter[5]; //当本记录的月分为空时表明单元格合并了取不到数据。那本记录的月分=上条记录的月份。 if (!string.IsNullOrEmpty(row[0].ToString())) { month = row[0].ToString(); } //当本记录的周为空时表明单元格合并了取不到数据。那本记录的周=上条记录的周。 if (!string.IsNullOrEmpty(row[1].ToString())) { week = row[1].ToString(); if (!string.IsNullOrEmpty(week)) { if (week.Equals("第一周") || week.Equals("第1周")) { week = "1周"; } else if (week.Equals("第二周") || week.Equals("第2周")) { week = "2周"; } else if (week.Equals("第三周") || week.Equals("第3周")) { week = "3周"; } else if (week.Equals("第四周") || week.Equals("第4周")) { week = "4周"; } } } if (!string.IsNullOrEmpty(MW)) { if (MW.Equals(month + week)) { QestingSequence += inDay; } else { QestingSequence = 1; MW = month + week; } } else MW = month + week; //string sql = "insert into hbh_t_importMessageData (GUID,aboutAgeBegin,aboutAgeEnd,message_Title,message_content,messDate)values(?,?,?,?,?,now());"; int endDay = 0;//时间段结束 if (QestingSequence == 1) { endDay = QestingSequence + 3; } else { endDay = QestingSequence + 2; } curParamArr[0] = new TableTypeParameter("GUID", strGuid); curParamArr[1] = new TableTypeParameter("aboutAgeBegin", strTableName + month + week + "第" + QestingSequence + "天"); curParamArr[2] = new TableTypeParameter("aboutAgeEnd", strTableName + month + week + "第" + endDay + "天"); string row_title = ""; if (string.IsNullOrEmpty(row[2].ToString().Trim())) row_title = ""; else row_title = row[2].ToString().Trim(); string row_content = ""; if (string.IsNullOrEmpty(row[3].ToString().Trim())) row_content = ""; else row_content = row[3].ToString().Trim(); curParamArr[3] = new TableTypeParameter("message_Title", row_title); curParamArr[4] = new TableTypeParameter("message_content", row_content); sqlParamArray[i] = curParamArr; } DatabaseAdapter sqlHelper = new DatabaseAdapter(this); sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, sql, sqlParamArray); } } } // ,concat(Content,'(',AgeGroupName,')') // ,(length(Sequence) - length(replace(Sequence,'.',''))) ,length(Sequence),length(replace(Sequence,'.','')) string strProcName = "hbh_proc_ImportMessageData"; // 为了效率,改为存储过程 //string updateText = string.Format(updateSql, strGuid); string updateText = string.Format("call {0}('{1}');", strProcName, strGuid); DatabaseAdapter sqlHelper2 = new DatabaseAdapter(this); sqlHelper2.DbHelper.ExecuteNonQuery(CommandType.Text, updateText); } } catch (Exception ex) { Response.Write(string.Format("<script>alert('{0}')</script> ", ex.Message)); } }
public void Fill(DataTable dt, CommandType p_CommandType, string p_CommandText, TableTypeParameter[] p_TableParams, SqlParameter[] p_SqlParams) { SqlConnection connection = null; SqlCommand command = null; SqlDataAdapter adapter = null; try { connection = new SqlConnection { ConnectionString = this._sqlConnStr }; OpenConnection(connection); command = new SqlCommand { Connection = connection, CommandText = p_CommandText, CommandType = p_CommandType, CommandTimeout = this._commandTimeout, Transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted) }; if ((p_TableParams != null) && (p_TableParams.Length > 0)) { foreach (TableTypeParameter parameter in p_TableParams) { command.Parameters.AddWithValue(parameter.ParameterName, parameter.ParameterValue); } } if ((p_SqlParams != null) && (p_SqlParams.Length > 0)) { foreach (SqlParameter parameter2 in p_SqlParams) { command.Parameters.Add(parameter2); } } adapter = new SqlDataAdapter { SelectCommand = command }; adapter.Fill(dt); } catch (Exception exception) { if (command != null && command.Transaction != null ) { command.Transaction.Rollback(); } throw exception; } finally { CloseConnection(connection); if (command != null) { command.Dispose(); } if (adapter != null) { adapter.Dispose(); } } }
public int ExecuteNonQuery(CommandType p_CommandType, string p_CommandText, TableTypeParameter[][] p_Parameters) { int result = 0; SqlConnection connection = null; SqlCommand command = null; SqlTransaction transaction = null; try { connection = new SqlConnection { ConnectionString = this._sqlConnStr }; OpenConnection(connection); transaction = connection.BeginTransaction(); for (int i = 0; i < p_Parameters.Length; i++) { command = new SqlCommand(p_CommandText, connection, transaction) { CommandType = p_CommandType, CommandTimeout = this._commandTimeout }; if (p_Parameters[i] != null) { if (p_Parameters[i].Length != 0) { //foreach (SqlParameter parameter in p_Parameters[i]) //{ // command.Parameters.Add(parameter); //} foreach (TableTypeParameter parameter in p_Parameters[i]) { command.Parameters.AddWithValue(parameter.ParameterName, parameter.ParameterValue); } } result = command.ExecuteNonQuery(); } } transaction.Commit(); } catch (Exception exception) { if (command != null && command.Transaction != null ) { command.Transaction.Rollback(); } throw exception; } finally { CloseConnection(connection); if (command != null) { command.Dispose(); } if (transaction != null) { transaction.Dispose(); } } return result; }
private void BatchImportDatabase(DataSet ds) { if (ds != null && ds.Tables != null && ds.Tables.Count > 0 ) { Guid guid = Guid.NewGuid(); string strGuid = guid.ToString(); string strUserName = string.Empty; DLL.User loginUser = Session["dsUser"] as DLL.User; if (loginUser == null) { strUserName = loginUser.Name; } string sql = "insert into T_ImportData (Sequence,Content,GUID,AgeGroupName,CreatedBy,CreatedOn,KeyWords)values(?,?,?,?,?,now(),?);"; // 每个表导入一次 foreach (DataTable table in ds.Tables) { if (table != null // Sheet名不可为空 && !PubClass.IsNull(table.TableName) // Sheet名不可包含忽略字符 && !table.TableName.Contains(Const_ExcelIgnoreFlag) // 有行 && table.Rows != null && table.Rows.Count > 0 && table.Columns != null // 多余两列 && table.Columns.Count >= 2 ) { int totalCount = table.Rows.Count; string strTableName = table.TableName; int BatchSize = 2000; int loopNumber = PubClass.GetInt(Math.Ceiling(((decimal)totalCount / BatchSize))); for (int j = 1; j <= loopNumber; j++) { int curCount = Math.Min(totalCount - BatchSize * (j - 1), BatchSize); int preBatchIndex = BatchSize * (j - 1); //TableTypeParameter[][] sqlParamArray = new TableTypeParameter[table.Rows.Count][]; TableTypeParameter[][] sqlParamArray = new TableTypeParameter[curCount][]; //foreach (DataRow row in table.Rows) //for (int i = 0; i < table.Rows.Count; i++) for (int i = 0; i < curCount; i++) { DataRow row = table.Rows[preBatchIndex + i]; TableTypeParameter[] curParamArr = new TableTypeParameter[6]; //string sql = "insert into T_ImportData (Sequence,Content,GUID,AgeGroupName,CreatedBy,CreatedOn)values(?,?,?,?,?,now());"; curParamArr[0] = new TableTypeParameter("Sequence", row[0]); curParamArr[1] = new TableTypeParameter("Content", row[1]); curParamArr[2] = new TableTypeParameter("GUID", strGuid); curParamArr[3] = new TableTypeParameter("AgeGroupName", strTableName); curParamArr[4] = new TableTypeParameter("CreatedBy", strUserName); if (table.Columns.Count >= 3) { curParamArr[5] = new TableTypeParameter("KeyWords", row[2]); } else { curParamArr[5] = new TableTypeParameter("KeyWords", ""); ; } sqlParamArray[i] = curParamArr; } DatabaseAdapter sqlHelper = new DatabaseAdapter(this); sqlHelper.DbHelper.ExecuteNonQuery(CommandType.Text, sql, sqlParamArray); } } } // ,concat(Content,'(',AgeGroupName,')') // ,(length(Sequence) - length(replace(Sequence,'.',''))) ,length(Sequence),length(replace(Sequence,'.','')) string updateSql = @" -- 年龄段-新增 insert into T_AgeGroup ( Code,Name,Memo ) select distinct AgeGroupName,AgeGroupName,null from T_ImportData where GUID = '{0}' and AgeGroupName not in (select tb.Code from T_AgeGroup tb) order by AgeGroupName ; -- 创建需更新Menu目录的临时表 drop table if exists tmp_NeedUpdateMenu ; create temporary table tmp_NeedUpdateMenu ( select T_Menu.ID,imp2.Content as Code,concat(imp2.Sequence,' ',imp2.Content) as Name,imp2.AgeGroupName as AgeGroupName from T_Menu,T_ImportData imp2 where 1=1 and T_Menu.Sequence = imp2.Sequence and imp2.GUID = '{0}' and (length(imp2.Sequence) - length(replace(imp2.Sequence,'.',''))) < 3 and (T_Menu.Code != imp2.Content or T_Menu.Name != concat(imp2.Sequence,' ',imp2.Content) or T_Menu.AgeGroupName != imp2.AgeGroupName ) ) ; -- 目录-更新 update T_Menu,tmp_NeedUpdateMenu imp2 set T_Menu.Code = imp2.Code ,T_Menu.Name = imp2.Name ,T_Menu.AgeGroupName = imp2.AgeGroupName where T_Menu.ID = imp2.ID ; xz -- 目录-新增 insert into T_Menu ( Sequence,Code,Name,ParentMenu,AgeGroupName ) select Sequence,Content,concat(Sequence,' ',Content),null,AgeGroupName from T_ImportData where GUID = '{0}' and (length(Sequence) - length(replace(Sequence,'.',''))) < 3 and Sequence not in (select tb.Sequence from T_Menu tb) order by Sequence ; -- 更新父目录ID drop table if exists tmp_menu ; create table tmp_menu as select ID,Sequence from T_Menu ; update T_Menu set ParentMenu = (select min(parent.ID) from tmp_menu parent where parent.Sequence = substring_index(T_Menu.Sequence,'.' ,(length(T_Menu.Sequence) - length(replace(T_Menu.Sequence,'.',''))) ) ) where ParentMenu is null ; drop table if exists tmp_menu ; -- 问题 insert into T_Question ( Sequence,Title,Description,KeyWords,AgeGroupName,ParentMenu ) select Sequence,Content,Content,KeyWords,AgeGroupName,(select min(parent.ID) from T_Menu parent where parent.Sequence = substring_index(T_ImportData.Sequence,'.' ,(length(T_ImportData.Sequence) - length(replace(T_ImportData.Sequence,'.',''))) ) ) from T_ImportData where GUID = '{0}' and (length(Sequence) - length(replace(Sequence,'.',''))) = 3 and Sequence not in (select tb.Sequence from T_Question tb) order by Sequence ; -- 答案 insert into T_Solution ( Sequence,SText,Intro,Question ) select Sequence,Content,null,(select min(parent.ID) from T_Question parent where parent.Sequence = substring_index(T_ImportData.Sequence,'.' ,(length(T_ImportData.Sequence) - length(replace(T_ImportData.Sequence,'.',''))) ) ) from T_ImportData where GUID = '{0}' and (length(Sequence) - length(replace(Sequence,'.',''))) > 3 and Sequence not in (select tb.Sequence from T_Solution tb) order by Sequence ; -- 更新年龄段表Sequence update T_AgeGroup,T_Menu menu set T_AgeGroup.Sequence = menu.Sequence where T_AgeGroup.Code = menu.AgeGroupName and (length(menu.Sequence) - length(replace(menu.Sequence,'.',''))) = 0 ; "; /* // 效率有问题,所以换成增临时表来处理 -- 目录-更新 update T_Menu,T_ImportData imp2 set T_Menu.Code = imp2.Content ,T_Menu.Name = concat(imp2.Sequence,' ',imp2.Content) ,T_Menu.AgeGroupName = imp2.AgeGroupName where 1=1 and T_Menu.Sequence = imp2.Sequence and imp2.GUID = '{0}' and (length(imp2.Sequence) - length(replace(imp2.Sequence,'.',''))) < 3 ; */ string strProcName = "hbh_proc_updateImportData"; // 为了效率,改为存储过程 //string updateText = string.Format(updateSql, strGuid); string updateText = string.Format("call {0}('{1}');", strProcName, strGuid); DatabaseAdapter sqlHelper2 = new DatabaseAdapter(this); sqlHelper2.DbHelper.ExecuteNonQuery(CommandType.Text, updateText); } }