private bool CallLuaPostExport(string strTableName, string strTargetPath)
{
string oldcurdir = System.IO.Directory.GetCurrentDirectory();
System.IO.Directory.SetCurrentDirectory(Application.StartupPath);
string luafile = Path.GetDirectoryName(Process.GetCurrentProcess().MainModule.FileName) + @"\post_export.lua";
if (!File.Exists(luafile))
{
return(false);
}
LuaEx lua = new LuaEx();
lua["RootDir"] = Program.RootDir;
lua["Conn"] = m_conn;
lua.DoString("function trace(txt)\r\n MainForm.LogEditBox.Visible = true\r\n MainForm.LogEditBox.OutputBox.Text = MainForm.LogEditBox.Text ..txt..'\\r\\n' \r\n end");
lua.RegisterFunction("msgbox", this, typeof(TabExport).GetMethod("ShowMessage"));
lua.RegisterFunction("GetDataTableRow", this, typeof(TabExport).GetMethod("GetDataTableRow"));
lua.DoFile(luafile);
LuaFunction fun = lua.GetFunction("onpostexport");
if (fun != null)
{
object[] retobjs = fun.Call(strTableName, strTargetPath);
if (retobjs.Length > 0)
{
System.IO.Directory.SetCurrentDirectory(oldcurdir);
return((bool)retobjs[0]);
}
}
System.IO.Directory.SetCurrentDirectory(oldcurdir);
return(false);
}
private void buttonItem6_Click(object sender, EventArgs e)
{
string strLua = @"
luanet.load_assembly('IconSelector.dll')
local cls1 = luanet.import_type('IconSelector.Implement')
iconselector = cls1()
local os = iconselector
os:EnterEditMode()
os:Init(Conn, RootDir..'\\Ui\\Image\\icon')
os:Show()
";
LuaEx m_lua = new LuaEx();
m_lua["RootDir"] = Program.RootDir;
m_lua["Conn"] = MainForm.conn;
m_lua.DoString(strLua);
}
public string lt2ArrayList_parms(LuaEx lua, string keyword)
{
string ret = keyword;
try
{
LuaInterface.LuaTable lt = (LuaInterface.LuaTable)(lua.DoString("return debug.getinfo(" + keyword + ")")[0]);
if (lt["what"].ToString() == "Lua")
{
ret += "(";
ret += lt["strparms"].ToString();
if (lt["has3dot"].ToString() == "1")
{
ret += ",...";
}
ret += ")";
}
}
catch
{
ret = "";
}
return(ret);
}
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();
}
private bool CallLuaPostExport(string strTableName, string strTargetPath)
{
string oldcurdir = System.IO.Directory.GetCurrentDirectory();
System.IO.Directory.SetCurrentDirectory(Application.StartupPath);
string luafile = Path.GetDirectoryName(Process.GetCurrentProcess().MainModule.FileName) + @"\post_export.lua";
if (!File.Exists(luafile))
return false;
LuaEx lua = new LuaEx();
lua["RootDir"] = Program.RootDir;
lua["Conn"] = m_conn;
lua.DoString("function trace(txt)\r\n MainForm.LogEditBox.Visible = true\r\n MainForm.LogEditBox.OutputBox.Text = MainForm.LogEditBox.Text ..txt..'\\r\\n' \r\n end");
lua.RegisterFunction("msgbox", this, typeof(TabExport).GetMethod("ShowMessage"));
lua.RegisterFunction("GetDataTableRow", this, typeof(TabExport).GetMethod("GetDataTableRow"));
lua.DoFile(luafile);
LuaFunction fun = lua.GetFunction("onpostexport");
if (fun != null)
{
object[] retobjs = fun.Call(strTableName, strTargetPath);
if (retobjs.Length > 0)
{
System.IO.Directory.SetCurrentDirectory(oldcurdir);
return (bool)retobjs[0];
}
}
System.IO.Directory.SetCurrentDirectory(oldcurdir);
return false;
}
/* public string[] CloneValue(string[] strvalues)
{
if (strvalues != null)
return (string[])(strvalues.Clone());
return null;
}*/
private bool CallLuaExport(string strLuaFile, string strTableName, ref string fields, ref string values)
{
LuaEx lua = new LuaEx();
lua["Conn"] = m_conn; // 注册sql连接
lua["RootDir"] = Program.RootDir;
lua.DoString("function trace(txt)\r\n MainForm.LogEditBox.Visible = true\r\n MainForm.LogEditBox.OutputBox.Text = MainForm.LogEditBox.Text ..txt..'\\r\\n' \r\n end");
lua.RegisterFunction("writefile", this, typeof(FileFolderHelper).GetMethod("WriteStringToFile"));
lua.RegisterFunction("msgbox", this, typeof(TabExport).GetMethod("ShowMessage"));
lua.RegisterFunction("GetDataTableRow", this, typeof(TabExport).GetMethod("GetDataTableRow"));
//lua.RegisterFunction("clonevalues", this, typeof(TabExport).GetMethod("CloneValue"));
String luafile = strLuaFile;
try
{
if (!File.Exists(luafile))
return false;
try
{
lua.DoFile(luafile);
LuaFunction fun = lua.GetFunction("onexport");
if (fun != null)
{
ImportInfoHelper helper = new ImportInfoHelper();
ArrayList feildlist = new ArrayList();
feildlist.AddRange(fields.Split(new char[] { '\t' }));
helper.Fields = feildlist;
helper.Values = values.Split(new string[] { "\r\n" }, StringSplitOptions.None);
object[] retobjs = fun.Call(m_strModlName, strTableName, Program.RootDir, helper);
if (retobjs != null && retobjs.GetLength(0) > 0)
{
if (retobjs[0] is bool)
{
StringBuilder strFields = new StringBuilder();
foreach (string strLine in feildlist)
{
strFields.Append(strLine);
strFields.Append('\t');
}
strFields.Remove(strFields.Length - 1, 1);
strFields.Append("\r\n");
fields = strFields.ToString();
StringBuilder strValues = new StringBuilder();
foreach (string strLine in helper.Values)
{
strValues.Append(strLine);
strValues.Append("\r\n");
}
values = strValues.ToString();
return (bool)retobjs[0];
}
}
}
}
catch (Exception ex)
{
ScriptDebugForm frm = Program.MainForm.DebugForm;
frm.OutputBox.Text += ex.Message + "\r\n";
frm.Show();
frm.BringToFront();
}
}
finally
{
lua.Dispose();
}
return false;
}
//数据库脚本编辑调用入口
public void Show(int fieldid, int modtabid, object tag)
{
g_lua["Conn"] = m_lua_p["Conn"];
g_lua["tag"] = tag;
g_lua.DoString(string.Format("OpenWindow({0},{1})", fieldid, modtabid));
}
/* public string[] CloneValue(string[] strvalues)
* {
* if (strvalues != null)
* return (string[])(strvalues.Clone());
* return null;
* }*/
private bool CallLuaExport(string strLuaFile, string strTableName, ref string fields, ref string values)
{
LuaEx lua = new LuaEx();
lua["Conn"] = m_conn; // 注册sql连接
lua["RootDir"] = Program.RootDir;
lua.DoString("function trace(txt)\r\n MainForm.LogEditBox.Visible = true\r\n MainForm.LogEditBox.OutputBox.Text = MainForm.LogEditBox.Text ..txt..'\\r\\n' \r\n end");
lua.RegisterFunction("writefile", this, typeof(FileFolderHelper).GetMethod("WriteStringToFile"));
lua.RegisterFunction("msgbox", this, typeof(TabExport).GetMethod("ShowMessage"));
lua.RegisterFunction("GetDataTableRow", this, typeof(TabExport).GetMethod("GetDataTableRow"));
//lua.RegisterFunction("clonevalues", this, typeof(TabExport).GetMethod("CloneValue"));
String luafile = strLuaFile;
try
{
if (!File.Exists(luafile))
{
return(false);
}
try
{
lua.DoFile(luafile);
LuaFunction fun = lua.GetFunction("onexport");
if (fun != null)
{
ImportInfoHelper helper = new ImportInfoHelper();
ArrayList feildlist = new ArrayList();
feildlist.AddRange(fields.Split(new char[] { '\t' }));
helper.Fields = feildlist;
helper.Values = values.Split(new string[] { "\r\n" }, StringSplitOptions.None);
object[] retobjs = fun.Call(m_strModlName, strTableName, Program.RootDir, helper);
if (retobjs != null && retobjs.GetLength(0) > 0)
{
if (retobjs[0] is bool)
{
StringBuilder strFields = new StringBuilder();
foreach (string strLine in feildlist)
{
strFields.Append(strLine);
strFields.Append('\t');
}
strFields.Remove(strFields.Length - 1, 1);
strFields.Append("\r\n");
fields = strFields.ToString();
StringBuilder strValues = new StringBuilder();
foreach (string strLine in helper.Values)
{
strValues.Append(strLine);
strValues.Append("\r\n");
}
values = strValues.ToString();
return((bool)retobjs[0]);
}
}
}
}
catch (Exception ex)
{
ScriptDebugForm frm = Program.MainForm.DebugForm;
frm.OutputBox.Text += ex.Message + "\r\n";
frm.Show();
frm.BringToFront();
}
}
finally
{
lua.Dispose();
}
return(false);
}
public string lt2ArrayList_parms(LuaEx lua, string keyword)
{
string ret = keyword;
try
{
LuaInterface.LuaTable lt = (LuaInterface.LuaTable)(lua.DoString("return debug.getinfo(" + keyword + ")")[0]);
if (lt["what"].ToString() == "Lua")
{
ret += "(";
ret += lt["strparms"].ToString();
if (lt["has3dot"].ToString() == "1")
ret += ",...";
ret += ")";
}
}
catch
{
ret = "";
}
return (ret);
}
private void buttonItem6_Click(object sender, EventArgs e)
{
string strLua = @"
luanet.load_assembly('IconSelector.dll')
local cls1 = luanet.import_type('IconSelector.Implement')
iconselector = cls1()
local os = iconselector
os:EnterEditMode()
os:Init(Conn, RootDir..'\\Ui\\Image\\icon')
os:Show()
";
LuaEx m_lua = new LuaEx();
m_lua["RootDir"] = Program.RootDir;
m_lua["Conn"] = MainForm.conn;
m_lua.DoString(strLua);
}