A basic and focused utility to send SQL statements with ADO.NET

  • Article

Sometimes, I just need to quickly send SQL queries or commands to a SQL database one by one or in group.

Of course there are serious and full-fledged utilities from SQL Server to doing just that, and have their own use cases.

The point of doing my own basic and focused utility is to test and hence to gain a sense of trust on a particular way of using a basic piece of SQL data access functionality with ADO.NET. That is to say, while in the process of designing a –say– enterprise architecture of a software service for a given customer and want to check, in that particular point in time how it feels some aspect or aspects of my current design decision that involve SQL data access and if any of those full-fledged utilities are handy, then I just invoke my basic and focused utility, like:

 >query "select au_fname,city from authors"
CLR:2.0.50727.42
dbcon:Data Source=someserver;Initial Catalog=pubs;Integrated Security=True;Connection Timeout=120
Query: select au_fname, city from authors

rowset:

au_fname        city
Johnson        Menlo Park
Marjorie      Oakland
Cheryl       Berkeley
Michael     San Jose

Or sending a SQL command:

 >query "cmd:exec a_stored_procedure"
CLR:2.0.50727.42
dbcon:Data Source=someserver;Initial Catalog=pubs;Integrated Security=True;Connection Timeout=120
Non query: exec a_stored_procedure

affected rows count: -1

Or executing several queries and commands stored in a text file:

 >query @sequence.sql

Or a combination of both

 >query @sequence.sql "cmd:exec a_stored_procedure" "select au_fname,city from authors"

C# 2.0 iterators were handy for doing the argument processing of this utility.