To create object in MS Access by Sql Script

[TestClass]
public class AccessSqlScriptTest
{
public AccessSqlScriptTest()
{
//
// TODO: Add constructor logic here
//
}

#region Additional test attributes
//
// You can use the following additional attributes as you write your tests:
//
// Use ClassInitialize to run code before running the first test in the class
// [ClassInitialize()]
// public static void MyClassInitialize(TestContext testContext) { }
//
// Use ClassCleanup to run code after all tests in a class have run
// [ClassCleanup()]
// public static void MyClassCleanup() { }
//
// Use TestInitialize to run code before running each test
// [TestInitialize()]
// public void MyTestInitialize() { }
//
// Use TestCleanup to run code after each test has run
// [TestCleanup()]
// public void MyTestCleanup() { }
//
#endregion

[TestMethod]
[Ignore]
public void CreateTable()
{
/// create table
const string TABLE_NAME = "Tab1";
string cmdText = string.Format(@"Create Table {0} (
ID Counter,
Name string,
Age integer,
[Date] DateTime); ", TABLE_NAME);
AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);

// create index
//string indexName = "iName";
//cmdText = string.Format(@"Create Index {0} ON {1} ([Name]); ", TABLE_NAME, indexName);
//AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);

// create unique index
//string indexID = "iID";
//cmdText = string.Format(@"Create Unique Index {0} ON {1} ([ID]); ", TABLE_NAME, indexID);
//AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);

//// drop index
//cmdText = string.Format(@"Drop Index {0} ON {1};",indexID,TABLE_NAME);
//AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);
//cmdText = string.Format(@"Drop Index {0} ON {1};", indexName, TABLE_NAME);
//AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);

// drop table
cmdText = string.Format(@"Drop Table {0};", TABLE_NAME);
AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);
}
[TestMethod]
[Ignore]
public void CreateStoreProcedure()
{
// creat store procedure
const string procedureName = "CreateStoreProcedureTest";
string cmdText = string.Format(@"CREATE PROC {0} AS SELECT * FROM t_Cow;"
, procedureName);
AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);

// drop store procedure
cmdText = string.Format(@"Drop PROC {0};", procedureName);
AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);
}
[TestMethod]
[Ignore]
public void CreateStoreProcedureWithParams()
{
// creat store procedure
const string procedureName = "CreateStoreProcedureWithParamsTest";
string cmdText = string.Format(@"CREATE PROC {0} ([@ID] Short) AS SELECT * FROM t_Cow WHERE ID=[@ID];"
, procedureName);
AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);

// drop store procedure
cmdText = string.Format(@"Drop PROC {0};", procedureName);
AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);
}
[TestMethod]
[Ignore]
public void AlterTableTest()
{
/// create table
const string TABLE_NAME = "Tab1";
string cmdText = string.Format(@"Create Table {0} (
ID Counter,
Name string,
Age integer,
[Date] DateTime); ", TABLE_NAME);
AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);
// add column
//ALTER TABLE [表名] ADD [字段名] INT
const string NewColumn = "NewColumn";
cmdText = string.Format(@"Alter Table {0} ADD [{1}] INT", TABLE_NAME, NewColumn);
AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);

// update column
//ALTER TABLE [表名] ALTER COLUMN [字段名] [字段类型]

// if not specified the text length, the column will be created as Memo type : Alter Table {0} Alter Column [{1}] Text;
cmdText = string.Format(@"Alter Table {0} Alter Column [{1}] Text(200);", TABLE_NAME, NewColumn);
AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);

// delete column
//ALTER TABLE [表名] DROP COLUMN [字段名]

cmdText = string.Format(@"Alter Table {0} Drop Column [{1}];", TABLE_NAME, NewColumn);
AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);

// drop table
cmdText = string.Format(@"Drop Table {0};", TABLE_NAME);
AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);
}
[TestMethod]
[Ignore]
public void AlterProcedureTest()
{
// first check if it exists
// delete it first
// creat store procedure
const string procedureName = "CreateStoreProcedureTest";
string cmdText = string.Format(@"CREATE PROC {0} AS SELECT * FROM t_Cow;"
, procedureName);
AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);

// Alter it by droping it then creating it with new implementation
cmdText = string.Format(@"Drop PROC {0};", procedureName);
AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);

cmdText = string.Format(@"CREATE PROC {0} ([@ID] Short) AS SELECT * FROM t_Cow WHERE ID=[@ID];"
, procedureName);
AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);

// drop store procedure
cmdText = string.Format(@"Drop PROC {0};", procedureName);
AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);
}

//[TestMethod]
public void CheckObjectExistence()
{
//SELECT count(*) FROM MSysObjects Where Name = 'Admin'
string cmdText = string.Empty;
const string TABLE_NAME = "Tab1";
if (Exists(TABLE_NAME))
{
// drop it
cmdText = string.Format(@"Drop Table {0};", TABLE_NAME);
AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);
}
cmdText = string.Format(@"Create Table {0} (
ID Counter,
Name string,
Age integer,
[Date] DateTime); ", TABLE_NAME);
AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);
Assert.IsTrue(Exists(TABLE_NAME));

// drop it
cmdText = string.Format(@"Drop Table {0};", TABLE_NAME);
AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);
Assert.IsFalse(Exists(TABLE_NAME));
}
private bool Exists(string objectName)
{
// create procedure
const string ProcedureName = "IsExists";
try
{
string cmdText = string.Format(@"Create Proc {0} ([ObjectName] text(50)) AS SELECT count(*) FROM MSysObjects Where Name = [ObjectName];", ProcedureName);
AccessHelper.ExecuteNonQuery(Config.ConnectionString, cmdText);
}
catch (Exception ex)
{
// The procedure has been created.
}

int count = int.Parse(AccessHelper.ExecuteScalar(Config.ConnectionString, ProcedureName,objectName).ToString());

if (count == 1)
return true;
else if (count == 0)
return false;
else
throw new Exception("There are multi objects existing in data base!");
}
}