/// <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); } }
/// <summary> /// Test batch update, using DataAdapter /// </summary> private static void Test_DataAdapter_BatchUpdate() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); using (CUBRIDDataAdapter da = new CUBRIDDataAdapter()) { // Set the INSERT command and parameter. da.InsertCommand = new CUBRIDCommand("insert into nation values ('A', 'B', 'C', 'D')"); da.InsertCommand.UpdatedRowSource = UpdateRowSource.None; // Set the UPDATE command and parameters. da.UpdateCommand = new CUBRIDCommand("update nation set capital = 'X' where `code` = 'A'"); da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None; // Set the DELETE command and parameter. da.DeleteCommand = new CUBRIDCommand("delete from nation where `code` = 'A'"); da.DeleteCommand.UpdatedRowSource = UpdateRowSource.None; // Set the batch size. da.UpdateBatchSize = 3; // Execute the update. DataTable dt = new DataTable("nation"); da.Update(dt); } Debug.Assert(GetTableRowsCount("nation", conn) == 215); } }
//http://msdn.microsoft.com/en-us/library/bbw6zyha%28v=vs.80%29.aspx /// <summary> /// Test DataTable explicit UPDATE /// </summary> private static void Test_DataTable_UpdateExplicit() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); String sql = "select * from nation order by `code` asc LIMIT 10"; using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn)) { //Initialize the command object that will be used as the UpdateCommand for the DataAdapter. CUBRIDCommand daUpdate = new CUBRIDCommand("update nation set capital = ? where code = ?", conn); //Parameter: capital daUpdate.Parameters.Add(new CUBRIDParameter("?p1", DbType.String)); daUpdate.Parameters[0].SourceVersion = DataRowVersion.Current; daUpdate.Parameters[0].SourceColumn = "capital"; daUpdate.Parameters[0].SourceColumnNullMapping = false; //Parameter: code daUpdate.Parameters.Add(new CUBRIDParameter("?p2", DbType.String)); daUpdate.Parameters[1].SourceVersion = DataRowVersion.Original; daUpdate.Parameters[1].SourceColumn = "code"; daUpdate.Parameters[1].SourceColumnNullMapping = false; daUpdate.UpdatedRowSource = UpdateRowSource.None; //Assign the command to the UpdateCommand property of the DataAdapter. da.UpdateCommand = daUpdate; DataTable dt = new DataTable("nation"); da.Fill(dt); DataRow workRow = dt.Rows[0]; Debug.Assert(workRow["capital"].ToString() == "Kabul"); workRow.BeginEdit(); workRow["capital"] = "MyKabul"; workRow.EndEdit(); da.Update(dt); dt.AcceptChanges(); 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"); } }
//http://msdn.microsoft.com/en-us/library/bbw6zyha%28v=vs.80%29.aspx /// <summary> /// Test DataTable explicit DELETE /// </summary> private static void Test_DataTable_DeleteExplicit() { 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)) { //Initialize the command object that will be used as the DeleteCommand for the DataAdapter. CUBRIDCommand daDelete = new CUBRIDCommand("delete from nation where code = ?", conn); //Parameter: code daDelete.Parameters.Add(new CUBRIDParameter("?p1", DbType.String)); daDelete.Parameters["?p1"].SourceVersion = DataRowVersion.Original; daDelete.Parameters["?p1"].SourceColumn = "code"; daDelete.Parameters["?p1"].SourceColumnNullMapping = false; daDelete.UpdatedRowSource = UpdateRowSource.OutputParameters; //Assign the command to the DeleteCommand property of the DataAdapter. da.DeleteCommand = daDelete; 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 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"); } }
//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); } }
/// <summary> /// Test DataTable explicit INSERT /// </summary> private static void Test_DataTable_InsertExplicit() { using (CUBRIDConnection conn = new CUBRIDConnection()) { conn.ConnectionString = TestCases.connString; conn.Open(); String sql = "select * from nation order by `code` DESC LIMIT 10"; using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn)) { //Initialize the command object that will be used as the UpdateCommand for the DataAdapter. CUBRIDCommand daInsert = new CUBRIDCommand("insert into nation values(?,?,?,?)", conn); daInsert.CommandType = CommandType.Text; //Parameter: code daInsert.Parameters.Add(new CUBRIDParameter("?p1", DbType.String)); daInsert.Parameters["?p1"].SourceVersion = DataRowVersion.Current; daInsert.Parameters["?p1"].SourceColumn = "code"; daInsert.Parameters["?p1"].SourceColumnNullMapping = false; //Parameter: name daInsert.Parameters.Add(new CUBRIDParameter("?p2", DbType.String)); daInsert.Parameters["?p2"].SourceVersion = DataRowVersion.Original; daInsert.Parameters["?p2"].SourceColumn = "name"; daInsert.Parameters["?p2"].SourceColumnNullMapping = false; //Parameter: continent daInsert.Parameters.Add(new CUBRIDParameter("?p3", DbType.String)); daInsert.Parameters["?p3"].SourceVersion = DataRowVersion.Current; daInsert.Parameters["?p3"].SourceColumn = "continent"; daInsert.Parameters["?p3"].SourceColumnNullMapping = false; //Parameter: capital daInsert.Parameters.Add(new CUBRIDParameter("?p4", DbType.String)); daInsert.Parameters["?p4"].SourceVersion = DataRowVersion.Original; daInsert.Parameters["?p4"].SourceColumn = "capital"; daInsert.Parameters["?p4"].SourceColumnNullMapping = false; daInsert.UpdatedRowSource = UpdateRowSource.None; //Assign the command to the InsertCommand property of the DataAdapter. da.InsertCommand = daInsert; 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.InsertAt(newRow, 0); da.Update(dt); dt.AcceptChanges(); Debug.Assert(dt.Rows[0]["capital"].ToString() == "MyXYZ"); Debug.Assert(newRow.RowState.ToString() != "New"); } Debug.Assert(GetTableRowsCount("nation", conn) == 216); //Revert changes ExecuteSQL("delete from nation where `code` = 'ZZZ'", conn); Debug.Assert(GetTableRowsCount("nation", conn) == 215); } }
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(); dt.Rows[0].Delete(); //TODO: Verifcation: might need to 'SqlCommandBuilder'? dt.AcceptChanges(); 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.DeleteCommand.CommandText); // -- //InsertCommand //revert test db DBHelper.ExecuteSQL("drop table if exists t", conn); conn.Close(); }