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

Hi...

Do you guys know a good tool that generates c# code to connect with stored procedures?
It's such a pain to add all taht parameters code... with the right dimentions and column types... ahhhhhh
And now I have about 80 SP's to connecto to... FEAR!! :)

Thanks!
Alex
LVL 31
Alexandre SimõesManager / Technology SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

naveenkohliCommented:
Search on MSDN. Rcenetly there was an article about it. MS developed some toolkit or some library for exactly this purpose.
mrichmonCommented:
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.
mrichmonCommented:
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.
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Alexandre SimõesManager / Technology SpecialistAuthor Commented:
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
mrichmonCommented:
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.
Alexandre SimõesManager / Technology SpecialistAuthor Commented:
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
naveenkohliCommented:
Check this out.. I think this is the article I was refering to.

http://www.microsoft.com/indonesia/msdn/SPOIL.aspx
Alexandre SimõesManager / Technology SpecialistAuthor Commented:
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
Alexandre SimõesManager / Technology SpecialistAuthor Commented:
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
GranModCommented:
PAQed with points refunded (500)

GranMod
Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.