Creating Tables in Run time using c#

[Raghu_M]

I am doing Project in OPC.In that project,when ever user create Group means,it has to create in Database also in runtime.I am using Sql Server 2005 and C#.My Question is how to create table with four columns in database at run time using c#.I am waiting for your reply. Thanks and Regards M.Raghu

[Pete OHanlon]

You could always just write the SQL to create a table and execute it using a SqlCommand, but a better way to do it would be to use Sql Server Management Objects (SMO).

private void MakeMyTable()
{
string connectionString = "...";
using (SqlConnection connection = new SqlConnection(connectionString))
{
Server server =
new Server(new ServerConnection(connection));

// Create table in my personal database
Database db = server.Databases\["MyDatabase"\];

// Create new table, called TestTable
Table newTable = new Table(db, "MyTestTable");

// Create the primary key
Column pk = GetColumn(newTable, "id", DataType.Int, false);
pk.Identity = true;
pk.IdentitySeed = 1;
pk.IdentityIncrement = 1;

newTable.Add(pk);

DataType varchar = new DataType(SqlDataType.NVarChar, 50);
newTable.Add(GetColumn(newTable, "Name", varchar, false));
newTable.Add(GetColumn(newTable, "Nickname", varchar, true));
newTable.Add(GetColumn(newTable, "Title", varchar, false));

// Create a PK Index for the table
Index index = new Index(newTable, "PK\_MyTestTable");
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();

}
}

private Column GetGolumn(Table tb, string columnName, DataType type, bool isNullable)
{
Column column = new Column(tb, columnName);
column.DataType = type;
column.Nullable = nullable;
}

"WPF has many lovers. It's a veritable porn star!" - Josh Smith

As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.

My blog | My articles | MoXAML PowerToys | Onyx