Quantcast

Semantics regarding command instances and queries in the C# client

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Semantics regarding command instances and queries in the C# client

Joseph L. Casale
Typically I open a connection and a command instance and associate instantiated
parameters with the command instances, then process in a loop simply changing
parameter values.

I have a situation when I am needing to insert and select based on several criteria
and that practice looks a bit ugly.

What are the guidelines surrounding command instances, parameters and query
text with the client in terms of best practice when performance is a consideration?

Thanks,
jlc
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Semantics regarding command instances and queries in the C# client

Clemens Ladisch
Joseph L. Casale wrote:
> that practice looks a bit ugly.

Show some example.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Semantics regarding command instances and queries in the C# client

Joseph L. Casale
From: sqlite-users <[hidden email]> on behalf of Clemens Ladisch <[hidden email]>
Sent: Friday, April 28, 2017 2:51 AM
To: [hidden email]
Subject: Re: [sqlite] Semantics regarding command instances and queries in the C# client
   
> Show some example.

Hey Clemens,
Check out this paste for a quick script quality console app I wrote to help a user consume
some data from a collection of csv files into an SQLite database.

https://paste.ofcode.org/bFQnrpeQdCkqUES7zfjuZe

Each row from the CSV required several tables with relationships to be populated. One could
certainly abstract this out into an api, but that can have impacts on performance for large
batch processing if you are creating parameters for every insert rather than reusing them.

In simple cases, the code is trivial but in this example, it looks terrible given the number of
Command instances...

Thanks for any opinions,
jlc
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Semantics regarding command instances and queries in the C# client

Clemens Ladisch
Joseph L. Casale wrote:
> Each row from the CSV required several tables with relationships to be populated. One could
> certainly abstract this out into an api, but that can have impacts on performance for large
> batch processing if you are creating parameters for every insert rather than reusing them.
>
> In simple cases, the code is trivial but in this example, it looks terrible given the number of
> Command instances...

.NET's handling of SQL parameter objects is horribly verbose.

If you had access to the SQLite C API, you could write a helper function
that automatically created all the parameter objects for you.  But even
so, you can write a helper function with a list of parameter names:

  var attributeCommand = CreateCommandHelperFunction(
            connection,
            @"INSERT INTO Attribute
                  (Type, Value, AccountId)
              VALUES
                  (@Type, @Value, @AccountId)",
            "@Type", "@Value", "@AccountId"
  );

If the returned object is your own wrapper, you can also make the
parameter binding easier; something like this:

  attributeCommand.bindParameter(1,        type);   // or:
  attributeCommand.bindParameter("@Value", value);


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...