Databases
StackOverflow2013
Postsdbo
POHow to add parameters and execute a generic IDbCommand
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to add parameters and execute a generic IDbCommand
    primarykey
    data
    text
    <p>Here is my problem in detail. 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 layer 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:</p> <pre><code> 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 &gt; 0) Then Return True End If End Using Return False End Function </code></pre> <p>So, <code>DAL.SCRFL.GetParamStatement</code> returns string formatted as <code>INSERT INTO t1 (f1, f2, f3...) values (?, ?, ?...)</code> for insert and appropriate strings for update, delete, select statements. All are done with reflection. There is no syntax error here. I can manually execute returned values through direct provider type commands. The <code>DAL.SCRFL.GetProperties</code> method returns a hashtable formatted as key=property (field), value=field value.</p> <p>Now, I need to create parameters for each property and add that to my command parameters then execute it. This attempt you will see in my code (I’m creating parameters for each propert/value pair by looping the hash table). However at the end I'm getting an exception with <code>Data type mismatch in criteria expression.</code> description. I've tried adding <code>type</code> property to parameter object, <code>size</code> etc., all was unsuccessful (I commented them). I tried changing <code>param.Value = k.value</code> to <code>param.Value = If(IsDBNull(k.value), DBNull.Value, k.value)</code> thinking that this might be the problem, though <code>k.value</code> is from my business class and I intentionally prevent from null values. Nothing worked! here is the test; business class returned value from <code>DAL.SCRFL.GetParamStatement</code> call: The test is done for OleDb/Access database and, as you can see, I enclosed the Memo field in single quotes. My reflection methods read class properties' attributes (which I set to be table field names) and <code>DAL.SCRFL.GetParamStatement</code> builds basic sql statements for insert, update, delete and select use. <code>AdapterCommandType</code> is a built in enum type for it).</p> <pre><code>INSERT INTO Clinics (ClinicId, ClinicName, Phone, Fax, FederalId, DateContracted, Address, City, State, Zip, Inactive, [Memo], DateEntered, EnteredBy, DateModified, ModifiedBy) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) </code></pre> <p>Note that 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, <code>InvokeSql</code>, method works w/out a single warning (Rouphly: <code>cnn As IDbConnection = CreateConnection(), cmd = CreateCommand(_cmdText, cnn), cmd.ExecuteNonQuery()</code> where <code>_cmdText</code> is the sql statement. 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 <code>DataFactory.CreateCommand(s, cnn)</code> returns generic IDbCommand).</p> <p>Prior to my DAL development, I was doing all above steps manually though all objects (commands, connections etc.) were explicitly declared to be provider specific types. Technically speaking I’m exercising exact same scenarios as I used to with generic type of objects (not provider specific). But I can’t make it work, there is somewhere, probably, I’m missing something.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USjonsca
    1. USSam Saarian
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to add parameters and execute a generic IDbCommand
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload