如何存在Sql数据库和表。 [英] How to that Sql Database and Table is Exists or not.

问题描述

先生，



我在运行时在vb.net appliaction创建sql数据库和表

它创建但是当我重新打开时应用它显示sql数据库

已经存在以及表。



请帮帮我，如果已经存在（Sql数据库和表）然后如何才能进入下一步。



Indranil

解决方案

< blockquote>尝试下面的代码

 bool存在; 
 
尝试
 {
 // ANSI SQL方式。适用于PostgreSQL，MSSQL，MySQL。 
 var cmd = new OdbcCommand（
select case at exists（（select * from information_schema.tables where table_name =''+ tableName +''或Table_catalog =''+ dbName +' '））然后1其他0结束）; 
 
 exists =（int）cmd.ExecuteScalar（）== 1; 
} 
 catch 
 {
 
} 

您已经知道如何运行SQL从VB.net查询，我给你两个查询表存在的查询

  select  *  FROM  sys.databases  WHERE  name = '  DB1'   -   替换你的DB1的数据库名称 

  SELECT  *  FROM  sys.tables  WHERE  name =  '  TB1'   -   替换你的表名字用TB1  

以下是VB.Net的代码

<前lang = VB> ' 创建数据库.................... .....
Dim conStr As String = Server = .; Database =; Trusted_Connection = yes; MultipleActiveResultSets = True;
Dim objCon As 新 SqlConnection（conStr）
Dim obj As SqlCommand
< span class =code-keyword> Dim strSQL As String
< span class =code-keyword> Dim myReader As SqlDataReader

' 创建数据库
objCon.Open（）
obj = objCon.CreateCommand（）

尝试
strSQL = SELECT * FROM sys.databases WHERE name ='INDRA'
obj.CommandText = strSQL
SET myReader = obj.ExecuteReader（）
IF NOT myReader .Read（）
' obj.Close（）
strSQL = CREATE DATABASE INDRA
' 执行
obj.CommandText = strSQL
obj.ExecuteNonQuery（）
MsgBox（ 数据库已成功创建...，MsgBoxStyle.Information， Indranil Chatterjee ..）
END IF
Catch ex As 异常
MessageBox.Show（ex.Message）
结束 尝试

' objCon.Close（）

为表创建做同样的事情





希望这有帮助，如果是，那么接受并投票这个解决方案，否则回复你的查询

--RDB

我解决了更改显示的代码/线条后的问题.....！



''创建数据库....... ..................

Dim conStr As String =Server =。; Database =; Trusted_Connection = yes; MultipleActiveResultSets = True;

Dim objCon As New SqlConnection（conStr）

Dim obj As SqlCommand

Dim strSQL As String

Dim myReader作为SqlDataReader



''创建数据库

objCon.Open（）

obj = objCon.CreateCommand（ ）



尝试

strSQL =SELECT * FROM sys.databases WHERE name =''INDRA''

obj.CommandText = strSQL

myReader = obj.ExecuteReader（）

如果不是myReader.Read（）

myReader.Close（）



strSQL =CREATE DATABASE INDRA

''执行

obj.CommandText = strSQL

obj.ExecuteNonQuery（）

MsgBox（数据库创建成功...，MsgBoxStyle.Information，Indranil Chatterjee ..）

END IF

Catch ex As Exception

MessageBox.Show（ex.Message）

结束尝试



objCon.Close（）

Sir,

I am creating sql database and tables at runtime in vb.net appliaction
its created but when i reopen the application it shows that sql database
already exists as well as tables.

Please help me, if already exists(Sql database and Tables) then how can
it will go to next step.

Indranil

解决方案

Try below code

bool exists;

try
{
    // ANSI SQL way.  Works in PostgreSQL, MSSQL, MySQL.  
    var cmd = new OdbcCommand(
      "select case when exists((select * from information_schema.tables where table_name = ''" + tableName + "'' or Table_catalog = ''" + dbName + "'')) then 1 else 0 end");

    exists = (int)cmd.ExecuteScalar() == 1;
}
catch
{
   
}

As you already aware how to run SQL query from VB.net , I am giving you two queries for checking table existance

select * FROM sys.databases WHERE name='DB1' -- Replace your database name with DB1

SELECT * FROM sys.tables WHERE name='TB1' -- Replace your table name with TB1

Below are codes for VB.Net

'CREATING DATABASE......................... 
 Dim conStr As String = "Server=.;Database=;Trusted_Connection = yes;MultipleActiveResultSets=True;" 
 Dim objCon As New SqlConnection(conStr) 
 Dim obj As SqlCommand 
 Dim strSQL As String 
 Dim myReader As SqlDataReader 

  ' Create the database 
 objCon.Open() 
 obj = objCon.CreateCommand() 
  
Try 
 strSQL = "SELECT * FROM sys.databases WHERE name='INDRA'"
 obj.CommandText = strSQL 
 SET myReader=obj.ExecuteReader()
  IF NOT myReader.Read()
'obj.Close()
   strSQL = "CREATE DATABASE INDRA" 
  ' Execute 
   obj.CommandText = strSQL 
  obj.ExecuteNonQuery() 
  MsgBox("Database Created Successfully...", MsgBoxStyle.Information, "Indranil Chatterjee..") 
  END IF 
  Catch ex As Exception 
  MessageBox.Show(ex.Message) 
End Try 

'objCon.Close()

Do the same thing for table creation


Hope this helps if yes then accept and vote this solution otherwise revert back with your queries
--RDB

I SOLVED THE PROBLEM AFTER CHANGING THE CODES/LINES SHOWING IN BOLD.....!

''CREATING DATABASE.........................
Dim conStr As String = "Server=.;Database=;Trusted_Connection = yes;MultipleActiveResultSets=True;"
Dim objCon As New SqlConnection(conStr)
Dim obj As SqlCommand
Dim strSQL As String
Dim myReader As SqlDataReader

'' Create the database
objCon.Open()
obj = objCon.CreateCommand()

Try
strSQL = "SELECT * FROM sys.databases WHERE name=''INDRA''"
obj.CommandText = strSQL
myReader=obj.ExecuteReader()
IF NOT myReader.Read()
myReader.Close()

strSQL = "CREATE DATABASE INDRA"
'' Execute
obj.CommandText = strSQL
obj.ExecuteNonQuery()
MsgBox("Database Created Successfully...", MsgBoxStyle.Information, "Indranil Chatterjee..")
END IF
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

objCon.Close()

这篇关于如何存在Sql数据库和表。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！