// create the purviewTable
public static bool CreatePurviewTab(string dbName)
{
string totaltableSql = "create table purviewtable(id integer identity(1,1) primary key," +
" dept_userid integer not null," +
" dept_table varchar(50) not null," +
" purview integer not null )";
OleDbConnection con = getCon(dbName);
OleDbCommand cmd = con.CreateCommand();
cmd.Connection = con;
cmd.CommandText = totaltableSql;
try
{
cmd.ExecuteNonQuery();
}
catch (SystemException ex)
{
return(false);
}
// close the con
AccessOp.closeAll(con, cmd, null);
return(true);
}
// 创建表
// 通过遍历map对象，获取字段名称,以及字段类型
public static bool CreateAccessTab(MyDatabase domain)
{
// get the database value
string dbName = domain.DbName;
string tableName = domain.TableName;
Dictionary <string, string> columns = domain.Columns;
// end get the database value
// the head sql
string tabSql = "create table ?(id int not null identity(1,1) primary key";
// make suer the table name
tabSql = tabSql.Replace("?", tableName);
// the end ")"
string end = ")";
foreach (KeyValuePair <string, string> entry in columns)
{
string column_name = entry.Key;
string column_prop = entry.Value;
string catSql = ", " + column_name + " " + column_prop + " not null";
// catSql = catSql.Replace("?", column_name).Replace("?", column_prop);
tabSql = tabSql + catSql;
}
// the created SQL has finished
tabSql = tabSql + end;
// connect the db
OleDbConnection con = AccessOp.getCon(dbName);
OleDbCommand cmd = con.CreateCommand();
cmd.Connection = con;
cmd.CommandText = tabSql;
try
{
cmd.ExecuteNonQuery();
}
catch (SystemException ex)
{
return(false);
}
// close the con
AccessOp.closeAll(con, cmd, null);
return(true);
}
// get table columns
public static List <string> getColumns(string dbName, string tabName)
{
List <string> list = new List <string>();
string sql = "select top 1 * from " + tabName;
OleDbConnection con = getCon(dbName);
OleDbCommand cmd = con.CreateCommand();
cmd.Connection = con;
cmd.CommandText = sql;
OleDbDataReader reader = cmd.ExecuteReader();
for (int i = 0; i < reader.FieldCount; i++)
{
list.Add(reader.GetName(i));
}
// close the connection
AccessOp.closeAll(con, cmd, reader);
return(list);
}
// 获取数据库连接
public static OleDbConnection getCon(string db)
{
return(AccessOp.getCon(db));
}