访问编程方式创建一个数据库之前等待？ [英] Wait before accessing a database created programmatically?
问题描述
我在我的程序中使用的SqlClient创建数据库。该应用程序要求定期创建一个新的数据库，但结构是一样的。所以我有一个文件中创建脚本，我执行它的一行的时间。当我做，我只是关闭它，因为应用程序（使用亚音速编写）的另一部分将然后操纵新创建的数据库。
I'm creating a database in my program using SqlClient. The app requires a new database to be created periodically, but the structure is the same. So I have the create script in a file, and I execute it one line at a time. When I'm done, I just close it up, because another part of the app (written using SubSonic) will then manipulate the newly created database.
我遇到的问题是，即使在成功创建数据库，当我去访问它，我得到一个异常，告诉我的登录失败。一段时间后，该访问成功。我已经缩小下来到5秒 - 东西少导致失败，在5秒钟，这工作正常。下面的代码演示（在一种做作的方式），但它也向我证明亚音速没有参与这个问题的。
The problem I'm having is that even after successfully creating the database, when I go to access it I get an exception, telling me the Login failed. Some time later, this access succeeds. I've narrowed this down to 5 seconds - anything less causes the failure, at 5 seconds it works fine. The code below demonstrates that (in a kind of contrived way), but it also proved to me that SubSonic wasn't involved in the issue at all.
这是不是我只需要忍受，或者是有什么我不这样做很正确？
Is this something I just need to put up with, or is there something I'm not doing quite right?
我的代码是：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;
namespace SqlConnTest
{
class Program
{
static void Main(string[] args)
{
var x = new ContentDB();
if (x.IsNew)
{
x.Dispose();
System.Threading.Thread.Sleep(4500);
Console.WriteLine("Second time around");
x = new ContentDB();
}
Console.WriteLine("All done");
Console.ReadKey();
}
}
class ContentDB : IDisposable
{
private bool disposed = false;
public void Dispose()
{
Dispose(true);
}
protected virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
if (disposing)
{
}
disposed = true;
}
}
public bool IsNew {get;set;}
public ContentDB()
{
SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=Arch;Persist Security Info=False;User ID=Brunner;Password=Brunn3r1x");
try
{
conn.Open();
}
catch (SqlException ex)
{
Console.WriteLine("Arch database needs to be created. {0}", ex.Message);
IsNew = true;
}
finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
}
if (IsNew)
CreateDB();
}
private void CreateDB()
{
using (var SqlScriptReader = new StreamReader("c:\\temp\\create.sql"))
{
var conn = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=master;Persist Security Info=False;User ID=Brunner;Password=Brunn3r1x");
conn.Open();
string SqlCmd = SqlScriptReader.ReadLine();
while (SqlCmd != null && SqlCmd != String.Empty)
{
try
{
var cmd = new SqlCommand(SqlCmd,conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (SqlException ex)
{
Console.WriteLine("SQL command {0} failed,\n{1}", SqlCmd,ex.Message);
}
SqlCmd = SqlScriptReader.ReadLine();
}
if (conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
}
Console.WriteLine("CreateDB finished");
}
}
}
}
而.SQL文件的内容是：
And the contents of the .SQL file are:
USE [master]
CREATE DATABASE [Arch] ON PRIMARY ( NAME = N'Arch', FILENAME = N'C:\temp\Arch.mdf', SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'Arch_log', FILENAME = N'C:\temp\Arch_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
USE [Arch]
CREATE TABLE [dbo].[RptContent] ([RptContentID] [int] IDENTITY(1,1) NOT NULL, [RptContentBLOB] [varbinary](max) NOT NULL CONSTRAINT [PK_RptContent] PRIMARY KEY CLUSTERED ([RptContentID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
当我没有拱数据库中现有运行它，我得到：
When I run it with no Arch database existing, I get:
Arch database needs to be created. Cannot open database "Arch" requested by the
login. The login failed.
Login failed for user 'Brunner'.
CreateDB finished
Second time around
Arch database needs to be created. Cannot open database "Arch" requested by the
login. The login failed.
Login failed for user 'Brunner'.
SQL command CREATE DATABASE [Arch] ON PRIMARY ( NAME = N'Arch', FILENAME = N'C:
\temp\Arch.mdf', SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG
ON ( NAME = N'Arch_log', FILENAME = N'C:\temp\Arch_log.ldf' , SIZE = 1024KB , M
AXSIZE = 2048GB , FILEGROWTH = 10%) failed,
Database 'Arch' already exists. Choose a different database name.
SQL command CREATE TABLE [dbo].[RptContent] ([RptContentID] [int] IDENTIT
Y(1,1) NOT NULL, [RptContentBLOB] [varbinary](max) NOT NULL CONSTRAINT
[PK_RptContent] PRIMARY KEY CLUSTERED ([RptContentID] ASC ) WITH (PAD_INDEX =
OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] failed,
There is already an object named 'RptContent' in the database.
CreateDB finished
All done
随着5000睡眠运行它给了我
Running it with a sleep of 5000 gives me:
Arch database needs to be created. Cannot open database "Arch" requested by the
login. The login failed.
Login failed for user 'Brunner'.
CreateDB finished
Second time around
All done
和运行如果拱门数据库已经存在，给了：
And running if the Arch database already exists gives:
All done
很抱歉，如果这是漫长的 - 我试图剥离下来的代码的短版，将证明我在做什么和问题。
Sorry if this is long - I tried to strip it down to the short version of the code that would demonstrate what I'm doing and the problem.
推荐答案
而不是一个线程睡眠，你可以把这个任务：创建一个循环，搜索创建的数据库和循环，而你可以'找不到它
Instead a thread sleep, you could make this task: Create a loop that searches for created database, and loops while you can't find it.
如何找到，如果数据库中存在：的如何检查数据库是否在SQL Server中存在？
How to find if a database exists: How to check if a database exists in SQL Server?
有关性能的目的，你应该把一个计时器（比如一个线程睡眠）for each循环，而不是不断地探索。你说的平均时间为5秒，所以在每1秒或2s，循环可能会被罚款！
For performance purpose, you should put a timer (like a thread sleep) for each loop, instead searching continuously. You said that average time is 5 seconds, so looping at each 1s or 2s might be fine!
这篇关于访问编程方式创建一个数据库之前等待？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
