2023年6月21日发(作者：)

使⽤SqlHelper的⼀个⼩技巧在数据库设计时，也许会有⼀些约定，说⼀下我⾃⼰的三点基本硬性规定：1、所有字段都为可空设定（主键、布尔类型字段除外）2、字符串类型不允许有前后空格（可能特殊情况时除外）3、如果是空字符串，则应存储Null（为了防⽌不同数据库类型对字符串为空和NULL的不同对待）

第⼀点是设计时的事情，在这⾥可以略过，⽽后两点则是归程序判断处理的，也许细⼼的你发现了，第⼆点和第三点是衔接的。

接下来我们来看⼀下后两点是如何实现的，以TextBox为例，我是这样进⾏的：'在验证数据时进⾏()'在保存时进⾏With info If (ue) Then .Remark = Else .Remark = Nothing End IfEnd With这段代码并不难理解，不过需要说明的是，必填的字段为了验证是否有值Trim是得有的，⽽不必填的字段实质上则只需要赋值的那⼀⾏，Trim和Null则可以交由底层SqlHelper⾥进⾏处理。

⾸先我们定义⼀个配置类来进⾏控制：'''

''' Database configuration''' 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 = TrueEnd Class前三项正是我们要实现的功能的开关，⽽最后⼀项NullValueToDBNull则需要另外说明⼀下了：在实体类中，值类型我都是⽤Nullable(Of T)来存储的，这当中就包含了Null的情况，⽽在传递⾄数据库时，Null是作为默认值还是DBNull呢？这是不确定的，所以这个开关就是⽤于约定Null作为DBNull处理。

接下来就是对SqlHelper的改造了，需要改动的只有⼀个⽅法：PrepareCommand'''

''' This method opens (if necessary) and assigns a connection, transaction, command type and parameters''' to the provided command.''' ''' the SqlCommand to be prepared''' a valid SqlConnection, on which to execute this command''' a valid SqlTransaction, or 'null'''' the CommandType (stored procedure, text, etc.)''' the stored procedure name or T-SQL command''' an array of SqlParameters to be associated with the command or 'null' if no parameters are requiredPrivate 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 <> Then () End If 'associate the connection with the command tion = connection 'set the command text (stored procedure name or SQL statement) dText = commandText 'if we were provided a transaction, assign it. If Not (transaction Is Nothing) Then ction = transaction End If 'set the command type dType = commandType 'attach the command parameters if they are provided If Not (commandParameters Is Nothing) Then For Each p As SqlParameter In commandParameters If (ion <> ) Then Select Case Case , FixedLength, ring, ringFixedLength If (Not Is Nothing AndAlso Not Is ) Then Dim str As String = ng() If (ring) Then str = () End If If (tringToNull AndAlso = 0) Then str = Nothing End If = str End If Case n If (oleanToFalse AndAlso Is Nothing) Then = False End If End Select If (lueToDBNull AndAlso Is Nothing) Then = End If End If (p) Next End IfEnd Sub 'PrepareCommand可以看到根据Parameter的DbType作了相应的处理，这样处理后，⾮必填的字段，就只以只⽤⼀句赋值语句，剩下的去空⽩字符和Null判断就交由底层处理了，省⼼省⼒！~~~

2023年6月21日发(作者：)

使⽤SqlHelper的⼀个⼩技巧在数据库设计时，也许会有⼀些约定，说⼀下我⾃⼰的三点基本硬性规定：1、所有字段都为可空设定（主键、布尔类型字段除外）2、字符串类型不允许有前后空格（可能特殊情况时除外）3、如果是空字符串，则应存储Null（为了防⽌不同数据库类型对字符串为空和NULL的不同对待）

第⼀点是设计时的事情，在这⾥可以略过，⽽后两点则是归程序判断处理的，也许细⼼的你发现了，第⼆点和第三点是衔接的。

接下来我们来看⼀下后两点是如何实现的，以TextBox为例，我是这样进⾏的：'在验证数据时进⾏()'在保存时进⾏With info If (ue) Then .Remark = Else .Remark = Nothing End IfEnd With这段代码并不难理解，不过需要说明的是，必填的字段为了验证是否有值Trim是得有的，⽽不必填的字段实质上则只需要赋值的那⼀⾏，Trim和Null则可以交由底层SqlHelper⾥进⾏处理。

⾸先我们定义⼀个配置类来进⾏控制：'''

''' Database configuration''' 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 = TrueEnd Class前三项正是我们要实现的功能的开关，⽽最后⼀项NullValueToDBNull则需要另外说明⼀下了：在实体类中，值类型我都是⽤Nullable(Of T)来存储的，这当中就包含了Null的情况，⽽在传递⾄数据库时，Null是作为默认值还是DBNull呢？这是不确定的，所以这个开关就是⽤于约定Null作为DBNull处理。

接下来就是对SqlHelper的改造了，需要改动的只有⼀个⽅法：PrepareCommand'''

''' This method opens (if necessary) and assigns a connection, transaction, command type and parameters''' to the provided command.''' ''' the SqlCommand to be prepared''' a valid SqlConnection, on which to execute this command''' a valid SqlTransaction, or 'null'''' the CommandType (stored procedure, text, etc.)''' the stored procedure name or T-SQL command''' an array of SqlParameters to be associated with the command or 'null' if no parameters are requiredPrivate 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 <> Then () End If 'associate the connection with the command tion = connection 'set the command text (stored procedure name or SQL statement) dText = commandText 'if we were provided a transaction, assign it. If Not (transaction Is Nothing) Then ction = transaction End If 'set the command type dType = commandType 'attach the command parameters if they are provided If Not (commandParameters Is Nothing) Then For Each p As SqlParameter In commandParameters If (ion <> ) Then Select Case Case , FixedLength, ring, ringFixedLength If (Not Is Nothing AndAlso Not Is ) Then Dim str As String = ng() If (ring) Then str = () End If If (tringToNull AndAlso = 0) Then str = Nothing End If = str End If Case n If (oleanToFalse AndAlso Is Nothing) Then = False End If End Select If (lueToDBNull AndAlso Is Nothing) Then = End If End If (p) Next End IfEnd Sub 'PrepareCommand可以看到根据Parameter的DbType作了相应的处理，这样处理后，⾮必填的字段，就只以只⽤⼀句赋值语句，剩下的去空⽩字符和Null判断就交由底层处理了，省⼼省⼒！~~~