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