Create Table in SQL Server 2005 Using C# and SQL Server Management Objects (SMO) - Code Generation
by David Hayden ( Florida ASP.NET Developer )
The other day I had this epiphany that my Database Explorer and Code Generator needed to physically create stored procedures in SQL Server 2005 as opposed to just creating scripts that would create stored procedures. This led me down a path where I discovered SQL Server Management Objects (SMO) that are available with SQL Server 2005 and allow me to create stored procedures in SQL Server 2005 programmatically using C# ( and VB ). I created a post that provided a short snippet on how to create stored procedures using C# and SQL Server 2005:
SQL Server Management Objects (SMO) - Create Stored Procedures in C# - Code Generation
This got me wondering if I could also create Tables in SQL Server 2005 using C# and SQL Server Management Objects, and of course the answer is YES. All this new information on SMO has got my mind racing about the code generation possibilities of SQL Server Management Objects and additional features I can add to my Database Explorer and Code Generator. I have a feeling that the Database Explorer part will eventually become a simplistic Database Manager of sorts now that I have discovered these new features.
Create Table in SQL Server 2005 Using C# and SQL Server Management Objects (SMO)
Creating a table in SQL Server 2005 using C# and SQL Server Management Objects is fairly straight forward and not much more complicated than creating stored procedures using SMO. Below is code that creates a simple table, called TestTable, in my personal database, called davidhayden. It creates two columns, ID and Title, with a primary key and identity on the ID column. The remarks on the code give you a step-by-step description:
// Establish the database server
string connectionString = "...";
SqlConnection connection =
new SqlConnection(connectionString);
Server server =
new Server(new ServerConnection(connection));
// Create table in my personal database
Database db = server.Databases["davidhayden"];
// Create new table, called TestTable
Table newTable = new Table(db, "TestTable");
// Add "ID" Column, which will be PK
Column idColumn = new Column(newTable, "ID");
idColumn.DataType = DataType.Int;
idColumn.Nullable = false;
idColumn.Identity = true;
idColumn.IdentitySeed = 1;
idColumn.IdentityIncrement = 1;
// Add "Title" Column
Column titleColumn = new Column(newTable, "Title");
titleColumn.DataType = DataType.VarChar(50);
titleColumn.Nullable = false;
// Add Columns to Table Object
newTable.Columns.Add(idColumn);
newTable.Columns.Add(titleColumn);
// Create a PK Index for the table
Index index = new Index(newTable, "PK_TestTable");
index.IndexKeyType = IndexKeyType.DriPrimaryKey;
// The PK index will consist of 1 column, "ID"
index.IndexedColumns.Add(new IndexedColumn(index,"ID"));
// Add the new index to the table.
newTable.Indexes.Add(index);
// Physically create the table in the database
newTable.Create();
You can easily make this code generic and wrap it up as a method in a class that creates tables using SQL Server Management Objects.
Conclusion
SQL Server Management Objects are a cool new feature with SQL Server 2005 that allow you to create database objects ( tables, stored procedures, etc.) programmatically using C# and VB.NET. This has a number of Code Generation and O/R Mapper possibilities.
Source: David Hayden ( Florida ASP.NET Developer )
SQL Server Tutorials