ADO.net without writing SQL (esp. WHERE)

Question

I have this idea that using SQL VIEWS to abstract simple database computations (such as a count on a relation) is sufficient, and you don't need procedures (== procedural code)

A simple sql view + a where clause >> a stored procedure with parameters sometimes

While making this point I imagined a way of retrieving table/view data without writing SQL and without writing the where clause..

But, to my surprise, there does not seem a way to accomplish this in ADO.NET 2.0 or later.

Let me tell you what I tried:

• SqlDataAdapter + SqlCommandBuilder still requires you to write "SELECT ... FROM" and the WHERE CLAUSE in strings (plus, if you put the 'where', you dont have much use of Update/Insert/DeleteCommand)

• typed DataSets only allow you to retrieve _entire DataTable_s and then applying filters to them. Filters are strings, without escaping aid... (must double the single quote!)

• SQL to Entities looked promising but they seem to: be limited to MSSQL, generate bloated SQL queries, generate a whole new stack of DAOs (besides the existing Domain Model classes), reqiuire .net 3.5+ for all this etc. (that is, all these are disadvantages for me)

Other ORMs have similar problems as SQL to Entities.

What I'm looking for is a strong-typed method of accessing database tables/views that:

• doesn't come with another set of DAOs (K.I.S.S)
• allows me to query a table without writing "SELECTs" in strings (strong-typed)
• allows me to filter(WHERE) a table with properly-escaped parameters (and without retrieving the whole data beforehand)
• can later issue updates/inserts/deletes

I am fairly new to .Net but not stupid: does this exist?

Thanks.

Answer 1

If you don't want to write the WHERE clause, one way is to use a Filter object and add the conditions you want. For example:

        var sc = new Filter();
        sc.Add("Contacttitle", "Sales Agent");
        sc.Add("city", "london", Logical.Or);
        var customers = D2Bk.Fetch(sc, new Customers());

But you don't want to use DAOs (Customers above is such), so you would have to write the SQL statement and specify the where clause:

        DataSet ds = D2Bk.Fetch("SELECT * FROM Customers WHERE Contacttitle=@PAR1 OR City=@PAR2", "Sales Agent", "london");

Answer 2

I did something like this with a stored procedure once. Basically, I wanted to specify any permutation of fields to match on in my WHERE clause, but I didn't want to write 100 sprocs with slightly different param lists and where clauses.

So, I did something like this:

CREATE PROCEDURE [GetSimpleCustomers]
(
@ID varchar(50) = null,
@Name varchar(50) = null,
@IsActive  bit = null,
@Address1 varchar(50) = null,
@Address2 varchar(50) = null,
@City varchar(50) = null,
@State varchar(50) = null,
@Zip varchar(50) = null
)
AS

SELECT ID, Name, IsActive, Address1, Address2, City, State, Zip
FROM SimpleCustomerExample
WHERE (ID = @ID OR @ID is NULL)
AND (Name = @Name OR @Name is NULL)
AND (IsActive = @IsActive or @IsActive is NULL)
AND (Address1= @Address1 or @Address1 is NULL)
AND (Address2= @Address2 or @Address2 is NULL)
AND (City= @City or @City is NULL)
AND (State= @State or @State is NULL)
AND (Zip= @Zip or @Zip is NULL)

This will let you call the sproc in your code and only pass the params you are interested in filtering on, and the rest will not be factored in if you leave them null.

So, you can do something like

public List<SimpleCustomer> GetAllCustomersFromOhio()
{
    List<SimpleCustomer> list = new List<SimpleCustomer>();
    using (SqlCommand cmd = new SqlCommand(blah blah))
    {
        cmd.Parameters.AddWithValue("State", "Ohio");//or "OH" depending on your convention
        using(IDataReader oDR = cmd.ExecuteReader())
        {
             //hydrate your list of SimpleCustomers from the record set.
        }
    }
    return list;
}

EDIT: In reply to comment: You could easily enough alter the GetSimpleCustomers to be DeleteSimpleCustomers by changing the

SELECT <columns> FROM SimpleCustomers

to

DELETE FROM SimpleCustomers

and keep the same logic. The same is true for an Update. Also, I'll answer a question with a question: How many tables do you have that actually need this level of custom filtering? The syntax would be so similar you could pound it all out in a day (or less if you hammered together a simple script to write it for you).

Answer 3

I would assume that you've looked at LINQ and ADO.Net Data Services and these do not meet some of your requirements?

Native ADO.Net is a database provider, and therefore it provides a direct SQL interface into the underlying data sources. There is various CRUB based solutions out there that simulate what you suggest to various degrees.

We have a strong internal trend to leave the database to the Database team and use Webservices as a main interface to our databases, mainly because of a Delphi codebase that still needs to be supported.

Actually can't believe I forgot to add ADO.Net Entity Framework which is used by ADO.Net Data Services among others. There is also a LINQ to Entities provider.

Answer 4

I don't really believe that what you want to do is achievable without using some sort of ORM, or a specialized DSL with a compiler that somehow knows about your database schema, type/column information, etc.

Take into account that C# is a general purpose language, and its compiler is totally unaware of your database types, that's why you cannot bind them without using some abstraction layer, which usually involves ad hoc SQL queries(strings), NHibernate or similar mapping files (more strings) and/or DAOs.

Answer 5

FluentADO

Answer 6

If you are using strongly-typed DataSets, you can create parameterized queries in the Visual Studio Editor by adding identifiers prefixed with @ in the query. Create a DataSet XSD file in Visual Studio and create a new table called Products, then add a new query to it.

For example:

select * from Products where Category = @category;

This will autogenerate methods for filled datasets or getting datatables that take the extra parameter. It will also handle propery escaping the strings (uses parameters on the command objects). I used this to create some super simple prototype web apps fairly quickly.

Answer 7

I recently wrote a query 'framework' for generating SQL where clauses.

The primary component is a BaseQueryArgs class with a ToWhereClause() function that uses reflection to convert properties into string sections. This needs to handle the work of escaping and properly formatting values.

Any class inheriting BaseQueryArgs simply needs to declare public properties, and you end up with a strongly typed query object. For optional properties, you make the value nullable (ref type or Nullable<>) and the SQL generator filters out null values.

You can use custom attributes to define extra features for each property:

• custom column name that differs from the property name
• custom value handling (such as a date value being used as a BETWEEN test expression)

This can be used to create queries with a strongly-typed query object like so:

MyCustomQueryArgs args = new MyCustomQueryArgs
{
    ProductFamilyID = 17,
    Region = Regions.Northwest,
    Active = true
};

List<Product> product = QueryProcessor.GetProductsWhere(args);

GetProductsWhere() would obviously call some data method that accesses the view with the generated SQL.

I don't have a solution for updates/deletes, but it doesn't seem that hard to write a method that converts an object instance into a SQL statement using a switch or attribute to determine table name.

This is very "roll your own", but that gives you the freedom to customize it for your needs, and doesn't include lots of heavy ORM/DAO wrapping.

Answer 8

Have a look at Mindscapes Lightspeed products

It builds stongly typed LINQ queriable models that results in efficient SQL code accross a variety of database engines and includes Memcached and Lucene support

Answer 9

I have used XPO on several projects and their newer version has better support for queries.

http://www.devexpress.com/Products/NET/ORM/

The implementation, like all of them is not without its drawbacks however.

Answer 10

I use Data Abstract for my projects.

Answer 11

Subsonic has a fairly lightweight query tool that you can use to directly query against the database with a Query object that abstracts the SQL. If you want to, you can also use its code generation facility to map your database tables to POCOs, or to only create a strongly typed schema (for column/table names and so on).