Some ADO.NET Tricks

Passing Null as a Parameter Value

When sending a null value as a Parameter value in a command to the database, you cannot use null Instead you need to use DBNull.Value.
For example:

VB.NET
Dim param As SqlParameter = New SqlParameter("@Name", SqlDbType.NVarChar, 20)
param.Value = DBNull.Value

C#

SqlParameter param = new SqlParameter("@Name", SqlDbType.NVarChar, 20);
param.Value = DBNull.Value;


Optimizing Connections with the DataAdapter

The Fill and Update methods, of the DataAdapter, automatically open the connection specified for the related command property if Specific Connection is closed. If the Fill or Update method open the connection, Fill or Update will close it when the operation is complete. For best performance, keep connections to the database open only when required. Also, reduce the number of times you open and close a connection for multiple operations.

when performing transactions, explicitly open the connection before beginning the transaction and close the connection after you commit


Public Sub RunSqlTransaction(ByVal MyDA As SqlDataAdapter, ByVal myConn As SqlConnection, ByVal MyDS As DataSet)

myConn.Open()
Dim myTrans As SqlTransaction = myConn.BeginTransaction()
myCommand.Transaction = myTrans

Try
MyDA.Update(MyDS)
myTrans.Commit()
Console.WriteLine("Update successful.")

Catch e As Exception

Try
myTrans.Rollback()

Catch ex As SqlException

If Not myTrans.Connection Is Nothing Then
Console.WriteLine("An exception of type " & ex.GetType().ToString() & _
" was encountered while attempting to roll back the transaction.")
End If

End Try

Console.WriteLine("An exception of type " & e.GetType().ToString() & " was encountered.")
Console.WriteLine("Update failed.")
End Try

myConn.Close()

End Sub