如何添加参数和执行通用IDbCommand [英] How to add parameters and execute a generic IDbCommand

问题描述

我创建了一个数据访问层类，该类允许我创建与数据库进行通信所需的大多数对象(Odbc，OleDb和SqlClient).我还创建了一个业务对象处理类，该类大量使用反射来处理我的业务对象的许多任务.除其他事项外，此类还生成我进行DAL处理所需的每个属性/对象(SQL流，值列表，属性，设置检索值等).请看下面的代码以进一步说明:

I have created a data access layer class that allows me to create most of objects I needed to communicate with databases (Odbc, OleDb and SqlClient). I also have created a business object handling class with intensive use of reflection to handle lots of tasks with my business object(s). Among other things this class generates every single property/object I needed for my DAL handling (SQL stream, list of values, properties, set retrieve values etc.). Take a look the code below for further explanation:

Public Shared Function InvokeParam(Of T)(_classObject As T, _commandType As AdapterCommandType, _arguments As Object()) As Boolean
        Dim s As String = DAL.SCRFL.GetParamStatement(_classObject, _commandType, _arguments)
        'Debug.Print(s)
        Dim hT As Hashtable = DAL.SCRFL.GetProperties(_classObject)
        Using cnn As IDbConnection = DataFactory.CreateConnection()
            Dim cmd As IDbCommand = DataFactory.CreateCommand(s, cnn)
            'cmd.CommandType = CommandType.Text
            cmd.CommandText = s
            For Each k In hT
                Dim param As IDbDataParameter = cmd.CreateParameter()
                'param.DbType = DataFactory.ConvertToDbType(k.value.GetType)

                param.Value = k.value
                param.ParameterName = k.key
                'param.Direction = ParameterDirection.Input

                'Debug.Print("value:={0}, name:={1}", TypeName(k.value), TypeName(k.key))
                Debug.Print("typeMatch:={0}, value:={1}, name:={2}", TypeName(param.Value) = TypeName(k.value), param.Value, param.ParameterName)

                cmd.Parameters.Add(param)
            Next
            If (cmd.ExecuteNonQuery > 0) Then
                Return True
            End If
        End Using
        Return False
    End Function

因此，DAL.SCRFL.GetParamStatement返回格式化为"INSERT INTO t1 (f1, f2, f3...) values (?, ?, ?...)"等的字符串，以进行插入(基于_commandType枚举值，此方法将返回适当的字符串以进行更新，删除，选择语句).所有操作都是通过反射完成的，我确定这里没有语法错误.我可以通过直接提供程序类型的命令通过另一个方法手动执行此方法返回的值.

DAL.SCRFL.GetProperties方法返回格式为key=property(字段)，value=field值对的哈希表.

现在，我需要为每个这些(keys=values)属性创建参数，并将它们添加到我的命令参数集合中，然后执行命令(ExecuteNonQuery).

您会在我的代码中看到这种尝试(我正在通过循环哈希表从每个属性/值对创建参数).但是，在方法执行结束时，当我执行命令时，我收到了异常，条件是"条件表达式中的数据类型不匹配."说明.

我尝试将type属性添加到参数object，size等，所有操作均未成功(我对它们进行了评论).我尝试将param.Value = k.value更改为param.Value = If(IsDBNull(k.value), DBNull.Value, k.value)，以为可能是问题所在，尽管k.value来自我的业务类别，并且在此测试中我有意防止其为空值.这是测试返回的值；业务类从DAL.SCRFL.GetParamStatement调用返回值:测试已针对OleDb/Access数据库完成，并且如您所见，我将Memo字段括在单引号中，因此，我也意识到这一点.我的反射方法读取类属性''的属性(我将其设置为表字段名称)，并且DAL.SCRFL.GetParamStatement构建基本的sql语句以进行插入，更新，删除和选择使用. AdapterCommandType是内置的枚举类型).

So, the DAL.SCRFL.GetParamStatement returns string formatted as "INSERT INTO t1 (f1, f2, f3...) values (?, ?, ?...)" etc., for insert (based on _commandType enum value this method will return appropriate strings for update, delete, select statements). All are done with reflection and I''m sure there is no syntax error here. I can manually execute returned values from this method through direct provider type commands through another method.

The DAL.SCRFL.GetProperties method returns a hashtable formatted as key=property (field), value=field value pairs.

Now, I need to create parameters for each these (keys=values) properties and add them to my command parameters collection, then execute the command (ExecuteNonQuery).

This attempt you will see in my code (I’m creating parameters from each propert/value pair by looping the hash table). However at the end of the method when I''m executing the command, I''m getting an exception with ''Data type mismatch in criteria expression.'' description.

I''ve tried adding type property to parameters object, size, etc., all was unsuccessful (I commented them). I tried changing param.Value = k.value to param.Value = If(IsDBNull(k.value), DBNull.Value, k.value) thinking that this might be the problem, though k.value is from my business class and I intentionally prevent it from null values for this test. Here is the test returned values; business class returned value from DAL.SCRFL.GetParamStatement call: The test is done for OleDb/Access database and, as you can see, I enclosed the Memo field in single quotes so, I''m aware of that too. My reflection methods read class properties'' attributes (which I set to be table field names) and DAL.SCRFL.GetParamStatement builds basic sql statements for insert, update, delete and select use. AdapterCommandType is a built in enum type for it).

INSERT INTO Clinics (ClinicId, ClinicName, Phone, Fax, FederalId, DateContracted, Address, City, State, Zip, Inactive, [Memo], DateEntered, EnteredBy, DateModified, ModifiedBy) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

备注:我有另一个与此类似的方法，该方法执行一个sql语句(InvokeSql)，在该方法中，我会彻底检查每个属性的值类型，以在sql语句中构造property=value对.在此InvokeSql中使用完全限定的sql语句"，该方法无需一个警告即可运行(滚动:cnn As IDbConnection = CreateConnection()，cmd = CreateCommand(_cmdText, cnn)，cmd.ExecuteNonQuery()，其中_cmdText是sql语句.
如您所见，没有任何参数！).
我提到这一点是为了指出，只要我将参数与泛型IDbCommands一起使用，就会出现问题.即使在我的DataFactory中，IDbCommand设置为提供程序特定的命令类型(我的DataFactory.CreateCommand(s, cnn)返回通用的IDbCommand).

在我的新DAL项目之前，我已经执行了上述所有步骤，并明确声明为提供程序特定的类型对象，而我没有任何问题.从技术上讲，我正在使用与以前完全相同的骨架，但是将它们转换为通用类型的对象(而不是特定于提供程序的对象).当有参数和IDbCommand对象时，我无法使其正常工作.可能在某处，我想念一些东西.

如果有人可以指引我，我的错误在哪里?

在此先感谢Sam

REMARK: I have another method similar to this that executes an sql statement (InvokeSql) where I thoroughly check value types for each property to construct property=value pairs in my sql statement. Using a "fully qualified sql statement" in this, InvokeSql, method works w/out a single warning (Rouphly: cnn As IDbConnection = CreateConnection(), cmd = CreateCommand(_cmdText, cnn), cmd.ExecuteNonQuery() where _cmdText is the sql statement.
There is no parameters as you can see!).
I''m mentioning this to point out that the problem arises whenever I use parameters with generic IDbCommands. Even though inside my DataFactory the IDbCommand set to be provider specific command type (my DataFactory.CreateCommand(s, cnn) returns generic IDbCommand).

Prior to my new DAL project I was doing all above steps with explicitly declared to be provider specific type objects and I had no problem. Technically speaking I’m exercising exact same skeleton as I used to, but converting them to a generic type of objects (not provider specific). And I can’t make it work when there is a parameter and IDbCommand objects. There is somewhere, probably, I’m missing something.

If someone can direct me, please, where is my mistake?

Thanks in advance, Sam

推荐答案

如果我正确地解释了这个问题，那么当您遍历字段名和值时，您可能不会得到相应的元素?

因此，基本上可以使用排序列表或字典(对象，字段(值)，值)来代替哈希表，这可能会有所帮助.

另外，我个人将为参数创建一个单独的类，该类将包含所有相关信息，例如字段名称，值，类型等，并将该类的实例包含到单个列表中.然后在构造语句时将使用该列表.由于该类本身对任何表均不可知，因此不需要使用反射，这也许也可以提高代码块的性能.

If I interpreted the question correctly, could it be possible that when you loop through the field names and the values, you''re not getting the corresponding elements?

So basically instead of using hash table, perhaps using sorted lists or a dictionary of strin, object (field, value) would perhaps help.

Also, personally I would create a separate class for a parameter which would contain all the relevant information such as field name, value, type and so on and include the instances of that class to a single list. that list would then again be used when constucting the statements. Since the class itself is agnostic to any tables etc, it wouldn''t be necessary to use reflection and that would perhaps enhance the performance of the code blocks also.

好，Mika先生，我们知道了！

我在这里错过了两点.

1.即使这两个数据集是独立设置的，sql语句字段的顺序和添加的参数名称也很重要:例如
"INSERT INTO t1(f1，f2，f3 ...)值(?，?，?...)"必须与IDbCommand.Parameters.Add(param)顺序匹配，这表示
param.ParameterName = f1
param.Value = v1
cmd.Parameters.Add(param)

param.ParameterName = f2
param.Value = v2
cmd.Parameters.Add(param)

param.ParameterName = f3
param.Value = v3
cmd.Parameters.Add(param)

等

2.我仍然必须维护我已评论的"param.DbType".因此，我启用了它.

因此，我用类属性和相应的字段名创建了一个"Dictionary(String，String)"，然后构造了另一个带有属性名称和相应值的"Dictionary(of String，Object)"(请注意，我将循环第一个字典获取属性值并构建第二个字典，因此我保留了列表顺序). ``DAL.SCRFL.GetParamStatement''从我的第一个字典构建sql语句，然后``DAL.SCRFL.GetProperties''返回第二个字典.

有效！
谢谢Mika

关于您对参数的评论(将它们作为一个单独的类).我完全同意你的看法.我实际上已经在进行中，但是由于遇到了这个问题，我不得不进行一些工作/测试，然后更新原始类.

再次感谢，
Sam

OK, Mr. Mika, WE GOT IT!

I was missing two points here.

1. Order of sql statement fields and added parameter names ARE matter even though these two data set independently: e.g.
"INSERT INTO t1 (f1, f2, f3...) values (?, ?, ?...)" must match IDbCommand.Parameters.Add(param) order, meaning
param.ParameterName = f1
param.Value = v1
cmd.Parameters.Add(param)

param.ParameterName = f2
param.Value = v2
cmd.Parameters.Add(param)

param.ParameterName = f3
param.Value = v3
cmd.Parameters.Add(param)

etc.

2. I still have to maintain ''param.DbType'' that I had commented. So, I enabled it.

So, I made a ''Dictionary(of String, String)'' with class property and corresponding field names then I constructed another ''Dictionary(of String, Object)'' with property name and corresponding values (note that I loop the 1st dictionary to get property values and build the 2nd one, so I kept the list order). The ''DAL.SCRFL.GetParamStatement'' builds the sql statement from my 1st dictionary and The ''DAL.SCRFL.GetProperties'' returns the 2nd dictionary.

Works!
Thanks Mika

About your comment regarding parameters (making them as a separate class). I totally agree with you. I Actually had one already in progress but because I had this problem I had to make some working/testing sub then update the original class.

Thanks again,
Sam

以下是业务类中的几种方法:


公共共享函数GetProperties(Of T)(_ classObject As T)As Dictionary(Of String，Object)
Dim cT As Type = GetType(T)
字典中的Dim aL(字符串，字符串)= GetPropertyList(T)()
昏暗的pL作为新字典(字符串，对象)
对于aL.Keys中的每个s作为字符串
昏暗v作为对象= _
cT.InvokeMember(s，_
(Reflection.BindingFlags.GetField或_
Reflection.BindingFlags.GetProperty)，_
没事，
_classObject，
没事)
pL.Add(s，v)
下一个
返回pL
最终功能

公共共享函数GetPropertyList(Of T)()As Dictionary(Of String，String)
Dim cT As Type = GetType(T)
昏暗的pL()为Reflection.PropertyInfo = cT.GetProperties()
如果pL什么都不是，并且pL.Count> 0然后
昏暗的cL作为新字典(字符串，字符串)
对于每个pI作为pL中的Reflection.PropertyInfo
Dim aL()As Attribute = pI.GetCustomAttributes(GetType(AttColumnName)，True)
如果aL不算什么并且aL.Count = 1则
昏暗的cA作为AttColumnName = DirectCast(aL(0)，AttColumnName)
cL.Add(pI.Name，cA.ColumnName)
如果结束
下一个
返回cL
如果结束
一无所有
最终功能

我用来提供属性的属性:
< attributeusage(attributetargets.property> _
公共类AttColumnName
继承属性

私有m_ColumnName作为字符串= String.Empty
私有m_IsPrimaryKey为Boolean = False

公共属性ColumnName()作为字符串
获取
返回m_ColumnName
结束获取
设置(ByVal值作为字符串)
m_ColumnName =值
端套
最终财产
公共属性IsPrimaryKey()作为Boolean
获取
返回m_IsPrimaryKey
结束获取
设置(ByVal值作为布尔值)
m_IsPrimaryKey =值
端套
最终财产

Public Sub New(_columnName作为字符串)
Me.new(_columnName，False)
结束子
Public Sub New(_columnName作为字符串，_isPrimaryKey作为布尔值)
Me.ColumnName = _columnName
Me.IsPrimaryKey = _isPrimaryKey
结束子

结束类

业务对象的样本属性:
< attcolumnname("clinicid"，> _
公共属性ClinicId()作为字符串
获取
返回m_ClinicId
结束获取
设置(值作为字符串)
m_ClinicId =值
端套
最终财产


等等.

Here are few methods from business class:


Public Shared Function GetProperties(Of T)(_classObject As T) As Dictionary(Of String, Object)
Dim cT As Type = GetType(T)
Dim aL As Dictionary(Of String, String) = GetPropertyList(Of T)()
Dim pL As New Dictionary(Of String, Object)
For Each s As String In aL.Keys
Dim v As Object = _
cT.InvokeMember(s, _
(Reflection.BindingFlags.GetField Or _
Reflection.BindingFlags.GetProperty), _
Nothing,
_classObject,
Nothing)
pL.Add(s, v)
Next
Return pL
End Function

Public Shared Function GetPropertyList(Of T)() As Dictionary(Of String, String)
Dim cT As Type = GetType(T)
Dim pL() As Reflection.PropertyInfo = cT.GetProperties()
If pL IsNot Nothing AndAlso pL.Count > 0 Then
Dim cL As New Dictionary(Of String, String)
For Each pI As Reflection.PropertyInfo In pL
Dim aL() As Attribute = pI.GetCustomAttributes(GetType(AttColumnName), True)
If aL IsNot Nothing AndAlso aL.Count = 1 Then
Dim cA As AttColumnName = DirectCast(aL(0), AttColumnName)
cL.Add(pI.Name, cA.ColumnName)
End If
Next
Return cL
End If
Return Nothing
End Function

an attribute that I use to furnish the property:
<attributeusage(attributetargets.property> _
Public Class AttColumnName
Inherits Attribute

Private m_ColumnName As String = String.Empty
Private m_IsPrimaryKey As Boolean = False

Public Property ColumnName() As String
Get
Return m_ColumnName
End Get
Set(ByVal value As String)
m_ColumnName = value
End Set
End Property
Public Property IsPrimaryKey() As Boolean
Get
Return m_IsPrimaryKey
End Get
Set(ByVal value As Boolean)
m_IsPrimaryKey = value
End Set
End Property

Public Sub New(_columnName As String)
Me.new(_columnName, False)
End Sub
Public Sub New(_columnName As String, _isPrimaryKey As Boolean)
Me.ColumnName = _columnName
Me.IsPrimaryKey = _isPrimaryKey
End Sub

End Class

a sample property from business object:
<attcolumnname("clinicid",> _
Public Property ClinicId() As String
Get
Return m_ClinicId
End Get
Set(value As String)
m_ClinicId = value
End Set
End Property


etc.

这篇关于如何添加参数和执行通用IDbCommand的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！