Badly needed DataAdapter feature

  • Thread starter Thread starter John Viele
  • Start date Start date
J

John Viele

Any plans to implement SQL logging in the data adapters? It seems like a
very basic feature. It would help with debugging if one could view the
actual SQL statements that get generated when one does something like:
DataAdapter.Update(SomeDataTable);

Or, is the feature there and I have just overlooked it?
 
John:

To the best of my knowledge, it isn't readily available.
However, you can get there from here.

1) Unless you are using a command builder, the update
logic is explicit, either you wrote it or the IDE Did.
You can view the mappings and see what's going on by just
looking at the system generated code.

2) You can use DataSet.GetChanges to find out all of the
values that are being passed to the update commands.
With little effort you could create a class, pass it the
changes and what the update logic is from your code, and
construct the exact statement being used.

It may be available somehow already but I don't know of
it and if it's not, it'd be pretty easy to create.

I'll gladly help you out if you are interested in
creating such a component.

Good Luck,

Bill


Cordially,

W.G. Ryan
(e-mail address removed)
www.knowdotnet.com
 
Do you mean the commands that are generated? You can view those. Actually
you have to define them in some way. Either manually or with the
CommandBuilder.

Look at the SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand.
Each typically also contains a Parameters collection, except maybe a simple
SelectCommand that does not have a "Where" clause.
 
John Viele said:
Any plans to implement SQL logging in the data adapters? It seems like a
very basic feature. It would help with debugging if one could view the
actual SQL statements that get generated when one does something like:
DataAdapter.Update(SomeDataTable);

Or, is the feature there and I have just overlooked it?
Click to expand...

Overlooked it.

See
SqlDataAdapter.RowUpdating
and
SqlDataAdapter.RowUpdated
events.

From the event args you can get to the command and view/edit the parameter
values before they are passed to the database. In the event handler you
could also log the command out.

For instance, here is a RowUpdating event that should be on EVERY
SQLDataAdapter. It's hard to keep the front end from passing empty strings
back in updates sometimes. But I hate empty strings. They are an
abomination. ANSI standard SQL requires SQLServer to store empty strings on
demand, but you should never do it. Here's a handler you can hook up to
your SQLDataAdapter to automatically change empty strings to nulls.


Use it like

dim da as new SQLDataAdapter(cmd)
addhandler da.RowUpdating , addressof ChangeEmptyStringsToNulls
da.update(ds)


Private shared Sub ChangeEmptyStringsToNulls(ByVal sender As Object, ByVal e
As SqlRowUpdatingEventArgs)

Dim p As SqlClient.SqlParameter
For Each p In e.Command.Parameters
If TypeOf p.Value Is String AndAlso DirectCast(p.Value, String) = ""
Then
p.Value = DBNull.Value
End If
Next

End Sub

Here's a handy routine to describe a command, combining the SQL and the
parameter values. Also it works for any provider since it uses the IDB*
interfaces.

Public Shared Function DescribeCommand(ByVal cmd As IDbCommand) As String

Dim msg As New Text.StringBuilder()
msg.Append(cmd.CommandText)
msg.Append(" "c)
Dim p As IDbDataParameter
For Each p In cmd.Parameters
msg.Append(p.ParameterName)
msg.Append("{" & p.DbType.ToString & ") - [")
If TypeOf p.Value Is DBNull Then
msg.Append("(null)")
Else
msg.Append(p.Value.ToString)
End If
msg.Append("]")
Next
Return msg.ToString

End Function


So to log all the SQL statements from a dataadapter:

dim da as new SQLDataAdapter(cmd)
addhandler da.RowUpdated , addressof LogCommands
da.update(ds)


Private shared Sub LogCommands(ByVal sender As Object, ByVal e As
SqlRowUpdatedEventArgs)

System.Diagnostics.Trace.WriteLine(DescribeCommand(e.Command())

End Sub

David
 
I do that when my business layer passes changes back to my data layer. If a
string type is empty or null, I don't even update the value for that column
of the datarow. see code snippet below.

Preventing empty strings certainly isn't reason enough to implement a
separate business layer, but it is just another of the many benefits of a
layered application.

================================================
.... in class ContactDB.cs of CRM.Data.DLL ...
public bool Update(Guid contactid, string firstname, string lastname)
{
...excerpt ...
System.Data.DataRow dr = GetByID(contactid);
string oldfirstname = "";
if (dr["FirstName"] != DBNull.Value)
{oldfirstname = Convert.ToString(dr["FirstName"]);}
if (oldfirstname != firstname){dr["FirstName"] = firstname;}

string oldlastname = "";
if (dr["LastName"] != DBNull.Value)
{oldlastname = Convert.ToString(dr["LastName"]);}
if (oldlastname != lastname){dr["LastName"] = lastname;}

return (Update() > 0);
}
public int Update()
{
if (_ds.HasChanges()){return _da.Update(_ds);}
else {return 0;}
}
================================================
--
Michael Lang, MCSD
See my .NET open source projects
http://sourceforge.net/projects/dbobjecter (code generator)
http://sourceforge.net/projects/genadonet ("generic" ADO.NET)

David Browne said:
John Viele said:
Any plans to implement SQL logging in the data adapters? It seems like a
very basic feature. It would help with debugging if one could view the
actual SQL statements that get generated when one does something like:
DataAdapter.Update(SomeDataTable);

Or, is the feature there and I have just overlooked it?
Click to expand...

Overlooked it.

See
SqlDataAdapter.RowUpdating
and
SqlDataAdapter.RowUpdated
events.

From the event args you can get to the command and view/edit the parameter
values before they are passed to the database. In the event handler you
could also log the command out.

For instance, here is a RowUpdating event that should be on EVERY
SQLDataAdapter. It's hard to keep the front end from passing empty strings
back in updates sometimes. But I hate empty strings. They are an
abomination. ANSI standard SQL requires SQLServer to store empty strings on
demand, but you should never do it. Here's a handler you can hook up to
your SQLDataAdapter to automatically change empty strings to nulls.


Use it like

dim da as new SQLDataAdapter(cmd)
addhandler da.RowUpdating , addressof ChangeEmptyStringsToNulls
da.update(ds)


Private shared Sub ChangeEmptyStringsToNulls(ByVal sender As Object, ByVal e
As SqlRowUpdatingEventArgs)

Dim p As SqlClient.SqlParameter
For Each p In e.Command.Parameters
If TypeOf p.Value Is String AndAlso DirectCast(p.Value, String) = ""
Then
p.Value = DBNull.Value
End If
Next

End Sub

Here's a handy routine to describe a command, combining the SQL and the
parameter values. Also it works for any provider since it uses the IDB*
interfaces.

Public Shared Function DescribeCommand(ByVal cmd As IDbCommand) As String

Dim msg As New Text.StringBuilder()
msg.Append(cmd.CommandText)
msg.Append(" "c)
Dim p As IDbDataParameter
For Each p In cmd.Parameters
msg.Append(p.ParameterName)
msg.Append("{" & p.DbType.ToString & ") - [")
If TypeOf p.Value Is DBNull Then
msg.Append("(null)")
Else
msg.Append(p.Value.ToString)
End If
msg.Append("]")
Next
Return msg.ToString

End Function


So to log all the SQL statements from a dataadapter:

dim da as new SQLDataAdapter(cmd)
addhandler da.RowUpdated , addressof LogCommands
da.update(ds)


Private shared Sub LogCommands(ByVal sender As Object, ByVal e As
SqlRowUpdatedEventArgs)

System.Diagnostics.Trace.WriteLine(DescribeCommand(e.Command())

End Sub

David
Click to expand...
 
I took a peek at the documentation for the RowUpdating event, but I didn't
try using it. The reason is that it didn't seem to give me what I wanted,
but that's due to a misunderstanding of what was going on under the hood. I
had originally thought that the framework or ADO would take my SQL statement
and string-substitute values (other than binary params), passing an
end-result statement to the SQL server. In fact, it's doing parameterized
Queries.

I understand that I can stick event handlers on each and every one of my
data adapters (there are many in this particular project). That's not
terribly convenient, though. Knowing what I know now, it would be great if
there were a configuration setting that would log the statements sent AND
the values of the parameters.

I worked for a company in the 90's that did quite a bit of its own C++ class
libraries, and we had excellent logging capabilities in our SQL library.
You could log to a window and/or a file, and you had options like whether to
show the plan or just the statements and their results (not data returned,
but error information, how many rows affected, etc). If enabled, your
entire application would log no matter how many SQL objects you instantiated
and used. If only ADO.NET were that convenient.
 
John Viele said:
I took a peek at the documentation for the RowUpdating event, but I didn't
try using it. The reason is that it didn't seem to give me what I wanted,
but that's due to a misunderstanding of what was going on under the hood. I
had originally thought that the framework or ADO would take my SQL statement
and string-substitute values (other than binary params), passing an
end-result statement to the SQL server. In fact, it's doing parameterized
Queries.

I understand that I can stick event handlers on each and every one of my
data adapters (there are many in this particular project). That's not
terribly convenient, though. Knowing what I know now, it would be great if
there were a configuration setting that would log the statements sent AND
the values of the parameters.

I worked for a company in the 90's that did quite a bit of its own C++ class
libraries, and we had excellent logging capabilities in our SQL library.
You could log to a window and/or a file, and you had options like whether to
show the plan or just the statements and their results (not data returned,
but error information, how many rows affected, etc). If enabled, your
entire application would log no matter how many SQL objects you instantiated
and used. If only ADO.NET were that convenient.
Click to expand...

You can easilly make it that convenient. Just don't create your data
adapters directly. Build a factory method to manufacture them, and attach
any event handlers you want.

Your code just changes from
dim da as new SQLDataAdapter(cmd)

to
dim da as SQLDataAdapter = MyFactory.CreateDataAdapter(cmd)

David
 
Back
Top