//http://msdn.microsoft.com/en-us/library/tf579hcz%28v=vs.80%29.aspx
/// <summary>
/// Test CUBRIDCommandBuilder class, and methods used to automatically get SQL commands
/// </summary>
private static void Test_CommandBuilder_GetCommands()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
String sql = "select * from nation order by `code` asc";
CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn);
CUBRIDCommandBuilder cmdBuilder = new CUBRIDCommandBuilder(da);
da.UpdateCommand = cmdBuilder.GetUpdateCommand();
Debug.Assert(da.UpdateCommand.CommandText == "UPDATE `nation` SET `code` = ?, `name` = ?, `continent` = ?, `capital` = ? WHERE ((`code` = ?) AND (`name` = ?) AND ((? = 1 AND `continent` IS NULL) OR (`continent` = ?)) AND ((? = 1 AND `capital` IS NULL) OR (`capital` = ?)))");
da.InsertCommand = cmdBuilder.GetInsertCommand();
Debug.Assert(da.InsertCommand.CommandText == "INSERT INTO `nation` (`code`, `name`, `continent`, `capital`) VALUES (?, ?, ?, ?)");
da.DeleteCommand = cmdBuilder.GetDeleteCommand();
Debug.Assert(da.DeleteCommand.CommandText == "DELETE FROM `nation` WHERE ((`code` = ?) AND (`name` = ?) AND ((? = 1 AND `continent` IS NULL) OR (`continent` = ?)) AND ((? = 1 AND `capital` IS NULL) OR (`capital` = ?)))");
}
}
//http://msdn.microsoft.com/en-us/library/bbw6zyha%28v=vs.80%29.aspx
/// <summary>
/// Test DataTable implicit UPDATE
/// </summary>
private static void Test_DataTable_UpdateImplicit()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
String sql = "select * from nation order by `code` asc";
using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn))
{
using (CUBRIDDataAdapter daCmd = new CUBRIDDataAdapter(sql, conn))
{
CUBRIDCommandBuilder cmdBuilder = new CUBRIDCommandBuilder(daCmd);
da.UpdateCommand = cmdBuilder.GetUpdateCommand();
}
DataTable dt = new DataTable("nation");
da.Fill(dt);
//Update data
DataRow workRow = dt.Rows[0];
Debug.Assert(workRow["code"].ToString() == "AFG");
Debug.Assert(workRow["capital"].ToString() == "Kabul");
workRow.BeginEdit();
workRow["capital"] = "MyKabul";
workRow.EndEdit();
da.Update(dt);
Debug.Assert(workRow["capital"].ToString() == "MyKabul");
Debug.Assert(workRow.RowState.ToString() != "New");
}
Debug.Assert((string)GetSingleValue("select capital from nation where `code` = 'AFG'", conn) == "MyKabul");
//Revert changes
ExecuteSQL("update nation set capital = 'Kabul' where capital = 'MyKabul'", conn);
Debug.Assert((string)GetSingleValue("select capital from nation where `code` = 'AFG'", conn) == "Kabul");
}
}
private static void Test_CommandBuilder()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
String sql = "select * from nation order by `code` asc";
CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn);
CUBRIDCommandBuilder cmdBuilder = new CUBRIDCommandBuilder(da);
string sql_format = string.Format("select {0},{1} from {2}",
cmdBuilder.QuoteIdentifier("name"),
cmdBuilder.QuoteIdentifier("age"),
cmdBuilder.QuoteIdentifier("user"));
Console.WriteLine(sql_format);
}
}
/// <summary>
/// Test 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);
}
}
//http://msdn.microsoft.com/en-us/library/bbw6zyha%28v=vs.80%29.aspx
/// <summary>
/// Test DataTable implicit INSERT
/// </summary>
private static void Test_DataTable_InsertImplicit()
{
using (CUBRIDConnection conn = new CUBRIDConnection())
{
conn.ConnectionString = TestCases.connString;
conn.Open();
String sql = "select * from nation order by `code` asc";
using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn))
{
using (CUBRIDDataAdapter daCmd = new CUBRIDDataAdapter(sql, conn))
{
CUBRIDCommandBuilder cmdBuilder = new CUBRIDCommandBuilder(daCmd);
da.InsertCommand = cmdBuilder.GetInsertCommand();
}
DataTable dt = new DataTable("nation");
da.Fill(dt);
DataRow newRow = dt.NewRow();
newRow["code"] = "ZZZ";
newRow["name"] = "ABCDEF";
newRow["capital"] = "MyXYZ";
newRow["continent"] = "QWERTY";
dt.Rows.Add(newRow);
da.Update(dt);
Debug.Assert(dt.Rows.Count == 216);
}
Debug.Assert(GetTableRowsCount("nation", conn) == 216);
//Revert changes
ExecuteSQL("delete from nation where `code` = 'ZZZ'", conn);
Debug.Assert(GetTableRowsCount("nation", conn) == 215);
}
}
