C# DB2Connection.Close方法代码示例

 internal List<Right> GetBexisRights(string dataBase,  Dictionary<int, int> dataSetsMapping)
 {
     List<Right> bexisRights = new List<Right>();
     string datasetQuery = "";
     foreach (var dataSetMapping in dataSetsMapping)
     {
         datasetQuery += "DATASETID = "+ dataSetMapping.Key;
         if (dataSetsMapping.Last().Key != dataSetMapping.Key)
             datasetQuery += " or ";
     }
     if (dataSetsMapping.Any())
     {
         datasetQuery = "where " + datasetQuery + "";
     }
     // DB query
     string mySelectQuery = "SELECT ROLENAME, DATASETID, FOREDIT, APPLICATIONNAME FROM \"PROVIDER\".\"RIGHTS\"   "+ datasetQuery;
     DB2Connection connect = new DB2Connection(dataBase);
     DB2Command myCommand = new DB2Command(mySelectQuery, connect);
     connect.Open();
     DB2DataReader myReader = myCommand.ExecuteReader();
     while (myReader.Read())
     {
         bexisRights.Add(new Right()
         {
             RoleName = myReader.GetString(0),
             DataSetId = (int)(myReader.GetValue(1)),
             CanEdit = myReader.GetString(2)=="N"?false:true
         });
     }
     myReader.Close();
     connect.Close();
     return bexisRights;
 }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">ArrayList</param>
        public static void ExecuteSqlTran(ArrayList sqlList)
        {
            bool mustCloseConnection = false;
            string ConString = System.Configuration.ConfigurationSettings.AppSettings["conInsertDB2"].ToString();
            using (DB2Connection conn = new DB2Connection(ConString))
            {
                conn.Open();
                using (DB2Transaction trans = conn.BeginTransaction())
                {
                    DB2Command cmd = new DB2Command();
                    try
                    {
                        for (int i = 0; i < sqlList.Count; i++)
                        {
                            string cmdText = sqlList[i].ToString();
                            PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, null, out mustCloseConnection);
                            int val = cmd.ExecuteNonQuery();

                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                    finally
                    {
                        conn.Close();
                        cmd.Dispose();
                    }
                }
            }
        }

        public void IBMBuldCopy()
        {
            string sql_bruv_1 = "Server=10.24.1.202:446;Database=BRUVDB4V;UID=ATWO;PWD=24rete31;Max Pool Size=100;Min Pool Size=10;";
            string sql_frymek = "Server=10.27.5.197:50000;Database=BRUV;UID=db2admin;[email protected]#$;Max Pool Size=100;Min Pool Size=10;";

            try
            {
                using (DB2Connection myConn = new DB2Connection(sql_bruv_1))
                {
                    using (DB2Connection conn = new DB2Connection(sql_frymek))
                    {
                        myConn.Open();
                        conn.Open();

                        log.InfoFormat("{0}: Time elapsed: {1}", Table, DateTime.Now);
                        string myInsertQuery = String.Format("SELECT * FROM RREV.{0}", Table);

                        using (DB2Command myDB2Command = new DB2Command(myInsertQuery, myConn))
                        {
                            using (DB2DataReader reader = myDB2Command.ExecuteReader())
                            {
                                using (DB2BulkCopy salesCopy = new DB2BulkCopy(conn))
                                {
                                    salesCopy.DestinationTableName = String.Format("ATWO.{0}", Table);
                                    salesCopy.WriteToServer(reader);
                                    var errors = salesCopy.Errors;
                                    if (errors.Count > 0)
                                    {
                                        log.ErrorFormat("table:{0}, errors:{1}", Table, errors.Count);
                                        foreach (var er in errors)
                                        {
                                            log.ErrorFormat("table:{0}, msg:{1}", Table, er.ToString());
                                        }
                                    }
                                    salesCopy.Close();

                                    myConn.Close();
                                    conn.Close();
                                }
                            }
                        }
                    }
                }
                log.InfoFormat("{0}: Time elapsed: {1}", Table, DateTime.Now);
            }
            catch (Exception ex)
            {
                log.Error(ex.ToString());
                Console.WriteLine(String.Format("error: {0} with exception: {1}", Table, ex.Message));
            }
        }

 internal List<string> GetBexisUsersInRole(string dataBase, string roleName)
 {
     List<string> bexisUsersInRole = new List<string>();
     // DB query
     string mySelectQuery = "SELECT USERNAME FROM \"PROVIDER\".\"USERSINROLES\" where ROLENAME='" + roleName + "'";
     DB2Connection connect = new DB2Connection(dataBase);
     DB2Command myCommand = new DB2Command(mySelectQuery, connect);
     connect.Open();
     DB2DataReader myReader = myCommand.ExecuteReader();
     while (myReader.Read())
     {
         bexisUsersInRole.Add(myReader.GetValue(0).ToString());
     }
     myReader.Close();
     connect.Close();
     return bexisUsersInRole;
 }

 internal List<string> GetBexisRoles(string DataBase)
 {
     List<string> bexisRoles = new List<string>();
     // DB query
     string mySelectQuery = "SELECT ROLENAME, APPLICATIONNAME FROM \"PROVIDER\".\"ROLES\"";
     DB2Connection connect = new DB2Connection(DataBase);
     DB2Command myCommand = new DB2Command(mySelectQuery, connect);
     connect.Open();
     DB2DataReader myReader = myCommand.ExecuteReader();
     while (myReader.Read())
     {
         bexisRoles.Add(myReader.GetValue(0).ToString());
     }
     myReader.Close();
     connect.Close();
     return bexisRoles;
 }

        public void queryAuthorAndVariables(ref User user, ref string variableNames, string dataSetID, string DataBase)
        {
            string mySelectQuery = "select X.* from explorer.datasets, XMLTABLE ('$METADATA/*:metaProfile' Columns " +
                                        "Author varchar(256) Path '*:general/*:metadataCreator'," +
                                        "VarNames varchar(1028) Path 'string-join(*:data/*:dataStructure/*:variables/*:variable/*:name,\",\")'" +
                                    ") as X where datasetid = " + dataSetID + ";";

            DB2Connection connect = new DB2Connection(DataBase);
            DB2Command myCommand = new DB2Command(mySelectQuery, connect);
            connect.Open();
            DB2DataReader myReader = myCommand.ExecuteReader();

            string author = "";
            while (myReader.Read())
            {
                author = myReader.GetString(0);
                variableNames = myReader.GetString(1);
            }
            myReader.Close();
            connect.Close();

            SubjectManager subjectManager = new SubjectManager();
            user = subjectManager.UsersRepo.Get(u => author.Equals(u.FullName)).FirstOrDefault();
        }

        // query bexis1 user from provider.users and generate a random password
        public List<UserProperties> GetFromBExIS(string DataBase)
        {
            List<UserProperties> transferUsers = new List<UserProperties>();

            // DB query
            string mySelectQuery = "select username, email, firstname, lastname, " +
                                   "organization, projectname, projectleader, " +
                                   "url, phone, mobile, fax, original, street, zipcode, city";
            mySelectQuery += " from provider.users;";
            DB2Connection connect = new DB2Connection(DataBase);
            DB2Command myCommand = new DB2Command(mySelectQuery, connect);
            connect.Open();
            DB2DataReader myReader = myCommand.ExecuteReader();
            // random password
            Random gen = new Random();
            while (myReader.Read())
            {
                UserProperties transferUser = new UserProperties();

                // bexis1 DB user data
                transferUser.username = myReader.GetValue(0).ToString();
                transferUser.email = myReader.GetValue(1).ToString();
                transferUser.firstname = myReader.GetValue(2).ToString();
                transferUser.lastname = myReader.GetValue(3).ToString();
                transferUser.organization = myReader.GetValue(4).ToString();
                transferUser.projectname = myReader.GetValue(5).ToString();
                transferUser.projectleader = myReader.GetValue(6).ToString();
                transferUser.url = myReader.GetValue(7).ToString();
                transferUser.phone = myReader.GetValue(8).ToString();
                transferUser.mobile = myReader.GetValue(9).ToString();
                transferUser.fax = myReader.GetValue(10).ToString();
                transferUser.original = myReader.GetValue(11).ToString();
                transferUser.street = myReader.GetValue(12).ToString();
                transferUser.zipcode = myReader.GetValue(13).ToString();
                transferUser.city = myReader.GetValue(14).ToString();
                // bexis2 required security data
                transferUser.password = randomPassword(ref gen); // random password
                transferUser.securityQuestionId = 1;
                transferUser.securityAnswer = "1";
                transferUser.authenticatorId = 1;

                // add to list; username required
                if (transferUser.username != "")
                    transferUsers.Add(transferUser);
            }
            myReader.Close();
            connect.Close();

            return transferUsers;
        }

        public XmlDocument getMetadataXml(string dataSetID, string DataBase)
        {
            XmlDocument doc = new XmlDocument();

            string mySelectQuery = "select datasetid, metadata";
            mySelectQuery += " from explorer.datasets where datasetid = " + dataSetID + ";";
            DB2Connection connect = new DB2Connection(DataBase);
            DB2Command myCommand = new DB2Command(mySelectQuery, connect);
            connect.Open();
            DB2DataReader myReader = myCommand.ExecuteReader();
            while (myReader.Read())
            {
                doc.LoadXml(myReader.GetString(1));
            }
            myReader.Close();
            connect.Close();

            return doc;
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new db2Parameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>
        /// <param name="commandText">T存储过程名称或T-SQL语句</param>
        /// <param name="commandParameters">DB2Paramter参数数组</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(DB2Connection connection, CommandType commandType, string commandText, params DB2Parameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            // 创建DB2Command命令,并进行预处理
            DB2Command cmd = new DB2Command();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (DB2Transaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

            // Finally, execute the command
            int retval = cmd.ExecuteNonQuery();

            // 清除参数,以便再次使用.
            cmd.Parameters.Clear();
            if (mustCloseConnection)
                connection.Close();
            return retval;
        }

        /// <summary>
        /// [私有方法][内部调用]执行指定数据库连接对象/事务的命令,映射数据表并填充数据集,DataSet/TableNames/DB2Parameters.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new DB2Parameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="transaction">一个有效的连接事务</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名称或T-SQL语句</param>
        /// <param name="dataSet">要填充结果集的DataSet实例</param>
        /// <param name="tableNames">表映射的数据表数组
        /// 用户定义的表名 (可有是实际的表名.)
        /// </param>
        /// <param name="commandParameters">分配给命令的DB2Paramter参数数组</param>
        private static void FillDataset(DB2Connection connection, DB2Transaction transaction, CommandType commandType,
            string commandText, DataSet dataSet, string[] tableNames,
            params DB2Parameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (dataSet == null) throw new ArgumentNullException("dataSet");

            // 创建DB2Command命令,并进行预处理
            DB2Command command = new DB2Command();
            bool mustCloseConnection = false;
            PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // 执行命令
            using (DB2DataAdapter dataAdapter = new DB2DataAdapter(command))
            {

                // 追加表映射
                if (tableNames != null && tableNames.Length > 0)
                {
                    string tableName = "Table";
                    for (int index = 0; index < tableNames.Length; index++)
                    {
                        if (tableNames[index] == null || tableNames[index].Length == 0) throw new ArgumentException("The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames");
                        dataAdapter.TableMappings.Add(tableName, tableNames[index]);
                        tableName += (index + 1).ToString();
                    }
                }

                // 填充数据集使用默认表名称
                dataAdapter.Fill(dataSet);

                // 清除参数,以便再次使用.
                command.Parameters.Clear();
            }

            if (mustCloseConnection)
                connection.Close();
        }

        /// <summary>
        /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataSet.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new DB2Parameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名或T-SQL语句</param>
        /// <param name="commandParameters">DB2Paramter参数数组</param>
        /// <returns>返回一个包含结果集的DataSet</returns>
        public static DataSet ExecuteDataset(DB2Connection connection, CommandType commandType, string commandText, params DB2Parameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            // 预处理
            DB2Command cmd = new DB2Command();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (DB2Transaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

            // 创建DB2DataAdapter和DataSet.
            using (DB2DataAdapter da = new DB2DataAdapter(cmd))
            {
                DataSet ds = new DataSet();

                // 填充DataSet.
                da.Fill(ds);

                cmd.Parameters.Clear();

                if (mustCloseConnection)
                    connection.Close();

                return ds;
            }
        }

 public void queryBoundaryDate()
 {
     DB2Connection conndb2 = new DB2Connection(globalConnStringDb2);
     try
     {
         conndb2.Open();
         DB2Command cmd = conndb2.CreateCommand();
         DB2Transaction trans = conndb2.BeginTransaction();
         cmd.Transaction = trans;
         cmd.CommandText = "" + rtCheckBoundary.Text;
         tbBoundaryDate.Text = cmd.ExecuteScalar().ToString();
         string strBoundaryLog = System.DateTime.Today.ToShortDateString() + " LOG : Ran Archive Boundary Query. Current boundary date is " + tbBoundaryDate.Text + "@" ;
         strBoundaryLog = strBoundaryLog.Replace("@", "" + System.Environment.NewLine);
         rtLogging.AppendText(strBoundaryLog);
         conndb2.Close();
     }
     catch (Exception e)
     {
         MessageBox.Show("An error was encountered during runtime. Please try again.\n\n" + e.GetBaseException().Message, "Check Archive Boundary Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
     }
 }

 public BexisPublication[] getPublicationsMetadataXml(string DataBase)
 {
     var bexisPublications = new List<BexisPublication>();
     var docs = new List<XmlDocument>();
     var doc = new XmlDocument();
     string mySelectQuery = "SELECT ID,DATA  FROM \"EXPLORER\".\"PUBLICATIONLIST\"";
     DB2Connection connect = new DB2Connection(DataBase);
     DB2Command myCommand = new DB2Command(mySelectQuery, connect);
     connect.Open();
     DB2DataReader myReader = myCommand.ExecuteReader();
     while (myReader.Read())
     {
         doc.LoadXml(myReader.GetString(1));
         var bp = new BexisPublication();
         bp.Id = (int)(myReader.GetValue(0));
         bp.MetaDataXml = doc;
         var fileCommand = new DB2Command("SELECT FILENAME, MIMETYPE ,FILE  FROM \"EXPLORER\".\"PUBLICATIONFILES\" where PUBID=" + bp.Id, connect);
         var fileReader = fileCommand.ExecuteReader();
         string dataPath = AppConfiguration.DataPath;
         string storepath = Path.Combine(dataPath, "Temp", "Administrator");
         // if folder not exist
         if (!Directory.Exists(storepath))
             Directory.CreateDirectory(storepath);
         var index = 1;
         if (fileReader.Read())
         {
             PublicationContentDescriptor pubContent = new PublicationContentDescriptor()
             {
                 OrderNo = index++,
                 Name = fileReader.GetString(0),
                 MimeType = fileReader.GetString(1)
             };
             var filePath = Path.Combine(storepath, Guid.NewGuid().ToString() + Path.GetExtension(pubContent.Name));
             File.WriteAllBytes(filePath, ((Byte[])fileReader.GetValue(2)));
             pubContent.URI = filePath;
             bp.PublicationContentDescriptors.Add(pubContent);
         }
         fileReader.Close();
         bexisPublications.Add(bp);
     }
     myReader.Close();
     connect.Close();
     return bexisPublications.ToArray();
 }

        /// <summary>
        /// 执行指定数据库连接对象的DB2Command命令,并产生一个XmlReader对象做为结果集返回,指定参数.
        /// </summary>
        /// <remarks>
        /// 示例:  
        ///  XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new DB2Parameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名称或T-SQL语句 using "FOR XML AUTO"</param>
        /// <param name="commandParameters">分配给命令的DB2Paramter参数数组</param>
        /// <returns>返回XmlReader结果集对象.</returns>
        public static XmlReader ExecuteXmlReader(DB2Connection connection, CommandType commandType, string commandText, params DB2Parameter[] commandParameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            bool mustCloseConnection = false;
            // 创建DB2Command命令,并进行预处理
            DB2Command cmd = new DB2Command();
            try
            {
                PrepareCommand(cmd, connection, (DB2Transaction)null, commandType, commandText, commandParameters, out mustCloseConnection);

                // 执行命令
                XmlReader retval = cmd.ExecuteXmlReader();

                // 清除参数,以便再次使用.
                cmd.Parameters.Clear();

                return retval;
            }
            catch
            {
                if (mustCloseConnection)
                    connection.Close();
                throw;
            }
        }

        public void queryPrimaryWS()
        {
            DB2Connection conndb2 = new DB2Connection(globalConnStringDb2);
            try
            {
                conndb2.Open();
                DB2Command cmd = conndb2.CreateCommand();
                DB2Transaction trans = conndb2.BeginTransaction();
                cmd.Transaction = trans;
                cmd.CommandText = "" + rtPrimaryWS.Text;
                tbPrimWS.Text = cmd.ExecuteScalar().ToString();
                string strLog = System.DateTime.Today.ToShortDateString() + " LOG : Ran TA00WB.WORK_SUMMARY Query. " + tbPrimWS.Text + " rows [email protected]";
                strLog = strLog.Replace("@", "" + System.Environment.NewLine);
                rtLogging.AppendText(strLog);

                //logger
                string logFilePath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                using (TextWriter outputFile = new StreamWriter(logFilePath + @"\WBArchiving.txt", true))
                {
                    outputFile.Write(strLog);
                }
                conndb2.Close();
            }
            catch (Exception e)
            {
                tbPrimWS.Text = " ";
                MessageBox.Show("An error was encountered during runtime. Please try again.\n\n" + e.GetBaseException().Message, "TA00WB.WORK_SUMMARY Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                string strArchWSLog = System.DateTime.Today.ToShortDateString() + " LOG : Ran TA00WB.WORK_SUMMARY Query. Error occured. No" + tbPrimWS.Text + "rows [email protected]";
                strArchWSLog = strArchWSLog.Replace("@", "" + System.Environment.NewLine);
                rtLogging.AppendText(strArchWSLog);
            }//catch
        }