Tips for using SqlHelper

During database design, there may be some conventions to explain my three basic hard rules:
1. All fields are configurable (except for primary key and Boolean fields)
2. Leading and trailing spaces are not allowed for string types (except in special cases)
3. If it is a Null String, NULL should be stored (to prevent different database types from treating strings as Null or NULL)
 
The first point is the design thing. You can skip it here, and the second and second points are determined and handled by the program. You may have noticed carefully that the second and third points are connected.
 
Next let's take a look at how the last two points are implemented. Taking TextBox as an example, I am doing this:
'When verifying data
Me.txt Remark. Trim ()

'When saving
With info
If (Me.txt Remark. HasValue) Then
. Remark = Me.txt Remark. Text
Else
. Remark = Nothing
End If
End

This code is not difficult to understand, but it should be noted that the mandatory field is required to verify whether there is a value Trim available, and the field that does not need to be filled in actually only needs to assign a value to that line, trim and Null can be processed by the underlying SqlHelper.
 
First, we define a configuration class for control:
''' <Summary>
'''Database configuration
''' </Summary>
Friend NotInheritable Class Config

'Removes all occurrences of white space characters
Public Shared ReadOnly TrimString As Boolean = True
'Translate the empty string to null
Public Shared ReadOnly EmptyStringToNull As Boolean = True
'Translate the null boolean to false
Public Shared ReadOnly NullBooleanToFalse As Boolean = True
'Translate the null value to dbnull value
Public Shared ReadOnly NullValueToDBNull As Boolean = True

End Class

The first three are the functions we want to implement, and the last NullValueToDBNull needs to be explained separately:
In the object class, I use Nullable (Of T) to store the value type, which includes Null. When passed to the database, is Null the default value or DBNull? This is not certain, so this switch is used to define Null as DBNull.
 
The next step is the transformation of SqlHelper. There is only one method to change: PrepareCommand
''' <Summary>
'''This method opens (if necessary) and assigns a connection, transaction, command type and parameters
''' To the provided command.
''' </Summary>
''' <Param name = "command"> the SqlCommand to be prepared </param>
''' <Param name = "connection"> a valid SqlConnection, on which to execute this command </param>
''' <Param name = "transaction"> a valid SqlTransaction, or 'null' </param>
''' <Param name = "commandType"> the CommandType (stored procedure, text, etc.) </param>
''' <Param name = "commandText"> the stored procedure name or T-SQL command </param>
''' <Param name = "commandParameters"> an array of SqlParameters to be associated with the command or 'null' if no parameters are required </param>
Private Shared Sub PrepareCommand (ByVal command As SqlCommand ,_
ByVal connection As SqlConnection ,_
ByVal transaction As SqlTransaction ,_
ByVal commandType As CommandType ,_
ByVal commandText As String ,_
ByVal commandParameters () As SqlParameter)

'If the provided connection is not open, we will open it
If connection. State <> ConnectionState. Open Then
Connection. Open ()
End If

'Associate the connection with the command
Command. Connection = connection

'Set the command text (stored procedure name or SQL statement)
Command. CommandText = commandText

'If we were provided a transaction, assign it.
If Not (transaction Is Nothing) Then
Command. Transaction = transaction
End If

'Set the command type
Command. CommandType = commandType

'Attach the command parameters if they are provided
If Not (commandParameters Is Nothing) Then
For Each p As SqlParameter In commandParameters
If (p. Direction <> ParameterDirection. Output) Then
Select Case p. DbType
Case DbType. String, DbType. StringFixedLength, DbType. AnsiString, DbType. AnsiStringFixedLength
If (Not p. Value Is Nothing AndAlso Not p. Value Is DBNull. Value) Then
Dim str As String = p. Value. ToString ()

If (Config. TrimString) Then
Str = str. Trim ()
End If

If (Config. EmptyStringToNull AndAlso str. Length = 0) Then
Str = Nothing
End If

P. Value = str
End If
Case DbType. Boolean
If (Config. NullBooleanToFalse AndAlso p. Value Is Nothing) Then
P. Value = False
End If
End Select

If (Config. NullValueToDBNull AndAlso p. Value Is Nothing) Then
P. Value = DBNull. Value
End If
End If

Command. Parameters. Add (p)
Next
End If

End Sub 'preparecommand

We can see that the corresponding processing is performed based on the DbType of Parameter. After the processing, the non-mandatory field is only used with a single assignment statement, the remaining blank characters and Null judgment are handled by the underlying layer, saving your effort and effort! ~~~

 

From the day of worship