C#检查数据库中是否存在表
本文关键字:存在 是否 检查 数据库 | 更新日期: 2023-09-27 18:25:07
SQL Server 2008中的同一数据库中有两个表。我想将一个表的内容复制到另一个具有相同模式的空表中,但当我执行语句时
OleDbDataAdapter da = new OleDbDataAdapter("select * into dbo.leadmastersnew from dbo.leadmasters", myConnection);
它抛出一个错误,表示该表已存在于数据库中。
我希望如果该表已经存在,那么应该删除该表,因为上面的SQL查询创建了该表并将内容复制到其中
请建议
我的代码是
private void button5_Click(object sender, EventArgs e)
{
OleDbConnection myConnection = new OleDbConnection("File Name=E:''Vivek''ImplementUdl''ImplementUdl''new.udl");
try
{
myConnection.Open();
if (myConnection.State == ConnectionState.Open)
MessageBox.Show("Connection opened successfully!");
else
MessageBox.Show("Connection could not be established");
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter("select * into dbo.leadmastersnew from dbo.leadmasters", myConnection);
da.Fill(ds);
MessageBox.Show("Data Copied!!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
myConnection.Close();
}
return;
}
如果还不存在,我会创建一个空表,然后复制数据。如果可能的话,不要使用udl文件进行连接,而是将连接字符串存储在的其他地方
using(var conn = new SqlConnection("<connectionstring>"))
using(var cmd = new SqlCommand(@"
if object_id('dbo.leadmastersnew') is null
begin
select * into dbo.leadmastersnew from dbo.leadmasters where 1=2
alter table dbo.leadmastersnew add primary key (<key>)
end
insert into dbo.leadmastersnew
select *
from dbo.leadmasters lm
where not exists(select * from dbo.leadmastersnew lmn where lm.<key> = lmn.<key>)", conn))
{
conn.Open();
cmd.ExecuteNonQuery();
}
请注意,在where子句中,您需要填写正确的<key>
列。我还向新表添加了主键约束,因为select into不会复制源表的主键/索引。
我倾向于在一个SQL语句中完成所有操作:
IF OBJECT_ID(N'dbo.leadmastersnew', 'U') IS NOT NULL
DROP TABLE dbo.leadmastersnew;
SELECT *
INTO dbo.leadmastersnew
FROM dbo.leadmasters;
要回答SQL为什么不起作用的实际问题,出现错误的原因是:
select * into dbo.leadmastersnew from dbo.leadmasters
SQL将其解释为创建表leadmastersnew
的命令,然后填充它
if object_id('dbo.leadmastersnew') is not null begin drop table dbo.leadmastersnew end select * into dbo.leadmastersnew from dbo.leadmasters
这将测试表是否存在,如果存在则将其删除,然后再次创建和填充表。这不是一种非常干净的做事方式,但如果这是你想要的,那么它就会做到
问题的解决方案可能是使用此查询
EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'TheSchema'
AND TABLE_NAME = 'TheTable'))
之后,如果返回True,则表存在。现在您需要运行插入查询
String query = "INSERT INTO dbo.The_table(id,username,password,email) VALUES(@id,@username,@password, @email)";
SqlCommand command = new SqlCommand(query, db.Connection);
command.Parameters.Add("@id","abc");
command.Parameters.Add("@username","abc");
command.Parameters.Add("@password","abc");
command.Parameters.Add("@email","abc");
command.ExecuteNonQuery();
如果原始语句返回false,则需要在运行插入查询之前运行创建表查询。创建表查询看起来很像这样。
string queryString = @"
CREATE TABLE MyTable
(
Id int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Name varchar(50)
)";
SqlCommand command = new SqlCommand(
queryString, connection);
command.ExecuteNonReader();