public void conn_setIsolationLevel()
{
string conn_string = "server=test-db-server;database=demodb;port=33000;user=dba;password="******"drop table if exists test_isolation";
cmd.ExecuteNonQuery();
// open another session
CUBRIDConnection conn2 = new CUBRIDConnection();
conn2.ConnectionString = conn_string;
conn2.Open();
CUBRIDCommand cmd2 = new CUBRIDCommand();
cmd2.Connection = conn2;
// set up the isolation level to
conn.SetAutoCommit(false);
conn.SetIsolationLevel(CUBRIDIsolationLevel.TRAN_REP_CLASS_COMMIT_INSTANCE);
cmd.CommandText = "create table test_isolation(a int)";
cmd.ExecuteNonQuery();
conn.Commit();
conn.Close();
}
public void conn_Database()
{
string conn_string = "server=test-db-server;database=demodb;port=33000;user=dba;password=";
CUBRIDConnection conn = new CUBRIDConnection();
conn.ConnectionString = conn_string;
Console.WriteLine(conn.Database);
conn.Open();
Console.WriteLine(conn.Database);
conn.Close();
}
public void CUBRIDCommand_Constructor_SQL_Test()
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.ConnectionString = DBHelper.connString;
string sql = "select * from nation order by code asc";
CUBRIDCommand cmd = new CUBRIDCommand(sql);
cmd.Connection = conn;
conn.Open();
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
reader.Read();
Assert.AreEqual(4, reader.FieldCount);
Assert.AreEqual("AFG", reader.GetString(0));
Assert.AreEqual("Afghanistan", reader.GetString(1));
Assert.AreEqual("Asia", reader.GetString(2));
Assert.AreEqual("Kabul", reader.GetString(3));
cmd.Close();
reader.Close();
conn.Close();
}
/// <summary>
///A connect with URL
///</summary>
public static void Test_ConnectionURL_And_Reset()
{
string strURL = "cci:cubrid:test-db-server:33000:demodb:public::?logSlowQueries=true" +
"&slowQueryThresholdMillis=1000&logTraceApi=true&logTraceNetwork=true" +
"&autoCommit=false&althosts=" +ip+
"&querytimeout=10000&logintimeout=5000";
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = strURL;
conn.Open();
string sqlTablesCount = "select count(*) from db_class where is_system_class='NO' and class_type='CLASS'";
int tablesCount = 0;
using (CUBRIDCommand cmd = new CUBRIDCommand(sqlTablesCount, conn))
{
tablesCount = (int)cmd.ExecuteScalar();
}
Debug.Assert(tablesCount == 12);
conn.Close();
try
{
string strURL2 = "cci:cubrid:test-db-server:33690:demodb:public::?logSlowQueries=true" +
"&slowQueryThresholdMillis=1000&logTraceApi=false&logTraceNetwork=false&autoCommit=false";
conn.ConnectionString = strURL2;
conn.Open();
}
catch(Exception ex)
{
Debug.Assert(ex.Message == "Cannot connect to CUBRID CAS");
}
}
}
public void DataReader_NextResult_Test()
{
string conn_string = "server=test-db-server;database=demodb;port=33000;user=dba;password="******"select * from nation;";
CUBRIDCommand cmd = new CUBRIDCommand(sql, conn);
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
reader.Read();
Console.WriteLine(reader.GetString(0));
if (reader.NextResult())
{
reader.Read();
Console.WriteLine(reader.GetString(0));
}
conn.Close();
}
public void CUBRIDDataAdapter_ConstructorWithSqlAndConnString_Test()
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.ConnectionString = DBHelper.connString;
conn.Open();
DBHelper.ExecuteSQL("drop table if exists t", conn);
DBHelper.ExecuteSQL("create table t (id int, name varchar(100))", conn);
DBHelper.ExecuteSQL("insert into t values (1, 'Nancy')", conn);
DBHelper.ExecuteSQL("insert into t values (2, 'Peter')", conn);
conn.Close();
string selectCommandText = "select * from t";
CUBRIDDataAdapter adapter = new CUBRIDDataAdapter(selectCommandText, DBHelper.connString);
DataTable dt = new DataTable("student");
adapter.Fill(dt);
//verify data
Assert.AreEqual(1, (int)dt.Rows[0]["id"]);
Assert.AreEqual("Nancy", dt.Rows[0]["name"].ToString());
Assert.AreEqual(2, (int)dt.Rows[1]["id"]);
Assert.AreEqual("Peter", dt.Rows[1]["name"].ToString());
//revert test db
conn.Open();
DBHelper.ExecuteSQL("drop table if exists t", conn);
}
public void ConnectionString_Test()
{
CUBRIDConnection conn = new CUBRIDConnection();
LogTestStep("Invalid connection string, server is not specified");
try
{
conn.ConnectionString = "database=demodb;port=33000;user=public;password="******"There should be an exception when the server is not specified");
LogStepFail();
}
catch (Exception ex)
{
Console.WriteLine("1. " + ex.Message);
//Assert.AreEqual("The database name can't be empty!", ex.Message);
LogStepPass();
}
LogTestStep("Invalid connection string, dbname is not specified");
try
{
conn.ConnectionString = "server=localhost;port=33000;user=public;password="******"There should be an exception when the dbname is not specified");
LogStepFail();
}
catch (CUBRIDException ex)
{
Console.WriteLine("2." + ex.Message);
//Assert.AreEqual("The database name can't be empty!", ex.Message);
LogStepPass();
}
LogTestStep("Invalid connection string, user is not specified");
try
{
conn.ConnectionString = "server=localhost;database=demodb;port=33000;password="******"There should be an exception when the user is not specified");
LogStepFail();
}
catch (Exception ex)
{
Console.WriteLine("3." + ex.Message);
//Assert.AreEqual("The database name can't be empty!", ex.Message);
LogStepPass();
}
LogTestStep("Invalid connection string, password is not specified");
try
{
conn.ConnectionString = "server=localhost;database=demodb;port=33000;user=public;";
conn.Open();
Log("There should be an exception when the password is not specified");
LogStepFail();
}
catch (Exception ex)
{
Console.WriteLine("4." + ex.Message);
//Assert.AreEqual("The database name can't be empty!", ex.Message);
LogStepPass();
}
LogTestStep("Valid connection string");
conn.ConnectionString = DBHelper.connString;
conn.Open();
conn.Close();
LogStepPass();
LogTestResult();
}
public void APIS_485()
{
LogTestStep("Test the lockTimeout is set successfully");
CUBRIDConnection conn = new CUBRIDConnection();
conn.ConnectionString = DBHelper.connString;
Assert.AreEqual(-1, conn.LockTimeout);
conn.Open();
int timeout = 20;
conn.SetLockTimeout(timeout);
Assert.AreEqual(timeout, conn.LockTimeout);
DBHelper.ExecuteSQL("drop table if exists t", conn);
DBHelper.ExecuteSQL("create table t(id integer)", conn);
DBHelper.ExecuteSQL("insert into t values (1)", conn);
conn.SetAutoCommit(false);
CUBRIDConnection conn2 = null;
double elapseTime = 0;
var stopwatch = new Stopwatch();
try
{
Thread thread2 = new Thread(delegate()
{
conn2 = new CUBRIDConnection();
conn2.ConnectionString = DBHelper.connString;
conn2.Open();
conn2.SetAutoCommit(false);
conn2.BeginTransaction();
DBHelper.ExecuteSQL("update t set id=2", conn2);
});
conn.BeginTransaction();
thread2.Start();
Thread.Sleep(5000);
stopwatch.Start();
DBHelper.ExecuteSQL("update t set id=3", conn);
thread2.Join();
}
catch (Exception ex)
{
stopwatch.Stop();
elapseTime = (double)stopwatch.ElapsedMilliseconds / 1000;
double diffTime = elapseTime - (double)(timeout / 1000);
Console.WriteLine("different time = " + stopwatch.ElapsedMilliseconds);
Console.WriteLine("different time = " + diffTime);
Log(ex.Message);
Assert.AreEqual(timeout, conn.LockTimeout);
if (diffTime >= 0 && diffTime < 10)
{
LogStepPass();
}
else
{
LogStepFail();
}
}
finally
{
LogTestResult();
//DBHelper.ExecuteSQL("drop table t", conn);
conn.Close();
conn2.Close();
}
}
public void Conn_Invalid_Connstring()
{
CUBRIDConnection conn = new CUBRIDConnection();
// ConnectionString is null
try
{
conn.ConnectionString = null;
}
catch (CUBRIDException e)
{
Console.WriteLine("1. " + e.Message);
Assert.AreEqual("Connection string is null!: Closed.", e.Message);
}
// database is not specified
conn = new CUBRIDConnection();
try
{
conn.ConnectionString = "server=localhost;port=33000;user=dba;password="******"2. " + e.Message);
Assert.AreEqual("The database name can't be empty!", e.Message);
}
// server is not specified
conn = new CUBRIDConnection();
try
{
conn.ConnectionString = "database=demodb;port=33000;user=dba;password="******"3. " + e.Message);
Assert.AreEqual("The Server can't be empty!", e.Message);
}
// user is not specified
conn = new CUBRIDConnection();
try
{
conn.ConnectionString = "server=localhost;database=demodb;port=33000;password="******"4. " + e.Message);
Assert.AreEqual("The User can't be empty!", e.Message);
}
conn.Close();
}
public IList<Column> GetTableDetails(Table table, string owner)
{
var columns = new List<Column>();
var conn = new CUBRIDConnection(connectionStr);
conn.Open();
try
{
using (conn)
{
var schema = new CUBRIDSchemaProvider(conn);
DataTable dt_fk = schema.GetForeignKeys(new[] { table.Name.ToLower() });
string sqlInfo = String.Format("select * from [{0}] limit 1", table.Name.ToLower());
var adapter = new CUBRIDDataAdapter(sqlInfo, conn);
var tableInfo = new DataTable();
adapter.FillSchema(tableInfo, SchemaType.Source);
using (var reader = new DataTableReader(tableInfo))
{
DataTable schemaTable = reader.GetSchemaTable();
for (var k = 0; k < schemaTable.Rows.Count; k++)
{
string columnName = schemaTable.Rows[k]["ColumnName"].ToString().ToLower();
var isUnique = (bool)schemaTable.Rows[k]["IsUnique"];
var isNullable = (bool)schemaTable.Rows[k]["AllowDBNull"];
var isPrimaryKey = (bool)schemaTable.Rows[k]["IsKey"];
var isIdentity = (bool)schemaTable.Rows[k]["IsAutoIncrement"];
var dataLength = (int)schemaTable.Rows[k]["ColumnSize"];
int dataPrecision = 0;
if (schemaTable.Rows[k]["NumericPrecision"].ToString() != String.Empty)
{
dataPrecision = (int)schemaTable.Rows[k]["NumericPrecision"];
}
int dataScale = 0;
if (schemaTable.Rows[k]["NumericScale"].ToString() != String.Empty)
{
dataScale = (int)schemaTable.Rows[k]["NumericScale"];
}
bool isForeignKey = false;
string fkTableName = "";
string constraintName = "";
for (var i_fk = 0; i_fk < dt_fk.Rows.Count; i_fk++)
{
if (dt_fk.Rows[i_fk]["FKCOLUMN_NAME"].ToString().ToLower() == columnName)
{
isForeignKey = true;
fkTableName = dt_fk.Rows[i_fk]["PKTABLE_NAME"].ToString();
constraintName = dt_fk.Rows[i_fk]["FK_NAME"].ToString();
break;
}
}
string dataType;
using (var cmd = new CUBRIDCommand(sqlInfo, conn))
{
using (var CUBRIDReader = (CUBRIDDataReader)cmd.ExecuteReader())
{
CUBRIDReader.Read();
dataType = CUBRIDReader.GetColumnTypeName(k);
}
}
var m = new DataTypeMapper();
columns.Add(new Column
{
Name = columnName,
DataType = dataType,
IsNullable = isNullable,
IsUnique = isUnique,
IsPrimaryKey = isPrimaryKey,
IsForeignKey = isForeignKey,
IsIdentity = isIdentity,
DataLength = dataLength,
DataPrecision = dataPrecision,
DataScale = dataScale,
ForeignKeyTableName = fkTableName,
ConstraintName = constraintName,
MappedDataType =
m.MapFromDBType(ServerType.CUBRID, dataType, null, null, null).ToString(),
});
}
}
}
table.Columns = columns;
table.Owner = owner;
table.PrimaryKey = DeterminePrimaryKeys(table);
table.HasManyRelationships = DetermineHasManyRelationships(table);
}
finally
{
conn.Close();
}
return columns;
}
public void Conn_ConnectionString_Exception()
{
CUBRIDConnection conn = new CUBRIDConnection();
// database is not specified
try
{
conn.ConnectionString = "server=test-db-server;port=33000;user=dba;password="******"1. " + ex.Message);
}
// ConnectionString is null
try
{
conn.ConnectionString = null;
}
catch (Exception ex)
{
Console.WriteLine("2. " + ex.Message);
}
conn.Close();
}
public List<string> GetSequences(string tablename, string column)
{
var sequences = new List<string>();
var conn = new CUBRIDConnection(connectionStr);
conn.Open();
try
{
using (conn)
{
CUBRIDCommand seqCommand = conn.CreateCommand();
string sqlCmd = String.Format(@"select [name] from db_serial where class_name='{0}' and att_name='{1}'",
tablename,
column);
seqCommand.CommandText = sqlCmd;
var seqReader = (CUBRIDDataReader)seqCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (seqReader.Read())
{
sequences.Add(seqReader.GetString(0));
}
}
}
finally
{
conn.Close();
}
return sequences;
}
public List<string> GetSequences(List<Table> tables)
{
var sequences = new List<string>();
var conn = new CUBRIDConnection(connectionStr);
conn.Open();
try
{
using (conn)
{
CUBRIDCommand seqCommand = conn.CreateCommand();
seqCommand.CommandText = "select [name], class_name from db_serial";
var seqReader = (CUBRIDDataReader)seqCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (seqReader.Read())
{
sequences.AddRange(from table in tables where table.Name.ToUpper() == seqReader.GetString(1).ToUpper() select seqReader.GetString(0));
}
}
}
finally
{
conn.Close();
}
return sequences;
}
public void Clone_Test()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
LogTestStep("Clone a connection");
conn.ConnectionString = DBHelper.connString;
Log("change a property value of the original connection");
conn.SetConnectionTimeout(45);
conn.Open();
Log("call the Clone method");
CUBRIDConnection clonedConn = conn.Clone();
Assert.IsTrue(clonedConn != null);
Log("The property values are different between the original connection and the cloned connection");
Assert.AreEqual(45, conn.ConnectionTimeout);
Assert.AreEqual(30, clonedConn.ConnectionTimeout);
try
{
clonedConn.Open();
Log("Close the original connection, check the cloned connection works well");
conn.Close();
Assert.IsTrue(DBHelper.GetTableRowsCount("db_class", clonedConn) > 0);
clonedConn.Close();
LogStepPass();
}
catch (Exception ex)
{
Log(ex.Message);
LogStepFail();
}
finally
{
LogTestResult();
conn.Close();
clonedConn.Close();
}
}
}
public void Close_Test()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
LogTestStep("Close DB before connected");
try
{
conn.ConnectionString = DBHelper.connString;
conn.Close();
}
catch (Exception ex)
{
Assert.AreEqual("The connection is not open!", ex.Message);
LogStepPass();
}
LogTestStep("Close a valid DB Connection, and check: (1)the connection is closed (2)the transaction which is not committed is rolled back");
Log("Connect to a DB");
conn.Open();
Log("Verify the Connection is OK by feching some data");
Assert.IsTrue(DBHelper.GetTableRowsCount("db_class", conn) > 0);
Log("Update the DB in a transaction, and close the connection before commit");
DBHelper.ExecuteSQL("drop table if exists t", conn);
conn.BeginTransaction();
DBHelper.ExecuteSQL("create table t(idx integer)", conn);
int tablesCount = DBHelper.GetTablesCount("t", conn);
Assert.AreEqual(1, tablesCount);
conn.Close();
Log("Verify the Connection is closed by feching some data");
try
{
int count = DBHelper.GetTableRowsCount("db_class", conn);
}
catch (Exception ex)
{
Assert.AreEqual("The connection is not open!", ex.Message);
}
Log("Verify the transaction is rolled back");
conn.Open();
tablesCount = DBHelper.GetTablesCount("t", conn);
Assert.AreEqual(0, tablesCount);
LogStepPass();
conn.Close();
LogTestStep("Close a valid DB Connection twice, check no exception is generated");
conn.Close();
LogStepPass();
LogTestResult();
}
}
public void SetLockTimeOut_WithParam_Test()
{
int timeout;
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = DBHelper.connString;
LogTestStep("connection is not open");
try
{
timeout = 2;
conn.SetLockTimeout(timeout);
}
catch (Exception ex)
{
Assert.AreEqual("The connection is not open!", ex.Message);
LogStepPass();
}
conn.Close();
LogTestStep("Valid timout value, and connection is open");
timeout = 35;
conn.Open();
conn.SetLockTimeout(35);
Assert.AreEqual(35, conn.LockTimeout);
LogStepPass();
LogTestStep("Valid timout value, change the lock timeout");
try
{
Assert.AreEqual(35, conn.LockTimeout);
conn.SetLockTimeout(40);
Assert.AreEqual(40, conn.LockTimeout);
LogStepPass();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
//Assert.AreEqual("Not allowed to change the 'LockTimeout' property while the connection state is!: Open.", ex.Message);
LogStepFail();
}
finally
{
LogTestResult();
}
}
}
public void DataReader_MultiQuery_Test2()
{
string conn_string = "server=test-db-server;database=demodb;port=33000;user=dba;password="******"select s_name from code where s_name='X'; select name from nation where name='Algeria';";
CUBRIDCommand cmd = new CUBRIDCommand(sql, conn);
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
};
while (reader.NextResult())
{
Console.WriteLine("=============================");
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
};
}
conn.Close();
}
public List<Table> GetTables(string owner)
{
var tables = new List<Table>();
var conn = new CUBRIDConnection(connectionStr);
conn.Open();
try
{
using (conn)
{
var schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetTables(new[] { "%" });
for (var i = 0; i < dt.Rows.Count; i++)
{
tables.Add(new Table { Name = dt.Rows[i][2].ToString() });
}
}
}
finally
{
conn.Close();
}
tables.Sort((x, y) => String.Compare(x.Name, y.Name, StringComparison.Ordinal));
return tables;
}
public void Conn_Property_ServerVersion()
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.ConnectionString = DBHelper.connString;
conn.Open();
string serverVersion = "";
LogTestStep("get ServerVersion when the connection is opened");
try
{
serverVersion = conn.ServerVersion;
LogStepPass();
}
catch (Exception ex)
{
//Assert.AreEqual("some", ex.Message);
LogStepFail();
}
//TODO
//ServerVersion was called while the returned Task was not completed and the connection was not opened after a call to OpenAsync.
conn.Close();
LogTestStep("get ServerVersion when the connection is closed");
try
{
serverVersion = conn.ServerVersion;
LogStepFail();
}
catch (Exception ex)
{
Assert.AreEqual("The connection is not open!", ex.Message);
LogStepPass();
}
LogTestResult();
}
public void i18n_issue()
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.ConnectionString = "server=test-db-server;database=demodb;port=33000;user=dba;password="******"utf-8");
cmd.CommandText = "drop table if exists 测试表;";
cmd.ExecuteNonQuery();
cmd.CommandText = "create table 测试表 (名称 varchar);";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into 测试表 value('小明');";
cmd.ExecuteNonQuery();
cmd.CommandText = "select 名称 from 测试表;";
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
};
conn.Close();
}
public void Conn_ConnectionString_Exception3()
{
CUBRIDConnection conn = new CUBRIDConnection();
// user is not specified
try
{
conn.ConnectionString = "server=test-db-server;database=demodb;port=33000;password="******"1. " + ex.Message);
}
conn.Close();
}
public void APIS_728()
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.ConnectionString = "server=test-db-server;database=demodb;port=33000;user=dba;password="******"TABLES");
}
catch (Exception e)
{
Assert.AreEqual("The connection is not open!", e.Message);
}
conn.Open();
DataTable dt_test = conn.GetSchema("TABLES");
Assert.IsNotNull(dt_test);
dt_test = conn.GetSchema("ReservedWords");
Assert.IsNotNull(dt_test);
dt_test = conn.GetSchema("Users");
Assert.IsNotNull(dt_test);
dt_test = conn.GetSchema("Databases");
Assert.IsNotNull(dt_test);
dt_test = conn.GetSchema("Views");
Assert.IsNotNull(dt_test);
dt_test = conn.GetSchema("Columns", new String[] { "game", "event_code" });
Assert.IsNotNull(dt_test);
dt_test = conn.GetSchema("Indexes", new String[] { "nation", "code" });
Assert.IsNotNull(dt_test);
dt_test = conn.GetSchema("Index_Columns", new String[] { "nation", "pk_nation_code" });
Assert.IsNotNull(dt_test);
dt_test = conn.GetSchema("FOREIGN_KEYS", new String[] { "game", "fk_game_athlete_code" });
Assert.IsNotNull(dt_test);
dt_test = conn.GetSchema("INVALID");
Assert.IsNull(dt_test);
conn.Close();
}
private IList<HasMany> DetermineHasManyRelationships(Table table)
{
var hasManyRelationships = new List<HasMany>();
var conn = new CUBRIDConnection(connectionStr);
conn.Open();
try
{
using (conn)
{
var schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetForeignKeys(new[] { "%" });
for (var i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["PKTABLE_NAME"].ToString() == table.Name)
{
var newHasManyItem = new HasMany
{
Reference = dt.Rows[i]["FKTABLE_NAME"].ToString(),
ConstraintName = dt.Rows[i]["FK_NAME"].ToString(),
ReferenceColumn = dt.Rows[i]["FKCOLUMN_NAME"].ToString()
};
hasManyRelationships.Add(newHasManyItem);
}
}
}
}
finally
{
conn.Close();
}
return hasManyRelationships;
}
public void Conn_Property_State()
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.ConnectionString = DBHelper.connString;
LogTestStep("State before open");
Assert.AreEqual(ConnectionState.Closed, conn.State);
LogStepPass();
LogTestStep("State after open");
conn.Open();
Assert.AreEqual(ConnectionState.Open, conn.State);
LogStepPass();
LogTestStep("State after close");
conn.Close();
Assert.AreEqual(ConnectionState.Closed, conn.State);
LogStepPass();
//TODO
//State of Broken, Connecting, Executing, Fetching
LogTestResult();
}
public void APIS_727()
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.ConnectionString = "server=test-db-server;database=demodb;port=33000;user=dba;password="******"The collectionName is specified as null.!", e.Message);
}
conn.Close();
}
public IList<string> GetOwners()
{
var owners = new List<string>();
var conn = new CUBRIDConnection(connectionStr);
conn.Open();
try
{
using (conn)
{
var schema = new CUBRIDSchemaProvider(conn);
DataTable dt = schema.GetUsers(new[] { "%" });
for (var i = 0; i < dt.Rows.Count; i++)
{
owners.Add(dt.Rows[i][0].ToString().ToLower());
}
}
}
finally
{
conn.Close();
}
return owners;
}
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();
}
/// <summary>
/// Test CUBRIDConnection GetSchema() method
/// </summary>
private static void Test_ConnectionGetSchema()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
DataTable dt = new DataTable();
dt = conn.GetSchema("USERS");
Debug.Assert(dt.Rows.Count == 2);
Debug.Assert(dt.Rows[0]["USERNAME"].ToString() == "DBA");
Debug.Assert(dt.Rows[1]["USERNAME"].ToString() == "PUBLIC");
dt = conn.GetSchema("DATABASES");
Debug.Assert(dt.Rows.Count == 1);
Debug.Assert(dt.Rows[0]["CATALOG_NAME"].ToString() == "demodb");
Debug.Assert(dt.Rows[0]["SCHEMA_NAME"].ToString() == "demodb");
dt = conn.GetSchema("PROCEDURES");
Debug.Assert(dt.Rows.Count == 0);
dt = conn.GetSchema("TABLES", new String[] { "nation" });
Debug.Assert(dt.Rows.Count == 1);
Debug.Assert(dt.Rows[0]["TABLE_CATALOG"].ToString() == "demodb");
Debug.Assert(dt.Rows[0]["TABLE_SCHEMA"].ToString() == "demodb");
Debug.Assert(dt.Rows[0]["TABLE_NAME"].ToString() == "nation");
dt = conn.GetSchema("VIEWS");
Debug.Assert(dt.Columns.Count == 3);
Debug.Assert(dt.Columns[0].ColumnName == "VIEW_CATALOG");
Debug.Assert(dt.Columns[1].ColumnName == "VIEW_SCHEMA");
Debug.Assert(dt.Columns[2].ColumnName == "VIEW_NAME");
Debug.Assert(dt.Rows.Count == 0);
dt = conn.GetSchema("COLUMNS", new String[] { "game", "event_code" });
Debug.Assert(dt.Rows.Count == 1);
Debug.Assert(dt.Rows[0]["TABLE_CATALOG"].ToString() == "demodb");
Debug.Assert(dt.Rows[0]["TABLE_SCHEMA"].ToString() == "demodb");
Debug.Assert(dt.Rows[0]["TABLE_NAME"].ToString() == "game");
Debug.Assert(dt.Rows[0]["COLUMN_NAME"].ToString() == "event_code");
Debug.Assert((uint)dt.Rows[0]["ORDINAL_POSITION"] == (uint)1);
Debug.Assert(dt.Rows[0]["COLUMN_DEFAULT"].ToString() == "");
Debug.Assert((bool)dt.Rows[0]["IS_NULLABLE"] == false);
Debug.Assert(dt.Rows[0]["DATA_TYPE"].ToString() == "INTEGER");
Debug.Assert((uint)dt.Rows[0]["NUMERIC_PRECISION"] == (uint)10);
Debug.Assert((uint)dt.Rows[0]["NUMERIC_SCALE"] == (uint)0);
Debug.Assert((byte)dt.Rows[0]["CHARACTER_SET"] == (byte)0);
dt = conn.GetSchema("INDEXES", new String[] { "nation", "code" });
Debug.Assert(dt.Rows.Count == 1);
Debug.Assert(dt.Rows[0]["INDEX_CATALOG"].ToString() == "demodb");
Debug.Assert(dt.Rows[0]["INDEX_SCHEMA"].ToString() == "demodb");
Debug.Assert(dt.Rows[0]["INDEX_NAME"].ToString() == "pk_nation_code");
Debug.Assert(dt.Rows[0]["TABLE_NAME"].ToString() == "nation");
Debug.Assert((bool)dt.Rows[0]["UNIQUE"] == true);
Debug.Assert((bool)dt.Rows[0]["REVERSE"] == false);
Debug.Assert((bool)dt.Rows[0]["PRIMARY"] == true);
Debug.Assert((bool)dt.Rows[0]["FOREIGN_KEY"] == false);
Debug.Assert(dt.Rows[0]["DIRECTION"].ToString() == "ASC");
dt = conn.GetSchema("INDEX_COLUMNS", new String[] { "nation", "pk_nation_code" });
Debug.Assert(dt.Rows.Count == 1);
Debug.Assert(dt.Rows[0]["INDEX_CATALOG"].ToString() == "demodb");
Debug.Assert(dt.Rows[0]["INDEX_SCHEMA"].ToString() == "demodb");
Debug.Assert(dt.Rows[0]["INDEX_NAME"].ToString() == "pk_nation_code");
Debug.Assert(dt.Rows[0]["TABLE_NAME"].ToString() == "nation");
Debug.Assert(dt.Rows[0]["COLUMN_NAME"].ToString() == "code");
Debug.Assert((int)dt.Rows[0]["ORDINAL_POSITION"] == 0);
Debug.Assert(dt.Rows[0]["DIRECTION"].ToString() == "ASC");
dt = conn.GetSchema("FOREIGN_KEYS", new String[] { "game", "fk_game_athlete_code" });
Debug.Assert(dt.Rows.Count == 2);
Debug.Assert(dt.Rows[0]["PKTABLE_NAME"].ToString() == "event");
Debug.Assert(dt.Rows[0]["PKCOLUMN_NAME"].ToString() == "code");
Debug.Assert(dt.Rows[0]["FKTABLE_NAME"].ToString() == "game");
Debug.Assert(dt.Rows[0]["FKCOLUMN_NAME"].ToString() == "event_code");
Debug.Assert((short)dt.Rows[0]["KEY_SEQ"] == (short)1);
Debug.Assert((short)dt.Rows[0]["UPDATE_ACTION"] == (short)1);
Debug.Assert((short)dt.Rows[0]["DELETE_ACTION"] == (short)1);
Debug.Assert(dt.Rows[0]["FK_NAME"].ToString() == "fk_game_event_code");
Debug.Assert(dt.Rows[0]["PK_NAME"].ToString() == "pk_event_code");
conn.Close();
}
}
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 CUBRIDConnectionStringBuilder Constructor
///</summary>
public static void Test_CUBRIDConnectionStringBuilderConstructor()
{
string connString = "server=test-db-server;database=demodb;port=33690;user=public;password="******"No connection could be made because the target machine actively refused it 127.0.0.1:33690");
}
}
}
