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); } } }
private void FindViews(DataTable schemaTable, string[] filters) { StringBuilder sql = new StringBuilder(); StringBuilder where = new StringBuilder(); const string selectTables = "select class_name from db_class where is_system_class='NO' and class_type='VCLASS'"; sql.AppendFormat(CultureInfo.InvariantCulture, selectTables); if (filters != null) { string view_name_pattern = filters[0]; where.AppendFormat(CultureInfo.InvariantCulture, " and class_name LIKE '{0}'", view_name_pattern); sql.Append(@where); } using (CUBRIDCommand cmd = new CUBRIDCommand(sql.ToString(), conn)) { using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader()) { while (reader.Read()) { DataRow row = schemaTable.NewRow(); row["VIEW_CATALOG"] = conn.Database; row["VIEW_SCHEMA"] = conn.Database; row["VIEW_NAME"] = reader.GetString(0); schemaTable.Rows.Add(row); } } } }
public void conn_setIsolationLevel() { string conn_string = "server=test-db-server;database=demodb;port=33000;user=dba;password="******"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 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); } }
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 static void ExecuteSQL(string sql) //{ // using (CUBRIDCommand cmd = new CUBRIDCommand(sql, connectionStr)) // { // cmd.ExecuteNonQuery(); // } //} public static void ExecuteSQL(string sql, CUBRIDConnection conn) { using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { cmd.ExecuteNonQuery(); } }
private static void TestParameterCollection() { string sql = "drop table if exists TestTable;"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { cmd.ExecuteNonQuery(); } sql = "CREATE TABLE TestTable (clsid BLOB);"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { cmd.ExecuteNonQuery(); } byte[] bytes = new byte[36] { 55, 56, 50, 69, 55, 57, 67, 69, 45, 50, 70, 68, 68, 45, 52, 68, 50, 55, 45, 65, 51, 48, 48, 45, 69, 48, 56, 56, 70, 56, 68, 68, 55, 54, 66, 69 }; sql = "INSERT INTO TestTable VALUES(?);"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { CUBRIDBlob Blob = new CUBRIDBlob(conn); Blob.SetBytes(1, bytes); CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?p"; param.Value = Blob; cmd.Parameters.Add(param); cmd.Parameters[0].DbType = DbType.Binary; try { cmd.Parameters.Insert(0, param); } catch (Exception e) { Debug.Assert(e.Message == "Parameter already added to the collection!"); } try { cmd.Parameters.Insert(0, null); } catch (Exception e) { string es = e.ToString(); Debug.Assert(e.Message == "Only CUBRIDParameter objects are valid!"); } cmd.ExecuteNonQuery(); } using (CUBRIDCommand cmd = new CUBRIDCommand("Select * from TestTable", conn)) { using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader()) { reader.Read(); byte[] buffer = new byte[36]; long len = reader.GetBytes(0, 0, buffer, 0, 36); ASCIIEncoding encoding = new ASCIIEncoding(); string clsid = encoding.GetString(buffer); Debug.Assert(clsid == "782E79CE-2FDD-4D27-A300-E088F8DD76BE"); } } }
///<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 static object GetSingleValue(string sql, CUBRIDConnection conn) { object ret = null; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { ret = cmd.ExecuteScalar(); } return ret; }
public static int GetTableRowsCount(string tableName, CUBRIDConnection conn) { int count = -1; string sql = "select count(*) from `" + tableName + "`"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { count = (int)cmd.ExecuteScalar(); } return count; }
public static int GetTablesCount(string tableName, CUBRIDConnection conn) { int count = 0; string sql = "select count(*) from db_class where class_name = '" + tableName + "'"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { count = (int)cmd.ExecuteScalar(); } return count; }
/// <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); } }
/* * internal CUBRIDDataReader(CUBRIDCommand stmt, int handle, int count, ColumnMetaData[] columnInfos) * { * this.stmt = stmt; * conn = (CUBRIDConnection)stmt.Connection; * this.handle = handle; * resultCount = count; * columnMetaData = columnInfos; * currentRow = 0; * resultTuple = new ResultTuple(columnInfos.Length); * commandBehavior = CommandBehavior.Default; * InitColumnTypeName(); * } */ internal CUBRIDDataReader(CUBRIDCommand stmt, int handle, int count, ColumnMetaData[] columnInfos, int tupleCount) { this.stmt = stmt; conn = (CUBRIDConnection)stmt.Connection; this.handle = handle; resultCount = count; columnMetaData = columnInfos; currentRow = 0; this.tupleCount = tupleCount; resultTuple = new ResultTuple(columnInfos.Length); commandBehavior = CommandBehavior.Default; InitColumnTypeName(); }
private static void Test_apis_514() { string sql = "select * from nation order by code asc"; CUBRIDCommand cmd = new CUBRIDCommand(sql, conn); CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader(CommandBehavior.CloseConnection); Console.WriteLine(reader.IsClosed); Console.WriteLine(conn.State); reader.Close(); Console.WriteLine(reader.IsClosed); Console.WriteLine(conn.State); }
/// <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> /// Gets the databases matching the database name pattern. /// </summary> /// <param name="filters">the database name pattern, value is {"database name pattern"}. It should be a string array with Length==1. <para/> /// If filters == null or Length == 0, the default filters {"%"} is used. If the Length > 1, the first database name is used.<para/></param> /// <returns> A <see cref="DataTable" /> that contains database schema information and contains columns {"CATALOG_NAME", "SCHEMA_NAME"}</returns> public DataTable GetDatabases(string[] filters) { string catalog = "%"; if (filters != null && filters.Length > 0 && filters[0] != null) { catalog = filters[0]; } const string sql = "SELECT LIST_DBS()"; using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn)) { DataTable dt = new DataTable(); da.Fill(dt); using (DataTable table = new DataTable("Databases")) { table.Columns.Add("CATALOG_NAME", typeof(string)); table.Columns.Add("SCHEMA_NAME", typeof(string)); foreach (DataRow row in dt.Rows) //just one row is returned always { //CUBRID returns the list of databases as one single row/one column string[] dbs = row[0].ToString().Split(' '); foreach (String dbname in dbs) { string sqlDb = String.Format("SELECT COUNT('{0}') FROM db_root WHERE '{1}' LIKE '{2}'", dbname, dbname, catalog); using (CUBRIDCommand cmd = new CUBRIDCommand(sqlDb, conn)) { int count = (int)cmd.ExecuteScalar(); if (count > 0) { DataRow newRow = table.NewRow(); newRow[0] = dbname; newRow[1] = dbname; table.Rows.Add(newRow); } } } } return(table); } } }
/// <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); } }
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); } }
/// <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); } }
/// <summary> /// Clones this instance. /// </summary> /// <returns> </returns> public CUBRIDCommand Clone() { using (CUBRIDCommand clone = new CUBRIDCommand(cmdText, conn, transaction)) { clone.CommandType = CommandType; clone.cmdTimeout = cmdTimeout; clone.UpdatedRowSource = UpdatedRowSource; for (int i = 0; i < paramCollection.Count; i++) { CUBRIDParameter p = (CUBRIDParameter)paramCollection[i].Clone(); clone.Parameters.Add(p); } return(clone); } }
public static void Test_CUBRIDBlob_Select() { Configuration cfg = (new Configuration()).Configure().AddAssembly(typeof(TestCUBRIDBlobType).Assembly); 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); const string sql = "insert into TestCUBRIDBlob values(1, ?)"; CUBRIDCommand cmd = new CUBRIDCommand(sql, conn); CUBRIDBlob Blob = new CUBRIDBlob(conn); BinaryReader originalFileReader = new BinaryReader(File.Open("../../CUBRID.ico", FileMode.Open)); byte[] bytesOriginalData = originalFileReader.ReadBytes((int)originalFileReader.BaseStream.Length); originalFileReader.Close(); Blob.SetBytes(1, bytesOriginalData); CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?p"; param.Value = Blob; cmd.Parameters.Add(param); cmd.Parameters[0].DbType = DbType.Binary; cmd.ExecuteNonQuery(); cmd.Close(); ISessionFactory sessionFactory = cfg.BuildSessionFactory(); using (var session = sessionFactory.OpenSession()) { //Retrieve the inserted information IQuery query = session.CreateQuery("FROM TestCUBRIDBlobType"); IList<TestCUBRIDBlobType> testQuery = query.List<TestCUBRIDBlobType>(); Debug.Assert(testQuery[0].c_integer == 1); CUBRIDBlob bImage = testQuery[0].c_blob; 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); } }
/// <summary> /// Gets the procedures matching the procedure name filter. /// </summary> /// <param name="filters"> The procedure name filter, value is {"procedure name pattern"}. It should be a string array with Length==1.<para/> /// If filters == null or Length == 0, the default filters {"%"} is used. If the Length > 1, the first procedure name is used. <para/></param> /// <returns>A <see cref="DataTable" /> that contains procedure schema information and contains <para/> /// columns={"PROCEDURE_NAME", "PROCEDURE_TYPE", "RETURN_TYPE", "ARGUMENTS_COUNT", "LANGUAGE", "TARGET", "OWNER"} /// </returns> public DataTable GetProcedures(string[] filters) { using (DataTable dt = new DataTable("Procedures")) { dt.Columns.Add(new DataColumn("PROCEDURE_NAME", typeof(string))); dt.Columns.Add(new DataColumn("PROCEDURE_TYPE", typeof(string))); dt.Columns.Add(new DataColumn("RETURN_TYPE", typeof(string))); dt.Columns.Add(new DataColumn("ARGUMENTS_COUNT", typeof(int))); dt.Columns.Add(new DataColumn("LANGUAGE", typeof(string))); dt.Columns.Add(new DataColumn("TARGET", typeof(string))); dt.Columns.Add(new DataColumn("OWNER", typeof(string))); string procedureName = "%"; if (filters != null && filters.Length > 0 && filters[0] != null) { procedureName = filters[0]; } string sql = String.Format("select * from db_stored_procedure where sp_name like '{0}'", procedureName); using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader()) { while (reader.Read()) { DataRow row = dt.NewRow(); row["PROCEDURE_NAME"] = reader.GetString(0); row["PROCEDURE_TYPE"] = reader.GetString(1); row["RETURN_TYPE"] = reader.GetString(2); row["ARGUMENTS_COUNT"] = reader.GetInt(3); row["LANGUAGE"] = reader.GetString(4); row["TARGET"] = reader.GetString(5); row["OWNER"] = reader.GetString(6); dt.Rows.Add(row); } } } return(dt); } }
/// <summary> /// Test CUBRIDCommand column properties /// </summary> private static void Test_Command_ColumnProperties() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); String sql = "select * from nation"; CUBRIDCommand cmd = new CUBRIDCommand(sql, conn); CUBRIDCommand cmd2 = cmd.Clone(); try { cmd.Cancel(); } catch (Exception e) { string r = "System.NotSupportedException: Specified method is not supported"; Debug.Assert(e.Message.Substring(0,r.Length) == r); } Debug.Assert(cmd.CommandType == cmd2.CommandType); CUBRIDDataAdapter da = new CUBRIDDataAdapter(); da.SelectCommand = cmd; DataTable dt = new DataTable(""); da.FillSchema(dt, SchemaType.Source);//To retrieve all the column properties you have to use the FillSchema() method Debug.Assert(cmd.ColumnInfos[0].Name == "code"); Debug.Assert(cmd.ColumnInfos[0].IsPrimaryKey == true); Debug.Assert(cmd.ColumnInfos[0].IsForeignKey == false); Debug.Assert(cmd.ColumnInfos[0].IsNullable == false); Debug.Assert(cmd.ColumnInfos[0].RealName == ""); Debug.Assert(cmd.ColumnInfos[0].Precision == 3); Debug.Assert(cmd.ColumnInfos[0].Scale == 0); Debug.Assert(cmd.ColumnInfos[0].IsAutoIncrement == false); Debug.Assert(cmd.ColumnInfos[0].IsReverseIndex == false); Debug.Assert(cmd.ColumnInfos[0].IsReverseUnique == false); Debug.Assert(cmd.ColumnInfos[0].IsShared == false); Debug.Assert(cmd.ColumnInfos[0].Type == CUBRIDDataType.CCI_U_TYPE_CHAR); Debug.Assert(cmd.ColumnInfos[0].Table == "nation"); } }
public void conn_dataAdapter_update() { string conn_string = "server=test-db-server;database=demodb;port=33000;user=dba;password="******"drop table if exists tbl"; cmd.ExecuteNonQuery(); cmd.CommandText = "create table tbl (id int, name varchar(100))"; cmd.ExecuteNonQuery(); cmd.CommandText = "insert into tbl values (1, 'Nancy')"; cmd.ExecuteNonQuery(); cmd.CommandText = "insert into tbl values (2, 'Peter')"; cmd.ExecuteNonQuery(); CUBRIDDataAdapter adapter = new CUBRIDDataAdapter(); //SelectCommand string sql = "select * from tbl"; CUBRIDCommand cmd2 = new CUBRIDCommand(sql, conn); adapter.SelectCommand = cmd2; sql = "insert into tbl values (3, 'Kitty')"; cmd2 = new CUBRIDCommand(sql, conn); adapter.InsertCommand = cmd2; adapter.InsertCommand.ExecuteNonQuery(); sql = "update tbl set name='Mandy' where id=1"; cmd2 = new CUBRIDCommand(sql, conn); adapter.UpdateCommand = cmd2; adapter.UpdateCommand.ExecuteNonQuery(); sql = "delete from tbl where name='Mandy'"; cmd2 = new CUBRIDCommand(sql, conn); adapter.DeleteCommand = cmd2; adapter.DeleteCommand.ExecuteNonQuery(); conn.Close(); }
public void CUBRIDCommand_Constructor_SQLAndConn_Test() { CUBRIDConnection conn = new CUBRIDConnection(); conn.ConnectionString = DBHelper.connString; string sql = "select * from nation order by code asc"; CUBRIDCommand cmd = new CUBRIDCommand(sql, 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(); }
private static void Test_Sequence_Default() { ExecuteSQL("DROP TABLE IF EXISTS t", conn); //Create a new table with a collection ExecuteSQL("CREATE TABLE t(s SET(string))", conn); //Insert some data in the sequence column string[] sArray = new string[8]{"P", "Types of fragmentation", "File fragmentation", "Free space fragmentation", "File scattering", "File system fragmentation", "Preventing fragmentation",null}; string sql = "INSERT INTO t(s) VALUES( ?);"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?p"; param.Value = sArray; param.InnerCUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_LAST + 1; cmd.Parameters.Add(param); cmd.Parameters[0].CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_SET; cmd.ExecuteNonQuery(); } using (CUBRIDCommand cmd = new CUBRIDCommand("select * from t", conn)) { using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader()) { reader.Read(); object objValue = reader.GetValue(0); Array oArray = objValue as Array; if (oArray != null) Console.WriteLine(oArray.Length); cmd.ColumnInfos[0].ToString(); } } }
/// <summary> /// Closes the <see cref="CUBRIDDataReader" /> object. /// </summary> public override void Close() { if (isClosed) { return; } bool shouldCloseConnection = (commandBehavior & CommandBehavior.CloseConnection) != 0; commandBehavior = CommandBehavior.Default; //Clear all remaining resultsets try { while (NextResult()) { } } catch { } try { if (shouldCloseConnection) { conn.Close(); } stmt.Close(); } catch { //Do not propagate exceptions } finally { stmt = null; conn = null; isClosed = true; } }
/// <summary> /// Test CUBRIDCommand ExecuteNonQuery() method /// </summary> private static void Test_ExecuteNonQuery() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); CreateTestTable(conn); string sql = "insert into t values(1, 'a', 'abc', 1.2, 2.1, '10/31/2008')"; CUBRIDCommand cmd = new CUBRIDCommand(sql, conn); cmd.ExecuteNonQuery(); cmd.Close(); } using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); string sql = "select * from t"; CUBRIDCommand cmd = new CUBRIDCommand(sql, conn); DbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) //only one row will be available { Debug.Assert(reader.GetInt32(0) == 1); Debug.Assert(reader.GetString(1) == "a "); Debug.Assert(reader.GetString(2) == "abc"); Debug.Assert(reader.GetFloat(3) == 1.2f); Debug.Assert(reader.GetFloat(4) == (float)Convert.ToDouble(2.1)); Debug.Assert(reader.GetDateTime(5) == new DateTime(2008, 10, 31)); } cmd.Close(); CleanupTestTable(conn); } }
/// <summary> /// Gets the delete command. /// </summary> /// <returns> </returns> public new CUBRIDCommand GetDeleteCommand() { CUBRIDCommand cmd = (CUBRIDCommand)base.GetDeleteCommand(); char[] delimitators = { ',', ')', ' ', '=' }; int from = cmd.CommandText.IndexOf("?", StringComparison.Ordinal); int to = cmd.CommandText.IndexOfAny(delimitators, from); while (from != -1) { cmd.CommandText = cmd.CommandText.Remove(from, to - from); cmd.CommandText = cmd.CommandText.Insert(from, "?"); from = cmd.CommandText.IndexOf("?", from + 1, StringComparison.Ordinal); if (from != -1) { to = cmd.CommandText.IndexOfAny(delimitators, from); } } cmd.CommandText = cmd.CommandText.Replace(" = NULL", " IS NULL"); return(cmd); }
private static void Test_apis_669() { String sql = "select s_name from code where f_name = 'Woman';select * from code;"; CUBRIDCommand cmd = new CUBRIDCommand(sql, conn); CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader(); while (reader.Read()) { Console.WriteLine(reader.GetString(0)); }; while (reader.NextResult()) { Console.WriteLine("============================="); while (reader.Read()) { Console.WriteLine(reader.GetString(0)); // Console.WriteLine(reader.GetString(1)); }; } }
/// <summary> /// Test Encodings support with parameters /// </summary> private static void Test_EncodingsWithParameters() { 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)) { CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?"; param.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_STRING; param.Value = "¾Æ¹«°³"; cmd.Parameters.Add(param); cmd.ExecuteNonQuery(); } sql = "select * from t where b = ?"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?"; param.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_STRING; param.Value = "¾Æ¹«°³"; cmd.Parameters.Add(param); 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)) { CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?"; param.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_STRING; param.Value = "¾Æ¹°³"; cmd.Parameters.Add(param); cmd.ExecuteNonQuery(); } sql = "select * from t where b = ?"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?"; param.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_STRING; param.Value = "¾Æ¹°³"; cmd.Parameters.Add(param); 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); } }
/// <summary> /// Get the schemas of the foreign keys satisfying the foreign key filter. /// </summary> /// <param name="filters"> The foreign key filter, the value is {"table name pattern", "foreign key name pattern"}.<para/> /// If the table name pattern is null, the default "%" is used. If the foreign key name pattern is null, the default "" is used. </param> /// <returns>A <see cref="DataTable" /> that contains foreign key schema information and contains <para/> /// columns {"PKTABLE_NAME", "PKCOLUMN_NAME", "FKTABLE_NAME", "FKCOLUMN_NAME", "KEY_SEQ", "UPDATE_ACTION", "DELETE_ACTION", "FK_NAME", "PK_NAME"} </returns> public DataTable GetForeignKeys(string[] filters) { if (filters == null) { throw new ArgumentNullException(Utils.GetStr(MsgId.NoFiltersSpecified)); } if (filters.Length > 2) { throw new ArgumentException(Utils.GetStr(MsgId.IncorrectNumberOfFilters)); } string tableName = filters[0]; string keyName = ""; if (filters.Length > 1) { keyName = filters[1]; } T_CCI_ERROR err = new T_CCI_ERROR(); int handle = CciInterface.cci_schema_info(conn, T_CCI_SCH_TYPE.CCI_SCH_IMPORTED_KEYS, tableName, keyName, (char)0, ref err); if (handle < 0) { throw new CUBRIDException(err.err_msg); } ColumnMetaData[] columnInfos = CciInterface.cci_get_result_info(conn, handle); CUBRIDCommand command = new CUBRIDCommand(null, conn); CUBRIDDataReader reader = new CUBRIDDataReader(command, handle, columnInfos.Length, columnInfos, columnInfos.Length); DataTable dt = new DataTable("ForeignKeys"); dt.Columns.Add("PKTABLE_NAME", typeof(string)); dt.Columns.Add("PKCOLUMN_NAME", typeof(string)); dt.Columns.Add("FKTABLE_NAME", typeof(string)); dt.Columns.Add("FKCOLUMN_NAME", typeof(string)); dt.Columns.Add("KEY_SEQ", typeof(short)); dt.Columns.Add("UPDATE_ACTION", typeof(short)); dt.Columns.Add("DELETE_ACTION", typeof(short)); dt.Columns.Add("FK_NAME", typeof(string)); dt.Columns.Add("PK_NAME", typeof(string)); while (reader.Read()) { DataRow row = dt.NewRow(); row["PKTABLE_NAME"] = reader.GetString(0); row["PKCOLUMN_NAME"] = reader.GetString(1); row["FKTABLE_NAME"] = reader.GetString(2); row["FKCOLUMN_NAME"] = reader.GetString(3); row["KEY_SEQ"] = reader.GetString(4); row["UPDATE_ACTION"] = reader.GetString(5); row["DELETE_ACTION"] = reader.GetString(6); row["FK_NAME"] = reader.GetString(7); row["PK_NAME"] = reader.GetString(8); dt.Rows.Add(row); } return(dt); }
private static List<object> GetTableValues(string tableName, int indexPosition, string[] columnNames) { List<object> columnValues = new List<object>(); using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); using(CUBRIDCommand cmd = new CUBRIDCommand("select * from " + tableName, conn)) { DbDataReader reader = cmd.ExecuteReader(); for (int i = 0; i < indexPosition; i++) { reader.Read(); } for (int i = 0; i < columnNames.Length; i++) { columnValues.Add(reader[columnNames[i]]); } } } return columnValues; }
/// <summary> /// Test SQL statements execution, using DataReader /// </summary> private static void Test_DataReader_Basic() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); String sql = "select * from nation order by `code` asc"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { using (DbDataReader reader = cmd.ExecuteReader()) { reader.Read(); //retrieve just one row Debug.Assert(reader.FieldCount == 4); Debug.Assert(reader.GetString(0) == "AFG"); Debug.Assert(reader.GetString(1) == "Afghanistan"); Debug.Assert(reader.GetString(2) == "Asia"); Debug.Assert(reader.GetString(3) == "Kabul"); } } } }
/// <summary> /// Test read many rows in one SQL statement execution /// </summary> private static void Test_Read_ManyRows() { int curr_row = 0; using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); string sql = "select * from athlete"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { DbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { curr_row++; } cmd.Close(); } } Debug.Assert(curr_row == 6677); }
/// <summary> /// Test CUBRIDDataReader getter methods /// </summary> private static void Test_DataReader_Getxxx() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); string sql = "select * from nation;"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader()) { reader.Read(); Debug.Assert(reader.GetOrdinal("code") == 0); Debug.Assert(reader.GetName(0) == "code"); Debug.Assert(reader.GetColumnName(0) == "code"); Debug.Assert(reader.GetColumnType(0) == typeof(System.String)); Debug.Assert(reader.GetDataTypeName(0) == "CHAR"); } } } }
/// <summary> /// Test SQL statements execution, using DataReader and parameters /// </summary> private static void Test_DataReader_Parameters() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); CUBRIDCommand cmd = new CUBRIDCommand("select `code` from nation where capital = ?", conn); CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?"; param.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_STRING; param.Value = "Kabul"; cmd.Parameters.Add(param); DbDataReader reader = cmd.ExecuteReader(); Debug.Assert(reader.FieldCount == 1); while (reader.Read()) //only one row is available { Debug.Assert(reader.GetString(0) == "AFG"); } cmd.Close(); } }
/// <summary> /// Clones this instance. /// </summary> /// <returns> </returns> public CUBRIDCommand Clone() { using (CUBRIDCommand clone = new CUBRIDCommand(cmdText, conn, transaction)) { clone.CommandType = CommandType; clone.cmdTimeout = cmdTimeout; clone.UpdatedRowSource = UpdatedRowSource; for (int i = 0; i < paramCollection.Count; i++) { CUBRIDParameter p = (CUBRIDParameter)paramCollection[i].Clone(); clone.Parameters.Add(p); } return clone; } }
private void LoadTableColumns(DataTable dt, string tableName, string columnRestriction) { string sql = String.Format( "select * from db_attribute where class_name like '{0}' and attr_name like '{1}' order by def_order asc", tableName, columnRestriction); using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { int pos = 1; using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader()) { while (reader.Read()) { string colName = reader.GetString(0); DataRow row = dt.NewRow(); row["TABLE_CATALOG"] = conn.Database; row["TABLE_SCHEMA"] = conn.Database; row["TABLE_NAME"] = tableName; row["COLUMN_NAME"] = colName; row["ORDINAL_POSITION"] = pos++; for (int i = 0; i < reader.GetColumnCount(); i++) { switch (reader.GetColumnName(i)) { case "defalut_value": row["COLUMN_DEFAULT"] = reader.GetString(i); break; case "is_nullable": row["IS_NULLABLE"] = reader.GetString(i).Equals("YES"); break; case "data_type": row["DATA_TYPE"] = reader.GetString(i); break; case "prec": row["NUMERIC_PRECISION"] = reader.GetInt(i); break; case "scale": row["NUMERIC_SCALE"] = reader.GetInt(i); break; case "code_set": case "charset": row["CHARACTER_SET"] = reader.GetString(i); break; default: break; } } dt.Rows.Add(row); } } } }
/// <summary> /// Initializes a new instance of the <see cref="CUBRIDDataAdapter" /> class. /// </summary> /// <param name="selectCommand"> The select command. </param> public CUBRIDDataAdapter(CUBRIDCommand selectCommand) : this() { SelectCommand = selectCommand; }
/// <summary> /// Initializes a new instance of the <see cref="CUBRIDDataAdapter" /> class. /// </summary> /// <param name="selectCommandText"> The select command text. </param> /// <param name="connection"> The connection. </param> public CUBRIDDataAdapter(string selectCommandText, CUBRIDConnection connection) : this() { SelectCommand = new CUBRIDCommand(selectCommandText, connection); }
/// <summary> /// Initializes a new instance of the <see cref="CUBRIDDataAdapter" /> class. /// </summary> /// <param name="selectCommandText"> The select command text. </param> /// <param name="selectConnString"> The select connection string. </param> public CUBRIDDataAdapter(string selectCommandText, string selectConnString) : this() { SelectCommand = new CUBRIDCommand(selectCommandText, new CUBRIDConnection(selectConnString)); }