Using SMO for Dynamically Creating Stored Procedure and Database Tables in c#.net
- Tauseef.M
According to me “Giving a deadly desired storm to your brain yields in an ‘invention ‘.
What is SMO??
Server Management Object (SMO) is Microsoft® .NET Framework library of objects that let you manage one or more database servers. SMO can be used to examine a database’s objects and to perform a variety of administrative tasks like backing up and restoring databases, and issuing Data Definition Language (DDL) commands. Using SQL SMO you can also connect to SQL Server, iterate through a collection of database objects and perform a variety of tasks against them.
Let me demonstrate the use of SMO for dynamically creating of Stored Procedures and Database Tables.
The two main DLLs you must reference are:
- Microsoft.SqlServer.Smo.dll
- Microsoft.SqlServer.ConnectionInfo.dll
You will also need the following assembly for creating indexes for primary key:
Microsoft.SqlServer.SqlEnum.dll
After adding the above assemblies let’s begin Dynamic Creation of Stored Procedure and Database Tables.
Dynamically Creating Database TablesBelow is a simple Code that creates the database table known as tblEmployee in my personal database Employee with a primary key and identity on the ID column.
SqlConnection conn = new SqlConnection
("Server=<servername>;DATABASE=<databasename>;uid=<userid>;pwd=<pwd>");
//Create Server instance
Server server = new Server(new ServerConnection(conn));
//Create Database Instance from existing databases
Database database = server.Databases["Employee"];
//Create Table
Microsoft.SqlServer.Management.Smo.Table table = new
Microsoft.SqlServer.Management.Smo.Table(database, "tblEmployee");
//Create Column "ID" as Primary key
Column IDcolumn = new Column(table, "ID");
IDcolumn.DataType = DataType.Int; //Specify the datatype
IDcolumn.Nullable = false; //Allow Nulls=False
IDcolumn.Identity = true; //Set Identity=True
IDcolumn.IdentitySeed = 1; //Set Identity to start from 0
IDcolumn.IdentityIncrement = 1; //Increment identity value by 1
//Create Column "Title"
Column TitleColumn = new Column(table, "Title");
TitleColumn.DataType = DataType.VarChar(50);
TitleColumn.Nullable=true;
//Add Columns to the "table"
table.Columns.Add(IDcolumn);
table.Columns.Add(TitleColumn);
//Create index for primary key column "ID"
Index prIndex = new Index(table, "PK_ID");
//specify as primary key
prIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
//Assign the column "ID" to the created Index
prIndex.IndexedColumns.Add(new IndexedColumn(prIndex,"ID"));
//Add the new Index to the table
table.Indexes.Add(prIndex);
//PHYSICALLY create the table in the database
table.Create();
Response.Write("Table Created Sucessfully...");
Dynamically Creating Stored Procedures
Below is a simple Code that creates the Stored Procedure known as “dsp_created “in my personal database Employee with @cityid being the input variable.
//Establish Conection
SqlConnection conn = new SqlConnection
("Server=<servername>;DATABASE=<databasename>;uid=<userid>;pwd=<pwd>");
//Create Server instance
Server server = new Server(new ServerConnection(db));
//Select the database where stored procedure needs to be added
Database dsb = server.Databases["Employee"];
//Create a Stored Procedure named "dsp_created" in "Employee"
StoredProcedure sp = new StoredProcedure(dsb, "dsp_created");
sp.TextMode = false; //set TextMode as false
sp.AnsiNullsStatus = false; //set AnsiNullsStatus as false
sp.QuotedIdentifierStatus = false;
StoredProcedureParameter idparam = new
StoredProcedureParameter(sp, "@cityid", DataType.Int);
sp.Parameters.Add(idparam);
//The Sql Statement
sp.TextBody = "Select *from tblCity where Citycode= @cityid";
//PHYSICALLY create Stored Procedure
sp.Create();
Response.Write("Stored Procedure Created Sucessfully....");
Dynamically deletion of stored procedure is also very simple, which will be explained later.(The author, Tauseef.M, is a Software Engineer at Binary Spectrum.)