C# DB2Connection.Open方法代码示例

 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();
                    }
                }
            }
        }

		protected override IDbConnection CreateConnection()
		{
			DB2Connection conn = new DB2Connection(ConnectionString);

			conn.Open();

			return conn;
		}

        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));
            }
        }

 //Boolean result = false;
 public Boolean ConnectDatabase()
 {
     try
     {
         //ConnPath = "";
         Conn = new DB2Connection("Server=localhost; UserID=db2admin; Password=admin...; Database=HRESS;");
         Conn.Open();
         return true;
     }
     catch (Exception e)
     {
         return false;
     }
 }

        public List<MColumn> GetColumnList(string tableName)
        {
            string connstr = ConnectionFactory.TRSDbConnString;
            List<MColumn> ret = new List<MColumn>();
            using (DB2Connection conn = new DB2Connection(connstr))
            {
                conn.Open();
                var dao = DALFactory.GetSchemaDAO(MDataBaseType.DB2, MDBAccessType.WRITE);

                ret = dao.GetColumnList(conn, tableName);
            }

            return ret;
        }

        // 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 List<MTableDesc> GetTableList(string creator)
        {
            string connstr = ConnectionFactory.TRSDbConnString;

            List<MTableDesc> ret = null;

            using (DB2Connection conn = new DB2Connection(connstr))
            {
                conn.Open();
                var dao = DALFactory.GetSchemaDAO(MDataBaseType.DB2, MDBAccessType.WRITE);

                ret = dao.GetTableList(conn, creator);
            }

            return ret;
        }

 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;
 }

		/// <summary>
		/// Please refer to the documentation of <see cref="GentleProvider"/> and the
		/// <see cref="IGentleProvider"/> interface it implements for details. 
		/// </summary>
		public override IDbConnection GetConnection()
		{
			try
			{
				IDbConnection dbc = new DB2Connection( ConnectionString );
				dbc.Open();
				Check.VerifyEquals( dbc.State, ConnectionState.Open, Error.NoNewConnection );
				return dbc;
			}
			catch( GentleException )
			{
				throw; // expose the errors raised by ourselves (i.e. the data framework) in the try block
			}
			catch( Exception e )
			{
				Check.Fail( e, Error.DatabaseUnavailable, Name, ConnectionString );
				throw new GentleException( Error.Unspecified, "Unreachable code!" );
			}
		}

        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;
        }

        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();
        }

        /// <summary>
        /// 执行指定数据库连接字符串的命令,映射数据表并填充数据集,指定存储过程参数值.
        /// </summary>
        /// <remarks>
        /// 此方法不提供访问存储过程输出参数和返回值参数.
        /// 
        /// 示例:  
        ///  FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 24);
        /// </remarks>
        /// <param name="connectionString">一个有效的数据库连接字符串</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="dataSet">要填充结果集的DataSet实例</param>
        /// <param name="tableNames">表映射的数据表数组
        /// 用户定义的表名 (可有是实际的表名.)
        /// </param>    
        /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param>
        public static void FillDataset(string connectionString, string spName,
            DataSet dataSet, string[] tableNames,
            params object[] parameterValues)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
            if (dataSet == null) throw new ArgumentNullException("dataSet");
            // 创建并打开数据库连接对象,操作完成释放对象.
            using (DB2Connection connection = new DB2Connection(connectionString))
            {
                connection.Open();

                // 调用指定数据库连接字符串重载方法.
                FillDataset(connection, spName, dataSet, tableNames, parameterValues);
            }
        }

        /// <summary>
        /// 数据库连接测试
        /// </summary>
        /// <param name="constr">数据库连接字符串</param>
        /// <returns>True: OK , false : Error</returns>
        public static bool testCon(string conStr)
        {
            bool flag = false;

            DB2Connection db2con = new DB2Connection(conStr);

            try
            {
                if (db2con != null)
                {
                    db2con.Open();
                    flag = true;
                }
            }
            catch (Exception ce)
            {
                throw new Exception(ce.Message);
            }
            finally
            {
                db2con.Close();
            }
            return flag;
        }