[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2006-04-19
Medium Priority
Last Modified: 2008-01-09

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!! :)

Question by:Alexandre Simões
  • 4
  • 3
  • 2
  • +1
LVL 23

Expert Comment

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

Expert Comment

ID: 16488769
You can use Enterprise Library

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.
LVL 35

Expert Comment

ID: 16488797
Oh there are also more like

Mydatabase.ExecuteScalar - for returning scalar values


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


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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 30

Author Comment

by:Alexandre Simões
ID: 16489440
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?

LVL 35

Expert Comment

ID: 16489708
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.
LVL 30

Author Comment

by:Alexandre Simões
ID: 16502554
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

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
LVL 23

Expert Comment

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

LVL 30

Author Comment

by:Alexandre Simões
ID: 16503012
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
LVL 30

Author Comment

by:Alexandre Simões
ID: 16681964
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.



Accepted Solution

GranMod earned 0 total points
ID: 16753391
PAQed with points refunded (500)

Community Support Moderator

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Integration Management Part 2
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question