连接到网络驱动器中的数据库 [英] Connect to database in network drive

问题描述

我是 SQL Server 和 .net 的新手.如果我的问题不清楚，请在投票前告诉我.

I am a newbie to SQL Server and .net. Please let me know if my question is not clear before down voting.

我正在使用 C# 开发 Windows 应用程序.我应该让用户选择连接到网络驱动器上的 .mdf 文件.在我的机器上，我有 Windows 和 SQL Server 身份验证.用户有 SQL 身份验证，因此我应该使用 userid 和 pwd.我自己和用户在该网络驱动器上工作，读/写/修改.我们几乎共享文档，从网络驱动器添加和删除文档.

I am working on a Windows application with C#. I should give option to users to connect to a .mdf file on a network drive. On my machine, I have Windows and SQL Server authentication. Users have SQL authentication hence I should use userid and pwd. Myself and users work on that network drive, read/write/modify. We pretty much share documents, add and delete docs from network drive.

这里是设计师

我将选择位于网络驱动器中的 SQL Server 数据库 .mdf 文件，然后进行测试连接.对于测试连接，这是代码

I will choose the SQL Server database .mdf file which is located in network drive and then do test connection. For Test Connection this is the code

 string sTemp = System.Configuration.ConfigurationManager.AppSettings["connectionStringShare"];

 string connectionString = sTemp.Replace("{AppDir}", txtDB.Text.Trim());

 using (SqlConnection objSqlConnection = new SqlConnection(connectionString))
 {
          try
          {
              objSqlConnection.Open();
              objSqlConnection.Close();
              MessageBox.Show("Connection is successfull");
          }
          catch (Exception ex)
          {
              MessageBox.Show("Error : " + ex.Message.ToString());

          }
}

这是连接字符串

<add key="connectionStringShare" 
     value="Data Source=.\SQLEXPRESS;Initial Catalog=TableSQLExpress;AttachDBFilename={AppDir};Integrated Security=SSPI;user id=sa;password=pwd;" />

这是我收到的错误信息

文件S:\zrep\TableSQLExpress.mdf"的目录查找失败，操作系统错误3(系统找不到指定的路径.).
无法附加文件 'S:\zrep\TableSQLExpress.mdf' 作为数据库 'TableSQLExpress'.

Directory lookup for the file "S:\zrep\TableSQLExpress.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Cannot attach the file 'S:\zrep\TableSQLExpress.mdf' as database 'TableSQLExpress'.

我更改了连接字符串并尝试使用 Windows 身份验证也很累.没有运气.如果我需要提供任何其他详细信息，请告诉我.由于我是这个领域的新手，请给我详细的答案.我很高兴找到这个小组.感谢所有调查此事的人.

I changed connection string and tried also tired using windows authentication. No luck. Let me know if I need to provide any additional details. Since I am newbie to this field please give me detailed answer. I am glad to find this group. Thanks for everyone who looked into this.

推荐答案

当您使用基于服务器的 SQL Server(即 Microsoft SQL Server Express)时，您无法通过网络驱动器共享数据库文件，这是设计使然.即使您使用开关覆盖默认 SQL Server 行为并为数据库启用 UNC 路径，您的数据也会被多个服务器实例尝试使用单个数据库 MDF 文件损坏.如果您需要在无服务器环境中托管数据库(仅使用网络驱动器)，您可以选择 Microsoft SQL Server Compact (SQL Server CE) 版本.但请注意，在这种情况下，只有一个用户可以同时访问数据库文件(独占锁定 -> 低性能).另外 SQL Server CE 没有存储过程.

When you use server-based SQL Server (i.e. Microsoft SQL Server Express) you are unable to share database file via network drive, it is by design. Even if you override default SQL Server behavior with a switch and enable UNC paths for databases, your data will be corrupted by multiple server instances trying to use single database MDF file. If you need to host database in serverless environment (using only a network drive), you may opt to Microsoft SQL Server Compact (SQL Server CE) edition. But be aware that in such case only one user will be able to access database file at the same time (exclusive locking -> low performance). Plus SQL Server CE does not have stored procs.

这篇关于连接到网络驱动器中的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！