ADO.NET 3.5 Cookbook, 2nd Edition

11.9. Adding a Table to a SQL Server Database

Problem

You need to add a table to a SQL Server database.

Solution

Create the table using either SQL Management Objects (SMO) or using the CREATE TABLE T-SQL data definition language (DDL) statement. The solution demonstrates both approaches.

The solution first uses SMO to create a new database called MySmoTable. Next, the solution executes a CREATE TABLE T-SQL DDL statement to create a new database named MyDdlTable.

The solution needs a reference to the Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Smo, and Microsoft.SqlServer.SqlEnum assemblies.

The C# code in Program.cs in the project AddTableSqlServerDatabase is shown in Example 11-9.

Example 11-9. File: Program.cs for AddTableSqlServerDatabase solution

using System; using System.Data; using System.Data.SqlClient; using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management.Common; namespace AddTableSqlServerDatabase { class Program { static void Main(string[] args) { // Use SMO to create a table and add it to the database Console.WriteLine("---Create database using SMO---"); Server server = new Server("(local)"); Database db = server.Databases["AdoDotNet35Cookbook"]; // Create a new table Table table = new Table(db, "MySmoTable"); // Create a column Column col = new Column(table, "MySmoTableId"); col.DataType = DataType.Int; col.Nullable = false; col.Identity = true; col.IdentitySeed = 1; col.IdentityIncrement = 1; // Add the column to the table table.Columns.Add(col); ...