SqlCommandBuilder cb = new SqlCommandBuilder (da);
SqlCommand cmd = cb.GetInsertCommand ();
- Assert.AreEqual ("INSERT INTO employee (id, fname) VALUES (?, ?)",
+#if NET_2_0
+ Assert.AreEqual ("INSERT INTO [employee] ([id], [fname]) VALUES (@p1, @p2)",
cmd.CommandText, "#2");
+#else
+ Assert.AreEqual ("INSERT INTO employee (id, fname) VALUES (@p1, @p2)",
+ cmd.CommandText, "#2");
+#endif
} finally {
ConnectionManager.Singleton.CloseConnection ();
}
SqlCommandBuilder cb = new SqlCommandBuilder (da);
SqlCommand cmd = cb.GetInsertCommand ();
- Assert.AreEqual ("INSERT INTO employee (id, fname) VALUES (?, ?)",
+#if NET_2_0
+ Assert.AreEqual ("INSERT INTO [employee] ([id], [fname]) VALUES (@p1, @p2)",
+ cmd.CommandText, "#2");
+#else
+ Assert.AreEqual ("INSERT INTO employee (id, fname) VALUES (@p1, @p2)",
cmd.CommandText, "#2");
+#endif
} finally {
ConnectionManager.Singleton.CloseConnection ();
}
public void GetUpdateCommandTest ()
{
IDbConnection conn = ConnectionManager.Singleton.Connection;
- using (conn) {
- string selectQuery = "select id, fname, id+1 as next_id from employee where id = 1";
+ try {
+ string selectQuery = "select id, fname, lname, id+1 as next_id from employee where id = 1";
SqlDataAdapter da = new SqlDataAdapter (selectQuery, (SqlConnection) conn);
DataSet ds = new DataSet ();
da.Fill (ds, "IntTest");
SqlCommandBuilder cb = new SqlCommandBuilder (da);
SqlCommand cmd = cb.GetUpdateCommand ();
- Assert.AreEqual ("UPDATE employee SET id = ?, fname = ? WHERE ((id = ?) AND ((? = 1 AND age IS NULL) OR (fname = ?)))",
+#if NET_2_0
+ Assert.AreEqual ("UPDATE [employee] SET [id] = @p1, [fname] = @p2, [lname] = @p3 WHERE (([id] = @p4)" +
+ " AND ([fname] = @p5) AND ((@p6 = 1 AND [lname] IS NULL) OR ([lname] = @p7)))",
cmd.CommandText, "#2");
- Assert.AreEqual (5, cmd.Parameters.Count, "#3");
+#else
+ Assert.AreEqual ("UPDATE employee SET id = @p1, fname = @p2, lname = @p3 WHERE ((id = @p4)" +
+ " AND (fname = @p5) AND ((@p6 = 1 AND lname IS NULL) OR (lname = @p7)))",
+ cmd.CommandText, "#2");
+#endif
+ Assert.AreEqual (7, cmd.Parameters.Count, "#3");
+ } finally {
+ ConnectionManager.Singleton.CloseConnection ();
}
}
+#if NET_2_0
+ [Test]
+ public void GetUpdateCommandBoolTest ()
+ {
+ IDbConnection conn = ConnectionManager.Singleton.Connection;
+ try {
+ string selectQuery = "select id, fname, lname, id+1 as next_id from employee where id = 1";
+ SqlDataAdapter da = new SqlDataAdapter (selectQuery, (SqlConnection) conn);
+ DataSet ds = new DataSet ();
+ da.Fill (ds, "IntTest");
+ Assert.AreEqual (1, ds.Tables.Count, "#1 atleast one table should be filled");
+
+ SqlCommandBuilder cb = new SqlCommandBuilder (da);
+ SqlCommand cmd = cb.GetUpdateCommand (true);
+ Assert.AreEqual ("UPDATE [employee] SET [id] = @id, [fname] = @fname, [lname] = @lname WHERE (([id] = @id)" +
+ " AND ([fname] = @fname) AND ((@lname = 1 AND [lname] IS NULL) OR ([lname] = @lname)))",
+ cmd.CommandText, "#2");
+ Assert.AreEqual (7, cmd.Parameters.Count, "#3");
+ } finally {
+ ConnectionManager.Singleton.CloseConnection ();
+ }
+ }
+#endif
+ [Test]
+ public void GetUpdateCommandTest_CheckNonUpdatableColumns ()
+ {
+ IDbConnection conn = ConnectionManager.Singleton.Connection;
+ try {
+ ConnectionManager.Singleton.OpenConnection ();
+ IDbCommand cmd = conn.CreateCommand ();
+ cmd.CommandText = "create table #tmp_table (id int primary key , counter int identity(1,1), value varchar(10))";
+ cmd.ExecuteNonQuery ();
+
+ string selectQuery = "select id, counter, value, id+1 as next_id from #tmp_table";
+ SqlDataAdapter da = new SqlDataAdapter (selectQuery, (SqlConnection) conn);
+ DataSet ds = new DataSet ();
+ da.Fill (ds);
+ Assert.AreEqual (1, ds.Tables.Count, "#1");
+ Assert.AreEqual (4, ds.Tables [0].Columns.Count, "#2");
+
+ SqlCommandBuilder cb = new SqlCommandBuilder (da);
+ SqlCommand updateCmd = cb.GetUpdateCommand ();
+#if NET_2_0
+ Assert.AreEqual ("UPDATE [#tmp_table] SET [id] = @p1, [value] = @p2 WHERE (([id] = @p3) AND (" +
+ "[counter] = @p4) AND ((@p5 = 1 AND [value] IS NULL) OR ([value] = @p6)))",
+ updateCmd.CommandText, "#3");
+#else
+ Assert.AreEqual ("UPDATE #tmp_table SET id = @p1, value = @p2 WHERE ((id = @p3) AND (" +
+ "counter = @p4) AND ((@p5 = 1 AND value IS NULL) OR (value = @p6)))",
+ updateCmd.CommandText, "#3");
+#endif
+ Assert.AreEqual (6, updateCmd.Parameters.Count, "#4");
+
+ SqlCommand delCmd = cb.GetDeleteCommand ();
+#if NET_2_0
+ Assert.AreEqual ("DELETE FROM [#tmp_table] WHERE (([id] = @p1) AND ([counter] = @p2) AND " +
+ "((@p3 = 1 AND [value] IS NULL) OR ([value] = @p4)))", delCmd.CommandText, "#5");
+#else
+ Assert.AreEqual ("DELETE FROM #tmp_table WHERE ((id = @p1) AND (counter = @p2) AND " +
+ "((@p3 = 1 AND value IS NULL) OR (value = @p4)))", delCmd.CommandText, "#5");
+#endif
+ Assert.AreEqual (4, delCmd.Parameters.Count, "#6");
+ } finally {
+ ConnectionManager.Singleton.CloseConnection ();
+ }
+ }
+
+ [Test]
+ public void GetUpdateDeleteCommand_CheckParameters ()
+ {
+ IDbConnection conn = ConnectionManager.Singleton.Connection;
+ try {
+ ConnectionManager.Singleton.OpenConnection ();
+ SqlDataAdapter adapter = new SqlDataAdapter ("select id, type_varchar from string_family",
+ (SqlConnection)conn);
+ SqlCommandBuilder cb = new SqlCommandBuilder (adapter);
+
+ SqlCommand updateCommand = cb.GetUpdateCommand ();
+ Assert.AreEqual (5, updateCommand.Parameters.Count, "#1");
+ Assert.AreEqual (SqlDbType.Int, updateCommand.Parameters ["@p4"].SqlDbType, "#2");
+ Assert.AreEqual (1, updateCommand.Parameters ["@p4"].Value, "#3");
+
+ SqlCommand delCommand = cb.GetDeleteCommand ();
+ Assert.AreEqual (3, delCommand.Parameters.Count, "#4");
+ Assert.AreEqual (SqlDbType.Int, delCommand.Parameters ["@p2"].SqlDbType, "#5");
+ Assert.AreEqual (1, delCommand.Parameters ["@p2"].Value, "#6");
+ } finally {
+ ConnectionManager.Singleton.CloseConnection ();
+ }
+ }
+
+#if NET_2_0
+ [Test]
+ public void GetUpdateCommandBoolTest_CheckNonUpdatableColumns ()
+ {
+ IDbConnection conn = ConnectionManager.Singleton.Connection;
+ try {
+ ConnectionManager.Singleton.OpenConnection ();
+ IDbCommand cmd = conn.CreateCommand ();
+ cmd.CommandText = "create table #tmp_table (id int primary key , counter int identity(1,1), value varchar(10))";
+ cmd.ExecuteNonQuery ();
+
+ string selectQuery = "select id, counter, value, id+1 as next_id from #tmp_table";
+ SqlDataAdapter da = new SqlDataAdapter (selectQuery, (SqlConnection) conn);
+ DataSet ds = new DataSet ();
+ da.Fill (ds);
+ Assert.AreEqual (1, ds.Tables.Count, "#1");
+ Assert.AreEqual (4, ds.Tables [0].Columns.Count, "#2");
+
+ SqlCommandBuilder cb = new SqlCommandBuilder (da);
+ SqlCommand updateCmd = cb.GetUpdateCommand (true);
+ Assert.AreEqual ("UPDATE [#tmp_table] SET [id] = @id, [value] = @value WHERE (([id] = @id) AND (" +
+ "[counter] = @counter) AND ((@value = 1 AND [value] IS NULL) OR ([value] = @value)))",
+ updateCmd.CommandText, "#3");
+ Assert.AreEqual (6, updateCmd.Parameters.Count, "#4");
+
+ SqlCommand delCmd = cb.GetDeleteCommand (true);
+ Assert.AreEqual ("DELETE FROM [#tmp_table] WHERE (([id] = @id) AND ([counter] = @counter) AND " +
+ "((@value = 1 AND [value] IS NULL) OR ([value] = @value)))", delCmd.CommandText, "#5");
+ Assert.AreEqual (4, delCmd.Parameters.Count, "#6");
+ } finally {
+ ConnectionManager.Singleton.CloseConnection ();
+ }
+ }
+
+ [Test]
+ public void GetUpdateDeleteCommandBoolTest_CheckParameters ()
+ {
+ IDbConnection conn = ConnectionManager.Singleton.Connection;
+ try {
+ ConnectionManager.Singleton.OpenConnection ();
+ SqlDataAdapter adapter = new SqlDataAdapter ("select id, type_varchar from string_family",
+ (SqlConnection)conn);
+ SqlCommandBuilder cb = new SqlCommandBuilder (adapter);
+
+ SqlCommand updateCommand = cb.GetUpdateCommand (true);
+ Assert.AreEqual (5, updateCommand.Parameters.Count, "#1");
+ Assert.AreEqual (SqlDbType.VarChar, updateCommand.Parameters ["@type_varchar"].SqlDbType, "#2");
+ // FIXME: NotWorking
+ //Assert.AreEqual (1, updateCommand.Parameters ["@type_char"].Value, "#3");
+
+ SqlCommand delCommand = cb.GetDeleteCommand (true);
+ Assert.AreEqual (3, delCommand.Parameters.Count, "#4");
+ Assert.AreEqual (SqlDbType.Int, delCommand.Parameters ["@type_varchar"].SqlDbType, "#5");
+ Assert.AreEqual (1, delCommand.Parameters ["@type_varchar"].Value, "#6");
+ } finally {
+ ConnectionManager.Singleton.CloseConnection ();
+ }
+ }
+#endif
[Test]
[ExpectedException (typeof (DBConcurrencyException))]
public void GetUpdateCommandDBConcurrencyExceptionTest ()
}
}
+ [Test]
+ [ExpectedException (typeof (DBConcurrencyException))]
+ public void GetDeleteCommandDBConcurrencyExceptionTest ()
+ {
+ IDbConnection conn = ConnectionManager.Singleton.Connection;
+ try {
+ ConnectionManager.Singleton.OpenConnection ();
+ string selectQuery = "select id, fname from employee where id = 1";
+ SqlDataAdapter da = new SqlDataAdapter (selectQuery, (SqlConnection) conn);
+ DataSet ds = new DataSet ();
+ da.Fill (ds, "IntTest");
+ Assert.AreEqual (1, ds.Tables.Count, "#1 atleast one table should be filled");
+
+ SqlCommandBuilder cb = new SqlCommandBuilder (da);
+ DataRow [] rows = ds.Tables [0].Select ("id=1");
+ rows [0] [0] = 6660; // non existent
+ ds.Tables [0].AcceptChanges (); // moves 6660 to original value
+ rows [0].Delete (); // moves 6660 as search key into db table
+ da.Update (rows);
+ } finally {
+ ConnectionManager.Singleton.CloseConnection ();
+ }
+ }
+
[Test]
public void GetDeleteCommandTest ()
{
IDbConnection conn = ConnectionManager.Singleton.Connection;
try {
ConnectionManager.Singleton.OpenConnection ();
- string selectQuery = "select id, fname, id+1 as next_id from employee where id = 1";
+ string selectQuery = "select id, fname, lname, id+1 as next_id from employee where id = 1";
SqlDataAdapter da = new SqlDataAdapter (selectQuery, (SqlConnection) conn);
DataSet ds = new DataSet ();
da.Fill (ds, "IntTest");
SqlCommandBuilder cb = new SqlCommandBuilder (da);
SqlCommand cmd = cb.GetDeleteCommand ();
- Assert.AreEqual ("DELETE FROM employee WHERE ((id = ?) AND ((? = 1 AND fname IS NULL) OR (fname = ?)))",
- cmd.CommandText, "#2");
+#if NET_2_0
+ Assert.AreEqual ("DELETE FROM [employee] WHERE (([id] = @p1) AND ([fname] = @p2) AND " +
+ "((@p3 = 1 AND [lname] IS NULL) OR ([lname] = @p4)))", cmd.CommandText, "#2");
+#else
+ Assert.AreEqual ("DELETE FROM employee WHERE ((id = @p1) AND (fname = @p2) AND " +
+ "((@p3 = 1 AND lname IS NULL) OR (lname = @p4)))", cmd.CommandText, "#2");
+#endif
} finally {
ConnectionManager.Singleton.CloseConnection ();
}
public void DefaultPropertiesTest ()
{
SqlCommandBuilder cb = new SqlCommandBuilder ();
-#if NET_1_1 || NET_1_0 || ONLY_1_1
+#if NET_1_0
Assert.AreEqual (ConflictOption.CompareAllSearchableValues, cb.ConflictDetection);
-#endif // NET_1_1 || NET_1_0 || ONLY_1_1
Assert.AreEqual ("", cb.QuotePrefix, "#5");
Assert.AreEqual ("", cb.QuoteSuffix, "#6");
+#endif
#if NET_2_0
+ Assert.AreEqual ("[", cb.QuotePrefix, "#5");
+ Assert.AreEqual ("]", cb.QuoteSuffix, "#6");
Assert.AreEqual (".", cb.CatalogSeparator, "#2");
- Assert.AreEqual ("", cb.DecimalSeparator, "#3");
+ //Assert.AreEqual ("", cb.DecimalSeparator, "#3");
Assert.AreEqual (".", cb.SchemaSeparator, "#4");
Assert.AreEqual (CatalogLocation.Start, cb.CatalogLocation, "#1");
IDbConnection conn = ConnectionManager.Singleton.Connection;
try {
conn.Open ();
cb = new SqlCommandBuilder ();
- Assert.AreEqual ("\"monotest\"", cb.QuoteIdentifier ("monotest", (SqlConnection) conn), "#7");
- Assert.AreEqual ("monotest", cb.UnquoteIdentifier ("\"monotest\"", (SqlConnection) conn), "#8");
+#if NET_2_0
+ Assert.AreEqual ("[monotest]", cb.QuoteIdentifier ("monotest"), "#7");
+ Assert.AreEqual ("\"monotest\"", cb.UnquoteIdentifier ("\"monotest\""), "#8");
+#else
+ Assert.AreEqual ("\"monotest\"", cb.QuoteIdentifier ("monotest"), "#7");
+ Assert.AreEqual ("monotest", cb.UnquoteIdentifier ("\"monotest\""), "#8");
+#endif
conn.Close ();
} finally {
ConnectionManager.Singleton.CloseConnection ();
// FIXME: Add tests for examining RowError
// FIXME: Add test for ContinueUpdateOnError property
+
+ [Test]
+ public void CheckParameters_BuiltCommand ()
+ {
+ using (IDbConnection conn = ConnectionManager.Singleton.Connection) {
+ SqlDataAdapter adapter = new SqlDataAdapter ("select id,type_varchar from string_family", (SqlConnection)conn);
+ SqlCommandBuilder cb = new SqlCommandBuilder(adapter);
+ DataSet ds = new DataSet ();
+ adapter.Fill(ds);
+ Assert.AreEqual (2, cb.GetInsertCommand().Parameters.Count, "#1");
+
+ DataRow row_rsInput = ds.Tables[0].NewRow();
+ row_rsInput["id"] = 100;
+ row_rsInput["type_varchar"] = "ttt";
+ ds.Tables[0].Rows.Add(row_rsInput);
+
+ Assert.AreEqual (2, cb.GetInsertCommand().Parameters.Count, "#2");
+
+ row_rsInput = ds.Tables[0].NewRow();
+ row_rsInput["id"] = 101;
+ row_rsInput["type_varchar"] = "ttt";
+ ds.Tables[0].Rows.Add(row_rsInput);
+
+ Assert.AreEqual (2, cb.GetInsertCommand().Parameters.Count, "#3");
+ }
+ }
}
}
projects / mono.git / blobdiff