Generate C# code to connect with SQL2005 Stored Procedures...

Search on MSDN. Rcenetly there was an article about it. MS developed some toolkit or some library for exactly this purpose.

You can use Enterprise Library
http://www.microsoft.com/downloads/details.aspx?FamilyId=5A14E870-406B-4F2A-B723-97BA84AE80B5&displaylang=en

One you have this then you can use code like the following on your code behind page:

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

using Microsoft.Practices.EnterpriseLibrary.Data;

...


Database Mydatabase = DatabaseFactory.CreateDatabase("Myconnectionstring");

DataSet ds = Mydatabase.ExecuteDataSet("MystoredProc", param1, param2, param3);

For example:

DataSet ds = Mydatabase.ExecuteDataSet("PersonSearch", firstName, lastName);


The code automatically queries the database to see what your stored proc looks like and then assigns the parameters.

You could alternatively still explicitly do so like this:

Database Mydatabase= DatabaseFactory.CreateDatabase("Myconnectionstring");
DbCommand dbCommand = Mydatabase.GetStoredProcCommand("PersonSearch");
db.AddInParameter(dbCommand, "FirstName", DbType.String, firstname);
db.AddInParameter(dbCommand, "LastName", DbType.String, lastName);

DataSet ds = db.ExecuteDataSet(dbCommand);

Which still makes you define parameters, but saves that hit to the database.  It depends if you want less traffic to the DB server or to do less work in coding.

Oh there are also more like

Mydatabase.ExecuteScalar - for returning scalar values

and

Mydatabase.ExecuteNonQuery - for stored procs that do not return anything back

etc...

All built in to the Enterprise Libarary wrapper for connections.

You also can create more specific databases like SQL databases if you need that, but the above works for SQL 2005 and so is more portable if the DB system ever changes.

Hi guys...
I know the Enterprise Library for a long time, and I'm actually using it on this project.
I'm not using Datasets... I have my own data layer based on classes.

naveenkohli :
Can't you give me any more details about that?

Thanks,
Alex

I have a data layer too.  In general in an N-tier system the data layer gets the raw data from the sql server.  Then a business layer converts the ddata into the classes which the higher layers, like presentation layer, use.

The datat layer's job is to get the data.  This may be a dataset, it may be an XML Document, etc.  It is responsible for knowing where to go to get the data.  Then either it or the next layer is responsible for turning the raw data into objects defined by your classes - and vice versa.  The higher layers then access those class objects.

Hi mrichmon ...
I don't have any doubts about that... I can even describe my current scenario.

Currently I have a 4 tier system that operate on the following schema:
DAL <-> BO <-> BL <-> UI

where:
DAL = DataAccess Layer
BO = Business Objects
BL = Business Layer
UI = User Interface (that can be both Win or Web)

BO is an intemediate layer for connection between DAL and BL.
It reflects exactly the database, having classes as tables and properties as those tables columns.
This is useful because, in my scenario, DAL and BL layers may be on different machines.

-- // --

Now to my question...
On the DAL I have static classes representing the tables on the database with only static methods on them.
Each method represents an action of that object to the database... SELECTs, DELETE, UPDATE, INSERT.
The is no query on my C# code, just calls to stored procedures (SP's) on the database (this is very useful not only to performance but also for maintenance, where some problems can be solved without the need of recompiling the application).

My pain is exactly here...
I need to code every single parameter the SP needs to add to the SQLCommand object.
SELECTs and DELETEs are easy but most my INSERTs and UPDATEs have a lot of parameters.

I would like to have a tool that could generate those parameters in C# code... :)

Many thanks...
Alex :p

Check this out.. I think this is the article I was refering to.

http://www.microsoft.com/indonesia/msdn/SPOIL.aspx

Wow... this looks nice...

I usually use a 3rd party DAL that actually is embeded on the BL.
It's called XPO and is from a great company called DevExpress: http://www.devexpress.com/Products/NET/XPO/
(You may take a clese look at all the other produts... they're all great!)

The damn is that sometimes customers want the apps made exclusivelly on top of the .net Framework, with no 3rd party controls whatsoever...

naveenkohli :
This looks very nice... actually too nice for me right now! :)
I can't implement it on this project... The project is in fact 16 apps and we already have 12 done, so I cant just made some in a way and the others on another.
But again... I haven't tried it yet but it looks very nice... Thanks!


Alex :p

I ended up building my own tool to do this.
None of the replies did actually answer my question.

Btw, CodeSmith (http://www.codesmithtools.com/) have something that does what I was looking for.
Now they actually have a .netTiers engine to generate nTier models based on the Microsoft's DataAccessBlock.

Thanks,

Alex