Super simple: sharing two tools to automatically generate stored procedures

When developing a project or developing an application system, most of them begin with databases, classes, and UI interfaces. The most annoying is writing simple crud stored procedures and classes that call these stored procedures.

I spent a lot of time searching online to find a practical program that can generate most stored procedures and call the C # code of these stored procedures based on an existing data table in the database. Here we will share two programs that I think are good for you.

The first one is sqlautogen, such:

Program address: http://www.codeproject.com/KB/database/SQLAutoGen.aspx

This is the code we need by iteratively selecting columns in the data table. By selecting different columns (which will appear in the WHERE clause), the production script can be quite intelligent. (See the following sample code)
Looking at the above picture, we can say a lot, it indicates the operation of this application.
The following code iterates through the program in different ways. Here, we use sqlclient and sqlsmo to access the database and obtain table and columns information:

Code

// Code to iterate tables
Private serverconnection = NULL;

Private void btngo_click (Object sender, eventargs E)

{

This. cursor = cursors. waitcursor;

Lsttables. Items. Clear ();

Sqlconnection objcn = new sqlconnection (sqlcontrol1.connectionstring );

Serverconnection = new serverconnection (objcn );

Server = new server (serverconnection );

Tablecollection objtables = server. Databases [sqlcontrol1.databasename]. tables;

Foreach (Table objtable in objtables)

{

Lsttables. Items. Add (objtable. Name );

}

This. cursor = cursors. default;

}

 

 

Code

// Snippet to show column iteration and generation of script.

If (chkselect. Checked)

{

Strsql = "create procedure [getall" + strtablename + "]" + environment. newline;

Strsql + = "as select ";

Foreach (column item in server. Databases [sqlcontrol1.databasename]. Tables [strtablename]. columns)

{

Keycount ++;

Strsql + = "[" + item. Name. tostring () + "]";

If (keycount <server. Databases [sqlcontrol1.databasename]. Tables [strtablename]. Columns. Count) strsql ++ = "," + environment. newline;

}

Strsql + = "from [" + strtablename + "]";

Txtsql. Text + = strsql + environment. newline + environment. newline;

}

The following is an example Table screen and subsequent scripts.

 

 

Code

Create procedure [getallvariables] as select [ID], [varname], [vardesc], [vartype] from [variables]

Create procedure [deletevariables] (@ id int) as delete from [variables] Where id = @ ID

Create procedure [addvariables] (@ varname varchar, @ vardesc varchar, @ vartype varchar) as insert into [variables] (varname, vardesc, vartype) values (@ varname, @ vardesc, @ vartype) Select scope_identity ()

Create procedure [updatevariables] (@ ID int, @ varname varchar, @ vardesc varchar, @ vartype varchar) as update [variables] Set varname = @ varname, vardesc = @ vardesc, vartype = @ vartype where id = @ ID

Create procedure [addupdatevariables] (@ ID int, @ varname varchar, @ vardesc varchar, @ vartype varchar) as begin if (select ID from [variables] Where id = @ ID) <> 0 begin update [variables] Set varname = @ varname, vardesc = @ vardesc, vartype = @ vartype where id = @ ID end else begin insert into [variables] (varname, vardesc, vartype) values (@ varname, @ vardesc, @ vartype) Select scope_identity () end

 

 

The second is the program above codeplex: Stored Procedure generator (for SQL Server 2000/2005). The interface is shown in:

 

The solution structure is as follows:

Address: http://spgen.codeplex.com/

The code is not very difficult. If you are interested, you can download the research on your own. The columns in the WHERE clause in the Stored Procedure produced by this program are fixed, and are doomed to be less flexible than the first program.

For convenience, the following code is provided:

1./files/zhuqil/sqlgensource.zip

2./files/zhuqil/sp_gen_public_sourcecodes_vs2005.zip

I would like to recommend the following two websites to you, which are written on the last day of this year. We look forward to achieving the ultimate in simplicity next year.

1. http://www.codeproject.com/

2. http://www.codeplex.com/

Everyone on Earth knows about the blog Park.