将数据大容量复制到表时文件锁定的原因
本文关键字:锁定 文件锁 文件 大容量 数据 复制 | 更新日期: 2023-09-27 18:33:52
我正在浏览一个.MDF
文件并将其数据上传到SQL Server数据库表。
我的应用有一个浏览按钮来选择文件,然后上传按钮来执行批量复制操作。
但是在第二次尝试中,当我使用 OpenFileDialog
选择.mdf
文件时,它会抛出一个错误,指出该文件已被使用。
private void labelBrowse_Click(object sender, System.EventArgs e)
{
try
{
System.Windows.Forms.OpenFileDialog openFileDialog = new System.Windows.Forms.OpenFileDialog();
openFileDialog.Title = "Select the MDF file to upload";
openFileDialog.Filter = "Data|*.mdf";
if (openFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
textBoxMdfFilePath.Text = openFileDialog.FileName.ToString();
}
catch (System.Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.ToString());
}
}
private void labelUpload_Click(object sender, System.EventArgs e)
{
if (string.IsNullOrEmpty(textBoxMdfFilePath.Text.Trim()))
{
System.Windows.Forms.MessageBox.Show("Please select a MDF file to upload");
textBoxMdfFilePath.Text = "";
return;
}
else if (!(System.IO.Path.GetFileName(textBoxMdfFilePath.Text).Equals("Audit.mdf", System.StringComparison.InvariantCultureIgnoreCase)) && !(System.IO.Path.GetFileName(textBoxMdfFilePath.Text).Equals("IPAUDIT.mdf", System.StringComparison.InvariantCultureIgnoreCase)))
{
System.Windows.Forms.MessageBox.Show("Please select the correct MDF file to upload");
textBoxMdfFilePath.Text = "";
return;
}
else
{
uploadToServer(textBoxMdfFilePath.Text);
}
}
void uploadToServer(string path)
{
try
{
string mdfConnectionString = @"data source=172.16.2.136;attachdbfilename=" + path + ";" + "integrated security=true;" + "connect timeout=30;" + "user instance=true";
System.Data.SqlClient.SqlConnection sqlconnection = new System.Data.SqlClient.SqlConnection(mdfConnectionString);
if (System.IO.Path.GetFileName(path).Equals("Audit.mdf", System.StringComparison.InvariantCultureIgnoreCase))
{
System.Data.SqlClient.SqlCommand sqlcommand = new System.Data.SqlClient.SqlCommand("select * from [D-Audit]", sqlconnection);
sqlconnection.Open();
System.Data.DataTable dataTable = new System.Data.DataTable();
dataTable.Load(sqlcommand.ExecuteReader());
sqlconnection.Close();
System.Data.SqlClient.SqlBulkCopy sqlbulkcopy = new System.Data.SqlClient.SqlBulkCopy(System.Configuration.ConfigurationManager.ConnectionStrings["myServerAudit"].ConnectionString);
sqlbulkcopy.DestinationTableName = "[dbo].[D-Audit]";
sqlbulkcopy.BulkCopyTimeout = 1800;
sqlbulkcopy.WriteToServer(dataTable);
sqlbulkcopy.Close();
System.Windows.Forms.MessageBox.Show("Data Uploaded Successfully");
textBoxMdfFilePath.Text = "";
}
else if (System.IO.Path.GetFileName(path).Equals("IPAudit.mdf", System.StringComparison.InvariantCultureIgnoreCase))
{
System.Data.SqlClient.SqlCommand sqlcommand = new System.Data.SqlClient.SqlCommand("select * from IpTransaction", sqlconnection);
sqlconnection.Open();
System.Data.DataTable dataTable = new System.Data.DataTable();
dataTable.Load(sqlcommand.ExecuteReader());
sqlconnection.Close();
System.Data.SqlClient.SqlBulkCopy sqlbulkcopy = new System.Data.SqlClient.SqlBulkCopy(System.Configuration.ConfigurationManager.ConnectionStrings["myServerIPAudit"].ConnectionString);
sqlbulkcopy.DestinationTableName = "IpTransaction";
sqlbulkcopy.BulkCopyTimeout = 1800;
sqlbulkcopy.WriteToServer(dataTable);
sqlbulkcopy.Close();
System.Windows.Forms.MessageBox.Show("Data Uploaded Successfully");
textBoxMdfFilePath.Text = "";
}
sqlconnection.Close();
}
catch (System.Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
您需要在连接字符串中设置 Pooling=False
这里发生的事情是 SQLBulkcopy 正在使用您提供的连接字符串创建自己的 SQLConnection 对象。默认情况下,连接池为 true...因此,当 SQLBulkCopy 创建 sqlconnection 对象时,它会添加到连接池中,即使在应用程序关闭后,连接仍然存在。此机制旨在优化性能,但在您的情况下,这就是导致问题的原因......所以在你的连接中添加 Pooling=false 应该可以解决你的问题
ConnectionString = "Data Source=MYSQL;Initial Catalog=MyDatabase;Integrated Security=true;Pooling=false"
附加信息
最好使用Using block包装SQLBulkCopy,因为它的类型是IDisposable,因此不需要显式调用close。
当您说"第二次尝试"时,我假设它在第一次点击时有效,但在随后的点击中无效。如果是这种情况,则连接可能未释放,但仍保留在数据库上。在离开事件处理程序之前,可以尝试释放代码中的所有 IDisposable。