以编程方式在c＃中创建sql数据库 [英] programmatically creating a sql database in c#

问题描述

您好！



我正在尝试使用以下代码以编程方式创建数据库：

 @Select = @Select + sql; 
  String  str; 
 SqlConnection myConn =  new  SqlConnection（< a valid =   connection =    string  =   > < /跨度>）; 
 
 str =   CREATE DATABASE + sql +   ON PRIMARY + 
  （NAME = + sql +  ， + 
   FILENAME =' + @Select +  。mdf'， + 
   SIZE = 2MB，MAXSIZE = 10MB，FILEGROWTH = 10％） + 
   LOG ON（NAME = + sql +   _ Log， + 
   FILENAME =' + @Select +    .ldf'， + 
   SIZE = 1MB ， + 
   MAXSIZE = 5MB， + 
   FILEGROWTH = 10％）; 

其中变量sql是有效的数据库名称（ChaosSpaceMarines）。

@Select是一个有效的路径（E：\\databases\\）



此代码工作正常。但是，如果我删除数据库ChaosSpaceMarines（以及所有相关文件），我会收到SQL错误Database ChaosSpaceMarines已经存在。即使我在不​​同的驱动器上创建新数据库。 （还尝试了不同的目录）。



有人能告诉我为什么我只能创建一次命名数据库吗？该数据库包含22个表，目前我正在尝试调试创建表的SQL语句。每次重新启动程序时都必须使用不同的表名是一件痛苦的事。

解决方案

即使是路径，也无法添加具有相同名称的其他数据库是不同的。

看看这里：创建数据库 [< a href =http://msdn.microsoft.com/en-us/library/ms176061.aspxtarget =_ blanktitle =新窗口> ^ ]



更多详情：删除数据库 [ ^ ]

MSDN写道：

跟进：删除数据库后



备份master数据库。如果必须还原master，自上次备份master以来已删除的任何数据库仍将在系统目录视图中引用，并可能导致出现错误消息。

尝试

{

@Select = @Select + txtDatabase.Text;

str = USE MASTER;

myCommand = new SqlCommand（str，myConn）;

myConn.Open（）;

myCommand.ExecuteNonQuery（） ;

str =DROP DATABASE+ sql;

myCommand = new SqlCommand（str，myConn）;

myCommand.ExecuteNonQuery（）;

myConn.Close（）;

}

catch（例外）{myConn.Close（）; }



使用此try块（当然还有后续catch），我可以从Master表中删除指定的数据库并重新创建数据库。这不是一个非常优雅的解决方案，但是，嘿，它的工作原理。



谢谢你们所有人提出的出色建议。

Hello!

I am trying to create a database programmatically with the following code:

@Select = @Select + sql;
String str;
SqlConnection myConn = new SqlConnection(<a valid="" connection="" string="">);

str = "CREATE DATABASE " + sql + " ON PRIMARY " +
      "(NAME = " + sql + ", " +
      "FILENAME = '" + @Select + ".mdf', " +
      "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
      "LOG ON (NAME = " + sql + "_Log, " +
      "FILENAME = '" + @Select + ".ldf', " +
      "SIZE = 1MB, " +
      "MAXSIZE = 5MB, " +
      "FILEGROWTH = 10%)";

where the variable sql is a valid database name ("ChaosSpaceMarines").
@Select is a valid path ("E:\\databases\\")

This code works fine once. But if I delete the database ChaosSpaceMarines (and all associated files) I get the SQL error "Database ChaosSpaceMarines" already exists. Even if I create the new database on a different drive. (Also tried a different directory).

Can anybody tell me why I can only create the named database only once? The database contains 22 tables and currently I am trying to debug the SQL statements that create the tables. It is a pain to have to use a different table name each time I restart the program.

解决方案

You can't add another database with same name even if its path is differ.
Have a look here: CREATE DATABASE[^]

More details: Delete a Database[^]

MSDN wrote:

Follow Up: After deleting a database

Back up the master database. If master must be restored, any database that has been deleted since the last backup of master will still have references in the system catalog views and may cause error messages to be raised.

try
{
@Select = @Select + txtDatabase.Text;
str = "USE MASTER";
myCommand = new SqlCommand(str, myConn);
myConn.Open();
myCommand.ExecuteNonQuery();
str = "DROP DATABASE " + sql;
myCommand = new SqlCommand(str, myConn);
myCommand.ExecuteNonQuery();
myConn.Close();
}
catch (Exception) { myConn.Close(); }

With this try block (and subsequent catch, of course) I am able to "drop" the named database from the Master table and recreate the database. This is not a very elegant solution, but hey, it works.

Thank you one and all for your excellent suggestions.

这篇关于以编程方式在c＃中创建sql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！