Пример #1
0
        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();
        }
Пример #2
0
        /// <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);
                }     
            }
        }
Пример #3
0
        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();
            }
        }
Пример #4
0
        /// <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("在删除某个节点时,发生错误!");
            }
        }
Пример #5
0
        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();
            }
        }