The beginning of any new project/application mostly starts with a database, some classes, and then the UI. One of the most boring/tedious elements to this is generating the Stored Procedures needed for simple CRUD operations, as well as the class that references the database with all its methods etc.
I searched pretty far and wide for a simple and decent application that would take an existing table from a database and generate the most common Stored Procedures for me, as well as script out the C# code to call these procs. There weren't too many useful applications that worked for me (or actually worked!).
Using the Code
By iterating through a selected table's columns, we can generate the script as need be. By treating some columns differently (i.e., making them appear in a
Where clause, or knowing they can't be updated), the scripting can become fairly intelligent. (See example in code samples below.)
Here, a picture is worth quite a few words. The picture above shows the operations of the application.
Shown below is some code which repeats itself in various ways throughout the app. It's a mixture of SqlClient and SqlSMO operations to interrogate a database and its tables/columns etc.
private ServerConnection serverConnection = null;
private void btnGo_Click(object sender, EventArgs e)
this.Cursor = Cursors.WaitCursor;
SqlConnection objCn = new SqlConnection(sqlControl1.ConnectionString);
serverConnection = new ServerConnection(objCn);
Server server = new Server(serverConnection);
TableCollection objTables =
foreach (Table objTable in objTables)
this.Cursor = Cursors.Default;
strSQL = "CREATE PROCEDURE [GetAll" +
strTableName + "] " + Environment.NewLine;
strSQL += " As Select ";
foreach (Column item in server.Databases[
strSQL += "[" + item.Name.ToString() + "]";
if (keyCount < server.Databases[
strSQL += "," + Environment.NewLine;
strSQL +=" from [" + strTableName + "]";
txtSQL.Text += strSQL + Environment.NewLine + Environment.NewLine;
Below is a screenshot of a sample table, and the subsequent generated scripts.
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 End
Points of Interest
Nothing too special here about this project, just some string manipulation and working with the SQL SMO objects.
Note: The "Save To File" button will save each script individually. Also, I include "
select SCOPE_IDENTITY()" in my insert procedures, because that's something I use quite often.
This is the first iteration. The next one will be adding the ability to generate the classes and interfaces for a table.