public void DataReader_Basic_Test()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = DBHelper.connString;
conn.Open();
String sql = "select * from nation order by `code` asc";
LogTestStep("retrieve just one row");
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader())
{
reader.Read(); //retrieve just one row
Assert.AreEqual(4, reader.FieldCount);
Assert.AreEqual("AFG", reader.GetString(0));
Assert.AreEqual("Afghanistan", reader.GetString(1));
Assert.AreEqual("Asia", reader.GetString(2));
Assert.AreEqual("Kabul", reader.GetString(3));
LogStepPass();
}
}
}
LogTestResult();
}
public Task <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(Task.FromResult(tables));
}
/// <summary>
/// Initializes a new instance of the <see cref="CUBRIDCommand" /> class.
/// </summary>
/// <param name="sql"> The SQL statement. </param>
/// <param name="conn"> The connection. </param>
public CUBRIDCommand(string sql, CUBRIDConnection conn)
: this(sql)
{
this.conn = conn;
paramCollection.SetParametersEncoding(conn.GetEncoding());
paramCollection.sql = sql;
}
public static int cci_prepare(CUBRIDConnection conn_handle, string sql_stmt, ref T_CCI_ERROR err_buf)
{
int ret = cci_set_holdability(conn_handle.Conection, 0);
if (ret < 0)
{
return(ret);
}
byte[] sql = conn_handle.GetEncoding().GetBytes(sql_stmt);
string trimed_stmt = new string(sql_stmt.ToCharArray()
.Where(c => !Char.IsWhiteSpace(c))
.ToArray());
if ((trimed_stmt.Substring(0, 6).ToLower() == "?=call") ||
(trimed_stmt.Substring(0, 4).ToLower() == "call"))
{
return(cci_prepare(
conn_handle.Conection, sql,
(char)T_CCI_PREPARE_FLAG.CCI_PREPARE_CALL,
ref err_buf));
}
else
{
return(cci_prepare(
conn_handle.Conection, sql,
(char)(T_CCI_PREPARE_FLAG.CCI_PREPARE_INCLUDE_OID |
T_CCI_PREPARE_FLAG.CCI_PREPARE_UPDATABLE),
ref err_buf));
}
}
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 static void ExecuteSQL(string sql)
//{
// using (CUBRIDCommand cmd = new CUBRIDCommand(sql, connectionStr))
// {
// cmd.ExecuteNonQuery();
// }
//}
public static void ExecuteSQL(string sql, CUBRIDConnection conn)
{
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
cmd.ExecuteNonQuery();
}
}
public void migrate()
{
mt.migrateTables(os, ms, cs);
os = null;
ms = null;
cs = null;
}
public void CUBRIDCommand_ExecuteReader_Test()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = DBHelper.connString;
conn.Open();
string sql = "select * from nation order by code asc";
using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
{
using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader())
{
//verify the first two results
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));
reader.Read();
Assert.AreEqual(4, reader.FieldCount);
Assert.AreEqual("AHO", reader.GetString(0));
Assert.AreEqual("Netherlands Antilles", reader.GetString(1));
Assert.AreEqual("Americas", reader.GetString(2));
Assert.AreEqual("Willemstad", reader.GetString(3));
}
}
}
}
public void CUBRIDCommand_ExecuteScalar_Test()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = DBHelper.connString;
conn.Open();
string sql = "select * from nation order by code asc";
CUBRIDCommand cmd = new CUBRIDCommand(sql, conn);
LogTestStep("Test ExecuteScalar - return a value");
object obj = cmd.ExecuteScalar();
Assert.AreEqual("AFG", obj.ToString());
LogStepPass();
LogTestStep("Test ExecuteScalar - return null");
cmd.CommandText = "drop table if exists test_scalar;";
cmd.ExecuteNonQuery();
cmd.CommandText = "create table test_scalar(a int);";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into test_scalar values(NULL);";
cmd.ExecuteNonQuery();
cmd.CommandText = "select * from test_scalar";
obj = cmd.ExecuteScalar();
Assert.AreEqual(DBNull.Value, obj);
LogStepPass();
cmd.Close();
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 CUBRIDCommand_Constructor_SQLAndConnAndTran_Test()
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.ConnectionString = DBHelper.connString;
conn.Open();
conn.SetAutoCommit(false);
string sql = "drop table if exists t";
CUBRIDTransaction transaction = new CUBRIDTransaction(conn, CUBRIDIsolationLevel.TRAN_DEFAULT_ISOLATION);
CUBRIDCommand cmd = new CUBRIDCommand(sql, conn, transaction);
conn.BeginTransaction();
cmd.ExecuteNonQuery();
cmd.CommandText = "create table t (id int, name varchar(50))";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into t values(1, 'Nancy')";
cmd.ExecuteNonQuery();
conn.Commit();
cmd.CommandText = "select * from t";
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
reader.Read();
Assert.AreEqual(2, reader.FieldCount);
Assert.AreEqual("1", reader.GetString(0));
Assert.AreEqual("Nancy", reader.GetString(1));
cmd.Close();
reader.Close();
conn.Close();
}
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_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 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();
}
private static void Test_DbVersion()
{
try
{
CUBRIDConnection conn = new CUBRIDConnection();
string version = conn.DbVersion;
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
Debug.Assert(e.Message == "The connection is not open!");
}
try
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.ConnectionString = connString;
conn.Open();
string version = conn.DbVersion;
conn.DbVersion = version;
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
}
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();
}
private static void Test_Encoding()
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.SetEncoding("cp1252");
Debug.Assert(Encoding.GetEncoding("Windows-1252") == conn.GetEncoding());
conn.SetEncoding("iso-8859-1");
Debug.Assert(Encoding.GetEncoding("iso-8859-1") == conn.GetEncoding());
conn.SetEncoding("euc-kr");
Debug.Assert(Encoding.GetEncoding("euc-kr") == conn.GetEncoding());
conn.SetEncoding("euc-jp");
Debug.Assert(Encoding.GetEncoding("euc-jp") == conn.GetEncoding());
conn.SetEncoding("gb2312");
Debug.Assert(Encoding.GetEncoding("gb2312") == conn.GetEncoding());
conn.SetEncoding("gbk");
Debug.Assert(Encoding.GetEncoding("gbk") == conn.GetEncoding());
conn.SetEncoding("xxxx");
Debug.Assert(Encoding.Default == conn.GetEncoding());
}
public void CUBRIDCommand_Close_Test()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = DBHelper.connString;
string sql = "select * from nation order by code asc";
CUBRIDCommand cmd = new CUBRIDCommand(sql, conn);
conn.Open();
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
reader.Read();
Assert.AreEqual(4, reader.FieldCount);
Assert.AreEqual("AFG", reader.GetString(0));
Assert.AreEqual("Afghanistan", reader.GetString(1));
Assert.AreEqual("Asia", reader.GetString(2));
Assert.AreEqual("Kabul", reader.GetString(3));
cmd.Close();
try
{
cmd.CommandText = "drop table if exists t";
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Assert.AreEqual("Some message about the cmd cannot be used", ex.Message);
}
}
}
public void CUBRIDSchemaProviderConstructorTest()
{
CUBRIDConnection connection = new CUBRIDConnection();; // TODO: Initialize to an appropriate value
connection.ConnectionString = DBHelper.connString;
connection.Open();
CUBRIDSchemaProvider target = new CUBRIDSchemaProvider(connection);
CUBRIDCommand cmd = new CUBRIDCommand();
cmd.Connection = connection;
cmd.CommandText = "select * from nation order by code asc";
CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader();
reader.Read();
Assert.AreEqual(4, reader.FieldCount);
cmd.Close();
reader.Close();
connection.Close();
}
private static List <object> GetTableValues(string tableName, int indexPosition, string[] columnNames)
{
List <object> columnValues = new List <object>();
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
using (CUBRIDCommand cmd = new CUBRIDCommand("select * from " + tableName, conn))
{
DbDataReader reader = cmd.ExecuteReader();
for (int i = 0; i < indexPosition; i++)
{
reader.Read();
}
for (int i = 0; i < columnNames.Length; i++)
{
columnValues.Add(reader[columnNames[i]]);
}
}
}
return(columnValues);
}
public static void start(String ip, String port, String databasename, String username, String password, int type)
{
DBType databasetype = DBType.CubridDB;
dbfrom = new DBStringBuilder(databasetype, ip, port, databasename, username, password);
//MessageBox.Show(type+"");
try
{
if (type == 1)
{
csql = CubridSQLConnection.getConnection();
csql.connectionString = dbfrom;
MessageBox.Show(csql.OpenConnection() ? "erfolgreich" : "gescheitert");
cs = csql.getConn();
}
else if (type == 2)
{
ms = MSSQLConnection.getConnection();
ms.connectionString = dbfrom;
MessageBox.Show(ms.OpenConnection() ? "erfolgreich" : "gescheitert");
}
else
{
// os.CloseConnection();
os = OracleSQLConnection.getConnection();
os.connectionString = dbfrom;
MessageBox.Show(os.OpenConnection() ? "erfolgreich" : "gescheitert");
}
Console.Out.WriteLine(dbfrom.ToString());
}
catch (Exception e)
{
if (e.Message.Contains("Failed to connect to"))
{
MessageBox.Show("Invalid Databasename");
}
else if (e.Message.Contains("Cannot connect to"))
{
MessageBox.Show("Invalid port or address");
}
else if (e.Message.Contains("password"))
{
MessageBox.Show("Invalid password");
}
else if (e.Message.Contains("User"))
{
MessageBox.Show("Invalid username");
}
else
{
MessageBox.Show("An unexpected error occured");
// MessageBox.Show(e.ToString());
}
close();
}
}
private static void Test_GetDatabaseProductVersion()
{
try
{
CUBRIDConnection conn = new CUBRIDConnection();
string source = conn.DataSource;
}
catch (Exception e)
{
Debug.Assert(e.Message == "The connection is not open!");
}
try
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.GetDatabaseProductVersion();
}
catch (Exception e)
{
Debug.Assert(e.Message == "The connection is not open!");
}
try
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.ConnectionString = connString;
conn.Open();
conn.GetDatabaseProductVersion();
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
}
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);
}
private static void Test_GetAutoCommit()
{
try
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.GetAutoCommit();
}
catch (Exception e)
{
Debug.Assert(e.Message == "The connection is not open!");
}
try
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.ConnectionString = connString;
conn.Open();
conn.GetAutoCommit();
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
}
public Task <IList <string> > GetOwners()
{
IList <string> 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(Task.FromResult(owners));
}
private static void Test_MaxStringLength()
{
try
{
CUBRIDConnection conn = new CUBRIDConnection();
int max = conn.MaxStringLength;
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
Debug.Assert(e.Message == "The connection is not open!");
}
try
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.ConnectionString = connString;
conn.Open();
int max = conn.MaxStringLength;
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
}
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);
}
private static void Test_Isolation()
{
try
{
CUBRIDConnection conn = new CUBRIDConnection();
CUBRIDIsolationLevel level = conn.IsolationLevel;
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
Debug.Assert(e.Message == "The connection is not open!");
}
try
{
CUBRIDConnection conn = new CUBRIDConnection();
conn.ConnectionString = connString;
conn.Open();
CUBRIDIsolationLevel level = conn.IsolationLevel;
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
}
/// <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);
}
}
public void DataReader_GetDate_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 (timeTest time,datetimeTest datetime)", conn);
DBHelper.ExecuteSQL("insert into t values('12:07:39', '2013-03-18 12:07:12')", conn);
using (CUBRIDCommand cmd = new CUBRIDCommand("select * from t", conn))
{
using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader())
{
reader.Read();
Assert.AreEqual(2, reader.FieldCount);
Assert.AreEqual("12:07:39", reader.GetTime(0));
Assert.AreEqual("2013-03-18", reader.GetDate(1));
DateTime dt = reader.GetDateTime(1);
Assert.AreEqual("2013-03-18 12:07:12", dt.ToString("yyyy-MM-dd HH:mm:ss"));
}
}
//revert test db
DBHelper.ExecuteSQL("drop table if exists t", conn);
}
}