如何在C#中检查在SQL Server 2008 R2上是否存在我的表? [英] How can i check to exist my table on sql server 2008 R2 in C#?

问题描述

我想检查表是否存在.
我试图做某事，但是语法有错误.
我认为我的sql查询不正确.您能帮我编写应创建表并将其插入的查询吗?
并且存在"对象返回false，而该值应该在第一次尝试bloc中返回true.

有人可以帮我吗?

Hi,
I want to check if table exist or doesn''t exist.
I tried to do something, but i have a syntax error.
My sql query is not true, i think. Can you help me to write query that should create table and insert into?
and the "exist" object return false that is supposed to return true in first try bloc.

Could someone help me please ?

bool exist;

            //IMyInterface foo =  
            try {
                SqlCommand cmdExist = new SqlCommand("select case when exist(select * from information_schema.tables where table_name = "+ tableName + ")",sqlConn);
                exist = (int)cmdExist.ExecuteScalar() == 1;
            
            }catch(Exception ex){
            try{
                exist = true;
               
                SqlCommand cmdInsert = new SqlCommand("insert into " + tableName + " Values(" + veriler + ")",sqlConn);
                cmdInsert.ExecuteNonQuery();
                MessageBox.Show("The data has been successfully transferred.");
            }catch(Exception exc){
                exist = false;
                SqlCommand cmdCreate = new SqlCommand("Create table " + tableName + " ( " + degiskenBildirimi + " ) insert into " + tableName + " Values(" + veriler + ")", sqlConn);
               
                cmdCreate.ExecuteNonQuery();
                MessageBox.Show("Table created and the data has been transferred successfully.");

            }}

推荐答案

尝试以下方法.
下面的存储过程将表名作为参数，并根据表是否存在于数据库中而返回true或false.

Try the below approach.
The below stored procedure takes the table name as a parameter and returns true or false based on whether the table exists in database or not.

CREATE PROCEDURE usp_CheckTableExists
@TableName VARCHAR(50)
AS
BEGIN

    DECLARE @TableExists BIT

    IF EXISTS (SELECT 1 FROM Information_SCHEMA.Tables WHERE TABLE_NAME = @TableName)
    BEGIN
        SET @TableExists = 1
    END
    ELSE
    BEGIN
        SET @TableExists = 0
    END

    SELECT @TableExists AS TableExists

END

您可以在应用程序中获取此SP的结果，并使用它来决定是创建表还是仅向表中插入数据(如果表已存在).

You can take the result of this SP in your application and use it decide whether to create a table or just insert data into the table (if the table already exists).

bool TableExists;
using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    using (SqlCommand command = new SqlCommand("usp_CheckTableExists"))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Add(new SqlParameter("@TableName", tableName));
                        connection.Open();
                        command.Connection = connection;
                        TableExists = (bool)(command.ExecuteScalar());
                        connection.Close();


                    }


                }

if(TableExists)
{
    //Table already exists in database
}
else
{
    //Table does not exist in database
}

bool isExists;  
  const string sqlStatement = @"SELECT COUNT(*) FROM customtablename";   
  try   
  {      
  using (SqlCommand command= new SqlCommand (sqlStatement, sqlConn)) 
       {  
           command.ExecuteScalar();   
          isExists= true; 
       }    
 }    
 catch  
   {    
     isExists= false;  
   } 

检查以上内容是否解决了您的问题

check if the above solves your problem

SELECT 
COUNT(1) 
FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[YourSchema].[YourTableName]') AND type in (N'U')

将[YourSchema].[YourTableName]替换为您喜欢的任何变量.
例如:可以将[dbo].[Mydata]用于[YourSchema].[YourTableName]

使用OLEDB的ExecuteScalar并检查计数> 0表示存在/不存在
例如:

Replace the [YourSchema].[YourTableName] with any variable you like.
For example : You can use [dbo].[Mydata] for [YourSchema].[YourTableName]

Use ExecuteScalar of OLEDB and check count > 0 for exist/doesnt exist
For example:

bool exist = (Int32)cmd.ExecuteScalar()==1 ? true: false ;

这篇关于如何在C#中检查在SQL Server 2008 R2上是否存在我的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！