private void bImportTbl_Click(object sender, EventArgs e) // 导入表
{
if (Program.m_bLockDBForCompetition)
{
MessageBox.Show("比赛结束了，请不要导表。");
return;
}
//////////////////////////////////////////////////////////////////////////
// 准备工作
//////////////////////////////////////////////////////////////////////////
string[] strLines = null;
string[] strColNames = null;
string tblName = this.tree.SelectedNode.Text.Trim();
string rootPath = Program.RootDir; // 导入的根目录
string strFile = ""; // 导入的文件路径
// 判断该用户能不能导入先
if (Program.m_bUseNewAcl)
{
try
{
//DataTable tblExportTable = Helper.GetDataTable("SELECT id FROM sys_export_table_cfg WHERE tablename = '" + tblName + "' ", Conn);
DataTable tblExportTable = Helper.GetDataTableProxy("sys_export_table_cfg", "tablename = '" + tblName + "' ", null, Conn);
if (tblExportTable.Rows.Count > 0)
{
int tbl_id = Convert.ToInt32(tblExportTable.Rows[0]["id"]);
//DataTable tblExportModlCfg = Helper.GetDataTable("SELECT modid FROM sys_export_modl_cfg WHERE tableid = " + tbl_id.ToString() + " ", Conn);
DataTable tblExportModlCfg = Helper.GetDataTableProxy("sys_export_modl_cfg", "tableid = " + tbl_id.ToString(), null, Conn);
//if (tblExportModlCfg.Rows.Count > 0)
//{
// int moduleid = Convert.ToInt32(tblExportModlCfg.Rows[0][0]);
// string _sql = string.Format("SELECT * FROM sys_useracl_desc WHERE userindex = {0} AND moduleid = {1} AND canimport = 1", Program.m_rightmgr._userindex, moduleid);
// bool can_import = Helper.GetDataTable(_sql, Conn).Rows.Count > 0;
// if (Program.m_bUseNewAcl && !can_import)
// {
// MessageBox.Show("您对于当前数据表 " + tblName + " 没有导表权限！请申请开通。", "权限不足", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// return;
// }
//}
if (Program.m_bUseNewAcl)
{
bool can_import = false;
foreach (DataRow rowex in tblExportModlCfg.Rows)
{
int moduleid = Convert.ToInt32(rowex["modid"]);
//string _sql = string.Format("SELECT * FROM sys_useracl_desc WHERE userindex = {0} AND moduleid = {1} AND canimport = 1", Program.m_rightmgr._userindex, moduleid);
string filter = string.Format("userindex = {0} AND moduleid = {1} AND canimport = 1", Program.m_rightmgr._userindex, moduleid);
DataTable table = Helper.GetDataTableProxy("sys_useracl_desc", filter, null, Conn);
bool _b = table.Rows.Count > 0;
//bool _b = Helper.GetDataTable(_sql, Conn).Rows.Count > 0;
if (_b)
{
can_import = true;
break;
}
}
// 如果一行都没有，证明这个物理表不在任何一个编辑模块里。那么允许导入
if (tblExportModlCfg.Rows.Count == 0)
can_import = true;
if (!can_import)
{
MessageBox.Show("您对于当前数据表 " + tblName + " 没有导表权限！请申请开通。", "权限不足", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("该表无法导入。\r\n错误信息为：" + ex.Message, "无法导入", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
bool bBrowseFolder = false;
bool jump = false; // 是否跳过对tab和text文件的字符处理
LuaEx lua = new LuaEx();
lua["Conn"] = Conn;
lua.DoString("function trace(txt)\r\n MainForm.LogEditBox.Visible = true\r\n MainForm.LogEditBox.Text = MainForm.LogEditBox.Text ..txt..'\\r\\n' \r\n end");
object[] results = null;
try
{
results = CallLuaLoadMultiFile(lua, rootPath, tblName);
}
catch (Exception ex)
{
MessageBox.Show("脚本函数 CallLuaLoadMultiFile() 异常。请检查 import.lua 文件。\r\n\r\n错误信息：" + ex.Message, "导入失败", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (results != null && results[0] is bool && (bool)results[0] && results.Length >= 3)
{ // 脚本调用成功
strColNames = results[1] as string[];
strLines = results[2] as string[];
jump = true;
}
else
{ // 脚本失败或不导入, 按原先导入流程选择单个文件 或者 文件夹
/*
string s = string.Format("SELECT * FROM sys_table_cfg WHERE tablename='{0}'", tblName);
DataTable tbl_cfg = Helper.GetDataTable(s, Conn);
*/
string filter = string.Format("tablename='{0}'", tblName);
DataTable tbl_cfg = Helper.GetDataTableProxy("sys_table_cfg", filter, null, Conn);
if (tbl_cfg.Rows.Count == 1 && tbl_cfg.Rows[0]["bBrowseFolderWhileImport"] != DBNull.Value && Convert.ToBoolean(tbl_cfg.Rows[0]["bBrowseFolderWhileImport"]) == true)
bBrowseFolder = true;
if (bBrowseFolder)
strFile = FileFolderHelper.BrowserFolder("请选择 Client/data/source/maps 目录。");
else
strFile = FileFolderHelper.BrowserFile(new string[] { "tab", "txt" });
if (strFile == "") // 用户是否选择文件的检查放到这里来做
return;
}
//DataTable tbl = Helper.GetDataTable("SELECT * FROM " + tblName, Conn);
DataTable tbl = Helper.GetDataTableProxy(tblName, Conn);
List<string> newNodeList = new List<string>(); // 新增加的树结点名称链表
List<string> newColumnList = new List<string>(); // 新增加的列的名称链表
List<DataRow> overwriteRowList = new List<DataRow>(); // 被重写的数据行的链表
bool deleteMe = false; // 是否有___delete_me字段
int rowNumber = tbl.Rows.Count; // 数据表的行数
int iOverwriteCount = 0; // 覆盖的行数
int iAddCount = 0; // 新增的行数
//TimeLog log = new TimeLog();
//log.Log(TimeLog.enumLogType.ltstart, "数据表导入", "功能启动", string.Format("导入文件名:{0}, 导入目标数据表:{1}", strFile, tblName));
bool bHasTitle = true;
if(jump)
{
goto label0;
}
// split
string strContent = FileFolderHelper.FileToString(strFile);
if (!bBrowseFolder && strContent == string.Empty)
{
MessageBox.Show("不能读取文件内容，可能文件正在被其他程序打开。", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
strLines = strContent.Split(new char[2] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
// 是否有表头
/*
string sql = string.Format("SELECT * FROM sys_export_table_cfg WHERE tablename='{0}'", tblName);
DataTable tbl_exp = Helper.GetDataTable(sql, Conn);
*/
string _filter = string.Format("tablename='{0}'", tblName);
DataTable tbl_exp = Helper.GetDataTableProxy("sys_export_table_cfg", _filter, null, Conn);
if (tbl_exp.Rows.Count > 0 && tbl_exp.Rows[0]["hastitle"] != DBNull.Value && Convert.ToBoolean(tbl_exp.Rows[0]["hastitle"]) == false)
{
bHasTitle = false;
}
if (!bBrowseFolder && bHasTitle && strLines.Length < 2)
return;
if (!bBrowseFolder)
if (bHasTitle)
{
strColNames = strLines[0].Split(new char[1] { '\t' }, StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < strColNames.Length; i++)
{
strColNames[i] = strColNames[i].Trim();
}
}
else // 没表头(列名), 自己造
{
int col_count = strLines[0].Split(new char[1] { '\t' }).Length;
strColNames = new string[col_count];
for (int i = 0; i < col_count; i++)
{
strColNames[i] = string.Format("Field_{0}", i);
}
}
//////////////////////////////////////////////////////////////////////////
// 询问是否全部导入
//////////////////////////////////////////////////////////////////////////
label0:
bool bImportAllRow = true;
string strKeyField = "";
int iBeginNum = -1;
int iEndNum = -1;
ImportInfo imp_frm = new ImportInfo(strColNames);
DialogResult imp_r = imp_frm.ShowDialog();
if (imp_r == DialogResult.Cancel)
{
return;
}
if (imp_r == DialogResult.OK)
{
bImportAllRow = imp_frm.All;
strKeyField = imp_frm.KeyField;
iBeginNum = imp_frm.BeginNum;
iEndNum = imp_frm.EndNum;
}
if(!jump) // 如果是跳转过来的，就不用再用lua脚本来干涉
{
lua = new LuaEx();
lua["Conn"] = m_conn;
lua.DoString("function trace(txt)\r\n MainForm.LogEditBox.Visible = true\r\n MainForm.LogEditBox.Text = MainForm.LogEditBox.Text ..txt..'\\r\\n' \r\n end");
try
{
if (CallLuaImport(lua, tblName, strFile, ref strColNames, ref strLines))
return;
}
catch//(Exception ex)
{
return;
}
}
// log & ftp backup
TimeLog log = new TimeLog();
string backupName = Helper.GetHostName() + "_" + Convert.ToString(DateTime.Now);
backupName = backupName.Replace(':', '-');
string importParams = string.Format("bImportAllRow:{0} strKeyField:{1} iBeginNum:{2} iEndNum:{3}", bImportAllRow, strKeyField, iBeginNum, iEndNum);
log.Log(TimeLog.enumLogType.ltstart, "数据表导入", "功能启动", string.Format("导入文件名:{0}, 导入目标数据表:{1}, 导入参数:{2}, 备份名:{3}", strFile, tblName, importParams, backupName));
if (File.Exists(strFile))
Program.m_ftp.Upload(strFile, Program.m_ftp_basedir + backupName);
try // 开始对临时表进行操作，不实际写入数据库，可以忽略所有产生的异常
{
// 删除___delete_me字段
if (tbl.Columns.Contains(TRASH_FIELD))
{
deleteMe = true;
tbl.Columns.Remove(TRASH_FIELD);
}
// 添加字段
foreach (string colname in strColNames)
{
if (!tbl.Columns.Contains(colname)) // 列名未在表中
{
DataColumn c = tbl.Columns.Add(colname);
c.MaxLength = 3000; // 设置最大长度
newColumnList.Add(colname);
}
}
// 添加数据行
int key_col_index = -1;
if (!bImportAllRow)
{
for (int i = 0; i < strColNames.Length; i++)
{
if (strColNames[i].Trim() == strKeyField)
{
key_col_index = i;
break;
}
}
if (key_col_index == -1)
{
MessageBox.Show("无法判断某行是否应该被导入，这通常是因为指定了错误的主键表项。\r\n\r\n导入失败。",
"导入数据表", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
// 读取数据, 考虑是否是新表
bool bFirstLine = true;
bool bYesToAll = false;
bool bNoToAll = false;
bool bTblHasPrimKey = (tbl.PrimaryKey.Length != 0);
foreach (string eachLine in strLines)
{
// 第一行是字段名; 检查
if (bHasTitle)
{
if (bFirstLine)
{
bFirstLine = false;
continue;
}
}
string[] strFields = eachLine.Split(new char[1] { '\t' });
if (eachLine.Split(new char[1] {'\t'}, StringSplitOptions.RemoveEmptyEntries).Length == 0)
{
MessageBox.Show("发现文件中有空行，请检查。");
return;
}
// 判断行号是否在要求导入的范围内
bool bRowAcceptable = true;
if (bImportAllRow)
{
bRowAcceptable = true;
}
else
{
try
{
int id = Convert.ToInt32(strFields[key_col_index].Trim());
bRowAcceptable = bImportAllRow || (id >= iBeginNum && id <= iEndNum);
}
catch (Exception ex)
{
string err = string.Format("无法判断某行是否应该被导入，这通常是因为指定了错误的主键表项。\r\n\r\n错误信息为：{0}", ex.Message);
MessageBox.Show(err, "导入数据表", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
if (!bRowAcceptable)
continue;
// 开始导入
if (!bTblHasPrimKey) // 没定义key，当新表处理，添加全部行
{
DataRow newRow = tbl.NewRow();
newRow.BeginEdit();
if (!WriteRow(ref tbl, ref newRow, ref strColNames, ref strFields))
{
MessageBox.Show("导入失败，所有新增数据将不被导入!",
"导入数据表", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
tbl.Rows.Add(newRow);
newRow.EndEdit();
iAddCount++;
}
else // 是旧表，需要根据key判断原来是否存在该行
{
string strAsking = "已经存在 ";
// format key values to find
object[] objKeyValues = new object[tbl.PrimaryKey.Length];
int iKeyIndex = 0;
foreach (DataColumn col in tbl.PrimaryKey)
{
string primKeyToFind = col.ColumnName;
for (int i = 0; i < strColNames.Length; i++)
{
if (strColNames[i].Trim().ToLower() == primKeyToFind.Trim().ToLower())
{
if (strFields[i] == string.Empty)
{
string strError = string.Format("在导入表 {0} 时发现某些主键为空的行。\r\n请检查源文件 {1}。", tblName, strFile);
MessageBox.Show(strError, "失败", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
objKeyValues[iKeyIndex++] = strFields[i];
strAsking += primKeyToFind;
strAsking += "为";
strAsking += strFields[i];
strAsking += " ";
}
}
}
strAsking += "的行。";
// find it
DataRow row = tbl.Rows.Find(objKeyValues);
if (row != null) // 有相同key的行，询问
{
if (bYesToAll)
goto label_overwrite_it;
if (bNoToAll)
continue;
// 询问用户
TabImportComfirming cfmForm = new TabImportComfirming(strAsking);
DialogResult result = cfmForm.ShowDialog();
switch (result)
{
case DialogResult.Yes:
goto label_overwrite_it;
case DialogResult.OK:
bYesToAll = true;
goto label_overwrite_it;
case DialogResult.No:
continue;
case DialogResult.Ignore:
bNoToAll = true;
continue;
case DialogResult.Cancel:
return;
default:
break;
}
label_overwrite_it: // 覆盖行数据
if (!WriteRow(ref tbl, ref row, ref strColNames, ref strFields))
{
MessageBox.Show("导入失败，所有新增数据将不被导入!",
"导入数据表", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
overwriteRowList.Add(row); // 添加入重写行链表
iOverwriteCount++;
}
// 加入新行
else
{
DataRow newRow = tbl.NewRow();
newRow.BeginEdit();
if (!WriteRow(ref tbl, ref newRow, ref strColNames, ref strFields))
{
MessageBox.Show("导入失败，所有新增数据将不被导入!",
"导入数据表", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
tbl.Rows.Add(newRow);
newRow.EndEdit();
iAddCount++;
}
}
}
// 可以填充默认值。
if (MainForm.m_bModifyDbTableDefault)
{
if (bTblHasPrimKey)
{
DialogResult result = MessageBox.Show("你希望把该表中的空项填充成相应的默认值吗？\r\n\r\n推荐您选 < 是(Y) >。", "提示", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
try
{
Helper.FillDBNullWithDefault(ref tbl);
}
catch (SqlException ex)
{
MessageBox.Show("将表中空项填充成默认值时产生异常: " + ex.ToString() + "\r\n\r\n新增数据将不被保存",
"导入数据表", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
}
}
}
catch(Exception ex)
{
MessageBox.Show("读取数据行时产生异常: " + ex.ToString() + "\r\n\r\n新增数据将不被保存",
"导入数据表", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// 提交
if(Conn.State == ConnectionState.Closed) // 打开sql连接
{
Conn.Open();
}
// 允许数据库系统表更新
/* remoting
SqlCommand cfg_cmd = Conn.CreateCommand();
cfg_cmd.CommandText = "sp_configure 'allow updates', '1'";
cfg_cmd.ExecuteNonQuery();
cfg_cmd.CommandText = "reconfigure with override";
cfg_cmd.ExecuteNonQuery();
*/
Helper.ExecuteCommand("sp_configure 'allow updates', '1'", Conn);
Helper.ExecuteCommand("reconfigure with override", Conn);
/*
SqlTransaction transaction = Conn.BeginTransaction(); //** 开启事务
SqlCommand cmd = new SqlCommand(); // 新建sql命令
cmd.Connection = Conn;
cmd.Transaction = transaction; // 绑定事务对象
*/
DataTableProxy proxy = null;
if (Program.EnableRemoting)
{
proxy = DataTableServerProxy.GetDataTableProxy();
proxy.DeleteRemotingTable(tblName);
}
int nID = Helper.BeginTransaction(Conn);
int col_index = 0; // 列序号
int index = 0; // 行计数游标
List<string> strColNameList = new List<string>(); // 记录导入列名的链表
foreach(string s in strColNames)
{
strColNameList.Add(s);
}
try
{
// 添加字段
foreach (string colname in newColumnList)
{
string columnName = colname.Replace("]", "]]"); // 替换特殊字符
/* remoting
cmd.CommandText = string.Format("ALTER TABLE {0} ADD [{1}] [varchar] (3000) NULL", tblName, columnName);
cmd.ExecuteNonQuery();
*/
string sql = string.Format("ALTER TABLE {0} ADD [{1}] [varchar] (3000) NULL", tblName, columnName);
Helper.ExecuteTransaction(tblName, sql, nID);
newNodeList.Add(colname);
}
// 删除___delete_me字段
if(deleteMe)
{
/* remoting
cmd.CommandText = string.Format("ALTER TABLE {0} DROP COLUMN {1}", tblName, TRASH_FIELD);
cmd.ExecuteNonQuery();
*/
string sql = string.Format("ALTER TABLE {0} DROP COLUMN {1}", tblName, TRASH_FIELD);
Helper.ExecuteTransaction(tblName, sql, nID);
}
// 检查是否导入列的顺序发生了变化
bool sameOrder = true; // 列顺序是否变化的标记
for (int i = 0; i < strColNames.Length; i++)
{
if (strColNames[i] != tbl.Columns[i].ColumnName)
{
sameOrder = false;
break;
}
}
if (!sameOrder) // 导入列的顺序发生了变化，需要重排序
{
// 因为系统表的列重排序后可能会出现数据问题，所以先清空所有行数据，调整后再重新加入
/*
cmd.CommandText = string.Format("DELETE FROM {0}", tblName);
cmd.ExecuteNonQuery();
*/
string _sql = string.Format("DELETE FROM {0}", tblName);
Helper.ExecuteTransaction(tblName, _sql, nID);
foreach (DataColumn c in tbl.Columns) // 所有列colid加1000，为重排序做准备
{
col_index++;
//cmd.CommandText = string.Format("UPDATE syscolumns SET colid = '{0}' WHERE name = '{1}' AND id = object_id('{2}')", col_index + 1000, c.ColumnName, tblName);
//cmd.ExecuteNonQuery();
string sql = string.Format("UPDATE syscolumns SET colid = '{0}' WHERE name = '{1}' AND id = object_id('{2}')", col_index + 1000, c.ColumnName, tblName);
Helper.ExecuteTransaction("syscolumns", sql, nID);
}
col_index = 0;
foreach (string colname in strColNames) // 新列重排序
{
col_index++;
//cmd.CommandText = string.Format("UPDATE syscolumns SET colid = '{0}' WHERE name = '{1}' AND id = object_id('{2}')", col_index, colname, tblName);
//cmd.ExecuteNonQuery();
string sql = string.Format("UPDATE syscolumns SET colid = '{0}' WHERE name = '{1}' AND id = object_id('{2}')", col_index, colname, tblName);
Helper.ExecuteTransaction("syscolumns", sql, nID);
}
foreach (DataColumn c in tbl.Columns) // 旧列重排序
{
string colname = c.ColumnName;
if (!strColNameList.Contains(colname))
{
col_index++;
//cmd.CommandText = string.Format("UPDATE syscolumns SET colid = '{0}' WHERE name = '{1}' AND id = object_id('{2}')", col_index, colname, tblName);
//cmd.ExecuteNonQuery();
string sql = string.Format("UPDATE syscolumns SET colid = '{0}' WHERE name = '{1}' AND id = object_id('{2}')", col_index, colname, tblName);
Helper.ExecuteTransaction("syscolumns", sql, nID);
}
}
// 恢复主键约束 列重排序可能改变了原先的主键约束
DataColumn[] primaryKeys = tbl.PrimaryKey;
if (primaryKeys.Length > 0) // 存在主键约束
{
string keyString = string.Empty;
foreach (DataColumn c in primaryKeys)
{
keyString = keyString + "[" + c.ColumnName + "]" + ",";
}
// 获取主键约束
////cmd.CommandText = string.Format("SELECT name FROM sysindexes WHERE id = OBJECT_ID('{0}') AND name LIKE 'PK%'", tblName);
//string filter = string.Format("id = OBJECT_ID('{0}') AND name LIKE 'PK%'", tblName);
string sql = string.Format("SELECT name FROM sysindexes WHERE id = OBJECT_ID('{0}') AND name LIKE 'PK%'", tblName);
object constraint = Helper.ExecuteTransactionScalar("sysindexes", sql, nID, false);
// 删除主键约束
/*cmd.CommandText = string.Format("ALTER TABLE {0} DROP CONSTRAINT {1}", tblName, constraint);
cmd.ExecuteNonQuery();
*/
sql = string.Format("ALTER TABLE {0} DROP CONSTRAINT {1}", tblName, constraint);
Helper.ExecuteTransaction(tblName, sql, nID);
// 增加新的主键约束
/*
cmd.CommandText = string.Format("ALTER TABLE {0} ADD CONSTRAINT PK_{1} PRIMARY KEY({2})", tblName, tblName, keyString.TrimEnd(','));
cmd.ExecuteNonQuery();
*/
sql = string.Format("ALTER TABLE {0} ADD CONSTRAINT PK_{1} PRIMARY KEY({2})", tblName, tblName, keyString.TrimEnd(','));
Helper.ExecuteTransaction(tblName, sql, nID);
}
/* remoting
cmd.CommandText = string.Format("SELECT OBJECTPROPERTY(OBJECT_ID('{0}'), 'TableHasIdentity')", tblName);
object executeResult = cmd.ExecuteScalar();
*/
string sqlCmd = string.Format("SELECT OBJECTPROPERTY(OBJECT_ID('{0}'), 'TableHasIdentity')", tblName);
object executeResult = Helper.ExecuteTransactionScalar(tblName, sqlCmd, nID, false);
if (executeResult != null && executeResult.ToString() == "1") // 检查数据表是否有自动编号列
{
// 允许将值插入到自动编号列中
/* remoting
cmd.CommandText = string.Format("SET IDENTITY_INSERT {0} ON", tblName);
cmd.ExecuteNonQuery();
*/
_sql = string.Format("SET IDENTITY_INSERT {0} ON", tblName);
Helper.ExecuteTransaction(tblName, _sql, nID);
}
// 重新添加表数据
foreach (DataRow r in tbl.Rows)
{
string newValue = "";
string columns = "";
//for (int i = 0; i < strColNames.Length; i++)
for (int i = 0; i < tbl.Columns.Count; i++)
{
string raw_colname = tbl.Columns[i].ColumnName;
string value = r[raw_colname].ToString().Replace("'", "''"); // 替换特殊字符
string columnName = raw_colname.Replace("]", "]]"); // 替换特殊字符
if (r[raw_colname] is Boolean) // 判断是否是布尔值
{
value = (bool)r[raw_colname] ? "1" : "0";
}
if (value == string.Empty) // 自动忽略空值，解决数字的空值转换的问题
{
continue;
}
else
{
columns += string.Format("[{0}], ", columnName);
newValue += string.Format("'{0}', ", value);
}
}
newValue = newValue.Trim().TrimEnd(',');
columns = columns.Trim().TrimEnd(',');
/*cmd.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", tblName, columns, newValue);*/
try
{
// cmd.ExecuteNonQuery();
string sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", tblName, columns, newValue);
Helper.ExecuteTransaction(tblName, sql, nID);
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message + "\r\n\r\n" + string.Format("INSERT INTO {0} ({1}) VALUES ({2})", tblName, columns, newValue));
throw ex;
}
}
}
else // 列顺序不变，只需添加行数据即可
{
// 覆盖旧的数据行
if (overwriteRowList.Count > 0)
{
DataColumn[] keys = tbl.PrimaryKey;
foreach (DataRow r in overwriteRowList)
{
string setValue = "";
string keyValue = "";
foreach (string s in strColNames)
{
string columnName = s.Replace("]", "]]"); // 替换特殊字符
string value = r[s].ToString().Replace("'", "''"); // 替换特殊字符
if (r[s] is Boolean) // 判断是否是布尔值
{
value = (bool)r[s] ? "1" : "0";
}
if (value == string.Empty) // 空值是特殊条件，需要特殊处理
{
setValue += string.Format("[{0}] = NULL, ", columnName);
}
else
{
setValue += string.Format("[{0}] = \'{1}\', ", columnName, value);
}
}
setValue = setValue.Trim().TrimEnd(',');
foreach (DataColumn c in keys)
{
string columnName = c.ColumnName.Replace("]", "]]"); // 替换特殊字符
string value = r[c.ColumnName].ToString().Replace("'", "''"); // 替换特殊字符
keyValue += string.Format("[{0}] = \'{1}\' AND ", columnName, value);
}
keyValue = keyValue.Substring(0, keyValue.Length - 5);
/*cmd.CommandText = string.Format("UPDATE {0} SET {1} WHERE {2}", tblName, setValue, keyValue);*/
try
{
//cmd.ExecuteNonQuery();
string sql = string.Format("UPDATE {0} SET {1} WHERE {2}", tblName, setValue, keyValue);
Helper.ExecuteTransaction(tblName, sql, nID);
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message + "\r\n\r\n" + string.Format("UPDATE {0} SET {1} WHERE {2}", tblName, setValue, keyValue));
throw ex;
}
}
}
// 增加新数据行
foreach (DataRow r in tbl.Rows)
{
if (index < rowNumber) // 忽略旧的数据行
{
index++;
continue;
}
string newValue = "";
string columns = "";
for (int i = 0; i < strColNames.Length; i++)
{
string value = r[strColNames[i]].ToString().Replace("'", "''"); // 替换特殊字符
string columnName = strColNames[i].Replace("]", "]]"); // 替换特殊字符
if (r[strColNames[i]] is Boolean) // 判断是否是布尔值
{
value = (bool)r[strColNames[i]] ? "1" : "0";
}
if (value == string.Empty) // 自动忽略空值，解决数字的空值转换的问题
{
continue;
}
else
{
columns += string.Format("[{0}], ", columnName);
newValue += string.Format("'{0}', ", value);
}
}
newValue = newValue.Trim().TrimEnd(',');
columns = columns.Trim().TrimEnd(',');
/*cmd.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", tblName, columns, newValue);*/
try
{
//cmd.ExecuteNonQuery();
string sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", tblName, columns, newValue);
Helper.ExecuteTransaction(tblName, sql, nID);
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message + "\r\n\r\n" + string.Format("INSERT INTO {0} ({1}) VALUES ({2})", tblName, columns, newValue));
throw ex;
}
}
}
tbl.AcceptChanges();
//transaction.Commit(); // 提交事务
Helper.CommitTransaction(nID);
}
catch (SqlException ex)
{
MessageBox.Show("数据更新时产生异常: " + ex.ToString() + "\r\n\r\n新增数据将不被保存",
"导入数据表", MessageBoxButtons.OK, MessageBoxIcon.Warning);
//transaction.Rollback(); // 事务回滚
Helper.RollbackTransaction(nID);
if (Program.EnableRemoting)
{
proxy.AddRemotingTable(tblName);
}
}
finally
{
// 禁止数据库系统标更新
/* remoting
cfg_cmd.CommandText = "sp_configure 'allow updates', '0'";
cfg_cmd.ExecuteNonQuery();
cfg_cmd.CommandText = "reconfigure with override";
cfg_cmd.ExecuteNonQuery();
*/
Helper.ExecuteCommand("sp_configure 'allow updates', '0'", Conn);
Helper.ExecuteCommand("reconfigure with override", Conn);
if (Conn.State == ConnectionState.Open) // 关闭sql连接
{
Conn.Close();
}
if (Program.EnableRemoting)
{
proxy.AddRemotingTable(tblName);
}
}
// 增加树结点
tree.SelectedNode.Collapse();
tree.SelectedNode.Nodes.Clear();
tree.SelectedNode.Nodes.Add("无效属性");
//////////////////////////////////////////////////////////////////////////
// 更新内存 报告
//////////////////////////////////////////////////////////////////////////
AllTables[tblName] = tbl;
string rpt = string.Format("成功导入数据,覆盖行：{0} 增加行：{1}", iOverwriteCount, iAddCount);
log.Log(TimeLog.enumLogType.ltend, null, null, string.Format("导入文件名:{0}, 导入目标数据表:{1}, 导入参数:{2}, 备份名:{3}, 导入结果:{4}", strFile, tblName, importParams, backupName, rpt));
log.Clear();
MessageBox.Show(rpt);
lua.Dispose();
}
/// <summary>
/// 新建模块
/// </summary>
/// <param name="sender">事件发送者</param>
/// <param name="e">事件</param>
private void CreatMod_Click(object sender, EventArgs e)
{
// 获得主表名
DBTools.ChooseForm cf = new DBTools.ChooseForm(m_conn);
DialogResult result = cf.ShowDialog();
if(result != DialogResult.OK) // 用户未确认输入
{
return;
}
string tableName = cf.TableName;
// 新建模块和主表
if (treeView.SelectedNode != null)
{
TreeNode selectNode = null;
try
{
DataRow newRow = m_ModelDefTable.NewRow();
newRow.BeginEdit();
newRow["modelname_en"] = "新模块_" + m_nMaxModelID;
newRow["modelname_cn"] = "新模块_" + m_nMaxModelID;
newRow["iconid"] = 1;
newRow.EndEdit();
m_ModelDefTable.Rows.Add(newRow);
UpdateData("SELECT * FROM sys_modl_def", m_ModelDefTable, UpdateType.ADD);
string strUserID = Helper.GetHostName();
string strIP = Helper.GetIPAddress();
string strSQL = string.Format("select _index from sys_useracl where userid ='{0}' and ip='{1}'", strUserID, strIP);
//DataTable dtab = Helper.GetDataTable(strSQL, m_conn);
DataTable dtab = Helper.GetDataTableWithSqlProxy("sys_useracl", strSQL, m_conn);
string userindex = dtab.Rows[0][0].ToString();
string strGroupName = string.Empty;
if (String.Compare(strUserID.ToUpper(), "A") > 0 && String.Compare(strUserID.ToUpper(), "J") < 0)
{
strGroupName = "A - J";
}
if (String.Compare(strUserID.ToUpper(), "K") > 0 && String.Compare(strUserID.ToUpper(), "S") < 0)
{
strGroupName = "K - S";
}
if (String.Compare(strUserID.ToUpper(), "T") > 0 && String.Compare(strUserID.ToUpper(), "X") < 0)
{
strGroupName = "T - X";
}
if (String.Compare(strUserID.ToUpper(), "Y") > 0 && String.Compare(strUserID.ToUpper(), "Z") < 0)
{
strGroupName = "Y - Z";
}
//DataTable newDataTable = Helper.GetDataTable("SELECT * FROM sys_useracl_desc", m_conn);
DataTable newDataTable = Helper.GetDataTableProxy("sys_useracl_desc", m_conn);
DataRow newDataRow = newDataTable.NewRow();
newDataRow.BeginEdit();
newDataRow["userindex"] = userindex;
newDataRow["moduleid"] = m_nMaxModelID;
newDataRow["groupname"] = strGroupName;
newDataRow["modulename_en"] = "新模块_" + m_nMaxModelID;
newDataRow["canread"] = 1;
newDataRow["canwrite"] = 1;
newDataRow["canimport"] = 1;
newDataRow["canconfigmeta"] = 1;
newDataRow["canmodifydefaultval"] = 1;
newDataRow.EndEdit();
newDataTable.Rows.Add(newDataRow);
UpdateData("SELECT * FROM sys_useracl_desc", newDataTable, UpdateType.ADD);
canCfgMeta = true;
TreeNode newNode = new TreeNode("新模块_" + m_nMaxModelID);
selectNode = newNode;
//
// 以下代码要做修正
//
//DataTable table = Helper.GetDataTable("SELECT max(id) as modelid FROM sys_modl_def", m_conn);
DataTable table = Helper.GetDataTableWithSqlProxy("sys_modl_def", "SELECT max(id) as modelid FROM sys_modl_def", m_conn);
newNode.Tag = (int)table.Rows[0]["modelid"];
newRow["id"] = (int)table.Rows[0]["modelid"];
m_ModelDefTable.AcceptChanges();
treeView.SelectedNode.Nodes.Add(newNode);
m_nMaxModelID++;
lockSelection = true;
comboBoxEx1.Text = tableName;
lockSelection = false;
comboBoxEx1.Tag = tableName;
treeView.SelectedNode = newNode;
modelTable[newNode] = tableName;
FillTabContain(newNode);
TimeLog modaddlog = new TimeLog();
modaddlog.Log(TimeLog.enumLogType.ltstart, "表元编辑器", "添加模块定义", tableName);
modaddlog.Log(TimeLog.enumLogType.ltend, null, null, null);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
static void Main(string[] args)
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
if (ConfigurationManager.AppSettings["remoting"] == "true")
{
EnableRemoting = true;
}
if (args.Length < 1)
{
MessageBox.Show(string.Format("启动失败，请使用 UpdatePrograme.exe 启动本程序。"), "错误",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
#if (!_DEBUG)
if (ProgramAlreadyRunning() && args[0] != "-Debug")
{
MessageBox.Show("此工具不能双开。", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
#endif
//add by cuiwei 07.8.21
EV ev = new EV();
ev.evPath(Path.GetDirectoryName(Application.ExecutablePath));
//add end
// ftp init
m_ftp = new FTPclient(m_ftp_hostName, m_ftp_user, m_ftp_psw);
//m_ftp.Upload("c:/path.txt", m_ftp_basedir + "xxxx");
// 数据库选择
if (!m_bSkillBranch)
{
if (forPublic)
{
m_bLoadBackupDB = false;
}
else
{
//DialogResult dr = MessageBox.Show("要读Head策划库，点<是>；\r\n要读分支库(1-0-8-1380，2009.03.25)，点<否>。", "选择策划库", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
//if (dr == DialogResult.Yes)
// m_bLoadBackupDB = false;
//else
// m_bLoadBackupDB = true;
DialogResult dr = MessageBox.Show("要读Head策划库，点<是>；\r\n要退出程序，点<否>。", "是否继续", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr == DialogResult.Yes)
{
m_bLoadBackupDB = false;
}
else
{
return;
}
}
}
TimeLog log = null;
do
{
try
{
if (!InitSettings())
{
return;
}
if (m_bRestart)
{
m_bRestart = false;
}
m_MainForm = new MainForm();
try
{
log = new TimeLog();
ClearRecordLocks(MainForm.conn);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
if (RightsSettings.AllowSave)
{
log.Log(TimeLog.enumLogType.ltstart, "主窗口", "应用程序启动", "主版本=" + Helper.GetVersionInfo());
}
else
{
log.Log(TimeLog.enumLogType.ltstart, "主窗口", "应用程序启动", "主版本=" + Helper.GetVersionInfo() + " [只读版本]");
}
m_MainForm.Text += "(" + Helper.GetVersionInfo() + ")";
Application.Run(m_MainForm);
m_rightmgr.Uninit();
}
catch (Exception ex)
{
MessageBox.Show(string.Format("{0}", ex.Message), "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
if (log != null)
{
log.Log(TimeLog.enumLogType.ltend, null, null, null);
log.Clear();
}
}
}while(m_bRestart); // 检查是否需要重新启动主窗口
if (EnableRemoting)
{
DataTableServerProxy.DisconnectServer();
}
}
/// <summary>
/// 删除功能模块
/// </summary>
/// <param name="sender">事件发送者</param>
/// <param name="e">事件</param>
private void DeleteMod_Click(object sender, EventArgs e)
{
bool bRetCode = false;
bool bResult = true;
if (treeView.SelectedNode != null)
{
TreeNode modelNode = treeView.SelectedNode;
DataRow row = m_ModelDefTable.Rows.Find(modelNode.Text);
if (row != null)
{
DialogResult result = MessageBox.Show("确定要删除这个模块，以及这个模块下的所有内容吗?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button2);
if (result != DialogResult.Yes)
goto Exit1;
foreach (TreeNode tabNode in modelNode.Nodes)
{
// 删除标签页下的所有叶子节点
if (tabNode.Nodes.Count > 0)
{
bRetCode = DeleteSubNodes(tabNode);
if (!bRetCode)
goto Exit0;
bRetCode = UpdateData("SELECT * FROM sys_meta_info", m_DicMetaInfoTable, UpdateType.DEL);
if (!bRetCode)
goto Exit0;
}
DataRow tabRow = m_ModelTabDefTable.Rows.Find(tabNode.Text.Trim());
if (tabRow == null)
continue;
// 删除标签页节点
tabRow.Delete();
}
bRetCode = UpdateData("SELECT * FROM sys_modl_tab_def", m_ModelTabDefTable, UpdateType.DEL);
if (!bRetCode)
goto Exit0;
// 删除模块节点
row.Delete();
bRetCode = UpdateData("SELECT * FROM sys_modl_def", m_ModelDefTable, UpdateType.DEL);
if (!bRetCode)
goto Exit0;
treeView.SelectedNode.Remove();
TimeLog moddellog = new TimeLog();
moddellog.Log(TimeLog.enumLogType.ltstart, "表元编辑器", "删除模块定义", modelNode.Text);
moddellog.Log(TimeLog.enumLogType.ltend, null, null, null);
}
else
{
MessageBox.Show("要删除的模块不存在!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button2);
}
}
Exit1:
bResult = true;
Exit0:
if (!bResult)
{
MessageBox.Show("在删除某个节点时，发生错误!");
}
}
static void Main(string[] args)
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
if (ConfigurationManager.AppSettings["remoting"] == "true")
{
EnableRemoting = true;
}
if (args.Length < 1)
{
MessageBox.Show(string.Format("启动失败，请使用 UpdatePrograme.exe 启动本程序。"), "错误",
MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
#if (!_DEBUG)
if (ProgramAlreadyRunning() && args[0] != "-Debug")
{
MessageBox.Show("此工具不能双开。", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
#endif
//add by cuiwei 07.8.21
EV ev = new EV();
ev.evPath(Path.GetDirectoryName(Application.ExecutablePath));
//add end
// ftp init
m_ftp = new FTPclient(m_ftp_hostName, m_ftp_user, m_ftp_psw);
//m_ftp.Upload("c:/path.txt", m_ftp_basedir + "xxxx");
// 数据库选择
if (!m_bSkillBranch)
{
if (forPublic)
{
m_bLoadBackupDB = false;
}
else
{
//DialogResult dr = MessageBox.Show("要读Head策划库，点<是>；\r\n要读分支库(1-0-8-1380，2009.03.25)，点<否>。", "选择策划库", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
//if (dr == DialogResult.Yes)
// m_bLoadBackupDB = false;
//else
// m_bLoadBackupDB = true;
DialogResult dr = MessageBox.Show("要读Head策划库，点<是>；\r\n要退出程序，点<否>。", "是否继续", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr == DialogResult.Yes)
m_bLoadBackupDB = false;
else
return;
}
}
TimeLog log = null;
do
{
try
{
if (!InitSettings())
return;
if (m_bRestart)
{
m_bRestart = false;
}
m_MainForm = new MainForm();
try
{
log = new TimeLog();
ClearRecordLocks(MainForm.conn);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
if (RightsSettings.AllowSave)
log.Log(TimeLog.enumLogType.ltstart, "主窗口", "应用程序启动", "主版本=" + Helper.GetVersionInfo());
else
log.Log(TimeLog.enumLogType.ltstart, "主窗口", "应用程序启动", "主版本=" + Helper.GetVersionInfo() + " [只读版本]");
m_MainForm.Text += "(" + Helper.GetVersionInfo() + ")";
Application.Run(m_MainForm);
m_rightmgr.Uninit();
}
catch (Exception ex)
{
MessageBox.Show(string.Format("{0}", ex.Message), "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
if (log != null)
{
log.Log(TimeLog.enumLogType.ltend, null, null, null);
log.Clear();
}
}
}
while(m_bRestart); // 检查是否需要重新启动主窗口
if (EnableRemoting)
{
DataTableServerProxy.DisconnectServer();
}
}