German Wear Discount Shop - Click Here Write for Dotnet-friends and earn for your each submission [Dot]Net-Friends
Skip Navigation Links
Home
Latest
Fast Code
Articles
Tutorials
Online Resources
Forums
Login   | Hi, Guest


SMO Scripting: Create MS SQL Table Using SMO and C#
Written By Omer Kamal On 07/08/2008

Creating a MS SQL Database Table using .NET SMO Object

Views: 238
Rating: Please Rate
Login to Rate
omerkamal
Tagged Under: ADO .NET, Data Connection Objects, Database Managment, Database Programming, SQL Server, T-SQL

Explanation:

The simple SMO Application to create a table is as follows. One of the things I added in this example is to show how to create a default constraint. In this example, the ID, which is a UniqueIdentifier, calls newid() by default to get its value if one is not provided.



// Get Instance of Local SQL Server
Server server = new Server();

// Create In-Memory Database
Database db = new Database(server,"Test");

// Create In-Memory Table
Table contacts = new Table(db, "Contacts");

// Create ID Column and Constraint
Column id = new Column(contacts, "ID", DataType.UniqueIdentifier);
id.AddDefaultConstraint("DF_Contacts_ID") .Text = "newid()";

// Create FirstName and LastName Columns
Column firstName = new Column(contacts, "FirstName", DataType.NVarChar(50));
Column lastName = new Column(contacts, "LastName", DataType.NVarChar(50));

// Create a PK Index on Table
Index index = new Index(contacts, "PK_Contacts");
index.IndexKeyType = IndexKeyType.DriPrimaryKey;

// PK Index is 1 column - "ID"
index.IndexedColumns.Add(new IndexedColumn(index, "ID"));

// Add the new index to the table.
contacts.Indexes.Add(index);

// Add Columns to Table
contacts.Columns.Add(id);
contacts.Columns.Add(firstName);
contacts.Columns.Add(lastName);

// Make Sure Constraint is Scripted
ScriptingOptions options = new ScriptingOptions();
options.Add(ScriptOption.DriAllConstraints);

// Get Script for Table
// It Only Exists in Memory

StringCollection script = contacts.Script(options);



The script generated looks like the following:



CREATE TABLE [dbo].[Contacts]
(
[ID] [uniqueidentifier] CONSTRAINT [DF_Contacts_ID] DEFAULT newid(),
[FirstName] [nvarchar](50),
[LastName] [nvarchar](50),
CONSTRAINT [PK_Contacts] PRIMARY KEY
(
[ID]
)
)
Delicious Digg reddit reddit Technorati
About the Author:

@@ Omer Kamal is a Software Developer at Elanize KG Germany. He MSc. Mathematics from Islamia University Bahawalpur, Pakistan and Certified Developer from National Institute of Electronics Islamabad, Pakistan. He is Founder of FriendsPoint.de and Dotnet-Friends.com. He is currently Involved with Microsoft Office SharePoint 2007, Microsoft Dynamics CRM, BI Portal Solutions (Microsoft Dynamics Customization) and Web Security Solutions.
Check Omer Kamal Profile

Related Useful Links:
Visitors/Readers Comments
(for questions please use The Forum)



"Be the First to Comment!"


Add your Comments

Name:  
Message:
Note: For faster response please use Forums >> for your questions instead of the comments area! (Admin)