I am trying to access several files in a SqlServer FILESTREAM table. This code works when the number of files returned by the SqlDataReader is relatively small. About 20 Files. However, when the SqlDataReader is returning a few hundred records, I receive this error on the very first time going through the while loop: "The process cannot access the file specified because it has been opened in another transaction."
The error happens at this line:
sfsList.Add(fileNameText, new SqlFileStream(filePath, txContext, FileAccess.Read));
Any ideas?
The code segment is below:
int ProjectID;
int.TryParse(Request.QueryString["ProjectID"], out ProjectID);
string filePath = null;
byte[] txContext = null;
string fileNameText = null;
Dictionary<string, SqlFileStream> sfsList = new Dictionary<string, SqlFileStream>();
using (SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ATRD"].ToString()))
{
cn.Open();
using (SqlTransaction trn = cn.BeginTransaction("GetAllDocsTran"))
{
SqlCommand cmd = new SqlCommand("GetAllDocs", cn, trn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProjectID", ProjectID);
using (SqlDataReader reader = cmd.ExecuteReader())
{
try
{
while (reader.Read())
{
txContext = (reader["txContext"] as byte[]);
filePath = reader["filePath"].ToString();
fileNameText = reader["fileNameText"].ToString();
sfsList.Add(fileNameText, new SqlFileStream(filePath, txContext, FileAccess.Read));
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
try
{
cmd.Transaction.Rollback();
}
catch (Exception ex2)
{
Response.Write(ex.Message + ex2.Message);
}
}
}
}
}
I solved this issue by first selecting all of the primary keys for the files that I wanted and adding them to a collection. Then I simply used a loop to retrieve the files one by one.