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 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); } } }
/// <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> /// 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> /// Registers the <see cref="T:System.Data.Common.DbCommandBuilder" /> to handle the <see /// cref="E:System.Data.OleDb.OleDbDataAdapter.RowUpdating" /> event for a <see cref="T:System.Data.Common.DbDataAdapter" />. /// </summary> /// <param name="dataAdapter"> The <see cref="T:System.Data.Common.DbDataAdapter" /> to be used for the update. </param> protected override void SetRowUpdatingHandler(DbDataAdapter dataAdapter) { CUBRIDDataAdapter adapter = (dataAdapter as CUBRIDDataAdapter); if (adapter != null) { if (dataAdapter != base.DataAdapter) { adapter.RowUpdating += RowUpdating; } else { adapter.RowUpdating -= RowUpdating; } } }
//http://msdn.microsoft.com/en-us/library/tf579hcz%28v=vs.80%29.aspx /// <summary> /// Test CUBRIDCommandBuilder class, and methods used to automatically get SQL commands /// </summary> private static void Test_CommandBuilder_GetCommands() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); String sql = "select * from nation order by `code` asc"; CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn); CUBRIDCommandBuilder cmdBuilder = new CUBRIDCommandBuilder(da); da.UpdateCommand = cmdBuilder.GetUpdateCommand(); Debug.Assert(da.UpdateCommand.CommandText == "UPDATE `nation` SET `code` = ?, `name` = ?, `continent` = ?, `capital` = ? WHERE ((`code` = ?) AND (`name` = ?) AND ((? = 1 AND `continent` IS NULL) OR (`continent` = ?)) AND ((? = 1 AND `capital` IS NULL) OR (`capital` = ?)))"); da.InsertCommand = cmdBuilder.GetInsertCommand(); Debug.Assert(da.InsertCommand.CommandText == "INSERT INTO `nation` (`code`, `name`, `continent`, `capital`) VALUES (?, ?, ?, ?)"); da.DeleteCommand = cmdBuilder.GetDeleteCommand(); Debug.Assert(da.DeleteCommand.CommandText == "DELETE FROM `nation` WHERE ((`code` = ?) AND (`name` = ?) AND ((? = 1 AND `continent` IS NULL) OR (`continent` = ?)) AND ((? = 1 AND `capital` IS NULL) OR (`capital` = ?)))"); } }
/// <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(); }
//http://msdn.microsoft.com/en-us/library/bbw6zyha%28v=vs.80%29.aspx /// <summary> /// Test DataTable implicit UPDATE /// </summary> private static void Test_DataTable_UpdateImplicit() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); String sql = "select * from nation order by `code` asc"; using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn)) { using (CUBRIDDataAdapter daCmd = new CUBRIDDataAdapter(sql, conn)) { CUBRIDCommandBuilder cmdBuilder = new CUBRIDCommandBuilder(daCmd); da.UpdateCommand = cmdBuilder.GetUpdateCommand(); } DataTable dt = new DataTable("nation"); da.Fill(dt); //Update data DataRow workRow = dt.Rows[0]; Debug.Assert(workRow["code"].ToString() == "AFG"); Debug.Assert(workRow["capital"].ToString() == "Kabul"); workRow.BeginEdit(); workRow["capital"] = "MyKabul"; workRow.EndEdit(); da.Update(dt); Debug.Assert(workRow["capital"].ToString() == "MyKabul"); Debug.Assert(workRow.RowState.ToString() != "New"); } Debug.Assert((string)GetSingleValue("select capital from nation where `code` = 'AFG'", conn) == "MyKabul"); //Revert changes ExecuteSQL("update nation set capital = 'Kabul' where capital = 'MyKabul'", conn); Debug.Assert((string)GetSingleValue("select capital from nation where `code` = 'AFG'", conn) == "Kabul"); } }
/// <summary> /// Gets the users matching the user name pattern. /// </summary> /// <param name="filters"> the user name pattern, value is {"user 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 user name is used.<para/> </param> /// <returns>A <see cref="DataTable" /> that contains user schema information and contains columns {"USERNAME"}</returns> public DataTable GetUsers(string[] filters) { string pattern = "%"; if (filters != null && filters.Length > 0 && filters[0] != null) { pattern = filters[0]; } String sb = String.Format("SELECT `name` from db_user where `name` LIKE '{0}'", pattern); using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sb, conn)) { DataTable dt = new DataTable(); da.Fill(dt); dt.TableName = "USERS"; dt.Columns[0].ColumnName = "USERNAME"; return(dt); } }
/// <summary> /// Test basic SQL Statements execution, using DataTable /// </summary> private static void Test_DataTable_Basic() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); String sql = "select * from nation order by `code` asc"; using (DataTable dt = new DataTable("nation")) { CUBRIDDataAdapter da = new CUBRIDDataAdapter(); da.SelectCommand = new CUBRIDCommand(sql, conn); da.Fill(dt); Debug.Assert(dt.Columns.Count == 4); Debug.Assert(dt.Rows.Count == 215); Debug.Assert(dt.Rows[1][1].ToString() == "Netherlands Antilles"); Debug.Assert(dt.Rows[3][2].ToString() == "Africa"); } } }
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; }
//http://msdn.microsoft.com/en-us/library/bbw6zyha%28v=vs.80%29.aspx /// <summary> /// Test DataTable explicit DELETE /// </summary> private static void Test_DataTable_DeleteExplicit() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); //Insert a new row ExecuteSQL("insert into nation values('ZZZZ', 'Z', 'Z', 'Z')", conn); Debug.Assert(GetTableRowsCount("nation", conn) == 216); String sql = "select * from nation order by `code` desc"; using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn)) { //Initialize the command object that will be used as the DeleteCommand for the DataAdapter. CUBRIDCommand daDelete = new CUBRIDCommand("delete from nation where code = ?", conn); //Parameter: code daDelete.Parameters.Add(new CUBRIDParameter("?p1", DbType.String)); daDelete.Parameters["?p1"].SourceVersion = DataRowVersion.Original; daDelete.Parameters["?p1"].SourceColumn = "code"; daDelete.Parameters["?p1"].SourceColumnNullMapping = false; daDelete.UpdatedRowSource = UpdateRowSource.OutputParameters; //Assign the command to the DeleteCommand property of the DataAdapter. da.DeleteCommand = daDelete; DataTable dt = new DataTable("nation"); da.Fill(dt); Debug.Assert(dt.Rows[0]["capital"].ToString() == "Z"); dt.Rows[0].Delete(); da.Update(dt); Debug.Assert(dt.Rows.Count == 215); } Debug.Assert(GetTableRowsCount("nation", conn) == 215); } }
/// <summary> /// Test SQL statements execution, using DataView /// </summary> private static void Test_DataView_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); DataTable dt = new DataTable("nation"); da.Fill(dt); DataView dataView = new DataView(dt); Debug.Assert(dataView.Count == 215); Debug.Assert(dataView.Table.TableName == "nation"); foreach (DataRowView view in dataView) { Debug.Assert(dataView[0][0].ToString() == "AFG"); break; //retrieve just one row } } }
/// <summary> /// Get the schemas of the index columns satisfying the index column filter. /// </summary> /// <param name="filters"> The index column filter, the value is {"table name pattern", "index name pattern"}.<para/> /// If the pattern string is null, the default "%" is used.</param> /// <returns>A <see cref="DataTable" /> that contains index column schema information and contains <para/> /// columns {"INDEX_CATALOG", "INDEX_SCHEMA", "INDEX_NAME", "TABLE_NAME", "COLUMN_NAME", "ORDINAL_POSITION", "DIRECTION"} </returns> public DataTable GetIndexColumns(string[] filters) { using (DataTable dt = new DataTable("IndexColumns")) { dt.Columns.Add("INDEX_CATALOG", typeof(string)); dt.Columns.Add("INDEX_SCHEMA", typeof(string)); dt.Columns.Add("INDEX_NAME", typeof(string)); dt.Columns.Add("TABLE_NAME", typeof(string)); dt.Columns.Add("COLUMN_NAME", typeof(string)); dt.Columns.Add("ORDINAL_POSITION", typeof(int)); dt.Columns.Add("DIRECTION", typeof(string)); string tableName = "%"; string indexName = "%"; if (filters != null) { if (filters.Length > 0 && filters[0] != null) { tableName = filters[0]; } if (filters.Length > 1 && filters[1] != null) { indexName = filters[1]; } } DataTable tables = GetTables(new[] { tableName }); string raw_sql = "select b.index_name, b.class_name, b.key_attr_name, b.key_order, b.asc_desc"; raw_sql += " from db_index a, db_index_key b"; raw_sql += " where a.class_name=b.class_name and a.index_name=b.index_name"; raw_sql += " and a.class_name like '{0}'"; raw_sql += " and a.index_name like '{1}'"; raw_sql += " order by b.key_order asc"; foreach (DataRow table in tables.Rows) { string sql = String.Format(raw_sql, tableName, indexName); using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn)) { DataTable indexes = new DataTable(); da.Fill(indexes); foreach (DataRow index in indexes.Rows) { DataRow row = dt.NewRow(); row["INDEX_CATALOG"] = conn.Database; row["INDEX_SCHEMA"] = conn.Database; row["INDEX_NAME"] = index[0].ToString(); row["TABLE_NAME"] = index[1].ToString(); row["COLUMN_NAME"] = index[2].ToString(); row["ORDINAL_POSITION"] = (int)index[3]; row["DIRECTION"] = index[4].ToString(); dt.Rows.Add(row); } } } return(dt); } }
public void CUBRIDDataAdapter_Command_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); CUBRIDDataAdapter adapter = new CUBRIDDataAdapter(); //SelectCommand string sql = "select * from t"; CUBRIDCommand cmd = new CUBRIDCommand(sql, conn); adapter.SelectCommand = cmd; 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()); Assert.AreEqual(sql, adapter.SelectCommand.CommandText); //UpdateCommand sql = "update t set name='Mandy' where id=1"; cmd = new CUBRIDCommand(sql, conn); adapter.UpdateCommand=cmd; adapter.UpdateCommand.ExecuteNonQuery(); dt.AcceptChanges(); adapter.Update(dt); Console.WriteLine(dt.Rows[0]["name"]); //dt.AcceptChanges(); //Assert.AreEqual(1, (int)dt.Rows[0]["id"]); //Assert.AreEqual("Mandy", dt.Rows[0]["name"].ToString()); //Assert.AreEqual(2, (int)dt.Rows[1]["id"]); //Assert.AreEqual("Peter", dt.Rows[1]["name"].ToString()); //Assert.AreEqual(sql, adapter.UpdateCommand.CommandText); //DeleteCommand sql = "delete from t where name='Mandy'"; cmd = new CUBRIDCommand(sql, conn); adapter.DeleteCommand=cmd; adapter.DeleteCommand.ExecuteNonQuery(); adapter.Update(dt); Assert.AreEqual(1, dt.Rows.Count); Assert.AreEqual(2, (int)dt.Rows[0]["id"]); Assert.AreEqual("Peter", dt.Rows[0]["name"].ToString()); Assert.AreEqual(sql, adapter.UpdateCommand.CommandText); //InsertCommand //revert test db DBHelper.ExecuteSQL("drop table if exists t", conn); conn.Close(); }
/// <summary> /// Test CLOB SELECT, using CUBRIDDataAdapter and DataSet /// </summary> private static void Test_Clob_SelectDataAdapter2() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); CreateTestTableLOB(conn); string sql1 = "insert into t (c) values(?)"; CUBRIDCommand cmd1 = new CUBRIDCommand(sql1, conn); CUBRIDClob Clob1 = new CUBRIDClob(conn); String str1 = conn.ConnectionString; //Use ConnectionString content for testing Clob1.SetString(1, str1); CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?"; param.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_CLOB; param.Value = Clob1; cmd1.Parameters.Add(param); cmd1.ExecuteNonQuery(); cmd1.Close(); string sql = "SELECT c from t"; DataSet ds = new DataSet(); CUBRIDDataAdapter da = new CUBRIDDataAdapter(); da.SelectCommand = new CUBRIDCommand(sql, conn); da.Fill(ds); DataTable dt = ds.Tables[0]; for (int j = 0; j < dt.Rows.Count; j++) { CUBRIDClob cImage = (CUBRIDClob)dt.Rows[j]["c"]; string str = cImage.GetString(1, (int)cImage.ClobLength); Debug.Assert(str.Length == str1.Length, "The selected CLOB length is not valid!"); Debug.Assert(str.Equals(str1), "The CLOB was not selected correctly!"); } CleanupTestTableLOB(conn); } }
/// <summary> /// Test DataTable explicit INSERT /// </summary> private static void Test_DataTable_InsertExplicit() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); String sql = "select * from nation order by `code` DESC LIMIT 10"; using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn)) { //Initialize the command object that will be used as the UpdateCommand for the DataAdapter. CUBRIDCommand daInsert = new CUBRIDCommand("insert into nation values(?,?,?,?)", conn); daInsert.CommandType = CommandType.Text; //Parameter: code daInsert.Parameters.Add(new CUBRIDParameter("?p1", DbType.String)); daInsert.Parameters["?p1"].SourceVersion = DataRowVersion.Current; daInsert.Parameters["?p1"].SourceColumn = "code"; daInsert.Parameters["?p1"].SourceColumnNullMapping = false; //Parameter: name daInsert.Parameters.Add(new CUBRIDParameter("?p2", DbType.String)); daInsert.Parameters["?p2"].SourceVersion = DataRowVersion.Original; daInsert.Parameters["?p2"].SourceColumn = "name"; daInsert.Parameters["?p2"].SourceColumnNullMapping = false; //Parameter: continent daInsert.Parameters.Add(new CUBRIDParameter("?p3", DbType.String)); daInsert.Parameters["?p3"].SourceVersion = DataRowVersion.Current; daInsert.Parameters["?p3"].SourceColumn = "continent"; daInsert.Parameters["?p3"].SourceColumnNullMapping = false; //Parameter: capital daInsert.Parameters.Add(new CUBRIDParameter("?p4", DbType.String)); daInsert.Parameters["?p4"].SourceVersion = DataRowVersion.Original; daInsert.Parameters["?p4"].SourceColumn = "capital"; daInsert.Parameters["?p4"].SourceColumnNullMapping = false; daInsert.UpdatedRowSource = UpdateRowSource.None; //Assign the command to the InsertCommand property of the DataAdapter. da.InsertCommand = daInsert; DataTable dt = new DataTable("nation"); da.Fill(dt); DataRow newRow = dt.NewRow(); newRow["code"] = "ZZZ"; newRow["name"] = "ABCDEF"; newRow["capital"] = "MyXYZ"; newRow["continent"] = "QWERTY"; dt.Rows.InsertAt(newRow, 0); da.Update(dt); dt.AcceptChanges(); Debug.Assert(dt.Rows[0]["capital"].ToString() == "MyXYZ"); Debug.Assert(newRow.RowState.ToString() != "New"); } Debug.Assert(GetTableRowsCount("nation", conn) == 216); //Revert changes ExecuteSQL("delete from nation where `code` = 'ZZZ'", conn); Debug.Assert(GetTableRowsCount("nation", conn) == 215); } }
/// <summary> /// Gets the users matching the user name pattern. /// </summary> /// <param name="filters"> the user name pattern, value is {"user 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 user name is used.<para/> </param> /// <returns>A <see cref="DataTable" /> that contains user schema information and contains columns {"USERNAME"}</returns> public DataTable GetUsers(string[] filters) { string pattern = "%"; if (filters != null && filters.Length > 0 && filters[0] != null) { pattern = filters[0]; } String sb = String.Format("SELECT `name` from db_user where `name` LIKE '{0}'", pattern); using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sb, conn)) { DataTable dt = new DataTable(); da.Fill(dt); dt.TableName = "USERS"; dt.Columns[0].ColumnName = "USERNAME"; return dt; } }
/// <summary> /// Get the schemas of the index columns satisfying the index column filter. /// </summary> /// <param name="filters"> The index column filter, the value is {"table name pattern", "index name pattern"}.<para/> /// If the pattern string is null, the default "%" is used.</param> /// <returns>A <see cref="DataTable" /> that contains index column schema information and contains <para/> /// columns {"INDEX_CATALOG", "INDEX_SCHEMA", "INDEX_NAME", "TABLE_NAME", "COLUMN_NAME", "ORDINAL_POSITION", "DIRECTION"} </returns> public DataTable GetIndexColumns(string[] filters) { using (DataTable dt = new DataTable("IndexColumns")) { dt.Columns.Add("INDEX_CATALOG", typeof(string)); dt.Columns.Add("INDEX_SCHEMA", typeof(string)); dt.Columns.Add("INDEX_NAME", typeof(string)); dt.Columns.Add("TABLE_NAME", typeof(string)); dt.Columns.Add("COLUMN_NAME", typeof(string)); dt.Columns.Add("ORDINAL_POSITION", typeof(int)); dt.Columns.Add("DIRECTION", typeof(string)); string tableName = "%"; string indexName = "%"; if (filters != null) { if (filters.Length > 0 && filters[0] != null) { tableName = filters[0]; } if (filters.Length > 1 && filters[1] != null) { indexName = filters[1]; } } DataTable tables = GetTables(new[] { tableName }); string raw_sql = "select b.index_name, b.class_name, b.key_attr_name, b.key_order, b.asc_desc"; raw_sql += " from db_index a, db_index_key b"; raw_sql += " where a.class_name=b.class_name and a.index_name=b.index_name"; raw_sql += " and a.class_name like '{0}'"; raw_sql += " and a.index_name like '{1}'"; raw_sql += " order by b.key_order asc"; foreach (DataRow table in tables.Rows) { string sql = String.Format(raw_sql, tableName, indexName); using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn)) { DataTable indexes = new DataTable(); da.Fill(indexes); foreach (DataRow index in indexes.Rows) { DataRow row = dt.NewRow(); row["INDEX_CATALOG"] = conn.Database; row["INDEX_SCHEMA"] = conn.Database; row["INDEX_NAME"] = index[0].ToString(); row["TABLE_NAME"] = index[1].ToString(); row["COLUMN_NAME"] = index[2].ToString(); row["ORDINAL_POSITION"] = (int)index[3]; row["DIRECTION"] = index[4].ToString(); dt.Rows.Add(row); } } } return dt; } }
/// <summary> /// Get the schemas of the indexes satisfying the index filter. /// </summary> /// <param name="filters"> The index filter, the value is {"table name pattern", "column name pattern", "index name pattern"}. <para/> /// If the pattern string is null, the default "%" is used </param> /// <returns>A <see cref="DataTable" /> that contains index schema information and contains columns {"INDEX_CATALOG", "INDEX_SCHEMA", <para/> /// "INDEX_NAME", "TABLE_NAME", "UNIQUE", "REVERSE", "PRIMARY", "FOREIGN_KEY", "DIRECTION"} </returns> public DataTable GetIndexes(string[] filters) { using (DataTable dt = new DataTable("Indexes")) { dt.Columns.Add("INDEX_CATALOG", typeof(string)); dt.Columns.Add("INDEX_SCHEMA", typeof(string)); dt.Columns.Add("INDEX_NAME", typeof(string)); dt.Columns.Add("TABLE_NAME", typeof(string)); dt.Columns.Add("UNIQUE", typeof(bool)); dt.Columns.Add("REVERSE", typeof(bool)); dt.Columns.Add("PRIMARY", typeof(bool)); dt.Columns.Add("FOREIGN_KEY", typeof(bool)); dt.Columns.Add("DIRECTION", typeof(string)); string tableName = "%"; string columnName = "%"; string indexName = "%"; if (filters != null) { if (filters.Length > 0 && filters[0] != null) { tableName = filters[0]; } if (filters.Length > 1 && filters[1] != null) { columnName = filters[1]; } if (filters.Length > 2 && filters[2] != null) { indexName = filters[2]; } } DataTable tables = GetTables(new[] { tableName }); string raw_sql = "select b.index_name, b.class_name, a.is_unique, a.is_reverse, a.is_primary_key, a.is_foreign_key, b.asc_desc"; raw_sql += " from db_index a,db_index_key b"; raw_sql += " where a.index_name=b.index_name"; raw_sql += " and a.class_name like '{0}'"; raw_sql += " and b.key_attr_name like '{1}'"; raw_sql += " and a.index_name like '{2}'"; raw_sql += " order by b.key_order"; foreach (DataRow table in tables.Rows) { string sql = String.Format(raw_sql, tableName, columnName, indexName); using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn)) { DataTable indexes = new DataTable(); da.Fill(indexes); foreach (DataRow index in indexes.Rows) { DataRow row = dt.NewRow(); row["INDEX_CATALOG"] = conn.Database; row["INDEX_SCHEMA"] = conn.Database; row["INDEX_NAME"] = index[0]; row["TABLE_NAME"] = index[1]; row["UNIQUE"] = index[2].ToString().Equals("YES"); row["REVERSE"] = index[3].ToString().Equals("YES"); row["PRIMARY"] = index[4].ToString().Equals("YES"); row["FOREIGN_KEY"] = index[5].ToString().Equals("YES"); row["DIRECTION"] = index[6].ToString(); dt.Rows.Add(row); } } } return dt; } }
//http://msdn.microsoft.com/en-us/library/bbw6zyha%28v=vs.80%29.aspx /// <summary> /// Test DataTable explicit UPDATE /// </summary> private static void Test_DataTable_UpdateExplicit() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); String sql = "select * from nation order by `code` asc LIMIT 10"; using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn)) { //Initialize the command object that will be used as the UpdateCommand for the DataAdapter. CUBRIDCommand daUpdate = new CUBRIDCommand("update nation set capital = ? where code = ?", conn); //Parameter: capital daUpdate.Parameters.Add(new CUBRIDParameter("?p1", DbType.String)); daUpdate.Parameters[0].SourceVersion = DataRowVersion.Current; daUpdate.Parameters[0].SourceColumn = "capital"; daUpdate.Parameters[0].SourceColumnNullMapping = false; //Parameter: code daUpdate.Parameters.Add(new CUBRIDParameter("?p2", DbType.String)); daUpdate.Parameters[1].SourceVersion = DataRowVersion.Original; daUpdate.Parameters[1].SourceColumn = "code"; daUpdate.Parameters[1].SourceColumnNullMapping = false; daUpdate.UpdatedRowSource = UpdateRowSource.None; //Assign the command to the UpdateCommand property of the DataAdapter. da.UpdateCommand = daUpdate; DataTable dt = new DataTable("nation"); da.Fill(dt); DataRow workRow = dt.Rows[0]; Debug.Assert(workRow["capital"].ToString() == "Kabul"); workRow.BeginEdit(); workRow["capital"] = "MyKabul"; workRow.EndEdit(); da.Update(dt); dt.AcceptChanges(); Debug.Assert(workRow["capital"].ToString() == "MyKabul"); Debug.Assert(workRow.RowState.ToString() != "New"); } Debug.Assert((string)GetSingleValue("select capital from nation where `code` = 'AFG'", conn) == "MyKabul"); //Revert changes ExecuteSQL("update nation set capital = 'Kabul' where capital = 'MyKabul'", conn); Debug.Assert((string)GetSingleValue("select capital from nation where `code` = 'AFG'", conn) == "Kabul"); } }
/// <summary> /// Test DataTable column properties /// </summary> private static void Test_DataTable_ColumnProperties() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); String sql = "select * from nation"; CUBRIDDataAdapter da = new CUBRIDDataAdapter(); da.SelectCommand = new CUBRIDCommand(sql, conn); DataTable dt = new DataTable("nation"); da.FillSchema(dt, SchemaType.Source);//To retrieve all the column properties you have to use the FillSchema() method Debug.Assert(dt.Columns[0].ColumnName == "code"); Debug.Assert(dt.Columns[0].AllowDBNull == false); Debug.Assert(dt.Columns[0].DefaultValue.ToString() == ""); Debug.Assert(dt.Columns[0].Unique == true); Debug.Assert(dt.Columns[0].DataType == typeof(System.String)); Debug.Assert(dt.Columns[0].Ordinal == 0); Debug.Assert(dt.Columns[0].Table == dt); } }
/// <summary> /// Test BLOB SELECT, using CUBRIDDataAdapter and DataSet /// </summary> private static void Test_Blob_SelectDataAdapter2() { 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 Blob1 = new CUBRIDBlob(conn); byte[] bytes1 = new byte[256]; bytes1[0] = 69; bytes1[1] = 98; bytes1[2] = 99; bytes1[255] = 122; Blob1.SetBytes(1, bytes1); CUBRIDParameter param = new CUBRIDParameter(); param.ParameterName = "?"; param.CUBRIDDataType = CUBRIDDataType.CCI_U_TYPE_BLOB; param.Value = Blob1; cmd1.Parameters.Add(param); cmd1.Parameters[0].DbType = DbType.Binary; cmd1.ExecuteNonQuery(); cmd1.Close(); string sql = "SELECT b from t"; DataSet ds = new DataSet("t"); CUBRIDDataAdapter da = new CUBRIDDataAdapter(); da.SelectCommand = new CUBRIDCommand(sql, conn); da.Fill(ds); DataTable dt = ds.Tables[0]; for (int j = 0; j < dt.Rows.Count; j++) { CUBRIDBlob bImage = (CUBRIDBlob)dt.Rows[j]["b"]; 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!"); } CleanupTestTableLOB(conn); } }
/// <summary> /// Initializes a new instance of the <see cref="CUBRIDCommandBuilder" /> class. /// </summary> /// <param name="dataAdapter"> The data dataAdapter. </param> public CUBRIDCommandBuilder(CUBRIDDataAdapter dataAdapter) : this() { DataAdapter = dataAdapter; }
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); }
/// <summary> /// Get the schemas of the indexes satisfying the index filter. /// </summary> /// <param name="filters"> The index filter, the value is {"table name pattern", "column name pattern", "index name pattern"}. <para/> /// If the pattern string is null, the default "%" is used </param> /// <returns>A <see cref="DataTable" /> that contains index schema information and contains columns {"INDEX_CATALOG", "INDEX_SCHEMA", <para/> /// "INDEX_NAME", "TABLE_NAME", "UNIQUE", "REVERSE", "PRIMARY", "FOREIGN_KEY", "DIRECTION"} </returns> public DataTable GetIndexes(string[] filters) { using (DataTable dt = new DataTable("Indexes")) { dt.Columns.Add("INDEX_CATALOG", typeof(string)); dt.Columns.Add("INDEX_SCHEMA", typeof(string)); dt.Columns.Add("INDEX_NAME", typeof(string)); dt.Columns.Add("TABLE_NAME", typeof(string)); dt.Columns.Add("UNIQUE", typeof(bool)); dt.Columns.Add("REVERSE", typeof(bool)); dt.Columns.Add("PRIMARY", typeof(bool)); dt.Columns.Add("FOREIGN_KEY", typeof(bool)); dt.Columns.Add("DIRECTION", typeof(string)); string tableName = "%"; string columnName = "%"; string indexName = "%"; if (filters != null) { if (filters.Length > 0 && filters[0] != null) { tableName = filters[0]; } if (filters.Length > 1 && filters[1] != null) { columnName = filters[1]; } if (filters.Length > 2 && filters[2] != null) { indexName = filters[2]; } } DataTable tables = GetTables(new[] { tableName }); string raw_sql = "select b.index_name, b.class_name, a.is_unique, a.is_reverse, a.is_primary_key, a.is_foreign_key, b.asc_desc"; raw_sql += " from db_index a,db_index_key b"; raw_sql += " where a.index_name=b.index_name"; raw_sql += " and a.class_name like '{0}'"; raw_sql += " and b.key_attr_name like '{1}'"; raw_sql += " and a.index_name like '{2}'"; raw_sql += " order by b.key_order"; foreach (DataRow table in tables.Rows) { string sql = String.Format(raw_sql, tableName, columnName, indexName); using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn)) { DataTable indexes = new DataTable(); da.Fill(indexes); foreach (DataRow index in indexes.Rows) { DataRow row = dt.NewRow(); row["INDEX_CATALOG"] = conn.Database; row["INDEX_SCHEMA"] = conn.Database; row["INDEX_NAME"] = index[0]; row["TABLE_NAME"] = index[1]; row["UNIQUE"] = index[2].ToString().Equals("YES"); row["REVERSE"] = index[3].ToString().Equals("YES"); row["PRIMARY"] = index[4].ToString().Equals("YES"); row["FOREIGN_KEY"] = index[5].ToString().Equals("YES"); row["DIRECTION"] = index[6].ToString(); dt.Rows.Add(row); } } } return(dt); } }
//http://msdn.microsoft.com/en-us/library/bbw6zyha%28v=vs.80%29.aspx /// <summary> /// Test DataTable implicit INSERT /// </summary> private static void Test_DataTable_InsertImplicit() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); String sql = "select * from nation order by `code` asc"; using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn)) { using (CUBRIDDataAdapter daCmd = new CUBRIDDataAdapter(sql, conn)) { CUBRIDCommandBuilder cmdBuilder = new CUBRIDCommandBuilder(daCmd); da.InsertCommand = cmdBuilder.GetInsertCommand(); } DataTable dt = new DataTable("nation"); da.Fill(dt); DataRow newRow = dt.NewRow(); newRow["code"] = "ZZZ"; newRow["name"] = "ABCDEF"; newRow["capital"] = "MyXYZ"; newRow["continent"] = "QWERTY"; dt.Rows.Add(newRow); da.Update(dt); Debug.Assert(dt.Rows.Count == 216); } Debug.Assert(GetTableRowsCount("nation", conn) == 216); //Revert changes ExecuteSQL("delete from nation where `code` = 'ZZZ'", conn); Debug.Assert(GetTableRowsCount("nation", conn) == 215); } }
/// <summary> /// Test DataTableReader GetSchemaTable() method /// </summary> private static void Test_GetSchemaTable() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); string sql = "select * from athlete order by `code`"; CUBRIDDataAdapter adapter = new CUBRIDDataAdapter(sql, conn); DataTable table = new DataTable(); //To retrieve the AlolowDBNull, IsUnique, IsKey, IsAutoIncrement and BaseTableName values from the Database Server //you must use the FillSchema() method. adapter.FillSchema(table, SchemaType.Source); using (DataTableReader reader = new DataTableReader(table)) { DataTable schemaTable = reader.GetSchemaTable(); DataRow row = schemaTable.Rows[0]; Debug.Assert(row["ColumnName"].ToString() == "code"); Debug.Assert(row["ColumnOrdinal"].ToString() == "0"); Debug.Assert(row["ColumnSize"].ToString() == "-1"); Debug.Assert(row["NumericPrecision"].ToString() == ""); Debug.Assert(row["NumericScale"].ToString() == ""); Debug.Assert(row["IsUnique"].ToString() == "True"); Debug.Assert(row["IsKey"].ToString() == "True"); Debug.Assert(row["BaseTableNamespace"].ToString() == ""); Debug.Assert(row["BaseColumnNamespace"].ToString() == ""); Debug.Assert(row["BaseCatalogName"].ToString() == ""); Debug.Assert(row["BaseColumnName"].ToString() == "code"); Debug.Assert(row["BaseSchemaName"].ToString() == ""); Debug.Assert(row["BaseTableName"].ToString() == "athlete"); Debug.Assert(row["DataType"].ToString() == "System.Int32"); Debug.Assert(row["AllowDBNull"].ToString() == "False"); Debug.Assert(row["ProviderType"].ToString() == ""); Debug.Assert(row["Expression"].ToString() == ""); Debug.Assert(row["AutoIncrementSeed"].ToString() == "0"); Debug.Assert(row["AutoincrementStep"].ToString() == "1"); Debug.Assert(row["IsAutoIncrement"].ToString() == "True"); Debug.Assert(row["IsRowVersion"].ToString() == "False"); Debug.Assert(row["IsLong"].ToString() == "False"); Debug.Assert(row["IsReadOnly"].ToString() == "False"); Debug.Assert(row["ColumnMapping"].ToString() == "1"); Debug.Assert(row["DefaultValue"].ToString() == ""); } } }
private static void Test_CommandBuilder() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); String sql = "select * from nation order by `code` asc"; CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn); CUBRIDCommandBuilder cmdBuilder = new CUBRIDCommandBuilder(da); string sql_format = string.Format("select {0},{1} from {2}", cmdBuilder.QuoteIdentifier("name"), cmdBuilder.QuoteIdentifier("age"), cmdBuilder.QuoteIdentifier("user")); Console.WriteLine(sql_format); } }
/// <summary> /// Test batch update, using DataAdapter /// </summary> private static void Test_DataAdapter_BatchUpdate() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); using (CUBRIDDataAdapter da = new CUBRIDDataAdapter()) { // Set the INSERT command and parameter. da.InsertCommand = new CUBRIDCommand("insert into nation values ('A', 'B', 'C', 'D')"); da.InsertCommand.UpdatedRowSource = UpdateRowSource.None; // Set the UPDATE command and parameters. da.UpdateCommand = new CUBRIDCommand("update nation set capital = 'X' where `code` = 'A'"); da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None; // Set the DELETE command and parameter. da.DeleteCommand = new CUBRIDCommand("delete from nation where `code` = 'A'"); da.DeleteCommand.UpdatedRowSource = UpdateRowSource.None; // Set the batch size. da.UpdateBatchSize = 3; // Execute the update. DataTable dt = new DataTable("nation"); da.Update(dt); } Debug.Assert(GetTableRowsCount("nation", conn) == 215); } }
private static void TestGetBytes() { 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)) { CUBRIDDataAdapter ad1 = new CUBRIDDataAdapter(cmd); CUBRIDDataAdapter ad2 = new CUBRIDDataAdapter("Select * from TestTable",connString); 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; 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"); } } sql = "drop table if exists TestTable;"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { cmd.ExecuteNonQuery(); } sql = "CREATE TABLE TestTable (clsid CLOB);"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { cmd.ExecuteNonQuery(); } sql = "INSERT INTO TestTable VALUES('1234567890');"; using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { 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, 8); try { len = reader.GetBytes(0, 0, buffer, 0, 36); } catch (Exception e) { } } } }
public void CUBRIDCommand_Clone_Test() { using (CUBRIDConnection conn = new CUBRIDConnection(DBHelper.connString)) { conn.Open(); DBHelper.ExecuteSQL("drop table if exists t", conn); DBHelper.ExecuteSQL("create table t (id int primary key, name varchar(50))", conn); DBHelper.ExecuteSQL("insert into t (id, name) values (2, 'Rachel Green')", conn); DBHelper.ExecuteSQL("insert into t (id, name) values (3, 'Rachel Green')", conn); DBHelper.ExecuteSQL("insert into t (id, name) values (5, 'Bill Gates')", conn); LogTestStep("Clone a CUBRIDCommand which has parameters"); CUBRIDCommand cmd = new CUBRIDCommand(null, conn); cmd.CommandText = "select * from t where id = ?myId and name = ?myName"; CUBRIDParameter idParam = new CUBRIDParameter("?myId", CUBRIDDataType.CCI_U_TYPE_INT, 8); CUBRIDParameter nameParam = new CUBRIDParameter("?myName", CUBRIDDataType.CCI_U_TYPE_STRING, 20); idParam.Value = 2; nameParam.Value = "Rachel Green"; cmd.Parameters.Add(idParam); cmd.Parameters.Add(nameParam); CUBRIDCommand cmdClone = cmd.Clone(); CUBRIDDataAdapter adapter = new CUBRIDDataAdapter(); adapter.SelectCommand = cmdClone; Log("Verify the cloned command"); DataTable dt = new DataTable(""); adapter.Fill(dt); Assert.AreEqual(1, dt.Rows.Count); Assert.AreEqual(2, (int)dt.Rows[0][0]); Assert.AreEqual("Rachel Green", dt.Rows[0][1].ToString()); adapter.FillSchema(dt, SchemaType.Source);//To retrieve all the column properties you have to use the FillSchema() method Assert.AreEqual(cmdClone.ColumnInfos[0].Name, "id"); Assert.AreEqual(cmdClone.ColumnInfos[0].IsPrimaryKey, true); Assert.AreEqual(cmdClone.ColumnInfos[0].IsForeignKey, false); Assert.AreEqual(cmdClone.ColumnInfos[0].IsNullable, false); Assert.AreEqual(cmdClone.ColumnInfos[0].RealName, "t"); Assert.AreEqual(cmdClone.ColumnInfos[0].Precision, 10); Assert.AreEqual(cmdClone.ColumnInfos[0].Scale, 0); Assert.AreEqual(cmdClone.ColumnInfos[0].IsAutoIncrement, false); Assert.AreEqual(cmdClone.ColumnInfos[0].IsReverseIndex, false); Assert.AreEqual(cmdClone.ColumnInfos[0].IsReverseUnique, false); Assert.AreEqual(cmdClone.ColumnInfos[0].IsShared, false); Assert.AreEqual(cmdClone.ColumnInfos[0].Type, CUBRIDDataType.CCI_U_TYPE_INT); Assert.AreEqual(cmdClone.ColumnInfos[0].Table, "t"); LogStepPass(); adapter.Dispose(); cmd.Close(); Log("delete test db"); //DBHelper.ExecuteSQL("drop table if exists t", conn); } LogTestResult(); }
/// <summary> /// Test DataTable implicit DELETE /// </summary> private static void Test_DataTable_DeleteImplicit() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); //Insert a new row ExecuteSQL("insert into nation values('ZZZZ', 'Z', 'Z', 'Z')", conn); Debug.Assert(GetTableRowsCount("nation", conn) == 216); String sql = "select * from nation order by `code` desc"; using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn)) { CUBRIDDataAdapter daCmd = new CUBRIDDataAdapter(sql, conn); CUBRIDCommandBuilder cmdBuilder = new CUBRIDCommandBuilder(daCmd); da.DeleteCommand = cmdBuilder.GetDeleteCommand(); DataTable dt = new DataTable("nation"); da.Fill(dt); Debug.Assert(dt.Rows[0]["capital"].ToString() == "Z"); dt.Rows[0].Delete(); da.Update(dt); Debug.Assert(dt.Rows.Count == 215); } Debug.Assert(GetTableRowsCount("nation", conn) == 215); } }