SqlCommandBuilder With Convert Statement

Question

I have an application that's built in .NET language.

In this application we mainly read/write to the database (SQL Server 2005). Sometimes (for a single input) I just use the SQL query, for example:

commandText = "INSERT INTO Test_Table (Number) VALUES ('10')";
command = new System.Data.SqlClient.SqlCommand(commandText, connection);
command.ExecuteNonQuery();

If I want to update a bunch of records in my database, I use the SqlCommandBuilder class, as in this example:

adapter = new System.Data.SqlClient.SqlDataAdapter("SELECT * FROM Test_Table WHERE Number = '9'",connection);

commandbuilder = new System.Data.SqlClient.SqlCommandBuilder(adapter);

dataset = new System.Data.DataSet();
adapter.Fill(dataset,"Example");

dataset.Tables["Example"].Rows[0].["Number"] = 10;
adapter.update(dataset,"Example");

These work great. But now, for some reason I need to insert/update datetimes and use the CONVERT function on it.

The single SQL query works great:

t = System.DateTime.Now;
commandText = "INSERT INTO Test_Table (DateTime) VALUES (datetime, 't.toString()', 103)";
command = new System.Data.SqlClient.SqlCommand(commandText, connection);
command.ExecuteNonQuery();

This works without a problem, however I have no idea how to achieve the same thing using my SqlCommandBuilder scripts. I could change everything to single query, but this would take a week.

I have already tried the following, without success:

t = System.DateTime.Now;
adapter = new System.Data.SqlClient.SqlDataAdapter("SELECT * FROM Test_Table WHERE Number = '9'", connection);

commandbuilder = new System.Data.SqlClient.SqlCommandBuilder(adapter);

dataset = new System.Data.DataSet();
adapter.Fill(dataset, "Example");

dataset.Tables["Example"].Rows[0].["DateTime"] = "CONVERT(datetime,'" + t.toString() + "',103);
adapter.update(dataset, "Example");

score 0 · Answer 1 · answered Jul 10 '15 at 14:53

This line of code is weird:

dataset.Tables["Example"].Rows[0].["DateTime"] = "CONVERT(datetime,'" + t.toString() + "',103);

Does it compile? Specifically, this:

.Rows[0].["DateTime"]

I think it should be:

.Rows[0]["DateTime"]

But regardles of the syntax...I don't think this is the right way to go. The datatable (in the dataset) expects a datetime object (btw, don't name your attributes by their datatype, it causes confusion) and you are providing it with something that is incompatible. Sytem.DateTime.Now returns a DateTime object, then you are concatenating it with string (again, does this compile?) and I assume you expect it to be injected into the INSERT statement?

Since you said that it would take a week to change everything, I assume that you have a lot of similar code to repair. I see three possible solutions, all require some work:

Create a database trigger

https://msdn.microsoft.com/en-us/library/ms189799.aspx

Add a default value to the DateTime field in the database and remove the DateTime from the select query.

http://www.w3schools.com/sql/sql_default.asp

https://www.youtube.com/watch?v=INfi7jkdXC8 (start watching at around 2:00)

You can write a function that does the actual text replacing but it can get tricky:

dasdas as

private string ChangeDate(string insertQuery)
    {
    // find the location of the date column
    // replace the actual value with the "CONVERT(datetime,'" + actualValue + "',103)"
    // return the new value and store it in the sqlcommandbuilder.insertstatement
    }

Admittedly, all three require work and are not really "elegant". I would go for option 2, because it seems less work. But I don't know if this solves your problem..

SqlCommandBuilder With Convert Statement

1 Answers1