检查MS Access数据库表是否存在创建它
答
简单的执行下面的代码,如果表将存在,它将返回其他方法错误,将创建一个新的:如果一个表已经存在
try
{
OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + frmMain.strFilePath + "\\ConfigStructure.mdb");
myConnection.Open();
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = "CREATE TABLE <yourtable name>(<columns>)";
myCommand.ExecuteNonQuery();
myCommand.Connection.Close();
}
catch(OleDbException e)
{
if(e.ErrorCode == 3010 || e.ErrorCode == 3012)
// if error then table exist do processing as required
}
这些错误代码返回 - check here所有。
答
您可以遍历表名来检查特定的表。请参阅下面的代码来获取表名。
string connectionstring = "Your connection string";
string[] restrictionValues = new string[4]{null,null,null,"TABLE"};
OleDbConnection oleDbCon = new OleDbConnection(connectionString);
List<string> tableNames = new List<string>();
try
{
oleDbCon.Open();
DataTable schemaInformation = oleDbCon.GetSchema("Tables", restrictionValues);
foreach (DataRow row in schemaInformation.Rows)
{
tableNames.Add(row.ItemArray[2].ToString());
}
}
finally
{
oleDbCon.Close();
}
答
为了完整起见,我会指出,前阵子我贴4种不同的编码了TableExists() function within Access的方式。在MSysObjects上运行SQL SELECT的版本可以从外部访问,尽管在某些情况下,您可能会遇到安全错误(因为您不允许访问Jet/ACE系统表)。
答
要检查表存在,你可以这样延伸的DbConnection:
public static class DbConnectionExtensions
{
public static bool TableExists(this DbConnection conn, string table)
{
conn.open();
var exists = conn.GetSchema("Tables", new string[4] { null, null, table, "TABLE" }).Rows.Count > 0;
conn.close();
return exists;
}
}
然后就可以调用TableExists在这样的OleDbConnection,SQLiteConnection或SqlConnection的任何派生类。
答
一个简单的方法做,这是
public bool CheckTableExistance(string TableName)
{
// Variable to return that defines if the table exists or not.
bool TableExists = false;
// Try the database logic
try
{
// Make the Database Connection
ConnectAt();
// Get the datatable information
DataTable dt = _cnn.GetSchema("Tables");
// Loop throw the rows in the datatable
foreach (DataRow row in dt.Rows)
{
// If we have a table name match, make our return true
// and break the looop
if (row.ItemArray[2].ToString() == TableName)
{
TableExists = true;
break;
}
}
//close database connections!
Disconnect();
return TableExists;
}
catch (Exception e)
{
// Handle your ERRORS!
return false;
}
}
+1,不仅仅是捕捉错误更优雅。请删除`catch {throw;但是:这是一个NOOP。 – Heinzi 2013-01-24 14:31:30