流式传输到数据库时，Sql ExecuteNonQuery挂起 [英] Sql ExecuteNonQuery hangs when streaming into database

问题描述

您好，



我使用以下代码将filedata流式传输到数据库：



< pre lang =c＃> private static bool WriteFileDataToDb（Guid fileid， string filename，System.IO.Stream stream， long length）
{
bool success = true ;

使用（System.Data.SqlClient.SqlConnection connection = new 系统。 Data.SqlClient.SqlConnection（Config.ConnectionString））
{
SqlCommand command = null ;

尝试
{
connection.Open（）;

command = connection.CreateCommand（）;
command.CommandTimeout = Config.SQLTimeout;
command.CommandText = UPDATE [File] SET FileData.WRITE（@ Bytes，@ Index，@ Length ）WHERE FileId = @Id;

SqlParameter param_bytes = command.CreateParameter（）;
SqlParameter param_index = command.CreateParameter（）;
SqlParameter param_length = command.CreateParameter（）;
SqlParameter param_id = command.CreateParameter（）;

param_bytes.DbType = DbType.Binary;
param_bytes.ParameterName = @ Bytes;
param_index.ParameterName = @ Index;
param_length.ParameterName = @ Length;
param_id.ParameterName = @ Id;

command.Parameters.Add（param_bytes）;
command.Parameters.Add（param_index）;
command.Parameters.Add（param_length）;
command.Parameters.Add（param_id）;

int bufflength = 1024 ;
int index = 0 ;
byte [] buff = new byte < /跨度> [bufflength];

使用（System.IO.BinaryReader br = new System.IO。 BinaryReader（stream，Encoding.GetEncoding（Config.Encode）））
{
param_id.Value = fileid;

int offset = 0 ;

while （（buff = br.ReadBytes（buff.Length））。Length > 0 ）
{
offset = index * bufflength;

param_bytes.Value = buff;
param_index.Value = index * bufflength;
param_length.Value = buff.Length;

try
{command.ExecuteNonQuery（）;
}
catch （例外情况）
{
var msg = ex.Message;
var stack = ex.StackTrace;
success = false ;
break ;
}
index ++;
}
}
}
catch （例外情况）
{
success = < span class =code-keyword> false ;
}
最后
{
if （命令！= null ）
command.Dispose（）;
if （connection.State == ConnectionState.Open）
connection.Close（）;
if （connection！= null ）
connection.Dispose（）;
}
}
返回成功;
}

问题是我在第一个ExecuteNonQuery或最后一个（文件末尾）上有超时。

我不知道这个错误可能来自哪里。

有没有人有想法？



亲切的问候。

解决方案

这种行为的根源似乎是auto_update_statistics选项中的一个错误（https://connect.microsoft.com/SQLServer/feedback/details / 174619 / update-statistics-holding-exclusive-lock-and-blocking）。

禁用此表的选项后，我无法重现错误。

Hello,

I use the following code to stream filedata into a database:

private static bool WriteFileDataToDb(Guid fileid, string filename, System.IO.Stream stream, long length)
        {
            bool success = true;               

            using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(Config.ConnectionString))
            {
                SqlCommand command = null;

                try
                {                    
                    connection.Open();                    

                    command = connection.CreateCommand();
                    command.CommandTimeout = Config.SQLTimeout;
                    command.CommandText = "UPDATE [File] SET FileData.WRITE(@Bytes,@Index,@Length) WHERE FileId = @Id";

                    SqlParameter param_bytes = command.CreateParameter();
                    SqlParameter param_index = command.CreateParameter();
                    SqlParameter param_length = command.CreateParameter();
                    SqlParameter param_id = command.CreateParameter();
                    
                    param_bytes.DbType = DbType.Binary;
                    param_bytes.ParameterName = "@Bytes";
                    param_index.ParameterName = "@Index";
                    param_length.ParameterName = "@Length";
                    param_id.ParameterName = "@Id";                    

                    command.Parameters.Add(param_bytes);
                    command.Parameters.Add(param_index);
                    command.Parameters.Add(param_length);
                    command.Parameters.Add(param_id);
                    
                    int bufflength = 1024;
                    int index = 0;                    
                    byte[] buff = new byte[bufflength];
                                        
                    using (System.IO.BinaryReader br = new System.IO.BinaryReader(stream, Encoding.GetEncoding(Config.Encode)))
                    {
                        param_id.Value = fileid;

                        int offset = 0;

                        while ((buff = br.ReadBytes(buff.Length)).Length > 0)
                        {
                            offset = index * bufflength;

                            param_bytes.Value = buff;
                            param_index.Value = index * bufflength;
                            param_length.Value = buff.Length;

                            try
                            {                             command.ExecuteNonQuery();                                
                            }                            
                            catch (Exception ex)
                            {
                                var msg = ex.Message;
                                var stack = ex.StackTrace;
                                success = false;
                                break;
                            }
                            index++;
                        }
                    }                    
                }
                catch (Exception ex)
                {                   
                    success = false;
                }
                finally
                {
                    if (command != null)
                        command.Dispose();
                    if (connection.State == ConnectionState.Open)
                        connection.Close();
                    if (connection != null)
                        connection.Dispose();
                }
            }
            return success;            
        }

The problem is I get a timeout either on the first ExecuteNonQuery or on the last one (end of file).
I have no idea where this error could come from.
Does anyone have an idea?

Kind regards.

解决方案

The root of this behavior seems to be a bug in the auto_update_statistics option (https://connect.microsoft.com/SQLServer/feedback/details/174619/update-statistics-holding-exclusive-lock-and-blocking).
After disabling the option for this table I could not reproduce the error.

这篇关于流式传输到数据库时，Sql ExecuteNonQuery挂起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！