[Mono-list] Parameters & SQLite

[Jay R. Wren]

Adam Tauno Williams wrote:
>> It is my blog, but take a look anyway. 
>> http://little.xmtp.net/blog/2005/10/21/proper-data-abstraction-in-net-and-mono-applications/
>> http://little.xmtp.net/blog/2005/11/22/more-proper-data-abstractions-in-net-and-mono-applications/
>> The short answer is "yes" parameters are supported.  There are examples
>> at the above URLs.
>> A short example:
>> |IDbCommand idcSelect = conn.CreateCommand();
>> idcSelect.CommandText = "select col from table where othercl=@someting";
>> ||IDataParameter param = idcSelect.CreateParameter();
>> Cmd.Parameters.Add(param);
>> param.ParameterName = "@someting";
>> param.DbType = DbType.String;
>> param.Value = "whatever something would equal";
>> The above is more geared to 1.1, since 2.0 has DbConnection and the
>> ADO2.0 db provider stuff.
>>     
>
> Excellent!
>
> I'm trying to create a routine that automatically stores a Hashtable (a
> result from an expensive RPC) into a database as a cache.  Each key in
> the Hashtable corresponds to a column in the table, which the key
> entityName containing the name of the table.  The routine compiles fine
> but then just hangs when ExecuteNonQuery is called.
>
> ------
> IDbCommand command = connection.CreateCommand();
> int count = 0;
> string columnList = "";
> string valueList = "";
> foreach(string key in dictionary.Keys)
> {
>  IDataParameter	parameter;
>   if (count > 0)
>   {
>     columnList = columnList + ", ";
>     valueList = valueList + ", ";
>   }
>   columnList = columnList + key;
>   valueList = valueList + "@" + key;
>   parameter = command.CreateParameter();
>   command.Parameters.Add(parameter);
>   if (dictionary[key].GetType().FullName == "System.String")
>   {
>     parameter.ParameterName = "@" + key;
>     parameter.DbType = DbType.String;
>     parameter.Value = (string)dictionary[key];
>   } else if (dictionary[key].GetType().FullName == "System.Int32")
>     {
>       parameter.ParameterName = "@" + key;
>       parameter.DbType = DbType.Int32;
>       parameter.Value = (int)dictionary[key];
>     } else if (dictionary[key].GetType().FullName == "System.DateTime")
>       {
>         parameter.ParameterName = "@" + key;
>         parameter.DbType = DbType.DateTime;
>         parameter.Value = (DateTime)dictionary[key];
>       } else throw new System.Exception("Unhandled type in response");
>   count++;
> }
> command.CommandText = 
>   "INSERT INTO " + (string)dictionary["entityName"] +
>   "  (" + columnList + ") VALUES (" + valueList + ");";
> Console.WriteLine("Dictionary.StoreHash: Executing non-query");
> command.ExecuteNonQuery();
> Console.WriteLine("Dictionary.StoreHash: Non-query complete");
> command.Dispose();
>   
I could not duplicate the error, but I was able to get something which
does this working.  What fixed it for me was to move the
command.Parameters.Add(parameter) to after the parameter object was int
the desired state.  I don't know if Parameter objects are immutable, or
if command.Parameters.Add() makes a copy of the paremeter, or what, but
this fixed it for me.  It may be worth comparing this behavior to the MS
platform and see what happens.

And of course I used command line sqlite3 to create the table, and after
execution did a .dump to verify it worked.  It did.

My connection string in the ProviderFactory string:
                <add key="test"
value="factory=Mono.Data.SqliteClient;URI=file:sqlitetest.db,version=3" />


My (only slightly modified) code:
/*  CREATE TABLE testtable ( int1 int, str1 nvarchar(1000), entityName
nvarchar(1000) );
 */
using System;
using System.Data;
using System.Collections;
using Mono.Data;

class MonoSqlLiteTest
{
        public static void Main(string[] args)
        {
                Hashtable dictionary = new Hashtable();
                dictionary.Add("str1","blah");
                dictionary.Add("entityName","testtable");
                dictionary.Add("int1",2);


                IDbConnection connection =
ProviderFactory.CreateConnectionFromConfig("test");

                IDbCommand command = connection.CreateCommand();
                int count = 0;
                string columnList = "";
                string valueList = "";
                foreach(string key in dictionary.Keys)
                {
                  IDataParameter parameter;
                  if (count > 0)
                  {
                    columnList = columnList + ", ";
                    valueList = valueList + ", ";
                  }
                  columnList = columnList + key;
                  valueList = valueList + "@" + key;
                  parameter = command.CreateParameter();
                  if (dictionary[key].GetType().FullName == "System.String")
                  {
                    parameter.ParameterName = "@" + key;
                    parameter.DbType = DbType.String;
                    parameter.Value = (string)dictionary[key];
                        Console.WriteLine("string parameter
added:"+key+" "+dictionary[key]);
                  } else if (dictionary[key].GetType().FullName ==
"System.Int32")
                  {
                    parameter.ParameterName = "@" + key;
                    parameter.DbType = DbType.Int32;
                    parameter.Value = (int)dictionary[key];
                        Console.WriteLine("int parameter added:"+key+"
"+dictionary[key]);
                  } else if (dictionary[key].GetType().FullName ==
"System.DateTime")
                  {
                    parameter.ParameterName = "@" + key;
                    parameter.DbType = DbType.DateTime;
                    parameter.Value = (DateTime)dictionary[key];
                        Console.WriteLine("datetime parameter
added:"+key+" "+dictionary[key]);
                  } else
                        throw new System.Exception("Unhandled type in
response");

                  command.Parameters.Add(parameter);
                  count++;
                }
                command.CommandText =
                  "INSERT INTO " + (string)dictionary["entityName"] +
                  "  (" + columnList + ") VALUES (" + valueList + ")";
                Console.WriteLine("Dictionary.StoreHash: Executing
non-query:" + command.CommandText);
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
                Console.WriteLine("Dictionary.StoreHash: Non-query
complete");
                command.Dispose();
        }
}

--
Jay