C# CUBRIDConnection.Open方法代码示例

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

    /// <summary>
    /// Test CUBRIDSchemaProvider GetForeignKeys() method
    /// </summary>
    private static void Test_GetForeignKeys()
    {
      using (CUBRIDConnection conn = new CUBRIDConnection())
      {
        conn.ConnectionString = TestCases.connString;
        conn.Open();

        CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn);
        DataTable dt = schema.GetForeignKeys(new string[] { "game" });

        Debug.Assert(dt.Columns.Count == 9);
        Debug.Assert(dt.Rows.Count == 2);

        Debug.Assert(dt.Rows[0][0].ToString() == "event");
        Debug.Assert(dt.Rows[0][1].ToString() == "code");
        Debug.Assert(dt.Rows[0][2].ToString() == "game");
        Debug.Assert(dt.Rows[0][3].ToString() == "event_code");
        Debug.Assert(dt.Rows[0][4].ToString() == "1");
        Debug.Assert(dt.Rows[0][5].ToString() == "1");
        Debug.Assert(dt.Rows[0][6].ToString() == "1");
        Debug.Assert(dt.Rows[0][7].ToString() == "fk_game_event_code");
        Debug.Assert(dt.Rows[0][8].ToString() == "pk_event_code");

        Debug.Assert(dt.Rows[1][0].ToString() == "athlete");
        Debug.Assert(dt.Rows[1][1].ToString() == "code");
        Debug.Assert(dt.Rows[1][2].ToString() == "game");
        Debug.Assert(dt.Rows[1][3].ToString() == "athlete_code");
        Debug.Assert(dt.Rows[1][4].ToString() == "1");
        Debug.Assert(dt.Rows[1][5].ToString() == "1");
        Debug.Assert(dt.Rows[1][6].ToString() == "1");
        Debug.Assert(dt.Rows[1][7].ToString() == "fk_game_athlete_code");
        Debug.Assert(dt.Rows[1][8].ToString() == "pk_athlete_code");
      }
    }

        public void DataReader_Basic_Test()
        {
            using (CUBRIDConnection conn = new CUBRIDConnection())
            {
                conn.ConnectionString = DBHelper.connString;
                conn.Open();

                String sql = "select * from nation order by `code` asc";

                LogTestStep("retrieve just one row");
                using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
                {
                    using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader())
                    {
                        reader.Read(); //retrieve just one row

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

                        LogStepPass();
                    }
                }
            }

            LogTestResult();

        }

        public void CUBRIDDataAdapter_ConstructorWithCUBRIDCommand_Test()
        {
           
            using (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);

                string selectCommandText = "select * from t";

                using (CUBRIDCommand cmd = new CUBRIDCommand(selectCommandText, conn))
                {
                    CUBRIDDataAdapter adapter = new CUBRIDDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);

                    //Update data
                    DataTable dt = ds.Tables[0];

                    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
                    DBHelper.ExecuteSQL("drop table if exists t", conn);
                }
            }
        }

    /// <summary>
    /// Test CREATE Database Stored Functions calls
    /// </summary>
    private static void Test_CreateFunction()
    {
      using (CUBRIDConnection conn = new CUBRIDConnection())
      {
        conn.ConnectionString = TestCases.connString;
        conn.Open();

        try
        {
          TestCases.ExecuteSQL("drop function sp1", conn);
        }
        catch { }

        string sql = "CREATE FUNCTION sp1(a int) RETURN string AS LANGUAGE JAVA NAME 'SpTest.test1(int) return java.lang.String'";
        using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
        {
          cmd.ExecuteNonQuery();
        }

        CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn);
        DataTable dt = schema.GetProcedures(null);

        Debug.Assert(dt.Rows.Count == 1);

        TestCases.ExecuteSQL("drop function sp1", conn);
      }
    }

    /// <summary>
    /// Test basic SQL statements execution, using DataSet
    /// </summary>
    private static void Test_DataSet_Basic()
    {
      using (CUBRIDConnection conn = new CUBRIDConnection())
      {
        conn.ConnectionString = TestCases.connString;
        conn.Open();

        String sql = "select * from nation order by `code` asc";
        CUBRIDDataAdapter da = new CUBRIDDataAdapter();
        da.SelectCommand = new CUBRIDCommand(sql, conn);
        DataSet ds = new DataSet("nation");
        da.Fill(ds);

        DataTable dt0 = ds.Tables["Table"];
        Debug.Assert(dt0 != null);

        dt0 = ds.Tables[0];

        Debug.Assert(dt0.Columns.Count == 4);
        Debug.Assert(dt0.DefaultView.Count == 215);
        Debug.Assert(dt0.DefaultView.AllowEdit == true);
        Debug.Assert(dt0.DefaultView.AllowDelete == true);
        Debug.Assert(dt0.DefaultView.AllowNew == true);
        Debug.Assert(dt0.DataSet.DataSetName == "nation");

        DataRow[] dataRow = dt0.Select("continent = 'Africa'");

        Debug.Assert(dataRow.Length == 54);
      }
    }

    /// <summary>
    /// Test exporting XML from DataSet
    /// </summary>
    private static void Test_DataSet_ExportXML()
    {
      using (CUBRIDConnection conn = new CUBRIDConnection())
      {
        conn.ConnectionString = TestCases.connString;
        conn.Open();

        String sql = "select * from nation order by `code` asc";
        CUBRIDDataAdapter da = new CUBRIDDataAdapter();
        da.SelectCommand = new CUBRIDCommand(sql, conn);
        DataSet ds = new DataSet();
        da.Fill(ds, "nation");

        string filename = @".\Test_DataSet_ExportXML.xml";
        ds.WriteXml(filename);

        if (!System.IO.File.Exists(filename))
        {
          throw new Exception("XML output file not found!");
        }
        else
        {
          System.IO.File.Delete(filename);
        }
      }
    }

        public IList<string> GetOwners()
        {
            var owners = new List<string>();
            var conn = new CUBRIDConnection(connectionStr);
            conn.Open();

            try
            {
                using (conn)
                {
                    var schema = new CUBRIDSchemaProvider(conn);
                    DataTable dt = schema.GetUsers(new[] { "%" });
                    for (var i = 0; i < dt.Rows.Count; i++)
                    {
                        owners.Add(dt.Rows[i][0].ToString().ToLower());
                    }
                }
            }
            finally
            {
                conn.Close();
            }

            return owners;
        }

    ///<summary>
    /// Test BLOB SELECT
    /// </summary>
    private static void Test_Blob_Select()
    {
      using (CUBRIDConnection conn = new CUBRIDConnection())
      {
        conn.ConnectionString = TestCases.connString;
        conn.Open();

        CreateTestTableLOB(conn);

        string sql1 = "insert into t (b) values(?)";
        CUBRIDCommand cmd1 = new CUBRIDCommand(sql1, conn);
        CUBRIDBlob Blob = new CUBRIDBlob(conn);

        byte[] bytes1 = new byte[256];
        bytes1[0] = 69;
        bytes1[1] = 98;
        bytes1[2] = 99;
        bytes1[255] = 122;

        Blob.SetBytes(1, bytes1);

        CUBRIDParameter param = new CUBRIDParameter();
        param.ParameterName = "?";
        param.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_BLOB;
        param.Value = Blob;
        cmd1.Parameters.Add(param);
        cmd1.Parameters[0].DbType = DbType.Binary;
        cmd1.ExecuteNonQuery();
        cmd1.Close();

        string sql = "SELECT b from t";
        CUBRIDCommand cmd = new CUBRIDCommand(sql, conn);
        DbDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
          CUBRIDBlob bImage = (CUBRIDBlob)reader[0];
          byte[] bytes = new byte[(int)bImage.BlobLength];
          bytes = bImage.GetBytes(1, (int)bImage.BlobLength);

          Debug.Assert(bytes1.Length == bytes.Length, "The selected BLOB length is not valid!");
          bool ok = true;
          for (int i = 0; i < bytes.Length; i++)
          {
            if (bytes1[i] != bytes[i])
              ok = false;
          }

          Debug.Assert(ok == true, "The BLOB was not selected correctly!");
        }

        cmd.Close();

        CleanupTestTableLOB(conn);
      }
    }

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

    /// <summary>
    /// Test CUBRIDTransaction class
    /// </summary>
    private static void Test_Transaction()
    {
      using (CUBRIDConnection conn = new CUBRIDConnection())
      {
        conn.ConnectionString = TestCases.connString;
        conn.Open();

        TestCases.ExecuteSQL("drop table if exists t", conn);

        conn.BeginTransaction();

        string sql = "create table t(idx integer)";
        using (CUBRIDCommand command = new CUBRIDCommand(sql, conn))
        {
          command.ExecuteNonQuery();
        }

        int tablesCount = GetTablesCount("t", conn);
        Debug.Assert(tablesCount == 1);

        conn.Rollback();

        //Verify the table does not exist
        tablesCount = GetTablesCount("t", conn);
        Debug.Assert(tablesCount == 0);

        conn.BeginTransaction();

        sql = "create table t(idx integer)";
        using (CUBRIDCommand command = new CUBRIDCommand(sql, conn))
        {
          command.ExecuteNonQuery();
        }

        tablesCount = GetTablesCount("t", conn);
        Debug.Assert(tablesCount == 1);

        conn.Commit();

        tablesCount = GetTablesCount("t", conn);
        Debug.Assert(tablesCount == 1);

        conn.BeginTransaction();

        TestCases.ExecuteSQL("drop table t", conn);

        conn.Commit();

        tablesCount = GetTablesCount("t", conn);
        Debug.Assert(tablesCount == 0);
      }
    }

    /// <summary>
    /// Test CUBRIDTransaction class, using parameters
    /// </summary>
    private static void Test_Transaction_Parameters()
    {
      DbTransaction tran = null;

      using (CUBRIDConnection conn = new CUBRIDConnection())
      {
        conn.ConnectionString = TestCases.connString;
        conn.Open();

        CreateTestTable(conn);

        tran = conn.BeginTransaction();

        string sql = "insert into t values(?, ?, ?, ?, ?, ?)";
        using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
        {
          CUBRIDParameter p1 = new CUBRIDParameter("?p1", CUBRIDDataType.CCI_U_TYPE_INT);
          p1.Value = 1;
          cmd.Parameters.Add(p1);

          CUBRIDParameter p2 = new CUBRIDParameter("?p2", CUBRIDDataType.CCI_U_TYPE_CHAR);
          p2.Value = 'A';
          cmd.Parameters.Add(p2);

          CUBRIDParameter p3 = new CUBRIDParameter("?p3", CUBRIDDataType.CCI_U_TYPE_STRING);
          p3.Value = "cubrid";
          cmd.Parameters.Add(p3);

          CUBRIDParameter p4 = new CUBRIDParameter("?p4", CUBRIDDataType.CCI_U_TYPE_FLOAT);
          p4.Value = 1.1f;
          cmd.Parameters.Add(p4);

          CUBRIDParameter p5 = new CUBRIDParameter("?p5", CUBRIDDataType.CCI_U_TYPE_DOUBLE);
          p5.Value = 2.2d;
          cmd.Parameters.Add(p5);

          CUBRIDParameter p6 = new CUBRIDParameter("?p6", CUBRIDDataType.CCI_U_TYPE_DATE);
          p6.Value = DateTime.Now;
          cmd.Parameters.Add(p6);

          cmd.ExecuteNonQuery();

          tran.Commit();
        }

        Debug.Assert(GetTableRowsCount("t", conn) == 1);

        CleanupTestTable(conn);
      }
    }

    /// <summary>
    /// Test multiple connections
    /// </summary>
    private static void Test_MultipleConnections()
    {
      using (CUBRIDConnection conn = new CUBRIDConnection())
      {
        conn.ConnectionString = TestCases.connString;
        conn.Open();

        TestCases.ExecuteSQL("drop table if exists t", conn);
        TestCases.ExecuteSQL("create table t(idx integer)", conn);

        string sql = "select * from nation";
        using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
        {
          using (DbDataReader reader = cmd.ExecuteReader())
          {
            int count = 0;
            while (reader.Read() && count++ < 3)
            {
              using (CUBRIDConnection conn2 = new CUBRIDConnection())
              {
                conn2.ConnectionString = conn.ConnectionString;
                conn2.Open();
                string sqlInsert = "insert into t values(" + count + ")";
                using (CUBRIDCommand cmdInsert = new CUBRIDCommand(sqlInsert, conn2))
                {
                  cmdInsert.ExecuteNonQuery();
                }
              }
            }
          }
        }

        using (CUBRIDConnection conn2 = new CUBRIDConnection())
        {
          conn2.ConnectionString = conn.ConnectionString;
          conn2.Open();
          string sqlSelect = "select count(*) from t";
          using (CUBRIDCommand cmd = new CUBRIDCommand(sqlSelect, conn2))
          {
            using (DbDataReader reader = cmd.ExecuteReader())
            {
              reader.Read();
              Debug.Assert(reader.GetInt32(0) == 3);
            }
          }
        }

        TestCases.ExecuteSQL("drop table if exists t", conn);
      }
    }

    /// <summary>
    /// Test Encodings support
    /// </summary>
    private static void Test_Encodings()
    {
      using (CUBRIDConnection conn = new CUBRIDConnection())
      {
        conn.ConnectionString = "server="+ip+";database=demodb;port=33000;user=public;password=;charset=utf-8";
        conn.Open();

        TestCases.ExecuteSQL("drop table if exists t", conn);
        TestCases.ExecuteSQL("create table t(a int, b varchar(100))", conn);

        String sql = "insert into t values(1 ,'¾Æ¹«°³')";
        using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
        {
          cmd.ExecuteNonQuery();
        }

        sql = "select * from t where b = '¾Æ¹«°³'";
        using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
        {
          using (DbDataReader reader = cmd.ExecuteReader())
          {
            reader.Read(); //retrieve just one row

            Debug.Assert(reader.GetInt32(0) == 1);
            Debug.Assert(reader.GetString(1) == "¾Æ¹«°³");
          }
        }

        sql = "update t set b='¾Æ¹°³'";
        using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
        {
          cmd.ExecuteNonQuery();
        }

        sql = "select * from t where b = '¾Æ¹°³'";
        using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
        {
          using (DbDataReader reader = cmd.ExecuteReader())
          {
            reader.Read(); //retrieve just one row

            Debug.Assert(reader.GetInt32(0) == 1);
            Debug.Assert(reader.GetString(1) == "¾Æ¹°³");
          }
        }

        TestCases.ExecuteSQL("drop table if exists t", conn);
      }
    }

    public static void Test_CUBRIDBlob_Insert()
    {
      Configuration cfg = (new Configuration()).Configure().AddAssembly(typeof(TestCUBRIDBlobType).Assembly);
      //Create the database schema
      using (CUBRIDConnection conn = new CUBRIDConnection(cfg.GetProperty(NHibernate.Cfg.Environment.ConnectionString)))
      {
        conn.Open();
        TestCases.ExecuteSQL("drop table if exists TestCUBRIDBlob", conn);
        TestCases.ExecuteSQL("create table TestCUBRIDBlob(c_integer int not null auto_increment," +
                             "c_blob BLOB," +
                              "primary key (c_integer))", conn);

        TestCUBRIDBlobType test = new TestCUBRIDBlobType
        {
          c_blob = new CUBRIDBlob(conn)
        };

        BinaryReader origianlFileReader = new BinaryReader(File.Open("../../CUBRID.ico", FileMode.Open));
        byte[] bytesOriginalData = origianlFileReader.ReadBytes((int)origianlFileReader.BaseStream.Length);
        origianlFileReader.Close();
        test.c_blob.SetBytes(1, bytesOriginalData);
        //Insert
        ISessionFactory sessionFactory = cfg.BuildSessionFactory();
        using (var session = sessionFactory.OpenSession())
        {
          using (var trans = session.BeginTransaction(IsolationLevel.ReadUncommitted))
          {
            session.Save(test);
            trans.Commit();
          }
        }

        const string sql2 = "SELECT c_blob from TestCUBRIDBlob";
        CUBRIDCommand cmd2 = new CUBRIDCommand(sql2, conn);
        DbDataReader reader = cmd2.ExecuteReader();
        while (reader.Read())
        {
          CUBRIDBlob bImage = (CUBRIDBlob)reader[0];
          byte[] bytesRetrievedData = bImage.GetBytes(1, (int)bImage.BlobLength);

          Debug.Assert(bytesOriginalData.Length == bytesRetrievedData.Length);
          Debug.Assert(bytesOriginalData[0] == bytesRetrievedData[0]);
          Debug.Assert(bytesOriginalData[bytesOriginalData.Length - 1] == bytesRetrievedData[bytesRetrievedData.Length - 1]);
        }

        //Clean the database schema
        TestCases.ExecuteSQL("drop table if exists TestCUBRIDBlob", conn);
      }
    }