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