/// <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>
/// 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");
}
}
/// <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);
}
}
public Task <IList <Column> > GetTableDetails(Table table, string owner)
{
IList <Column> 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),
});
}
}
}
table.Columns = columns;
table.Owner = owner;
table.PrimaryKey = DeterminePrimaryKeys(table);
table.HasManyRelationships = DetermineHasManyRelationships(table);
}
finally
{
conn.Close();
}
return(Task.FromResult(columns));
}
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();
}
