SqlCommand在C#中创建数据库竞争条件
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SqlCommand在C#中创建数据库竞争条件，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6120字，纯文字阅读大概需要9分钟。
内容图文
如果我创建一个数据库,然后尝试在2秒钟内使用MS Sync Framework连接到它,则会抛出异常“无法打开数据库…”
新增中
Thread.Sleep(4000);
会导致代码正常工作,但是我不希望设置太多时间,因为该代码将在平板电脑和其他弱硬件上运行,因此生成数据库的时间可能会大不相同.
我可以检查数据库在创建后是否存在,并且始终可以立即运行,因此不能选择条件等待(旋转).
using (SqlConnection masterConnection = new SqlConnection(masterConnectionString)) {
using (SqlCommand sqlCommand = new SqlCommand(string.Format("IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = '{0}') CREATE DATABASE {0}", databaseName), masterConnection)) {
masterConnection.Open();
sqlCommand.ExecuteNonQuery();
}
}
bool databaseExist = false;
while (!databaseExist) {
using (SqlConnection masterConnection = new SqlConnection(masterConnectionString)) {
using (SqlCommand verifySqlCommand = new SqlCommand(string.Format("SELECT database_id FROM sys.databases WHERE name = '{0}'", databaseName), masterConnection)) {
masterConnection.Open();
databaseExist = (int)verifySqlCommand.ExecuteScalar() > 0; // Always true
}
}
Thread.Sleep(1000);
}
...
using (SqlConnection sqlConnection = new SqlConnection(CONNECTION_STRING)) {
SqlSyncScopeProvisioning sqlSyncScopeProvisioning = new SqlSyncScopeProvisioning(sqlConnection); // throw ("Cannot open database") db doesn't exist
}
问题似乎与SqlConnection有关,第一个SqlConnection可以看到数据库存在,但是第二(新的)连接在最初的几秒钟内看不到它的存在(在SqlConnection.Open()上失败).
堆栈跟踪
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.Synchronization.Data.SyncUtil.TryOpenConnection(IDbConnection connection)
at Microsoft.Synchronization.Data.SyncUtil.OpenConnection(IDbConnection connection)
at Microsoft.Synchronization.Data.SqlServer.SqlEditionHelper.GetEdition(SqlConnection connection)
at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.set_Connection(SqlConnection value)
at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning..ctor(SqlConnection connection, DbSyncScopeDescription scopeDescription, SqlSyncScopeProvisioningType provisioningType, Boolean expectConnection)
at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning..ctor(SqlConnection connection)
Mark II基于@CharlieBrown的答案
@CharlieBrown发布的代码本身可以工作,但在我的设置中不起作用,以下代码演示了崩溃.如果取消注释手动抛出的代码,则代码将正常运行,否则将无法正常运行.
try {
//If I manually throw this exception then code in catch will run fine, otherwise not.
//throw new Exception("Cannot open database testDB");
using (var sqlConnection = new SqlConnection(CONNECTION_STRING)) {
SqlSyncScopeProvisioning sqlSyncScopeProvisioning = new SqlSyncScopeProvisioning(sqlConnection); // throw ("Cannot open database") db doesn't exist
}
} catch (Exception exception) {
if (exception.Message.StartsWith("Cannot open database")) { // Database does not exist, try to create
try {
using (var masterConnection = new SqlConnection(masterConnectionString)) {
using (var sqlCommand = new SqlCommand(string.Format("IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = '{0}') CREATE DATABASE {0}", databaseName), masterConnection)) {
masterConnection.Open();
sqlCommand.ExecuteNonQuery();
}
}
using (var sqlConnection = new SqlConnection(CONNECTION_STRING)) {
SqlSyncScopeProvisioning sqlSyncScopeProvisioning = new SqlSyncScopeProvisioning(sqlConnection); // throw ("Cannot open database") db doesn't exist
}
} catch (Exception ex) { // Always end up here
// ex = System.Data.SqlClient.SqlException (0x80131904): Cannot open database "testDB" requested by the login. The login failed. Login failed for user ...
}
} else { // Other exception
// Never hit
}
}
编辑
使用建议更新了代码,增加了堆栈跟踪.
添加了另一个代码示例来演示问题.
解
与@CharlieBrown交谈后,似乎没有解决此问题的方法,因此我将通过始终运行SqlCommand来创建数据库(如果不存在)来解决该问题,而不是尝试使用数据库,然后在catch()中创建例外.此代码仅在应用启动时运行.
解决方法:
在SQL Server有时间使数据库准备好进行同步之前,将调用对SqlSyncScopeProvisioning的调用.我找不到有关时间的参考,但是在创建数据库之后,会发生一些触发器以准备数据库.
工作代码：
已针对LocalDb,Sql Server 2008r2,Sql Server 2012测试
var connectionString = @"Data Source=(LocalDb)\v11.0;Initial Catalog=master;Integrated Security=True;";
var databaseName = "TestRepl";
using (var masterConnection = new SqlConnection(connectionString)) {
using (var sqlCommand = new SqlCommand(string.Format("IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = '{0}') CREATE DATABASE {0}", databaseName), masterConnection)) {
masterConnection.Open();
sqlCommand.ExecuteNonQuery();
}
}
var syncConnectionString = string.Format(@"Data Source=(LocalDb)\v11.0;Initial Catalog={0};Integrated Security=True;", databaseName);
using(var sqlConnection = new SqlConnection(syncConnectionString)){
SqlSyncScopeProvisioning sqlSyncScopeProvisioning = new SqlSyncScopeProvisioning(sqlConnection); // throw ("Cannot open database") db doesn't exist
}
内容总结
以上是互联网集市为您收集整理的SqlCommand在C#中创建数据库竞争条件全部内容，希望文章能够帮你解决SqlCommand在C#中创建数据库竞争条件所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】