Dotnet-Friends.com | SMO Scripting: Create MS SQL Table Using SMO and C#,Omer Kamal

Write for Dotnet-friends and earn for your each submission

Home

Latest

Fast Code

Articles

Tutorials

Online Resources

Forums

	Home

Forums

Tutorials

Articles

Fast Code

Selected Articles

Online Resources

ASP.NET NEWS

ASP.NET Latest

ASP.NET Blogs

ScottGu's Blog

4GuysFromRolla

The Code Project

ASP Alliance

Dotnetbips

Donations

Search Members

Search Web

Your IP Address

Sister Website FriendsPoint.de

Add Yours

Write an Article
Write a Tutorial
Add a Fast Code*
*Fast Code might be any helpful code with brief comment

Sponsors Links

Stem Cells
Blood Banks
Insurances
.Net Hosting
Credit Cards
PC Rental
Business

Our Feeds(RSS)

Tutorials:
ASP .NET
C# .NET
VB .NET
Articles:
ASP .NET
C# .NET
VB .NET
Fast Code:
ASP .NET
C# .NET
VB .NET

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

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

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:

@@ Create a Simple Web Part for SharePoint 2007 using Visual Studio Extensions for SharePoint 2007 Development

@@ Create a Rollover ImageButton Web User Custom Control and add it to your ASP .NET Or SharePoint Site

@@ Windows SharePoint Services v3.0 vs Microsoft Office SharePoint Server 2007

@@ AJAXified your SharePoint Webpart with using AJAX Update Panal

@@ What is AJAX and Microsoft AJAX Library?

@@ Use Microsoft AJAX Library and add Client Capabilities in you web application.

@@ Microsoft Dynamics CRM 4.0 Software Development Kit is now available!

@@ How to create a Transparent Panel Control in .NET?

@@ How to use ASP .NET AdRotater Control To Publish "Discount ASP.NET" adds?

@@ A Sorted List of Users on their Registration date using ASP .NET Membership Objects

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)