C# Dbconn.GetDataTable方法代码示例

        public Boolean Execute()
        {
            bool bReturn = true;
            string strMessage = "";
            try
            {
                Dbconn conn_DRP_EC = new Dbconn("DRP_EC");

                string strDepotID = "";
                DataTable dt = conn_DRP_EC.GetDataTable(@"
                     SELECT [CfgValue]
                      FROM [DRP_EC].[dbo].[Config]
                      WHERE Type='StockSync_DRPToWCS'
                      AND CfgName='Depot'
                ");

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (strDepotID != "")
                    {
                        strDepotID += ",";
                    }
                    strDepotID += "'" + dt.Rows[i]["CfgValue"].ToString() + "'";
                }

                string strSQL = @"
                        SELECT  clothingid AS SKU ,
                                SUM(k_num) AS totalInventory 
                        FROM    dbo.j_stock WITH(NOLOCK)
                        WHERE   depotid in (" + strDepotID + @")
                        GROUP BY clothingid
                        HAVING SUM(k_num)>0
                ";

                DataTable dt1 = m_conn.GetDataTable(strSQL);

                conn_DRP_EC.BeginTransaction();
                conn_DRP_EC.InsertBYSQLBC("Put_SkuStock", dt1);
                conn_DRP_EC.CommitTransaction();

                return bReturn;
            }
            catch (Exception ex)
            {
                m_conn.RollbackTransaction();
                bReturn = false;
                strMessage = ex.ToString();
                throw ex;
            }
            finally
            {
                //HZY.COM.Common.Log.WirteLogWS(bReturn.ToString() + strMessage,null);
            }
        }

        public DataSet GetData()
        {


            int nPage = 1;
            int nPageRowCount = 1000000;
            string strOrder = " DESC  ";
            string strSort = " System_ID";
            string strWhere = " ";

            if (m_request != null && m_request.Tables.Contains("Page_Info"))
            {
                nPage = Convert.ToInt32(m_request.Tables[0].Rows[0]["page"]);
                nPageRowCount = Convert.ToInt32(m_request.Tables[0].Rows[0]["pagerows"]);
                strOrder = m_request.Tables[0].Rows[0]["order"].ToString() != "" ? m_request.Tables[0].Rows[0]["order"].ToString() : strOrder;
                strSort = m_request.Tables[0].Rows[0]["sort"].ToString() != "" ? m_request.Tables[0].Rows[0]["sort"].ToString() : strSort;
                strWhere = m_request.Tables[0].Rows[0]["where"].ToString() != "" ? m_request.Tables[0].Rows[0]["where"].ToString() : strWhere;

                if (strWhere.Trim() != "")
                {
                    strWhere = " AND " + strWhere;
                }
            }

            Dbconn conn = new Dbconn("MDM");
            DataSet ds = conn.GetDataSet(@"
                 SELECT * FROM 
                        (

                        SELECT  
		                        ROW_NUMBER()OVER(ORDER BY " + strSort + " " + strOrder + @") AS ROW_NUMBER_INDEX,
		                                  [System_ID],
                                        [System_Code],
                                        [System_Name],
                                        [System_SN]
                                FROM    [mdm].[dbo].[MDM_System]
                                WHERE 1=1  " + strWhere + @"
                        ) t1 
                        WHERE ROW_NUMBER_INDEX >= " + (nPage * nPageRowCount - nPageRowCount +1 ).ToString() + " AND ROW_NUMBER_INDEX<=" + (nPage * nPageRowCount).ToString() + @"
            ");

            DataTable dt = conn.GetDataTable(@" SELECT  
		                        Count(1) as AllRowCount
                                 FROM dbo.MDM_System WITH(NOLOCK)
                                WHERE 1=1 " + strWhere + @"", new string[0]);

            dt.TableName = "PageRowCount";

            ds.Tables.Add(dt.Copy());
            return ds;


        }

        /// <summary>
        /// 编辑用户信息
        /// </summary>
        /// <returns></returns>
        public bool Execute()
        {
            Dbconn conn = new Dbconn("MDM_Master");
            Dbconn conn_Market = new Dbconn("MDM_Market");

            try
            {
                string strexpression_id = m_request.Tables[0].Rows[0]["expression_id"].ToString();
                string strbody_id = m_request.Tables[0].Rows[0]["body_id"].ToString();
                string struser_login_name = m_request.Tables[0].Rows[0]["user_login_name"].ToString();
                string struser_name = m_request.Tables[0].Rows[0]["user_name"].ToString();
                string struser_notuse = m_request.Tables[0].Rows[0]["user_notuse"].ToString();
                string strAction = m_request.Tables[0].Rows[0]["Action"].ToString();
               
                DataSet ds = conn_Market.GetDataSet(@"SELECT [Body_ID]  FROM [MKT_User_CN_For_HZY_F22] WHERE 
                [email protected]",new string[1]{struser_login_name});


                //如果是添加，先核对用户ID对不对
                if (strAction == "Add" && ds.Tables[0].Rows.Count>0)
                {
                    throw new Exception("要添加的用户登录名已经存在，请核对");
                }

                if (strAction == "Add")
                {
                    strbody_id = conn.GetDataTable("SELECT NEWID() as ID", new string[0]).Rows[0][0].ToString();

                    conn.GetDataSetBySP("SP_CreateMasterData", new string[2] { "@MDMName", "@bodyID" }, new string[2] { "User", strbody_id });

                    AddNewRow(conn, strbody_id, struser_name, struser_login_name, struser_notuse);
                }
                else
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        strbody_id = ds.Tables[0].Rows[i]["Body_ID"].ToString();
                        AddNewRow(conn, strbody_id, struser_name, struser_login_name, struser_notuse);
                    }
                }
                return true;
            }
            catch
            {
                
                throw;
            }



        }

    protected void GetData()
    {
        //Dbconn conn = new Dbconn("ConnectionString_CUSSMS");

        string strConnstr = ConfigurationManager.AppSettings["ConnectionString_CUSSMS"];
        Dbconn conn = new Dbconn(strConnstr, true);

        string strMessage = "";
        string strPhone = this.txtPhone.Text;

        if (strPhone.Trim() != "")
        {
            DataTable dt = conn.GetDataTable(@"SELECT [VM1_CONO]
                                              ,[VM1_DIVI]
                                              ,[VM1_Ticketid]
                                              ,[VM1_Mobtel]
                                              ,[VM1_Rgdt]
                                              ,[VM1_LMDT]
                                          FROM [CUSSMS].[B01_MDM].[VM1_VipticktMotelRecord]
                                          WHERE [email protected]
                                            AND VM1_CONO=100
                                            AND VM1_DIVI='D01'", new string[1] { strPhone });

            if (dt.Rows.Count > 0)
            {
                strMessage = "<div>查询结果<p>";
                strMessage += "手 机 号：" + dt.Rows[0]["VM1_Mobtel"].ToString() + "<p>";
                strMessage += "券　　号：" + dt.Rows[0]["VM1_Ticketid"].ToString() + "<p>";
                strMessage += "申请时间：" + dt.Rows[0]["VM1_LMDT"].ToString() + "<p></div>";
            }
            else
            {
                strMessage = "<font color='red'>没有查询到手机号为" + strPhone + "的相关信息，请重试</font>";
            }
        }
        else
        {
            strMessage = "<font color='red'>手机号不能为空</font>";
        }

        this.lblMessage.Text = strMessage;
    }

        /// <summary>
        /// VEX框架的主入口
        /// </summary>
        /// <returns></returns>
        public DataSet GetData()
        {

            Dbconn conn_DRP_EC = new Dbconn("DRP_EC");

            string strDepotID = "";
            DataTable dt = conn_DRP_EC.GetDataTable(@"
                     SELECT [CfgValue]
                      FROM [DRP_EC].[dbo].[Config]
                      WHERE Type='StockSync_DRPToWCS'
                      AND CfgName='Depot'
                ");

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (strDepotID != "")
                {
                    strDepotID += ",";
                }
                strDepotID += "'" + dt.Rows[i]["CfgValue"].ToString() + "'";
            }

            string strSQL = @"
                        SELECT  clothingid AS SKU ,
                                k_num AS totalInventory 
                        FROM    dbo.j_stock
                        WHERE   depotid in (" + strDepotID + @")
                                AND k_num > 0
                ";

            m_dsRequturn = m_conn.GetDataSet(strSQL);



            return m_dsRequturn;
        }

        /// <summary>
        /// 获取系统环境信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetData()
        {
            try
            {
                int nPage = 1;
                int nPageRowCount = 20;
                string strOrder = " desc  ";
                string strSort = " Env_ID";
                string strWhere = " ";

                if (m_request != null && m_request.Tables.Contains("Page_Info"))
                {
                    nPage = Convert.ToInt32(m_request.Tables[0].Rows[0]["page"]);
                    nPageRowCount = Convert.ToInt32(m_request.Tables[0].Rows[0]["pagerows"]);
                    strOrder = m_request.Tables[0].Rows[0]["order"].ToString() != "" ? m_request.Tables[0].Rows[0]["order"].ToString() : strOrder;
                    strSort = m_request.Tables[0].Rows[0]["sort"].ToString() != "" ? m_request.Tables[0].Rows[0]["sort"].ToString() : strSort;
                    strWhere = m_request.Tables[0].Rows[0]["where"].ToString() != "" ? m_request.Tables[0].Rows[0]["where"].ToString() :  strWhere;

                    if (strWhere.Trim() != "")
                    {
                        strWhere = " AND " + strWhere;
                    }
                }

                Dbconn conn = new Dbconn("MDM");
                DataSet ds = conn.GetDataSet(@"
                 SELECT * FROM 
                        (

                        SELECT  
		                        ROW_NUMBER()OVER(ORDER BY " + strSort +" " + strOrder + @") AS ROW_NUMBER_INDEX,
		                        t1.[Authorization_ID] ,
                                t1.[Env_ID] ,
                                t3.[Env_SN] ,
                                t3.[Env_Name] ,
                                t1.[Authorization_Name] ,
                                t1.[Createtime] ,
                                t1.[Updatetime] ,
                                CONVERT(BIGINT, t1.[Timestamps]) Timestamps
                        FROM    [dbo].MDM_System_Authorization t1 WITH ( NOLOCK )
                                LEFT JOIN  dbo.MDM_System_Env  t3 WITH ( NOLOCK ) ON t3.Env_ID=t1.Env_ID

                                WHERE 1=1 " + strWhere + @"
                        ) t1 
                        WHERE ROW_NUMBER_INDEX >= " + (nPage * nPageRowCount - nPageRowCount +1 ).ToString() + " AND ROW_NUMBER_INDEX<=" + (nPage * nPageRowCount).ToString() + @"
            ");

                DataTable dt = conn.GetDataTable(@" SELECT  
		                        Count(1) as AllRowCount
                                 FROM  [dbo].MDM_System_Authorization t1 WITH ( NOLOCK )
                                LEFT JOIN  dbo.MDM_System_Env  t3 WITH ( NOLOCK ) ON t3.Env_ID=t1.Env_ID
                                WHERE 1=1 " + strWhere + @"", new string[0]);

                dt.TableName = "PageRowCount";

                ds.Tables.Add(dt.Copy());

                return ds;
            }
            catch  
            {
                throw;

            }



        }

        private void FindFatherMenu(DataSet ds, Dbconn conn)
        {
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                string strMenu_Father_ID = ds.Tables[0].Rows[i]["Menu_Father_ID"].ToString();
                DataRow[] dr = ds.Tables[0].Select("Menu_ID=" + strMenu_Father_ID); //找到是否已经存在父节点
                if (dr.Length > 0)
                {
                    continue;
                }
                DataTable dt = conn.GetDataTable(@"
                         SELECT Menu_Id ,
                                Menu_Code ,
                                Menu_Name ,
                                Menu_Father_ID ,
                                ActionURL
                         FROM   dbo.MDM_System_Menu
                         WHERE Menu_ID='" + strMenu_Father_ID + @"'
                ", new string[0]);

                if (dt.Rows.Count > 0)
                {
                    ds.Tables[0].ImportRow(dt.Rows[0]);
                }
            }
        }

        /// <summary>
        /// 根据XML内容更新到货计划
        /// </summary>
        /// <returns></returns>
        public DataSet GetData()
        {
            try
            {
                DataSet ds = new DataSet();
                DataSet ds_Return = new DataSet();
                DataSet dsRequest = new DataSet();

                ArrayList listAMSendList = new ArrayList();

                Dbconn conn = new Dbconn("GL_ERP");

                string strXML = "";
                string strSQL = "";
                strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
                //XML是前台传入
                ds = Common.Common.GetDSByExcelXML(strXML);

                if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET")
                //读取ROWS到/ROWS 中的参数
                {
                    string strWHERE = "isnull(PRA_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
                    if (ds.Tables["LIST"].Columns.Contains("Condition"))
                    {
                        if (ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() != "")
                        //读取LIST之间参数
                        {
                            strWHERE += " AND (" + ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() + ")";
                        }
                    }

                    strSQL = @" SELECT 
                                [PRA_CONO]
                               ,[PRA_DIVI]
                               ,[PRA_ArrivalPlanId]
                               ,[PRA_ArrivalPlanCode]
                               ,[PRA_PR1_OrderId]
                               ,[PRA_PerchaseTotal]
                               ,[PRA_ArrivalTotal]
                               ,[PRA_PlanTotal]
                               ,[PRA_PerchaseAmount]
                               ,[PRA_ArrivalAmount]
                               ,[PRA_PlanAmount]
                               ,[PRA_TotalDiff]
                               ,[PRA_AmountDiff]
                               ,[PRA_Status]
                               ,[PRA_RgUser]
                               ,[PRA_RgDt]
                               ,[PRA_LmUser]
                               ,[PRA_LmDt]
                               ,[PRA_UptNo]
                               ,A.PR1_OrderCode
                               FROM B02_Bill.PRA_ArrivalPlan
                               LEFT JOIN B02_Bill.PR1_Order A ON PRA_PR1_OrderId=PR1_OrderId                           
                               WHERE " + strWHERE;
                    ds_Return = conn.GetDataSet(strSQL);
                }
                else
                {

                    ArrayList tableList = new ArrayList();
                    tableList.Add("B02_Bill.PRA_ArrivalPlan");
                    conn.BeginTransaction();
                    //开启事物
                    conn.TableLock(tableList);
                    //锁表
                    try
                    {
                        if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
                        {
                            for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
                            {
                                strSQL = @"UPDATE B02_Bill.PRA_ArrivalPlan SET PRA_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1' 
                               WHERE PRA_ArrivalPlanId='" + ds.Tables["LIST"].Rows[i]["PRA_ArrivalPlanId"].ToString() + @"'
                                AND isnull(PRA_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' 
                                ";
                                conn.ExcuteQuerryByTran(strSQL);
                            }

                        }
                        else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
                        {
                            for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
                            {
                                DataTable dt = ds.Tables["LIST"].Clone();
                                dt.ImportRow(ds.Tables["LIST"].Rows[i]);

                                strSQL = @"SELECT * FROM B02_Bill.PRA_ArrivalPlan WHERE  [email protected]
                                AND isnull(PRA_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
                                 ";

                                DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PRA_ArrivalPlanId"].ToString() });
                                //update
                                if (dt_SR.Rows.Count > 0)
                                {
                                    //处理结果
//.........这里部分代码省略.........

        public static void UpdateFullText(string pstrWSID, Dbconn pconn)
        {
            string strSQL = @"
                        DECLARE @cols NVARCHAR(MAX)
                        SET @cols = ''
                        SELECT  @cols = @cols + 'convert(varchar(1000),isnull(' + name
                                + ',''''),21)+'' ''+'
                        FROM    syscolumns
                        WHERE   ( id = ( SELECT id
                                         FROM   sysobjects
                                         WHERE  ( name = 'WS0E_WSExtDoc' )
                                       ) )

                        SELECT  @cols as A

                ";

            string strInfoColumns = pconn.GetDataTableFirstValue(strSQL).ToString();
            strSQL = strInfoColumns.Substring(0, strInfoColumns.Length - 1);
            strSQL = "SELECT " + strSQL + "  FROM [B01_MDM].[WS0E_WSExtDoc] WHERE [email protected]";
            string strValue = pconn.GetDataTableFirstValue(strSQL, new string[] { pstrWSID }).ToString();

            strSQL = @"
                        DECLARE @cols NVARCHAR(MAX)
                        SET @cols = ''
                        SELECT  @cols = @cols + 'convert(varchar(1000),isnull(' + name
                                + ',''''),21)+'' ''+'
                        FROM    syscolumns
                        WHERE   ( id = ( SELECT id
                                         FROM   sysobjects
                                         WHERE  ( name = 'WS0M_WSMethodDoc' )
                                       ) )

                        SELECT  @cols as A

                ";


            string strFunctionColumns = pconn.GetDataTableFirstValue(strSQL).ToString();

            strSQL = "SELECT " + strFunctionColumns.Substring(0, strFunctionColumns.Length - 1) + " AS A  FROM [B01_MDM].[WS0M_WSMethodDoc] WHERE [email protected]";


            DataTable dt = pconn.GetDataTable(strSQL, new string[] { pstrWSID });
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                strValue += " " + dt.Rows[i]["A"].ToString();
            }

            strSQL = " SELECT 1 AS A FROM [B05_RPT].[WS0V_WSMethodView] WHERE [email protected]";
            if (pconn.GetDataTableRowCount(strSQL, new string[] { pstrWSID }) == 1)
            {
                pconn.ExcuteQuerryByTran("UPDATE [B05_RPT].[WS0V_WSMethodView] SET [email protected] WHERE [email protected]", new string[] { strValue, pstrWSID });
            }
            else
            {
                pconn.ExcuteQuerryByTran("INSERT INTO [B05_RPT].[WS0V_WSMethodView](fulltext,WSID)Values(@Param0,@PARAM1)", new string[] { strValue, pstrWSID });
            }
        }

        /// <summary>
        /// 对审核步骤进行审核
        /// </summary>
        /// <returns></returns>
        public DataSet GetData()
        {
            try
            {
                //DataSet ds = new DataSet();
                DataSet ds_Return = new DataSet();
                DataSet dsRequest = new DataSet();

                ArrayList listAMSendList = new ArrayList();


                Dbconn conn = new Dbconn("HZY_ITSM");

                string strXML = "";
                strXML = m_request.Tables[0].Rows[0]["XML"].ToString();

                DataSet ds = Common.Common.GetDSByExcelXML(strXML);

                string strCheck_ID = "";
                string strCheck_User = "";
                string strCheck_Result = "";
                string strCheck_RemarK = "";
                string strKey_Value = "";
                string strKeyColumn = "";
                string strRequestTableName = "";

                strCheck_ID = ds.Tables["LIST"].Rows[0]["ID"].ToString();
                if (strCheck_ID == "")
                {
                    ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "1:您的请求有误!" });

                    return ds_Return;

                }

                DataTable dt = conn.GetDataTable(@"
                            SELECT  t1.ID ,
                                    t1.Key_ID ,
                                    t1.Seq_Index ,
                                    t1.Seq_Name ,
                                    t1.Check_User ,
                                    t1.Check_Type,
                                    t1.Check_Type_Table_Name,
                                    t1.Check_Type_Key_Column,
                                    t1.Check_Date ,
                                    t1.Check_Result ,
                                    t1.Remark ,
                                    t2.Table_Templete
                            FROM    dbo.Check_List t1
                                    LEFT JOIN dbo.check_Table_Type t2 ON t1.Check_Type = t2.Check_Type
                            WHERE   t1.id = @Param0

                    ", new string[1] { strCheck_ID });

                if (dt.Rows.Count == 0)
                {
                    ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "2:您的请求有误！" });

                    return ds_Return;
                }

                strKey_Value = dt.Rows[0]["Key_ID"].ToString();
                strKeyColumn = dt.Rows[0]["Check_Type_Key_Column"].ToString();
                strRequestTableName = dt.Rows[0]["Check_Type_Table_Name"].ToString();

                if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET")
                {

                    ITSM_Server_Request_EditByXML itsm_SR = new ITSM_Server_Request_EditByXML();

                    strXML = @"<ROOT><OPTYPE>GET</OPTYPE>
                                        <LIST>
                                        <ROW>    
                                        <condition>" + strKeyColumn + @"='" + strKey_Value + @"'</condition>
                                        </ROW>
                                        </LIST>
                                        </ROOT>";
                    DataSet ds_ITSMSR_Request = Common.Common.GetRequestDataSet(new string[1] { "XML" }, new string[1] { strXML});
                    itsm_SR.Request = ds_ITSMSR_Request;
                    DataSet ds_ITSMSR_Response = itsm_SR.GetData();
                    if (ds_ITSMSR_Response.Tables.Count == 0 || ds_ITSMSR_Response.Tables[0].Rows.Count == 0)
                    {
                        ds_Return = Common.Common.GetRequestDataSet(new string[] { "Result" }, new string[] { "3:获取原始单据有误！" });
                        return ds_Return;
                    }

                    string strTableTemplete = dt.Rows[0]["Table_Templete"].ToString();
                    for (int i = 0; i < ds_ITSMSR_Response.Tables[0].Columns.Count; i++)
                    {
                        string strTemp = ds_ITSMSR_Response.Tables[0].Rows[0][i].ToString().Replace("\n", "<p>");
                        if(strTemp == "")
                        {
                            strTemp = "&nbsp;";
                        }
                        strTableTemplete = strTableTemplete.Replace("[@@" + ds_ITSMSR_Response.Tables[0].Columns[i].ColumnName + "]", strTemp);
                    }
//.........这里部分代码省略.........

    public static HZYmessage CheckUser(string SessionID,bool pUpdateLastReustTime)
    {
        HZYmessage message = new HZYmessage();
        if (SessionID == null || SessionID == "")
        {
            throw new Exception("身份验证错误");
        }
        Dbconn conn = new Dbconn("WSRR");
        string strSQL = @"
              SELECT 
                    [LoginSessionID]
                  ,[LoginUser]
                  ,[LoginTime]
              FROM F01_Cert.Login
              where [email protected]
              and LogoutTime is null
        ";

        DataTable dt = conn.GetDataTable(strSQL, new string[] { SessionID });
        if (dt.Rows.Count == 1)
        {
            message.Session_ID = dt.Rows[0]["LoginSessionID"].ToString();
            //message.Employee_ID = dt.Rows[0]["LoginUser"].ToString();
            message.User_ID = dt.Rows[0]["LoginUser"].ToString();
            message.User_Name = dt.Rows[0]["LoginUser"].ToString();
            message.User_Name_CN = dt.Rows[0]["LoginUser"].ToString();

            if (pUpdateLastReustTime)
            {
                //需要更新用户的最后访问日期
                conn.BeginTransaction();
                conn.ExcuteQuerryByTran("update F01_Cert.Login set LastVisitTime=getdate() where [email protected]", new string[] { message.Session_ID });
                conn.CommitTransaction();
            }

            return message;
        }
        else
        {
            throw new Exception("身份验证错误");
        }
    }

        public bool Execute()
        {

            Dbconn conn = new Dbconn("DBDict");

            //取到所有的数据库服务器
            DataTable dt_DBServer = conn.GetDataTable("SELECT * FROM DBServer ", new string[0]);

            //清空数据
            conn.ExcuteQuerryByTran(@"
                    TRUNCATE TABLE DBDict.dbo.TableDict
                    TRUNCATE TABLE DBDict.dbo.TableSize
                    TRUNCATE TABLE DBDict.dbo.DBInfo
                    TRUNCATE TABLE DBDict.dbo.TableIndex
                    TRUNCATE TABLE DBDict.dbo.DBOtherInfo
                    TRUNCATE TABLE DBDict.dbo.LinkServers
                ");


            if (dt_DBServer.Rows.Count > 0)
            {

                for (int i = 0; i < dt_DBServer.Rows.Count; i++)
                {
                    string strConnString = "DataBase=master;Password=" + dt_DBServer.Rows[i]["sql密码"].ToString() + ";User ID=" + dt_DBServer.Rows[i]["sql用户名"].ToString() + ";Data Source=" + dt_DBServer.Rows[i]["ip"].ToString() + ";";

                    string strSQL = "SELECT Name FROM Master..SysDatabases ORDER BY Name";


                    Dbconn conn_AllDB = new Dbconn(strConnString, true);

                    //所有的数据库名
                    DataTable dt_AllDB = conn_AllDB.GetDataTable(strSQL, new string[0]);

                    for (int k = 0; k < dt_AllDB.Rows.Count; k++)
                    {

                        if (dt_AllDB.Rows[k][0].ToString() == "tempdb")
                        {
                            continue;
                        }

                        strConnString = "DataBase=" + dt_AllDB.Rows[k][0].ToString() + ";Password=" + dt_DBServer.Rows[i]["sql密码"].ToString() + ";User ID=" + dt_DBServer.Rows[i]["sql用户名"].ToString() + ";Data Source=" + dt_DBServer.Rows[i]["ip"].ToString() + ";";

                        Dbconn conn_DB = new Dbconn(strConnString, true);


                        string str_获取数据库中所有表的字段属性 = @"
SELECT  @@SERVERNAME 服务器,db_name() 数据库名,d.name AS 表名, 
      CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明, 
      a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id, 
      a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识, 
      CASE WHEN EXISTS
          (SELECT 1
         FROM dbo.sysindexes si INNER JOIN
               dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
               dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
               dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
         WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键, 
      b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION') 
      AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数, 
      CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '') 
      AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间, 
      CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间,
      GETDATE() 采集时间
FROM dbo.syscolumns a with(nolock)  LEFT OUTER JOIN
      dbo.systypes b with(nolock)  ON a.xtype = b.xusertype INNER JOIN
      dbo.sysobjects d with(nolock)  ON a.id = d.id AND d.xtype = 'U' AND 
      d.status >= 0 LEFT OUTER JOIN
      dbo.syscomments e with(nolock)  ON a.cdefault = e.id LEFT OUTER JOIN
      sys.extended_properties g with(nolock)  ON a.id = g.major_id AND a.colid = g.minor_id AND 
      g.name = 'MS_Description' LEFT OUTER JOIN
      sys.extended_properties f with(nolock)  ON d.id = f.major_id AND f.minor_id = 0 AND 
      f.name = 'MS_Description'
ORDER BY d.name, 字段序号	
        ";


                        string str_显示当前数据库中用户表的记录数量及大小 = @"
SELECT @@servername 服务器,db_name() 数据库,OBJECT_NAME(id) AS 表名,
       rowcnt AS 记录数量,
       8 * reserved / 1024 AS '大小(MB)',
       GETDATE() 采集时间
FROM   sysindexes a with(nolock) 
       LEFT JOIN sys.objects b with(nolock) 
            ON  a.name = b.name
WHERE  indid < 2
       AND OBJECTPROPERTY(id, 'isusertable ') = 1
ORDER BY
       rowcnt DESC
";


                        string str_查询所有表的索引 = @"
SELECT @@SERVERNAME AS 服务器,DB_NAME() AS 数据库, 
      c.name AS 表名, 
      a.name AS 索引名称, d.name AS 列名, 
      b.keyno AS 索引顺序, CASE indexkey_property(c.id, b.indid, b.keyno, 'isdescending') 
      WHEN 1 THEN '降序' WHEN 0 THEN '升序' END AS 排序, CASE WHEN p.id IS NULL 
      THEN '' ELSE '√' END AS 主键, CASE INDEXPROPERTY(c.id, a.name, 'IsClustered') 
//.........这里部分代码省略.........

        /// <summary>
        /// 根据XML内容更新到货计划字表
        /// </summary>
        /// <returns></returns>
        public DataSet GetData()
        {
            try
            {
                DataSet ds = new DataSet();
                DataSet ds_Return = new DataSet();
                DataSet dsRequest = new DataSet();

                ArrayList listAMSendList = new ArrayList();

                Dbconn conn = new Dbconn("GL_ERP");

                string strXML = "";
                string strSQL = "";
                strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
                //XML是前台传入
                ds = Common.Common.GetDSByExcelXML(strXML);

          
              if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET")
                //读取ROWS到/ROWS 中的参数
                {
                    string strWHERE = "isnull(PRAD_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
                    string strWHERE1 = "isnull(PRAD_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'"; 
                    if (ds.Tables["LIST"].Columns.Contains("Condition"))
                    {
                        if (ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() != "")
                        //读取LIST之间参数
                        {
                            strWHERE += " AND (" + ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() + ")";
                        }
                         if (ds.Tables["LIST"].Rows[0]["Condition1"].ToString().Trim() != "")
                        //读取LIST之间参数
                        {
                            strWHERE1 += " AND (" + ds.Tables["LIST"].Rows[0]["Condition1"].ToString().Trim() + ")";
                        }

                    }
                     
                    strSQL = @"SELECT * FROM B02_Bill.PRAD_ArrivalPlanDetail WHERE " + strWHERE;
                                
                       DataTable dt_SR = conn.GetDataTable(strSQL);

                        if (dt_SR.Rows.Count > 0)
                        {
                            strSQL = @"SELECT
                                       [PRAD_CONO]
                                      ,[PRAD_DIVI]
                                      ,[PRAD_ArrivalPlanDetailId]
                                      ,[PRAD_PRA_ArrivalPlanId]
                                      ,[PRAD_PD0_ProductId]
                                      ,[PRAD_UnitID]
                                      ,[PRAD_PR1D_OrderDetailId]
                                      ,[PRAD_ArrivalDate]
                                      ,[PRAD_PerchaseTotal]
                                      ,[PRAD_ArrivalTotal]
                                      ,[PRAD_PlanTotal]
                                      ,[PRAD_PlanArrival]
                                      ,[PRAD_PlanArrivalAmount]
                                      ,[PRAD_TotalDiff]
                                      ,[PRAD_Status]
                                      ,[PRAD_RgUser]
                                      ,[PRAD_RgDt]
                                      ,[PRAD_LmUser]
                                      ,[PRAD_LmDt]
                                      ,[PRAD_UptNo]
                             FROM  B02_Bill.PRAD_ArrivalPlanDetail 
                             WHERE " + strWHERE;
                        }
                         
                      else
                       {
                           strSQL = @"SELECT  
                                 [PRAD_CONO]
                                ,[PRAD_DIVI]      
                                ,'' AS [PRAD_ArrivalPlanDetailId]                                   
                                ,[PRA_ArrivalPlanId]
                                ,[PR1D_OrderDetailId] 
                                ,[PR1D_PD0_ProductId]                                                     
                                ,[PR1D_UnitID]
                                ,'' AS [PRAD_ArrivalDate]
                                ,ISNULL([PR1D_Total],'0') AS [PR1D_Total]
                                ,SUM(ISNULL(PRAD_PlanArrival,'0')) AS PRAD_PlanTotal
                                ,SUM(ISNULL([PRAD_ArrivalTotal],'0')) as PRAD_ArrivalTotal
                                ,'0' AS  [PRAD_PlanArrival] 
                                ,'0' AS  [PRAD_PlanArrivalAmount]
                                ,'0' as [PRAD_TotalDiff]
                                ,'' as [PRAD_Status]   
                                ,'' as [PRAD_RgUser]
                                ,'' as [PRAD_RgDt]
                                ,'' as [PRAD_LmUser]
                                ,'' as [PRAD_LmDt]
                                ,'0' as [PRAD_UptNo]
                            FROM  B02_Bill.PRA_ArrivalPlan 
                            LEFT JOIN B02_Bill.PR1D_OrderDetail  ON PRA_PR1_OrderId=PR1D_PR1_OrderId
                            LEFT JOIN B02_Bill.PRAD_ArrivalPlanDetail ON PR1D_OrderDetailId=PRAD_PR1D_OrderDetailId  
//.........这里部分代码省略.........

        public bool Execute()
        {
            string strSystem_Fid = "144";
            string strSystem_id = "ST0301A";
            string strSystem_Name = "F22分销系统 CS总部版";
            string strCompany_id = "235";
            Dbconn conn = new Dbconn("F22");
            Dbconn conn_MDM = new Dbconn("MDM");

            //取到总部的数据
            DataSet ds = conn.GetDataSet(
                @"  SELECT  userid ,
                            username
                    FROM    j_user t1 WITH ( NOLOCK )
                    WHERE   1 = 1
                            AND dptype = 0
                            AND notuse = 0
 	                    ");


            DataTable dt_Add = new DataTable();
            dt_Add.Columns.Add("System_FID");
            dt_Add.Columns.Add("System_ID");
            dt_Add.Columns.Add("System_Name");
            dt_Add.Columns.Add("AM_User_ID");
            dt_Add.Columns.Add("AM_Login");
            dt_Add.Columns.Add("Company_ID");
            dt_Add.Columns.Add("System_User_ID");
            dt_Add.Columns.Add("System_User_Name");
            dt_Add.Columns.Add("Create_AM_User_ID");
            dt_Add.Columns.Add("CreateTime");
            dt_Add.Columns.Add("UpdateTime");

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                string strName = ds.Tables[0].Rows[i]["username"].ToString();
                string strUserID = ds.Tables[0].Rows[i]["userid"].ToString();

                DataTable dt_AM = conn_MDM.GetDataTable(@"SELECT AM_User_ID,AM_login FROM dbo.AM_User
                                                WHERE [email protected]", new string[1] { strName });
                if (dt_AM.Rows.Count > 0)
                {
                    DataRow dr_add = dt_Add.NewRow();
                    dr_add["System_FID"] = strSystem_Fid;
                    dr_add["System_ID"] = strSystem_id;
                    dr_add["System_Name"] = strSystem_Name;
                    dr_add["AM_User_ID"] = dt_AM.Rows[0]["AM_User_ID"].ToString();
                    dr_add["AM_Login"] = dt_AM.Rows[0]["AM_Login"].ToString();

                    dr_add["Company_ID"] = strCompany_id;

                    dr_add["System_User_ID"] = strUserID;
                    dr_add["System_User_Name"] = strName;
                    dr_add["Create_AM_User_ID"] = "0"; //默认为系统自动建立
                    dr_add["CreateTime"] = DateTime.Now;
                    dr_add["UpdateTime"] = DateTime.Now;
                    dt_Add.Rows.Add(dr_add);
                }
            }

            if (dt_Add.Rows.Count > 0)
            {
                ArrayList listLockTable = new ArrayList();
                listLockTable.Add("SSO_System_Relation");

                conn_MDM.BeginTransaction();
                try
                {
                    conn_MDM.TableLock(listLockTable);
                    conn_MDM.Excute("DELETE FROM SSO_System_Relation WHERE System_FID='" + strSystem_Fid + "'");
                    conn_MDM.InsertBYSQLBC("SSO_System_Relation", dt_Add);
                    conn_MDM.CommitTransaction();
                }
                catch (Exception)
                {
                    conn_MDM.RollbackTransaction();
                    throw;
                }

            }

            return true;

        }

//.........这里部分代码省略.........
                else
                {

                    ArrayList tableList = new ArrayList();
                    tableList.Add("B02_Bill.PR0_Contract");
                    conn.BeginTransaction();
                    //开启事物
                    conn.TableLock(tableList);
                    //锁表
                    try
                    {
                        if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
                        {
                            for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
                            {
                                strSQL = @"UPDATE B02_Bill.PR0_Contract SET PR0_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
                               WHERE PR0_ContractId='" + ds.Tables["LIST"].Rows[i]["PR0_ContractId"].ToString() + @"'
                                AND ISNULL(PR0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
                                  ";
                                conn.ExcuteQuerryByTran(strSQL);
                            }

                        }
                        else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
                        {
                            for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
                            {
                                DataTable dt = ds.Tables["LIST"].Clone();
                                dt.ImportRow(ds.Tables["LIST"].Rows[i]);

                                strSQL = @"SELECT * FROM B02_Bill.PR0_Contract WHERE  [email protected]
                                  AND ISNULL(PR0_Status,'') <>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";

                                DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PR0_ContractId"].ToString() });
                                //update
                                if (dt_SR.Rows.Count > 0)
                                {

                                    strSQL = @"SELECT * FROM M02_BPM.WFP_AuditFlow  WHERE  [email protected]
                                         AND  WFP_AFResultId<>''
                                             ";

                                    DataTable dt_CO = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PR0_ContractId"].ToString() });
                                //update
                                 if (dt_CO.Rows.Count > 0)
                                 {
                                     throw new Exception("合同已审核，不能进行修改！");      
                                 }
                                 else

                                     //处理结果
                                     if (ds.Tables["LIST"].Rows[i]["PR0_LmUser"].ToString() != "")
                                     {
                                         string strCode = dt_SR.Rows[0]["PR0_UptNo"].ToString();
                                         if (strCode == "0")
                                         {
                                             strCode = Convert.ToInt32(strCode) + 1.ToString();
                                         }
                                         else
                                         {
                                             strCode = "0";
                                         }

                                         if (!dt.Columns.Contains("PR0_LmDt"))
                                         {
                                             dt.Columns.Add("PR0_LmDt");