检查表名是否存在SQL
本文关键字:SQL 存在 是否 检查表 | 更新日期: 2023-09-27 18:09:18
如何在创建新表之前检查表是否已经存在?
更新代码:
private void checkTable()
{
string tableName = quotenameTxt.Text + "_" + firstTxt.Text + "_" + surenameTxt.Text;
string connStr = @"Data Source=|DataDirectory|'LWADataBase.sdf";
// SqlCeConnection conn = new SqlCeConnection(connStr);
// if (conn.State == ConnectionState.Closed) { conn.Open(); }
using (SqlCeConnection conn = new SqlCeConnection(connStr))
{
conn.Open();
SqlCeCommand cmd = new SqlCeCommand(@"SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @tname", conn);
cmd.Parameters.AddWithValue("@tname", tableName);
SqlCeDataReader reader = cmd.ExecuteReader();
if(reader.Read()){
MessageBox.Show("Table exists");}
else{
MessageBox.Show("Table doesn't exist");
createtable();}
Sql Server Compact支持INFORMATION_SCHEMA视图
using (SqlCeConnection conn = new SqlCeConnection(connStr))
{
conn.Open();
SqlCeCommand cmd = new SqlCeCommand(@"SELECT TOP 1 *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @tname", conn);
cmd.Parameters.AddWithValue("@tname", tableName)
SqlCeDataReader reader = cmd.ExecuteReader();
if(reader.Read())
Console.WriteLine("Table exists");
else
Console.WriteLine("Table doesn't exist");
}
编辑在3.5版本中,TOP 1指令似乎不被接受。然而,给定WHERE子句,使用或不使用它应该没有区别,要使其工作,只需将查询更改为
SqlCeCommand cmd = new SqlCeCommand(@"SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @tname", conn);
第二个编辑查看创建表的代码。
(这是在聊天中,我建议将其添加到问题中以确保完整性)
using (SqlCeCommand command = new SqlCeCommand(
"CREATE TABLE ['" + tableName + "'] " +
"(Weight INT, Name NVARCHAR, Breed NVARCHAR)", con))
tableName变量周围的单引号成为表名的一部分。但是检查表是否存在不使用引号。您的代码将穿过尝试再次创建带有引号的表的路径。只要去掉名字周围的引号。
可以使用SqlClientConnection获取数据库中所有对象的列表。
private void checkTable()
{
string tableName = quotenameTxt.Text + "-" + firstTxt.Text + "-" + surenameTxt.Text;
string connStr = @"Data Source=|DataDirectory|'LWADataBase.sdf";
using (SqlCeConnection conn = new SqlCeConnection(connStr))
{
bool isTableExist = conn.GetSchema("Tables")
.AsEnumerable()
.Any(row => row[2] == tableName);
}
if (!isTableExist)
{
MessageBox.Show("No such data table exists!");
}
else
{
MessageBox.Show("Such data table exists!");
}
}
来源:https://stackoverflow.com/a/3005157/1271037