Ado.net data provider is throwing an error when I try to return a command from a method

Question

I'm sorry for the bad title, not certain how to describe the issue completely.

I'm using SQLBase NET data provider.

When I attempt to create a command and add parameters, it is throwing an exception

Object not set to instance of an object

However, when I create a command outside of my object, I can add parameters with no issues.

SQLBaseDatabase class is a wrapper I wrote that will basically return a connection and create a command.

var db = new SQLBaseDatabase(ConfigurationManager.ConnectionStrings["UDSConnectionString"].ConnectionString);

This code works: I created this code in my HomeController as a test. I can add parameters with no problem.

var command = new SQLBaseCommand();
command.Connection = db.GetConnection() as SQLBaseConnection;
command.Parameters.Add(db.GetParameter(":1", "1234", DbType.String));

This code fails:

var parameters = new List<IDataParameter>();
parameters.Add(db.GetParameter(":1", "1234", DbType.String));
var cmd = db.GetCommand(db.GetConnection(), "Select * From DX_CODES Where (DX = :1)", parameters);

When I try to add parameters in this method via the passed in parameters, it throws the object not set error.

public IDbCommand GetCommand(IDbConnection connection, string sql, IEnumerable<IDataParameter> parameters)
{
    var command = new SQLBaseCommand(connection as SQLBaseConnection);            

    foreach (var parameter in parameters)
        command.Parameters.Add(parameter); // This throws an error. Command.Parameters is read only.

    command.CommandText = sql;

    return command;
}

The command parameters is read only

Here is my method that creates the parameter

 public IDbDataParameter GetParameter(string name, object value, DbType type)
 {
     return new SQLBaseParameter(name, type, value);
 }

I'm not sure if I'm fundamentally doing some wrong in c# that could be causing this error. Or how I can fix it considering it works in one case, but not the other.

** Update ** Same issue happens with the SqlServer ADO.Provider.

you are passing / referencing the Params incorrectly.. use "{0}",parameters also try to use a better name opposed to using lower case reserved names.. and replace the Parameters.Add() method with Parameters.AddWithValue(@yourParam, yourValue); — MethodMan, Dec 18 '12 at 20:53
Command.Parameters dot anything is throwing an error because there is no instance. Why is it not creating the instance in my class, but outside my class it works fine. — sinclairchase, Dec 18 '12 at 20:55
@DJKRAZE that's not the problem. `{0}` is string formatting, this is using parameterized queries. `AddWithValue` is not a member of `IDbCommand` or `SqlBaseCommand`, so that's out too. — Jason Meckley, Dec 18 '12 at 20:55

max · Answer 1 · 2012-12-18T21:07:01.180

1

I would try initializing the SQLBaseCommand in your method the same way you did in the first example.

var command = new SQLBaseCommand();    
command.Connection = connection as SQLBaseConnection;

There could be a very different implementation of that constructor with and without constructor parameters. For example, the Parameters property may not get newed implicitly.

edited Dec 18 '12 at 21:07

answered Dec 18 '12 at 20:56

max

716
7
22

something else to consider, since you already have the connection in the `db` object. Change the signature to `db.GetCommand(string sql, IEnumberable parameters)`. then you can refernece the concrete connection within the db object itself. remove the need for casting and better encapsulation. – Jason Meckley Dec 18 '12 at 20:58

Ado.net data provider is throwing an error when I try to return a command from a method

1 Answers1