C# CUBRIDConnection.Close方法代码示例

        public void conn_setIsolationLevel()
        {
            string conn_string = "server=test-db-server;database=demodb;port=33000;user=dba;password=";
            CUBRIDConnection conn = new CUBRIDConnection();
            conn.ConnectionString = conn_string;
            conn.Open();

            CUBRIDCommand cmd = new CUBRIDCommand();
            cmd.Connection = conn;
            cmd.CommandText = "drop table if exists test_isolation";
            cmd.ExecuteNonQuery();

            // open another session
            CUBRIDConnection conn2 = new CUBRIDConnection();
            conn2.ConnectionString = conn_string;
            conn2.Open();

            CUBRIDCommand cmd2 = new CUBRIDCommand();
            cmd2.Connection = conn2;


            // set up the isolation level to 
            conn.SetAutoCommit(false);
            conn.SetIsolationLevel(CUBRIDIsolationLevel.TRAN_REP_CLASS_COMMIT_INSTANCE);
            cmd.CommandText = "create table test_isolation(a int)";
            cmd.ExecuteNonQuery();

            conn.Commit();

            conn.Close();
        }

        public void conn_Database()
        {
            string conn_string = "server=test-db-server;database=demodb;port=33000;user=dba;password=";
            CUBRIDConnection conn = new CUBRIDConnection();
            conn.ConnectionString = conn_string;
            Console.WriteLine(conn.Database);
            conn.Open();
            Console.WriteLine(conn.Database);

            conn.Close();
        }

        public void CUBRIDCommand_Constructor_SQL_Test()
        {
            CUBRIDConnection conn = new CUBRIDConnection();
            conn.ConnectionString = DBHelper.connString;

            string sql = "select * from nation order by code asc";
            CUBRIDCommand cmd = new CUBRIDCommand(sql);
            cmd.Connection = conn;

            conn.Open();
            CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
            reader.Read();
            Assert.AreEqual(4, reader.FieldCount);
            Assert.AreEqual("AFG", reader.GetString(0));
            Assert.AreEqual("Afghanistan", reader.GetString(1));
            Assert.AreEqual("Asia", reader.GetString(2));
            Assert.AreEqual("Kabul", reader.GetString(3));

            cmd.Close();
            reader.Close();
            conn.Close();
        }

    /// <summary>
    ///A connect with URL
    ///</summary>
    public static void Test_ConnectionURL_And_Reset()
    {
      string strURL = "cci:cubrid:test-db-server:33000:demodb:public::?logSlowQueries=true" +
                      "&slowQueryThresholdMillis=1000&logTraceApi=true&logTraceNetwork=true" +
                      "&autoCommit=false&althosts=" +ip+
                      "&querytimeout=10000&logintimeout=5000";
      using (CUBRIDConnection conn = new CUBRIDConnection())
      {
        conn.ConnectionString = strURL;
        conn.Open();

        string sqlTablesCount = "select count(*) from db_class where is_system_class='NO' and class_type='CLASS'";
        int tablesCount = 0;
        using (CUBRIDCommand cmd = new CUBRIDCommand(sqlTablesCount, conn))
        {
          tablesCount = (int)cmd.ExecuteScalar();
        }

        Debug.Assert(tablesCount == 12);

        conn.Close();

        try
        {
            string strURL2 = "cci:cubrid:test-db-server:33690:demodb:public::?logSlowQueries=true" +
                           "&slowQueryThresholdMillis=1000&logTraceApi=false&logTraceNetwork=false&autoCommit=false";
          conn.ConnectionString = strURL2;
          conn.Open();
        }
        catch(Exception ex)
        {
          Debug.Assert(ex.Message == "Cannot connect to CUBRID CAS");
        }
      }
    }

        public void DataReader_NextResult_Test()
        {
            string conn_string = "server=test-db-server;database=demodb;port=33000;user=dba;password=";
            CUBRIDConnection conn = new CUBRIDConnection();
            conn.ConnectionString = conn_string;
            conn.Open();

            String sql = "select * from nation;";
            CUBRIDCommand cmd = new CUBRIDCommand(sql, conn);

            CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
            reader.Read();
            Console.WriteLine(reader.GetString(0));

            if (reader.NextResult())
            {
                reader.Read();
                Console.WriteLine(reader.GetString(0));
            }

            conn.Close();
        }

        public void CUBRIDDataAdapter_ConstructorWithSqlAndConnString_Test()
        {

            CUBRIDConnection conn = new CUBRIDConnection();
            conn.ConnectionString = DBHelper.connString;
            conn.Open();

            DBHelper.ExecuteSQL("drop table if exists t", conn);
            DBHelper.ExecuteSQL("create table t (id int, name varchar(100))", conn);
            DBHelper.ExecuteSQL("insert into t values (1, 'Nancy')", conn);
            DBHelper.ExecuteSQL("insert into t values (2, 'Peter')", conn);
            conn.Close();

            string selectCommandText = "select * from t";
            CUBRIDDataAdapter adapter = new CUBRIDDataAdapter(selectCommandText, DBHelper.connString);
            DataTable dt = new DataTable("student");
            adapter.Fill(dt);

            //verify data

            Assert.AreEqual(1, (int)dt.Rows[0]["id"]);
            Assert.AreEqual("Nancy", dt.Rows[0]["name"].ToString());

            Assert.AreEqual(2, (int)dt.Rows[1]["id"]);
            Assert.AreEqual("Peter", dt.Rows[1]["name"].ToString());

            //revert test db
            conn.Open();
            DBHelper.ExecuteSQL("drop table if exists t", conn);
        }

        public void ConnectionString_Test()
        {
            CUBRIDConnection conn = new CUBRIDConnection();

            LogTestStep("Invalid connection string, server is not specified");
            try
            {
                conn.ConnectionString = "database=demodb;port=33000;user=public;password=";
                conn.Open();
                Log("There should be an exception when the server is not specified");
                LogStepFail();
            }
            catch (Exception ex)
            {
                Console.WriteLine("1. " + ex.Message);
                //Assert.AreEqual("The database name can't be empty!", ex.Message);
                LogStepPass();
            }

            LogTestStep("Invalid connection string, dbname is not specified");
            try
            {
                conn.ConnectionString = "server=localhost;port=33000;user=public;password=";
                conn.Open();
                Log("There should be an exception when the dbname is not specified");
                LogStepFail();
            }
            catch (CUBRIDException ex)
            {
                Console.WriteLine("2." + ex.Message);
                //Assert.AreEqual("The database name can't be empty!", ex.Message);
                LogStepPass();
            }

            LogTestStep("Invalid connection string, user is not specified");
            try
            {
                conn.ConnectionString = "server=localhost;database=demodb;port=33000;password=";
                conn.Open();
                Log("There should be an exception when the user is not specified");
                LogStepFail();
            }
            catch (Exception ex)
            {
                Console.WriteLine("3." + ex.Message);
                //Assert.AreEqual("The database name can't be empty!", ex.Message);
                LogStepPass();
            }

            LogTestStep("Invalid connection string, password is not specified");
            try
            {
                conn.ConnectionString = "server=localhost;database=demodb;port=33000;user=public;";
                conn.Open();
                Log("There should be an exception when the password is not specified");
                LogStepFail();
            }
            catch (Exception ex)
            {
                Console.WriteLine("4." + ex.Message);
                //Assert.AreEqual("The database name can't be empty!", ex.Message);
                LogStepPass();
            }

            LogTestStep("Valid connection string");
            conn.ConnectionString = DBHelper.connString;
            conn.Open();
            conn.Close();
            LogStepPass();
            
            LogTestResult();            
        }

        public void APIS_485()
        {
            LogTestStep("Test the lockTimeout is set successfully");
            CUBRIDConnection conn = new CUBRIDConnection();
            conn.ConnectionString = DBHelper.connString;
            Assert.AreEqual(-1, conn.LockTimeout);
            
            conn.Open();
            int timeout = 20;
            conn.SetLockTimeout(timeout);
            Assert.AreEqual(timeout, conn.LockTimeout);

            DBHelper.ExecuteSQL("drop table if exists t", conn);
            DBHelper.ExecuteSQL("create table t(id integer)", conn);
            DBHelper.ExecuteSQL("insert into t values (1)", conn);

            conn.SetAutoCommit(false);
            CUBRIDConnection conn2 = null;
            double elapseTime = 0;
            var stopwatch = new Stopwatch();
            try
            {
                Thread thread2 = new Thread(delegate()
                {
                    conn2 = new CUBRIDConnection();
                    conn2.ConnectionString = DBHelper.connString;
                    conn2.Open();
                    conn2.SetAutoCommit(false);

                    conn2.BeginTransaction();
                    DBHelper.ExecuteSQL("update t set id=2", conn2);
                });
                conn.BeginTransaction();
                thread2.Start();
                Thread.Sleep(5000);

                stopwatch.Start();
                DBHelper.ExecuteSQL("update t set id=3", conn);
                thread2.Join();
            }
            catch (Exception ex)
            {
                stopwatch.Stop();
                elapseTime = (double)stopwatch.ElapsedMilliseconds / 1000;
                double diffTime = elapseTime - (double)(timeout / 1000);
                Console.WriteLine("different time = " + stopwatch.ElapsedMilliseconds);
                Console.WriteLine("different time = " + diffTime);
                Log(ex.Message);
                Assert.AreEqual(timeout, conn.LockTimeout);

                if (diffTime >= 0 && diffTime < 10)
                {
                    LogStepPass();
                }
                else
                {
                    LogStepFail();
                }
            }
            finally
            {
                LogTestResult();
                //DBHelper.ExecuteSQL("drop table t", conn);
                conn.Close();
                conn2.Close();
            }
        }

        public void Conn_Invalid_Connstring()
        {
            CUBRIDConnection conn = new CUBRIDConnection();

            // ConnectionString is null
            try
            {
                conn.ConnectionString = null;
            }
            catch (CUBRIDException e)
            {
                Console.WriteLine("1. " + e.Message);
                Assert.AreEqual("Connection string is null!: Closed.", e.Message);
            }

            // database is not specified
            conn = new CUBRIDConnection();
            try
            {
                conn.ConnectionString = "server=localhost;port=33000;user=dba;password=";
            }
            catch (Exception e)
            {
                Console.WriteLine("2. " + e.Message);
                Assert.AreEqual("The database name can't be empty!", e.Message);
            }

            // server is not specified
            conn = new CUBRIDConnection();
            try
            {
                conn.ConnectionString = "database=demodb;port=33000;user=dba;password=";
            }
            catch (Exception e)
            {
                Console.WriteLine("3. " + e.Message);
                Assert.AreEqual("The Server can't be empty!", e.Message);
            }

            // user is not specified
            conn = new CUBRIDConnection();
            try
            {
                conn.ConnectionString = "server=localhost;database=demodb;port=33000;password=";
            }
            catch (Exception e)
            {
                Console.WriteLine("4. " + e.Message);
                Assert.AreEqual("The User can't be empty!", e.Message);
            }

            conn.Close();
        }

        public IList<Column> GetTableDetails(Table table, string owner)
        {
            var columns = new List<Column>();
            var conn = new CUBRIDConnection(connectionStr);
            conn.Open();
            try
            {
                using (conn)
                {
                    var schema = new CUBRIDSchemaProvider(conn);
                    DataTable dt_fk = schema.GetForeignKeys(new[] { table.Name.ToLower() });

                    string sqlInfo = String.Format("select * from [{0}] limit 1", table.Name.ToLower());
                    var adapter = new CUBRIDDataAdapter(sqlInfo, conn);
                    var tableInfo = new DataTable();
                    adapter.FillSchema(tableInfo, SchemaType.Source);

                    using (var reader = new DataTableReader(tableInfo))
                    {
                        DataTable schemaTable = reader.GetSchemaTable();
                        for (var k = 0; k < schemaTable.Rows.Count; k++)
                        {
                            string columnName = schemaTable.Rows[k]["ColumnName"].ToString().ToLower();
                            var isUnique = (bool)schemaTable.Rows[k]["IsUnique"];
                            var isNullable = (bool)schemaTable.Rows[k]["AllowDBNull"];
                            var isPrimaryKey = (bool)schemaTable.Rows[k]["IsKey"];
                            var isIdentity = (bool)schemaTable.Rows[k]["IsAutoIncrement"];
                            var dataLength = (int)schemaTable.Rows[k]["ColumnSize"];
                            int dataPrecision = 0;
                            if (schemaTable.Rows[k]["NumericPrecision"].ToString() != String.Empty)
                            {
                                dataPrecision = (int)schemaTable.Rows[k]["NumericPrecision"];
                            }
                            int dataScale = 0;
                            if (schemaTable.Rows[k]["NumericScale"].ToString() != String.Empty)
                            {
                                dataScale = (int)schemaTable.Rows[k]["NumericScale"];
                            }
                            bool isForeignKey = false;
                            string fkTableName = "";
                            string constraintName = "";
                            for (var i_fk = 0; i_fk < dt_fk.Rows.Count; i_fk++)
                            {
                                if (dt_fk.Rows[i_fk]["FKCOLUMN_NAME"].ToString().ToLower() == columnName)
                                {
                                    isForeignKey = true;
                                    fkTableName = dt_fk.Rows[i_fk]["PKTABLE_NAME"].ToString();
                                    constraintName = dt_fk.Rows[i_fk]["FK_NAME"].ToString();
                                    break;
                                }
                            }
                            string dataType;
                            using (var cmd = new CUBRIDCommand(sqlInfo, conn))
                            {
                              using (var CUBRIDReader = (CUBRIDDataReader)cmd.ExecuteReader())
                              {
                                CUBRIDReader.Read();
                                dataType = CUBRIDReader.GetColumnTypeName(k);
                              }
                            }
                            var m = new DataTypeMapper();
                            columns.Add(new Column
                                    {
                                        Name = columnName,
                                        DataType = dataType,
                                        IsNullable = isNullable,
                                        IsUnique = isUnique,
                                        IsPrimaryKey = isPrimaryKey,
                                        IsForeignKey = isForeignKey,
                                        IsIdentity = isIdentity,
                                        DataLength = dataLength,
                                        DataPrecision = dataPrecision,
                                        DataScale = dataScale,
                                        ForeignKeyTableName = fkTableName,
                                        ConstraintName = constraintName,
                                        MappedDataType =
                                            m.MapFromDBType(ServerType.CUBRID, dataType, null, null, null).ToString(),
                                    });
                        }
                    }
                }

                table.Columns = columns;

                table.Owner = owner;
                table.PrimaryKey = DeterminePrimaryKeys(table);

                table.HasManyRelationships = DetermineHasManyRelationships(table);
            }
            finally
            {
                conn.Close();
            }

            return columns;
        }

        public void Conn_ConnectionString_Exception()
        {

            CUBRIDConnection conn = new CUBRIDConnection();

            // database is not specified
            try
            {
                conn.ConnectionString = "server=test-db-server;port=33000;user=dba;password=";
            }
            catch (Exception ex)
            {
                Console.WriteLine("1. " + ex.Message);
            }

            // ConnectionString is null
            try
            {
                conn.ConnectionString = null;
            }
            catch (Exception ex)
            {
                Console.WriteLine("2. " + ex.Message);
            }

            conn.Close();

        }

        public List<string> GetSequences(string tablename, string column)
        {
            var sequences = new List<string>();
            var conn = new CUBRIDConnection(connectionStr);
            conn.Open();

            try
            {
                using (conn)
                {
                    CUBRIDCommand seqCommand = conn.CreateCommand();
                    string sqlCmd = String.Format(@"select [name] from db_serial where class_name='{0}' and att_name='{1}'",
                                                tablename,
                                                column);
                    seqCommand.CommandText = sqlCmd;
                    var seqReader = (CUBRIDDataReader)seqCommand.ExecuteReader(CommandBehavior.CloseConnection);
                    while (seqReader.Read())
                    {
                        sequences.Add(seqReader.GetString(0));
                    }
                }
            }
            finally
            {
                conn.Close();
            }

            return sequences;
        }

        public void Clone_Test()
        {
            using (CUBRIDConnection conn = new CUBRIDConnection())
            {
                LogTestStep("Clone a connection");
                conn.ConnectionString = DBHelper.connString;
                Log("change a property value of the original connection");
                conn.SetConnectionTimeout(45);
                conn.Open();

                Log("call the Clone method");
                CUBRIDConnection clonedConn = conn.Clone();
                Assert.IsTrue(clonedConn != null);

                Log("The property values are different between the original connection and the cloned connection");
                Assert.AreEqual(45, conn.ConnectionTimeout);
                Assert.AreEqual(30, clonedConn.ConnectionTimeout);

                try
                {
                    clonedConn.Open();

                    Log("Close the original connection, check the cloned connection works well");
                    conn.Close();
                    Assert.IsTrue(DBHelper.GetTableRowsCount("db_class", clonedConn) > 0);
                    clonedConn.Close();
                    LogStepPass();
                }
                catch (Exception ex)
                {
                    Log(ex.Message);
                    LogStepFail();
                }
                finally
                {
                    LogTestResult();
                    conn.Close();
                    clonedConn.Close();
                }
            }
        }

        public void Close_Test()
        {
            using (CUBRIDConnection conn = new CUBRIDConnection())
            {
                LogTestStep("Close DB before connected");
                try
                {
                    conn.ConnectionString = DBHelper.connString;
                    conn.Close();
                }
                catch (Exception ex)
                {
                    Assert.AreEqual("The connection is not open!", ex.Message);
                    LogStepPass();
                }

                LogTestStep("Close a valid DB Connection, and check: (1)the connection is closed (2)the transaction which is not committed is rolled back");
                Log("Connect to a DB");
                conn.Open();

                Log("Verify the Connection is OK by feching some data");
                Assert.IsTrue(DBHelper.GetTableRowsCount("db_class", conn) > 0);

                Log("Update the DB in a transaction, and close the connection before commit");
                DBHelper.ExecuteSQL("drop table if exists t", conn);

                conn.BeginTransaction();
                DBHelper.ExecuteSQL("create table t(idx integer)", conn);
                int tablesCount = DBHelper.GetTablesCount("t", conn);
                Assert.AreEqual(1, tablesCount);

                conn.Close();

                Log("Verify the Connection is closed by feching some data");
                try
                {
                    int count = DBHelper.GetTableRowsCount("db_class", conn);
                }
                catch (Exception ex)
                {
                    Assert.AreEqual("The connection is not open!", ex.Message);
                }

                Log("Verify the transaction is rolled back");
                conn.Open();
                tablesCount = DBHelper.GetTablesCount("t", conn);
                Assert.AreEqual(0, tablesCount);
                LogStepPass();
                conn.Close();

                LogTestStep("Close a valid DB Connection twice, check no exception is generated");
                conn.Close();
                LogStepPass();
                LogTestResult();
            }
        }

        public void SetLockTimeOut_WithParam_Test()
        {
            int timeout;

            using (CUBRIDConnection conn = new CUBRIDConnection())
            {
                conn.ConnectionString = DBHelper.connString;
                LogTestStep("connection is not open");
                try
                {
                    timeout = 2;
                    conn.SetLockTimeout(timeout);
                }
                catch (Exception ex)
                {
                    Assert.AreEqual("The connection is not open!", ex.Message);
                    LogStepPass();
                }
                conn.Close();

                LogTestStep("Valid timout value, and connection is open");
                timeout = 35;
                conn.Open();
                conn.SetLockTimeout(35);
                Assert.AreEqual(35, conn.LockTimeout);
                LogStepPass();

                LogTestStep("Valid timout value, change the lock timeout");
                try
                {
                    Assert.AreEqual(35, conn.LockTimeout); 
                    conn.SetLockTimeout(40);
                    Assert.AreEqual(40, conn.LockTimeout);
                    LogStepPass();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    //Assert.AreEqual("Not allowed to change the 'LockTimeout' property while the connection state is!: Open.", ex.Message);
                    LogStepFail();
                }
                finally
                {
                    LogTestResult();
                }
            }
        }