SPGen - Stored Procedure Generator

Summary

SPGen is a simple Windows application which can generate the TSQL code for an INSERT or UPDATE Microsoft SQL Stored Procedures. Point it at a table, click the generate button and the code is generated for you.

The article covers some basic SQLDMO (SQL Database Management Object) methods and provides a slim .NET wrapper class around SQLDMO to help you in using SQLDMO.

1. A screenshot of SPGen in action with a generated Stored Procedure in the main text box

Introduction

Writing the basics of Stored Procedures is mind numbing at best, even for DBAs. Megan Forbes, myself and a few others got into a heated rant about Microsoft SQL Server Enterprise Manager and it's extreme lack of SP tools and management. I decided to write a very simple app which takes away the drudge of typing in all the base code for an SP. When you are faced with a table of 50 fields and the need to create a simple UPDATE or INSERT SP, declaring all those parameters can be akin to agreeing to be the designated driver for the office Christmas party, i.e. deadly boring.

Using the application

Extract the downloaded demo zip, or re-compile the project, and run the executable.

SPGen starts up and lists all locally registered SQL Servers in the top left drop down list
Select, or type in, the SQL Server you want to connect to
Enter in the User Name and Password for the SQL Server. If there is no Password needed then just leave the Password field untouched
Click the Connect button
SPGen will now attempt to connect to the specified SQL Server and list all the Databases
Once the Databases are listed, expand the Database you wish to work with
SPGen will now list all the Tables within the expanded Database
Now expand the Table you wish to generate an SP for
There will be two options; UPDATE or INSERT. Click the one you want
SPGen will now attempt to retrieve the columns for the Table (but not display them) and generate the specified SP type
Once generated the code is placed in the text box on the right and you can cut & paste that code into Microsoft SQL Enterprise Manager, or Microsoft SQL Server Query Analyzer

That is the extent of SPGen's functionality. You can generate SPs for other Tables, without having to re-connect, or you can connect to another SQL Server and generate SPs for that.

SQLDMOHelper

SQLDMOHelper is a simple class which returns basic information about a SQL Server to the caller. Really it just wraps up the common methods I needed from SQLDMO into easy to use .NET methods which return easily usable data. To this end it only returns data and does not provide any methods to save changes to a SQL Server, yet.

Using SQLDMO in your .NET app is actually very simple. All you need to do is add a reference to the Microsoft SQLDMO Object Library COM object in your project. You can then utilise SQLDMO methods with the interopped SQLDMO namespace. All very simple thanks to .NET.

Property: `public Array RegisteredServers`

This property returns a one-dimensional string array containing the names of all registered SQL Servers in the local domain.

SQLDMO provides a class called ApplicationClass which you can use to gather this list, like so;

ArrayList aServers = new ArrayList();
SQLDMO.ApplicationClass acServers = new SQLDMO.ApplicationClass();

for (int iServerGroupCount = 1; 
        iServerGroupCount <= acServers.ServerGroups.Count; 
        iServerGroupCount++)
    for (int iServerCount = 1; 
            iServerCount <= acServers.ServerGroups.Item(
               iServerGroupCount).RegisteredServers.Count; 
            iServerCount++)
        aServers.Add(acServers.ServerGroups.Item
            (iServerGroupCount).RegisteredServers.Item(iServerCount).Name);

return aServers.ToArray();

Quite simply a new instance of ApplicationClass is created. Then a for loop runs through each ServerGroups returned and then in the second for loop adds each RegisteredServer name to the aServers ArrayList. aServers is then returned to the caller to be consumed.

ArrayList really makes working with un-known length arrays very easy. You can basically redimension the array on the fly and then once you are finished use the ToArray method to return a valid Array.

Property: `public Array Databases`

Databases is a property which returns, as the name suggest, a one-dimensional string array of all Databases in a specified SQL Server.

ArrayList aDatabases = new ArrayList();

foreach(SQLDMO.Database dbCurrent in Connection.Databases)
    aDatabases.Add(dbCurrent.Name);

return aDatabases.ToArray();

A simple foreach loop is run against the SQLDMO.Databases collection which is returned from Connection.Databases.

Connection is a property of SQLDMOHelper which provides a SQLDMO Server connection. You need to use the Connect method to set the Connection property up. Also remember to use the DisConnect method to, wait for it, disconnect the connection.

Databases then returns the string array of Database names for your app to use.

Property: `public Array Tables`

Looks familiar, doesn't it? It is. The Tables property returns a one-dimensional string array of all Table names in a specified Database.

ArrayList aTables = new ArrayList();
SQLDMO.Database dbCurrent = (SQLDMO.Database)Connection.Databases.Item(
    this.Database, Connection);

foreach(SQLDMO.Table tblCurrent in dbCurrent.Tables)
    aTables.Add(tblCurrent.Name);
				
return aTables.ToArray();

Property: `public SQLDMO.Columns Fields`

The Fields property however is a bit different. Instead of returning a one-dimensional string array it returns a SQLDMO.Columns collection which provides a full range of details on all columns (fields) within a table.

The code though is even simpler than before as we are really just returning what SQLDMO provides and not translating it at all:

SQLDMO.Database dbCurrent = (SQLDMO.Database)
    Connection.Databases.Item(this.Database, Connection);
SQLDMO.Table tblCurrent = (SQLDMO.Table)
    dbCurrent.Tables.Item(this.Table, Connection);

return tblCurrent.Columns;

Columns is a collection of SQLDMO.Column objects which contain various properties and methods for working on a field in a table. In SPGen only Name, DataType and Length are used, but there are many more.

Properties: `string ServerName`, `UserName`, `Password`, `DataBase` and `Table`

These four properties of SQLDMOHelper are simply strings which hold what SQL Server, user name, password, database and table respectively the methods of SQLDMOHelper should work on. For instance Databases requires just ServerName, UserName and Password to be filled in to work. To use Fields though you also need Database and Table filled in so that Fields knows what to work on.

StoredProcedure

The StoredProcedure class provides just one method at the moment, Generate. This, finally, is the heart of SPGen and provides the functionality for returning valid Stored Procedure code.

Method: `public string Generate`

Parameters:

StoredProcedureTypes sptypeGenerate
An enum indicating the type of Stored Procedure to generate. StoredProcedureTypes.INSERT and StoredProcedureTypes.UPDATE are currently valid choices
SQLDMO.Columns colsFields
The Columns collection to use in the generation of the Stored Procedure
string sTableName
The name of the Table the INSERT or UPDATE will affect

The code within Generate is pretty straight forward and consists largely of a StringBuilder being used to construct the Stored Procedure. On that note I found the AppendFormat method of StrinbBuilder to be highly effective for this kind of work.

Take this code for instance: sParamDeclaration.AppendFormat(" @{0} {1}", new string[]{colCurrent.Name, colCurrent.Datatype});. Without the AppendFormat method one would have to do the following: sParamDeclaration += " @" + colCurrent.Name + " " + colCurrent.Datatype; This latter way is terrible to debug and hard to understand when there is a whole page of similar code. The format functionality of StringBuilder (and just String itself) makes for much more manageable and understandable string manipulation.

StringBuilder also is faster than using sSample += "Not in kansas, " + sName + ", anymore";, especially when performing many string appends. Thanks to Tom Archer's fantastic sample chapter on using String in .NET, I certainly learnt a lot from it.

One other slight item of interest in the Generate method is this:

if (
 colCurrent.Datatype == "binary" || 
 colCurrent.Datatype == "char" || 
 colCurrent.Datatype == "nchar" || 
 colCurrent.Datatype == "nvarchar" || 
 colCurrent.Datatype == "varbinary" || 
 colCurrent.Datatype == "varchar")
    sParamDeclaration.AppendFormat("({0})", colCurrent.Length);

Basically in TSQL you must only declare the length of a parametre if it is one of the above data types. If you for instance try @NameFirst int(4) in TSQL you will get back an error as you may not declare the length of an int data type. At present I know of no way to programatically detect which data types must and must not have length declarations, therefore I have used the cumbersome if block you see above. I was hoping that SqlDbType would provide the neccesary information, but it does not, rendering it slightly less useful.

Apart from the the method is as stated mainly a big string manipulation method which takes in the provided fields, loops through them and returns a Stored Procedure of the type specified.

As I find more areas to automate in regards to Stored Procedures I hope to add new methods and helpers to this class.

Other Titbits

There is not much more to say or explain about SPGen, it really is a simple app. However following are two basically unrelated but still interesting titbits that you may find useful.

app.config

I have finally found a use for the app.config file beyond the usual. With SPGen you can open up app.config and modify the ServerName, UserName and Password application keys. SPGen will then read them in when the app starts and pre-fill the fields for you. This way if you have an often used SQL Server you can just fire up SPGen and hit connect without having to re-type the details in each time.

Obviously you want to be careful with the Password key especially, but I put it in with full confidence nobody would let their app.config file go wandering.

Code wise it is really quite simple:

NameValueCollection settingsAppSettings = 
    (NameValueCollection)ConfigurationSettings.AppSettings;

if (settingsAppSettings["ServerName"] != null && 
    settingsAppSettings["ServerName"] != "")
{
    selServers.Text = settingsAppSettings["ServerName"];
    dmoMain.ServerName = settingsAppSettings["ServerName"];
}

First I create a NameValueCollection collection, simply to make working with the configuration settings easier (i.e. instead of having to type ConfigurationSettings.AppSettings["key"] each time.) Then the code checks if there is a specified key value (I did not want the "Select Server" message being removed when there was no value) and then it sets the input field up to the value.

Nothing fancy, but it gives SPGen a small measure of customisation and gives your fingers a rest. By the way, on release build of SPGen app.config is automatically renamed to SPGen.exe.config, that is the file you need to edit to put in your SQL Server details.

TextBox PasswordChar

The PasswordChar property of the TextBox is pretty simple. You give it a char value that you want displayed instead of the actual text, e.g. *.

However, what if you want to reset that same TextBox so that it no longer masks the input? In SPGen I needed to do this as I, maybe wrongly, did not include labels for my input fields. MSDN provides a clue, but does not go on to show you exactly how. After a bit of stumbling around I figured it out;

char chResetPassword = (char)0;
txtPassword.PasswordChar = chResetPassword;

So you create a char of 0 (zero) and then assign that to the PasswordChar property.

It is quite obvious once you figure it out, but can be annoying before that.

Possible Improvements & Bugs

Allow the app to generate SPs from a SELECT query instead of just a Table selection
Allow the app to actually insert the SP into the database, saving you from having to cut & paste

Conclusion

Stored Procedures are very powerful but can be a tedious affair when multiple parameteres are required. Used in conjunction with Llewellyn Pritchard's DBHelper app though, you will have an end-to-end solution to working with Stored Procedures in an easy and fast manner.

SQLDMO is also a useful means of discovering and exploring SQL Servers and Databases. The main problem, in a .NET environment though, is that SQLDMO must be used through COM Interop, which is not an optimum situation. Hopefully in the near future a .NET SQLDMO will be released (though if you care to shed some light on how SQLDMO works I would be happy to write my own SQLDMO.NET class.)

If you have any ideas as to how to improve the app then please speak up.

You must Sign In to use this message board.


Per page

Error InteropServices SQL Server 2008
juliojesus

20:52 2 Mar '10

How can I fix the error:

Ocurrió System.Runtime.InteropServices.COMException
ErrorCode=-2147165949
HelpLink="SQLDMO80.hlp#700"
Message="[Microsoft][ODBC SQL Server Driver][SQL Server]Para conectar con este servidor debe utilizar Objetos de administración de SQL Server (SMO) o SQL Server Management Studio."
Source="Microsoft SQL-DMO (ODBC SQLState: 42000)"
StackTrace:
en SQLDMO.SQLServerClass.Connect(Object ServerName, Object Login, Object Password)
en Metasoftica.Data.SQLDMOHelper.Connect() en C:\HerramientasSQL\SQLDMOHelper.vb:línea 25
InnerException:

It's Greate Utility for largest DB
keyurmca@gmail.com

0:44 26 Dec '09

Hi Paul, Thank you very much. It's really nice tools for generating storeproce for large database.
Sign In·View Thread·PermaLink

Great Tool, but needs something
OmarGamil

1:01 30 Nov '09

thanks for the effort and the great tool, it just needs to check identity fields so as not to generate them in the sp good job
Sign In·View Thread·PermaLink

Thanks
mahmoud alam

1:26 25 Nov '09


Sign In·View Thread·PermaLink

Thanks
ddatproject

1:00 23 Nov '09

what a very useful tool. Thanks you so much. I have save a lot of time
Sign In·View Thread·PermaLink

sp gen problem
Member 2900494

1:33 5 Jan '09

hi, this tool is very nice but don't work with schema and don't have template for sp
Sign In·View Thread·PermaLink

Userful Tool
ashok_sole

14:02 22 Dec '08

Thank you for the Tool. It is very useful.
Sign In·View Thread·PermaLink

Great Utility
shripad_1

13:40 17 Aug '08

Paul..it's awsome utility saved lot of time for me. Thanks a lot!
Sign In·View Thread·PermaLink

pjt doesn't build in VS2008
mrob1

9:42 2 Jul '08

After making a few changes to the output, I tried to compile/build the pjt in VS2008. Got the following msg:

--------------------
Error 1
Warning as Error:
'System.Configuration.ConfigurationSettings.AppSettings'
is obsolete: 'This method is obsolete, it has been replaced by
System.Configuration!System.Configuration.ConfigurationManager.AppSettings'

frmMain.cs 41 67 SPGen
--------------------

Anyone know how to fix this so I can get the app working my way?

Thanks

Re: pjt doesn't build in VS2008
Paul Watson

12:51 2 Jul '08

mrob1 wrote:
'System.Configuration.ConfigurationSettings.AppSettings'

mrob1 wrote:
System.Configuration.ConfigurationManager.AppSettings'

Hi Robert. Sorry but I no longer even have Visual Studio or Windows to test any of this out on. Based on the error message though you may be able to just replace all occurences of the first statement with the second statement. Seems they changed ConfigurationSettings to ConfigurationManager.

Apologies if this is obvious and you've already tried it.

regards,
Paul Watson Ireland & South Africa

Fernando A. Gomez F. wrote:
At least he achieved immortality for a few years.

Re: pjt doesn't build in VS2008
marc lang

3:38 2 Feb '09

Hi, I had the same problem. I just replaced the line throwing the error with: NameValueCollection settingsAppSettings = (NameValueCollection)System.Configuration.ConfigurationManager.AppSettings; I had to right click on the project and go to "Add Reference", looked in the ".NET" tab and added "System.Configuration". It builds and runs after this. Nice tool.
Sign In·View Thread·PermaLink

Modified to use primary keys and generate delete procedure
RatmilTorres

8:19 17 Jan '08

Hi. I made a program like this one some years go in Visual Basic 6.0. I have modified your program to use primary keys and generate delete procedures. Would you like me to send you the changes so I can a little credit?
Sign In·View Thread·PermaLink	2.00/5

Can't Connect to DB for me
iceangel89

22:32 19 Jun '07

it can't seem to work for me.... my database is currently in localhost\SQLEXPRESS and the prog says connection to DB failed. no user name & password.
Sign In·View Thread·PermaLink	1.33/5

added a new feature... DELETE
Tim_Mackey

3:20 5 Dec '06

hi paul, greetings from dublin. really great code, this is just what i always wanted but never knew existed. i took the liberty of adding DELETE support, which was very straight forward, just posting it here if anyone is interested. my delete queries only delete by the identity column so the resulting query will look like this:

CREATE PROCEDURE Audits_DELETE
    @AuditID int
AS
DELETE FROM [Audits] 
WHERE    AuditID = @AuditID
GO

here are the changes i made:

step 1: add a new node for delete [in frmMain, tvwServerExplorer_BeforeExpand]

TreeNode treenodeTableDelete = new TreeNode("DELETE Stored Procedure", 2, 2);
treenodeTable.Nodes.Add(treenodeTableDelete);

step 2: add support for the DELETE nodes [in frmMain, tvwServerExplorer_AfterSelect]

// i found it simpler to replace the switch statement with a regex and enum parse
string query_type = Regex.Match(tnodeSelected.Text, @"\w+").Groups[0].Captures[0].Value;
StoredProcedureTypes spType = (StoredProcedureTypes)Enum.Parse(typeof(StoredProcedureTypes), query_type);

i made a few minor changes to SPGen.cs in order to support the DELETE syntax. rather than interleave all the changes, here is the whole file:

using System;
using System.Text;

namespace Bluegrass.Data
{	
	/// 
	/// Supported Stored Procedure types
	/// 
	public enum StoredProcedureTypes
	{
		UPDATE,
		INSERT,
		DELETE
	}

	/// 
	/// Stored Procedure Helper class
	/// 
	public class StoredProcedure
	{		
		/// 
		/// Generates code for an UPDATE or INSERT Stored Procedure
		/// 
		
		public string Generate(StoredProcedureTypes sptypeGenerate, SQLDMO.Columns colsFields, string sTableName)
		{
			StringBuilder sGeneratedCode = new StringBuilder();
			StringBuilder sParamDeclaration = new StringBuilder();
			StringBuilder sBody = new StringBuilder();			
			StringBuilder sINSERTValues = new StringBuilder();

			// Setup SP code, begining is the same no matter the type
			sGeneratedCode.AppendFormat("CREATE PROCEDURE {0}_{1}", new string[]{sTableName, sptypeGenerate.ToString()});			
			sGeneratedCode.Append(Environment.NewLine);

			// Setup body code, different for UPDATE and INSERT
			switch (sptypeGenerate)
			{
				case StoredProcedureTypes.INSERT:
					sBody.AppendFormat("INSERT INTO [{0}] (", sTableName);
					sBody.Append(Environment.NewLine);


					sINSERTValues.Append("VALUES (");
					sINSERTValues.Append(Environment.NewLine);
					break;
				
				case StoredProcedureTypes.UPDATE:
					sBody.AppendFormat("UPDATE [{0}]", sTableName);					
					sBody.Append(Environment.NewLine);
					sBody.Append("SET");
					sBody.Append(Environment.NewLine);
					break;

				case StoredProcedureTypes.DELETE:
					sBody.AppendFormat("DELETE FROM [{0}] ", sTableName);
					sBody.Append(Environment.NewLine);
					sBody.Append("WHERE");
					break;
				
			}
			 						
			foreach (SQLDMO.Column colCurrent in colsFields)
			{
				// only list identity columns for delete statements
				if(sptypeGenerate == StoredProcedureTypes.DELETE && !colCurrent.Identity)
					continue;

				// Param Declaration construction
				sParamDeclaration.AppendFormat("    @{0} {1}", new string[]{colCurrent.Name, colCurrent.Datatype});				
				
				// Only binary, char, nchar, nvarchar, varbinary and varchar may have their length declared								
				if (
					colCurrent.Datatype == "binary" || 
					colCurrent.Datatype == "char" || 
					colCurrent.Datatype == "nchar" || 
					colCurrent.Datatype == "nvarchar" || 
					colCurrent.Datatype == "varbinary" || 
					colCurrent.Datatype == "varchar")
					sParamDeclaration.AppendFormat("({0})", colCurrent.Length);
				
				sParamDeclaration.Append(",");
				sParamDeclaration.Append(Environment.NewLine);

				// Body construction, different for INSERT and UPDATE
				switch (sptypeGenerate)
				{
					case StoredProcedureTypes.INSERT:						
						sINSERTValues.AppendFormat("    @{0},", colCurrent.Name);						
						sINSERTValues.Append(Environment.NewLine);

						sBody.AppendFormat("    {0},", colCurrent.Name);						
						sBody.Append(Environment.NewLine);
						break;

					case StoredProcedureTypes.UPDATE:
					case StoredProcedureTypes.DELETE:
						sBody.AppendFormat("    {0} = @{0},", new string[]{colCurrent.Name, });											
						sBody.Append(Environment.NewLine);
						break;
				}
			}

			// Now stitch the body parts together into the SP whole			
			sGeneratedCode.Append(sParamDeclaration.Remove(sParamDeclaration.Length - 3, 3));			
			sGeneratedCode.Append(Environment.NewLine);
			sGeneratedCode.Append("AS");
			sGeneratedCode.Append(Environment.NewLine);
			sGeneratedCode.Append(sBody.Remove(sBody.Length -3, 3));			
			if (sptypeGenerate == StoredProcedureTypes.INSERT)
			{
				sGeneratedCode.Append(")");
				sGeneratedCode.Append(Environment.NewLine);
				sGeneratedCode.Append(sINSERTValues.Remove(sINSERTValues.Length - 3, 3));
				sGeneratedCode.Append(")");	
			}
			sGeneratedCode.Append(Environment.NewLine);
			sGeneratedCode.Append("GO");
					
			return sGeneratedCode.ToString();
		}
	}
}

sound.
tim

Re: added a new feature... DELETE
kiki5962

5:50 5 Apr '07

Voici la gestion du DELETE et surtout une meilleure gestion des colonnes IDENTITY et des colonnes inclues en clé primaire
Les colonnes Identity ne sont plus insérées/updatées et les restrictions se basent sur les clés primaires.

using System;
using System.Text;

namespace Bluegrass.Data
{
///
/// Supported Stored Procedure types
///
public enum StoredProcedureTypes
{
UPDATE,
INSERT,
DELETE
}

///
/// Stored Procedure Helper class
///
public class StoredProcedure
{

private void AddParameterDeclaration(StringBuilder sParamDeclaration, SQLDMO.Column colCurrent,bool onlyPrimaryKey)
{

if (onlyPrimaryKey && !colCurrent.InPrimaryKey) return;

if (!colCurrent.Identity)
{
if (sParamDeclaration.Length > 0)
{
sParamDeclaration.Append(',').Append(Environment.NewLine);
}

// Param Declaration construction
sParamDeclaration.AppendFormat(" @{0} {1}", colCurrent.Name, colCurrent.Datatype);

// Only binary, char, nchar, nvarchar, varbinary and varchar may have their length declared
if (
colCurrent.Datatype == "binary" ||
colCurrent.Datatype == "char" ||
colCurrent.Datatype == "nchar" ||
colCurrent.Datatype == "nvarchar" ||
colCurrent.Datatype == "varbinary" ||
colCurrent.Datatype == "varchar")
sParamDeclaration.AppendFormat("({0})", colCurrent.Length);
}
}
private void AddParameterWhere(StringBuilder sWhereClause, SQLDMO.Column colCurrent)
{
if (colCurrent.InPrimaryKey)
{
if (sWhereClause.Length == 0)
sWhereClause.AppendFormat(" WHERE {0} = @{0} ", colCurrent.Name);
else sWhereClause.AppendFormat(" AND {0} = @{0} ", colCurrent.Name);
sWhereClause.Append(Environment.NewLine);
}
}
private void AddParameterUpdate(StringBuilder sUpdateClause, SQLDMO.Column colCurrent)
{
if (!colCurrent.Identity)
{
if (sUpdateClause.Length == 0)
sUpdateClause.AppendFormat(" SET {0} = @{0} ", colCurrent.Name);
else sUpdateClause.AppendFormat(" ,SET {0} = @{0} ", colCurrent.Name);
sUpdateClause.Append(Environment.NewLine);
}
}
private void AddParameterInsert(StringBuilder sInsertClause,StringBuilder sInsertValues, SQLDMO.Column colCurrent)
{
if (!colCurrent.Identity)
{
if (sInsertClause.Length == 0)
{
sInsertClause.AppendFormat("{0}", colCurrent.Name);
sInsertValues.AppendFormat("@{0}", colCurrent.Name);
}
else
{
sInsertClause.AppendFormat(",{0}", colCurrent.Name);
sInsertValues.AppendFormat(",@{0}", colCurrent.Name);
}
}
}

///

/// construit les requêtes SQL INSERT, UPDATE et DELETE
///

/// /// /// /// public string Generate(StoredProcedureTypes sptypeGenerate, SQLDMO.Columns colsFields, string sTableName)
{
StringBuilder sGeneratedCode = new StringBuilder();
StringBuilder sParamDeclaration = new StringBuilder();

StringBuilder sInsertValues = new StringBuilder();
StringBuilder sInsertClause = new StringBuilder();
StringBuilder sUpdateClause = new StringBuilder();
StringBuilder sWhereClause = new StringBuilder();

switch (sptypeGenerate)
{
case StoredProcedureTypes.UPDATE:
{
foreach (SQLDMO.Column colCurrent in colsFields)
{
/* generation des paramètres */
AddParameterDeclaration(sParamDeclaration, colCurrent,false);
/* generation du code */
AddParameterUpdate(sUpdateClause, colCurrent);
/* generation des restrictions */
AddParameterWhere(sWhereClause, colCurrent);
}
break;
}
case StoredProcedureTypes.INSERT:
foreach (SQLDMO.Column colCurrent in colsFields)
{
/* generation des paramètres */
AddParameterDeclaration(sParamDeclaration, colCurrent, false);
/* generation du code */
AddParameterInsert(sInsertClause, sInsertValues, colCurrent);
}
break;
case StoredProcedureTypes.DELETE:
foreach (SQLDMO.Column colCurrent in colsFields)
{
/* generation des paramètres */
AddParameterDeclaration(sParamDeclaration, colCurrent,true);
/* generation des restrictions */
AddParameterWhere(sWhereClause, colCurrent);
}
break;
}

// Setup SP code, begining is the same no matter the type
sGeneratedCode.AppendFormat("CREATE PROCEDURE {0}_{1}", sTableName, sptypeGenerate.ToString());
sGeneratedCode.Append(Environment.NewLine);

//déclaration des paramètres
if (sParamDeclaration.Length > 0)
{
sGeneratedCode.AppendFormat("({0})", sParamDeclaration);
sGeneratedCode.Append(Environment.NewLine);
}
sGeneratedCode.Append(" AS ");
sGeneratedCode.Append(Environment.NewLine);

// Setup body code, different for UPDATE and INSERT
switch (sptypeGenerate)
{
case StoredProcedureTypes.INSERT:
sGeneratedCode.AppendFormat("INSERT INTO [{0}] ({1}) VALUES({2})", sTableName, sInsertClause, sInsertValues);
sGeneratedCode.Append(Environment.NewLine);
break;
case StoredProcedureTypes.UPDATE:
sGeneratedCode.AppendFormat("UPDATE [{0}] {1} {2} {3}", sTableName, Environment.NewLine,sUpdateClause, sWhereClause);
sGeneratedCode.Append(Environment.NewLine);
break;
case StoredProcedureTypes.DELETE:
sGeneratedCode.AppendFormat("DELETE FROM [{0}] {1} {2}", sTableName, Environment.NewLine,sWhereClause);
sGeneratedCode.Append(Environment.NewLine);
break;
}

sGeneratedCode.Append(Environment.NewLine);
sGeneratedCode.Append("GO");
sGeneratedCode.Append(Environment.NewLine);

return sGeneratedCode.ToString();
}
}
}

Open Source SQLDMO "Wrapper" Project
chanthailong

11:17 5 Nov '06

I would like everyone reading this article to know about a new open source project: SQLDMO.NET - a SQL Database Management Object for .NET.

SQLDMO.NET is meant to be a light weight .NET wrapper of the (COM) SQLDMO object that allows .NET developers the ability to manage SQL Server 7.0/2000 databases. This library provides an alternative to using the full strength of SQL SMO (e.g. to avoid installing SQL SMO and/or its prerequisites such as SP2 on Windows XP), or dealing with SQLDMO directly via interop. SQLDMO.NET is, and will be, characterized by a limited set of extremely practical services, such as:

. Finding all SQL Servers on a local network node
. Enumerating registered SQL Server groups and servers
. Creating a database via a SQL script
. Generating a SQL script of DDL for an existing database
. Adding/Deleting Stored Procedures, Tables, Views, etc, etc...
. Creating a new user login

The project is being hosted at http://code.google.com/p/sqldmonet/. Please feel free to check it out. We are in the early stages and anyone who wants to join and/or contribute is more than welcome!

...

Re: Open Source SQLDMO "Wrapper" Project
NetDefender

0:22 10 Oct '09

Thanks
Sign In·View Thread·PermaLink

Open Source SQLDMO "Wrapper" Project
chanthailong

11:16 5 Nov '06

Nice but it needs more work..
k_nanev@hotmail.com

13:00 3 Aug '06

I really liked it but i think in real life you will more than just insert and update SPs. For instance: Delete, Update if exist or Insert if doesnt exist, custom querres.
Sign In·View Thread·PermaLink	1.00/5

Re: Nice but it needs more work..
chanthailong

11:22 5 Nov '06

This is exactly the sort of thing we are trying to expand upon at the SQLDMO.NET project: http://code.google.com/p/sqldmonet/.

Feel free to check out our latest code, and contribute some of your ideas for features/improvements. For example, the ability to Delete, Update (if exists) or Insert (if doesn't exist) custom queries is something you could add to our issue tracker (as type "Enhancement").

Re: Nice but it needs more work..
john.burban

4:43 15 Dec '07

Definetly a nice tool. Our dev department uses OxyGen Code (http://www.techinceptions.com/codegenerator.html). This is not a free tool though. The advantage of OxyGen Code is that you can capture relationships between tables and also generates C# code for you.
Sign In·View Thread·PermaLink	2.00/5

minor bugs, but can't complain, it's free !!
prideaux90

18:20 7 Feb '06

it is very useful and saved me lots of typing and typos! thanks. bugs: did not handle some of my field names e.g. [--my field name--] 1800_1300_fieldname
Sign In·View Thread·PermaLink

WindowsAuthentication
downhillchris

18:31 2 Sep '05

My instance of SQL uses windows authentication and SPGen requires a user name and password to connect. I have tried connecting with my windows login details too. Is there any other work around for this problem?
Sign In·View Thread·PermaLink

Re: WindowsAuthentication
stmarti

1:17 26 Sep '05

Download the source not the compiled executable.
Open SQLDMOHelper.cs in vs.net, find the following method:

public void Connect()
{
 Connection.Connect(ServerName, UserName, Password);
}

replace with:

public void Connect()
{
 Connection.LoginSecure = true;
 Connection.Connect(ServerName, null, null);
}

Recompile, and now all connection are trusted. /This is the easiest path, little more effort needs to implement a checkbox indicating trusted and nontrusted connection./

Reset PasswordChar
Babu Aboobacker E.I

16:29 23 Mar '05

Thanks for providing the code snippet to reset the PasswordChar. May GOD accept all our good deeds, and keep us in the right path. Babu Aboobabacker E.I
Sign In·View Thread·PermaLink