SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using C# to Create a Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 06/06/2005 :  20:38:10  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
This is the first article in a series on writing stored procedures using the Common Language Runtime (CLR). This article focuses on basic C# syntax and using Visual Studio to build a stored procedure. It's targeted at DBA's and anyone else who primarily writes in Transact-SQL and hasn't had much exposure to .NET yet.

Article Link.

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/07/2005 :  09:06:36  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Very nice, Bill.

I do have a concern about DBAs suddenly learning how to write code in C# or other .NET languages, to be honest. It is one thing for developers who have no clue about relational database theory to make sloppy databases and write too many cursors, but the basics of database theory can be learned quite quickly if you are bright and dedicated.

But for DBA's without a solid background in programming to suddenly start writing lots of .NET code worries me ... learning how to write code and to properly utilize resources and to structure entire applications properly and efficiently is a whole 'nother story, and something that can take years to become even slightly competent at. And many DBA's have enough trouble even writing a decent SELECT as it is!

In short, it is a much, much easier transition for a good developer to learn database theory and set-based processing than it is for a good DBA to lean the fundamentals of object oriented programming and to understand inheritence, libraries, COM, interfaces, data structures, encapsulation, object variables and their scope, etc ...

I don't mean this to offend DBA's but since developers often get such a bad rap when it comes to SQL that it is only fair to mention the other side of the equation. I get concerned that in a year or two the tables will be turned and you'll have all of these developers complaining about DBA's not using OPTION STRICT and declaring all kinds of global variables as "Object" and doing other things that are the equivalent of a developer creating unnormalized tables with lots of cursor code.

I don't mean this to detract from your article or what you are trying to do, however. It is very well done and a great read, and I think it will really help DBA's get started with Visual Studio. I'm looking forward to future editions!

- Jeff

Edited by - jsmith8858 on 06/07/2005 09:14:02
Go to Top of Page

Sitka
Aged Yak Warrior

USA
571 Posts

Posted - 06/07/2005 :  11:16:13  Show Profile  Reply with Quote
I love this article.
I agree with the Dr. as well.

I feel the market is such that even smaller companies can have needs met by fairly sophisticated solutions, that means the individual will be called upon to wear both hats, and do their best to stay employeed. The responsiblity is on the individual to not build crap, and that isn't trivial, but what is the alternative? The brain flipping from relational(E/R and TSQL) - heirarchical(XML and schema mapping) - object(fundamentals as well as Patterns/UML) is sure to drive many to madness. Just how humble does a databse programmer have to be to ensure survival and quality work. How much can an ego take?



I wish someone would start an Official XML Rant Thread.
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 06/07/2005 :  16:24:37  Show Profile  Reply with Quote
Very nicely written Graz... tough topic...

Question for all: Beside UDTs, how has the CLR improved the ability of the DBMS to ensure the integrity of my data?

DavidM

A front-end is something that tries to violate a back-end.
Go to Top of Page

SFXMan
Starting Member

USA
1 Posts

Posted - 06/10/2005 :  02:34:27  Show Profile  Visit SFXMan's Homepage  Reply with Quote
Great article, thanks!

I tend to agree w/ you on C# vs VB.Net, although I will argue the point about it being more terse! True - if you look at the semantics of the language it is more terse, but because it is more strict you end up casting a lot more, and sometimes you'll end up seeing some pretty crazy things like casting a date from the database to a string than back to a date. Ughhh.... Like this (this is from memory, without the aid of intellisense - so give me a break, but you'll get the idea) (Oh, and btw , I said I've seen a lot of code like this, not written it - so don't yell at me).


DateTime dt = DateTime.Parse(Reader.GetOrdinal("OrderDate").ToString());


Say What????

I'm just learning C# too - so I'm not an authority, but I've seen plenty of code like this from otherwise reputable programmers. So go figure...

If you want more of the same perspective, but from both sides of the camp there are couple interesting articles side by side, by the same author on vbrad.com. One is entitlted "Top Ten Reasons C# is Better than VB.Net" and the other is "Top Ten Reasons VB.Net is Better than C#" Its pretty funny - read it at http://www.vbrad.com

Anyway - great article, so thanks.



SFXMan
SQL Effects Software - better tools for the busy SQL Pro
www.sqleffects.com
Go to Top of Page

DianeM
Starting Member

1 Posts

Posted - 06/10/2005 :  18:25:21  Show Profile  Reply with Quote
Could anyone recommend some good beginner C# books to me? Thanks.

Diane
Go to Top of Page

Crito
Starting Member

USA
40 Posts

Posted - 06/14/2005 :  13:49:46  Show Profile  Visit Crito's Homepage  Reply with Quote
SQL Server 2005 may be the end of the line for the product family. Microsoft has taken a product that could be used with any development environment and married it so closely to dot-net that it'll be virtually useless with any other (non-MS) language. Companies will flock to Sybase and Oracle in droves. C# stored procs... I don't think so.
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 06/14/2005 :  17:26:30  Show Profile  Visit graz's Homepage  Reply with Quote
Crito,

SQL Server 2005 works just as well in non-MS development environments as SQL Server 2000. Better actually since they've made many enhancements to the T-SQL language. If you want to write client applications in PHP or Java or COBOL or any other language SQL Server 2005 will be a great database product. Plus SQL Server can expose its stored procedures natively via a web service so anything that can call a web service can use stored procedures.

All the database vendors are looking to add stored procedures written in non-SQL based languages. I believe Oracle chose Java. DB2 has some CLR support and I think Java support also. Microsoft chose to use the CLR for their scripting environment meaning you can write stored procedures in C#, Visual Basic, Java, COBOL, etc. If you chose not to write any CLR stored procedures SQL Server 2005 will still be a competitive product with all the uptime, distaster recovery and developer enhancements.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 06/14/2005 :  22:15:21  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
There was an interesting article in SQL Server Magazine about the introduction of non-SQL languages into the RDMS space a few months ago I believe. Like Graz said, this is a direction that all major vendors are headed in. DB2 does have CLR support for example; however, unlike SQL Server, it does not run in the same memory space as the database engine. It will be interesting to see how that model scales vs the SQL Server tight integration model.

There are places inside the databases for the CLR, just like there is room for vendor extensions to the ANSI standards. You can be a Celko purist who writes books with examples that don't work. It's better to learn the strenghts and weaknesses of what's available and leverage the best of both worlds. If that's the model you follow, you will still be functional 20 years from now when the whole technology landscape has evolved far beyond what it's currently at.

/rant



MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 06/14/2005 :  23:01:22  Show Profile  Visit Merkin's Homepage  Reply with Quote
quote:

VB.NET are for Kids, C# are for Men. C# Rulz..



What are you, 14 years old ?
Grow up and stop being an idiot child and maybe you'll get treated like a grown up.


Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 06/16/2005 :  21:30:10  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:
Originally posted by raclede

I don't think that VB.NET is better than C#... VB.NET are for Kids, C# are for Men. C# Rulz..
like in the "Top 10 reasons VB.NET is better than C#", Optional Parameters?? hmmm I rather use Overloading...
Hey VB Programmers what about the keyword "Dim", Im sick of this word!... duh... and by the way
C# is case sensitive, unlike VB.NET which is so easy to use... damn.. I would love teach my 2 yr. daughter on how to use VB.NET simple as counting 1 to 3..



My browser must be messed up -- I thought this was SQLTeam but I think somehow I got redirected to an AOL script kiddie message board!

Raclede -- You should probably actually learn .NET before making any judgements as to which language is "better."

http://weblogs.sqlteam.com/randyp/archive/2005/05/31/5391.aspx

- Jeff

Edited by - jsmith8858 on 06/16/2005 22:35:19
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 06/16/2005 :  22:19:31  Show Profile  Visit Merkin's Homepage  Reply with Quote
quote:


Raclede -- You should probably actually learn .NET before making any judgements as to which language is "better."

http://weblogs.sqlteam.com/randyp/archive/2005/05/31/5391.aspx




HAHAHAHAHAHAHAHAHAHAHA

I think in script kiddie talk they call that "pwn3d".



Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson
Go to Top of Page

jhermiz
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 06/18/2005 :  01:22:09  Show Profile  Visit jhermiz's Homepage  Reply with Quote
quote:
Originally posted by raclede

I don't think that VB.NET is better than C#... VB.NET are for Kids, C# are for Men. C# Rulz..
like in the "Top 10 reasons VB.NET is better than C#", Optional Parameters?? hmmm I rather use Overloading...
Hey VB Programmers what about the keyword "Dim", Im sick of this word!... duh... and by the way
C# is case sensitive, unlike VB.NET which is so easy to use... damn.. I would love teach my 2 yr. daughter on how to use VB.NET simple as counting 1 to 3..


"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede



Nice argument not...
BTW operator overloading is available in vb.net now.
FYI the performance of a vb.net is just as good as C#. Syntax wise
they are easily converted from one to another (they even sell tools to easily convert).

What's wrong with Dim? Its like saying DECLARE...its just a keyword, C# has keywords as well.


Keeping the web experience alive -- http://www.web-impulse.com
Imperfection living for perfection --
http://jhermiz.blogspot.com/
Go to Top of Page

oepirobo
Starting Member

36 Posts

Posted - 08/26/2005 :  12:44:23  Show Profile  Reply with Quote
Just in case anyone is wondering... I think that the time gets stripped out because the code calls DateTime.Today instead of DateTime.Now
Go to Top of Page

araujoao
Starting Member

1 Posts

Posted - 02/26/2006 :  14:10:24  Show Profile  Reply with Quote

Great article, it goes from the beginning to end. The sketch is perfect, from now on, I can just add features to my stp sketch.

Unfortunately, we always have those guys trying to show up, replying to defending one or another language. They cant understand that the author is not trying to advocate in behalf one or another, He simply stated he likes C# plus some small adds. That's not the point of the article, so please keep on the subject. Let's not start a battle on CSharp X VB.

U
Go to Top of Page

dennisgorelik
Starting Member

1 Posts

Posted - 12/27/2007 :  23:37:19  Show Profile  Visit dennisgorelik's Homepage  Send dennisgorelik an ICQ Message  Reply with Quote
Nice article.
BTW, the reason why:
p.Send(System.DateTime.Today.ToString());
returns time component is -- C# formatting.

If you try:
p.Send(System.DateTime.Today.ToString("yyyy-MM-dd"));
it would return only date part.
Go to Top of Page

curbina
Starting Member

USA
1 Posts

Posted - 09/03/2008 :  19:53:48  Show Profile  Reply with Quote
I have downloaded C# Express, VB Express and SQL 2005 Express. Is there an IDE called Visual Studio Express? The article assumes that the reader has Visual Studio. Is this something you buy or is it free like the other Express editions? I'm trying to follow along with the article but get stuck when it says

"After launching Visual Studio 2005 choose File -> New Project. In the dialog box under Project Type choose Visual C# -> Database and then choose SQL Server Project on the right side."

All I have is Visual C# Express. How can I continue with the tutorial in this article?

cu
Go to Top of Page

willieb
Starting Member

5 Posts

Posted - 03/17/2011 :  17:41:14  Show Profile  Reply with Quote
I have to say, I like the article and my favorite typo

"CShartPart1"

:)

wb
Go to Top of Page

rajendar
Starting Member

1 Posts

Posted - 08/21/2011 :  07:47:44  Show Profile  Reply with Quote
required info on CLR in SQL
For more info http://www.4microsoftsolutions.com/post/CLR-integration-in-SQL-Server.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.39 seconds. Powered By: Snitz Forums 2000