I want some Moore

Blog about stuff and things and stuff...
mostly about SQL server and .Net
posts - 161, comments - 1491, trackbacks - 33

My Links

SQLTeam.com Links

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer. I'm also a MCP and MCTS for SQL Server. 
Welcome to my blog.
Search this Blog
 
My Blog Feed via Email
Get your Google PageRank
Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff
<< Extending DateTime to include dates less than 1753-01-01 in SQL Server 2005 with CLR UDT | Home | RFC 1925 - The Twelve Networking Truths - a must read >>

CRUD stored procedures generating SSMS Add-in

UPDATE:  This is now a part of a larger SSMS Add-In called SSMS Tools Pack

I thought I'd post something special as the last post of this year and i was thinking about what it would be.

Fellow SqlTeamer Rockmoose said he'd like to have an add-in for SSMS that would genereate basic

CRUD (Create - Insert, Read - Select, Update, Delete) stored procedures for a table.

And he'd like to have it on the context (right-mouse-click) menu of the table in Object Explorer in SSMS.

 

It sounded like a very cool little project and with the help of this excellent post about SSMS add-ins

I've decided to make one.

You can download it here.

Unzip the MSI and run it.

 

Tell me what you think and how it works for you.

If you find any bugs or have any future functionality wishes mail them to me via contact page.

 

Happy New Year everyone!

Print | posted on Thursday, December 28, 2006 11:10 PM

Feedback

# re: CRUD stored procedures generating SSMS Add-in

Great! :)
thanx for sharing.
12/29/2006 5:14 PM | Mladen

# Letem .NETem (2.)

Nepravidelný občasník lehce komentovaných linků ze světa vývoje v .NETu... Tentokrát shrnutí zajímavých
1/2/2007 6:57 AM | Weblog @ Rebex.cz :: Martin Vobr

# re: CRUD stored procedures generating SSMS Add-in

Thanks Mladen. Nice utility.

the MyGeneration software sounds interesting but unfortunately the website appears to be down, so I'll be sticking to your CRUD tool for now.

I guess one suggestion would be to have an option to choose which CRUD-type operation you would like to create. Sometimes I don't want to create all of them and I am too lazy to remove the others from the code.

Perhaps also an option to name the proc/s so that they can fit custom naming conventions. Once again I know it can be done through the generated code but I thought the more autonomous the better.

keep up the good work
1/3/2007 1:27 AM | Daren Kovacevic

# re: CRUD stored procedures generating SSMS Add-in

Good work.

It would be nice to have an option to generate "optimistic" concurency lockin as Visual Studio 2003/2005.

May be also option to generate (or not) SELECT statement after INSERT and/or UPDATE.

And option to generate SELECT by indexes.
1/10/2007 7:54 AM | Tomas Kouba

# re: CRUD stored procedures generating SSMS Add-in

what exatly do you mean by: "generate SELECT by indexes." Tomas?
1/10/2007 1:18 PM | Mladen

# re: CRUD stored procedures generating SSMS Add-in

For your select statements, give the parameter a default value of null and change your where statement to
WHERE ID = @ID OR @ID is null

That way you can use the procedure to select all record or a single one
1/24/2007 6:31 PM | Rick

# re: CRUD stored procedures generating SSMS Add-in

Mike Griffin's comment was accidently delted during maintainance, so i'm going to post it again. It was the first comment for this post and my comment on top is meant as a reply to this one.
here goes:

From Mike Griffin:
That's been done, with many a options and more, it's all free, MyGeneration Software
http://www.mygenerationsoftware.com
2/10/2007 1:00 PM | Mladen

# re: CRUD stored procedures generating SSMS Add-in

Any chance of including Try/Catch blocks around the generated code?

The Error stuff works like this:
DECLARE @Error nVarChar(4000)
BEGIN TRY
Statements...
BEGIN TRY
Statements...
END TRY
BEGIN CATCH
SET @Error = ERROR_MESSAGE()
RAISERROR('This Error Occurred: %s', 16, 1, @Error)
END CATCH
END TRY
BEGIN CATCH
SET @Error = ERROR_MESSAGE()
RAISERROR('This Error Occurred: %s', 16, 1, @Error)
END CATCH

The nested Try...Catch will raise the error to the outer Try...Catch, ERROR_MESSAGE() is a built in function that will return the Error causing the Catch. Others, like ERROR_NUMBER(), also exist. The 2nd parameter of RAISERROR is the Error level, level 10 can be used for information only, no error is actually thrown, but the message will be sent to output. Any level higher then 10 will cause the Catch to run. 16 is known as a standard, "something is wrong" level.

3/30/2007 5:57 PM | Tadd Stuart

# re: CRUD stored procedures generating SSMS Add-in

It's in the works...
but sinnce i'm playing with this in my free time it'll be a couple of weeks before i put something out...
3/30/2007 6:00 PM | Mladen

# re: CRUD stored procedures generating SSMS Add-in

When i try to launch Create CRUD Stored Procedures with the right click on table i retrive the following error:
Object Reference not set on istance of object.

Why?
Thx
4/18/2007 11:28 AM | WebZero

# re: CRUD stored procedures generating SSMS Add-in

I have no idea.
Can you mail me the stack trace? there should be one in the message box you're getting.

I'll be putting out a new version of this in a week or two, if that helps....
4/18/2007 11:32 AM | Mladen

# re: CRUD stored procedures generating SSMS Add-in

Message not display a stack trace.

I use another schema less than dbo, that can help you?
i have table like
[Schama1].[Table1]
[Schama1].[Table2]
[Schama2].[Table3]
[Schama2].[Table4]

I have tried also with AdventureWorks...
but :-(

here is the error:
http://img295.imageshack.us/img295/1961/errorgi8.jpg
4/19/2007 4:25 PM | WebZero

# re: CRUD stored procedures generating SSMS Add-in

this is after the new window has been atuomatically opened or before?

as i haven't tested this with a SSMS version other than English, i don't know if this is an
issue.

do you have an english version of SSMS that you can try this on?
4/19/2007 4:29 PM | Mladen

# re: CRUD stored procedures generating SSMS Add-in

before the new new window has been atuomatically ..

I use Italian version of SQL SERVER Developer edition.

thx
4/21/2007 3:31 PM | WebZero

# re: CRUD stored procedures generating SSMS Add-in

Hi,
nice work!

How can I use it for SSMS Expess Edition?

Thanx.
Regards.
4/27/2007 2:52 PM | win32nipuh

# re: CRUD stored procedures generating SSMS Add-in

haven't tried it on SSMSE. but i don't see why it shouldn't
4/27/2007 3:31 PM | Mladen

# re: CRUD stored procedures generating SSMS Add-in

just a suggestion...
why don´t you add "templates" for each operation..., in my case i use another namming convention... anyway its a good add-in.
5/9/2007 6:31 PM | ErickG

# re: CRUD stored procedures generating SSMS Add-in

Its Cool !!!!.

A good suggestion would be an interface to add templates .... as posted by ErickG.
It would be a wonderful option and save time
5/23/2007 4:09 PM | sreedharTR

# re: CRUD stored procedures generating SSMS Add-in

Hello, thanks for sharring.
I'm using the SSMS Express, but I can't find the option in the context menu.
The Add-in don't work with Express version ?

Thanks again!
5/29/2007 7:43 AM | Roni Peterson Xavier Carvalho

# re: CRUD stored procedures generating SSMS Add-in

don't know... haven't tried SSMSE.
5/29/2007 11:01 AM | Mladen

# re: CRUD stored procedures generating SSMS Add-in

I tried this nice add-in in SQL Server 2005 and it gave me the following exception. It would be really nice to fix it for 2005. Are there any alternative tools?


I get the following exception:


Must declare the scalar variable "@TableName".
Must declare the scalar variable "@TableName".
Incorrect syntax near ')'.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at a.a(String A_0, String A_1, String A_2)
6/7/2007 12:19 PM | Andrey

# re: CRUD stored procedures generating SSMS Add-in

The generated stored procedure code is making the length of the fields twice that specified in the table. ie if my field is nvarchar(50) it gets written as nvarchar(100) in the CRUD procedures.
6/14/2007 12:23 PM | Chris Wedgwood

# re: CRUD stored procedures generating SSMS Add-in

thanx andrey and chris..
both of these are fixed for the next release.
6/14/2007 1:34 PM | Mladen

# re: CRUD stored procedures generating SSMS Add-in

does this tool works with ssmse and if yes how to install this add into to sql server express edition .
6/29/2007 7:49 AM | jhoojhar

# re: CRUD stored procedures generating SSMS Add-in

no it doesn't work with ssmse
6/29/2007 10:24 AM | Mladen

# re: CRUD stored procedures generating SSMS Add-in

I have developed a similar tool which generates CRUD stored procedures and also generates data-access layer classes using the Subsonic Command line utility http://subsonicproject.com/. This add-in will not work with SSMS because the group of assemblies needed for extensiblity are native to SSMS and SQL 2005. Check out my article at http://developer.franklinrau.com/
9/14/2007 3:04 AM | Franklin Rau

# re: CRUD stored procedures generating SSMS Add-in

thanx for the link. nice!
9/14/2007 10:24 AM | Mladen

# re: CRUD stored procedures generating SSMS Add-in

I have released the BETA version of my data-access layer generation tool. It is an add-in for SSMS 2005. You can find it here: http://developer.franklinrau.com/post/DAL-Generator-BETA-Version-Released!!.aspx
9/18/2007 8:26 PM | Franklin Rau

# re: CRUD stored procedures generating SSMS Add-in

Awsome!
9/18/2007 8:27 PM | Mladen

# re: CRUD stored procedures generating SSMS Add-in

I have developed a template driven code generator (Lattice.SPGen) to generate CRUD stored procedures and business entities supporting SQL Server, Oracle, MySQL, PostgreSQL and DB2.

http://www.latticesoft.com
11/3/2007 6:47 AM | Li Xin

Post Comment

Title  
Name  
Email
Url
Comment   
Please add 3 and 8 and type the answer here:

Powered by:

Copyright © Mladen Prajdić