0

ASP.net C# 3.5 Framework, working in Visual Studio 2008 currently.

What I want is a Generic Data Access Class. I have seen several of them but what I want is to see one that I pass the Connection String and the SQL statement and it will return a List of Objects or when only one item an Object or when no response needed a boolean to let me know if it succeeded?

I hope this makes sense if not let me know and I will explain more.

Nathan Stanford
  • 1,336
  • 3
  • 23
  • 37

3 Answers3

3

You don't have to roll your own. Check out Microsoft.Data.dll. It's somewhere in between the low level ADO.NET code (using SqlConnection, SqlCommand, etc.) and a higher level ORM framework like LINQ to SQL or nHibernate:

Introduction to Microsoft.Data.dll

Then your access becomes as easy as:

var db = Database.OpenConnectionString(connString);
for(var user in db.Query("select * from users"))
{
    var username = user.Username;
    // Do other stuff here
}
Justin Niessner
  • 242,243
  • 40
  • 408
  • 536
  • I love the black magic of C# ... would love to know how this works behind the scenes. – Nix Aug 05 '10 at 14:37
  • Do you have to install MDAC to get that to work? What dll/namespace do you have to reference (i am testing this out now). – Nix Aug 05 '10 at 15:45
  • @Nix - If I remember correctly, Microsoft.Data.dll is part of WebMatrix: http://www.asp.net/webmatrix – Justin Niessner Aug 05 '10 at 15:50
3

Have you looked at LINQ-to-SQL (wouldn't recommend this though, it's not going to get much attention from Microsoft in the future), LINQ-to-Entities (this is what Microsoft is dedicating resources to in this area for the foreseeable future), NHibernate, or any other ORM(-ish) libraries for .NET?

Any of them will have mechanisms to allow you to create your queries (in a more typesafe way, I might add) and get the results back in an object form (or get results from procedures if you are dealing with scalar results).

Now, it is the case that it doesn't allow you to pass the query string, but if you are dynamically generating this, then more often than not, that's a really bad thing, as most composed dynamic SQL statements, if not using parameters (which you aren't indicating you are) are subject to SQL injection attacks.

When using any of the libraries above, you can compose your query, and the parameters will be parsed and the dynamic sql under the covers will be generated safely, not subject to SQL injection attacks.

casperOne
  • 73,706
  • 19
  • 184
  • 253
  • So if Microsoft is moving from LINQ-to-SQL to LINQ-to-Entities how much longer tell the move from that? Should I even worry about this? I might have to get NHibernate and try it out. – Nathan Stanford Aug 05 '10 at 14:54
  • 1
    @Nathan Stanford: I'd concentrate on LINQ-to-Entities or NHibernate. I prefer the LINQ-to-Entities stack, because there is better integration with other MS technologies (RIA, for example), but LINQ-to-SQL would be my last resort. – casperOne Aug 05 '10 at 15:17
1

Check out my answer to this question:

Fastest method for SQL Server inserts, updates, selects

It's not a complete class, but it lays out the groundwork for a nice generic implementation that will work with linq-to-objects.

Community
  • 1
  • 1
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794