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