RAD with SQL Server 2000 & C# - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

RAD with SQL Server 2000 & C#

Author

Topic

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2003-11-07 : 06:15:15

Hi guys

I've written an assembly for C# so to access a stored procedure, then get the returned rows back as a DataTable all I have to say is


// Get document links
public DataTable DBLinkDocument_Get( int iItemID )
{
	SQLDataAccess oDBComms = new SQLDataAccess( DB_CONNECTION_STRING );
	oDBComms.DBParamClear();
	oDBComms.DBParamAdd( "@ItemID",   SqlDbType.Int, iItemID, 0 );
	return oDBComms.DBRunProcedureReturn( DB_SP_LINKDOCUMENT_GET, true );
}

For example, which means my life easier and makes application development faster.
However, I still have to write this interface code that lets the SQL Server stored procedures talk to the C# code. So what I'd like to do is write a program, in C#, VB, doesn't matter... that will read in all the stored procedures for a database and procduce this interface code for them all.
i.e. Read in the input parameters, the create the C# code from them.

So all I want to know really is how can I query SQL server to give me the details of a stored procedure?
I know sp_stored_procedures will give me a list of the stored procedures for a database. But how can I retrieve the code of a specified stored procedure?

Thank you if you can help me out

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-07 : 06:19:05

Hi there

I think you need to take a look at CodeSmith
CodeSmith totally rocks, and with a little tweaking, you can do pretty much anything you want to do (code generation wise).

If you are determined to do it yourself, take a look at :

Select * from INFORMATION_SCHEMA.PARAMETERS

Damian

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2003-11-07 : 06:28:03

Thanks Merkin

I have no doubt it would be easier to use CodeSmith, but I'd rather put myself through the demands and trails of writing such an application myself.
So I come away not just with faster code develop, but the knowledge of how to develop such an application.

Again, thanks
Pete

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-07 : 06:31:43

I totally understand (have done something similar)

Also have a look at sysobjects and syscolumns if you are feeling adventurous (this is where the information schema views come from).

Damian

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-11-07 : 07:02:29

Isn't it the provider that figures out the datatypes, column names for you. Just patch into that do the automation from the returned info. Metadata some call it. Or..... I totally miss the point. ;-) Still need the proc names I guess.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-07 : 07:05:56

Sitka, Yes you can do it that way. but it means making an extra call to the database.
What Pete is trying to do is have an app that will pregenerate the parameter code so it can be compiled.

Damian

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2003-11-07 : 10:59:27

I'm in need of a little more help guys, if you'd be so kind.

I've written about 95% of the system now. It allows you to select a database, then the stored procedures you wish you generate.
Finally, it then generates the code, perfect.

Except there is one slight problem.
All my code generated expects there to be data returned, which won't always be the case.
Is there any way to see if a stored procedure will actually return data?

Thanks again

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-11-07 : 16:33:48

Hi Maverick.

I'm afraid there isn't. It's quite a problem sometimes. Also, you never know what will be returned, or how many recordsets will be returned. These things can all depend on control structures within the procedure.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-07 : 20:08:06

Your best way of doing this is by having a stored proc naming convention. If you always know that a stored proc named "ObjectList" or "ObjectGet" will return a set and "ObjectAdd" will return an ID etc then you can parse the names and determine what will be returned.

Damian

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2003-11-10 : 04:06:34

Yes, I feared this might be the case.
A shame, as other wise my tool would be flawless. But it still saves me alot of wasted time.

Thank you for your thoughts gentlemen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources