通过C#中调用的存储过程将Bytearray插入SQL [英] Inserting Bytearray into SQL through stored procedure called in C#

问题描述

首先，我已经尝试了所有方法，并且不明白为什么它不能正确更新我的varbinary字段.

First of all I've tried everything, and can't understand why it won't update my varbinary field properly.

在1728个字节中，只有字节数组中的最后一个字节被保存到字段中...

out of 1728 bytes only the last byte in the byte array is saved to the field...

我按如下方式生成字节数组:

I generate my byte array as follows:

public static byte[] StringToByteArray(String hex)
{
    int NumberChars = hex.Length;
    byte[] bytes = new byte[NumberChars / 2];
    for (int i = 0; i < NumberChars; i += 2)
        bytes[i / 2] = Convert.ToByte(hex.Substring(i, 2), 16);
    return bytes;
}

我也尝试了以下方法:

public static byte[] ParseHex(string hex)
{
    int offset = hex.StartsWith("0x") ? 2 : 0;
    if ((hex.Length % 2) != 0)
    {
        throw new ArgumentException("Invalid length: " + hex.Length);
    }
    byte[] ret = new byte[(hex.Length - offset) / 2];

    for (int i = 0; i < ret.Length; i++)
    {
        ret[i] = (byte)((ParseNybble(hex[offset]) << 4)
                         | ParseNybble(hex[offset + 1]));
        offset += 2;
    }
    return ret;
}

static int ParseNybble(char c)
{
    if (c >= '0' && c <= '9')
    {
        return c - '0';
    }
    if (c >= 'A' && c <= 'F')
    {
        return c - 'A' + 10;
    }
    if (c >= 'a' && c <= 'f')
    {
        return c - 'a' + 10;
    }
    throw new ArgumentException("Invalid hex digit: " + c);
}

我用于保存数据的c#代码是这样:

My c# code to save the data is this:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DB_Conn"].ConnectionString))
{
  byte[] to_store = StringToByteArray(inventory);

  //State the Stored Proc and add Values to 'cmd' to pass to the Stored Proc
  SqlCommand cmd = new SqlCommand("_USP_store", conn);
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.AddWithValue("@A", TB_A.Text);
  cmd.Parameters.Add("@B", SqlDbType.VarBinary, 1728).Value = to_store;

  try
  {
    // Open Connection and execute Stored Proc
    conn.Open();
    cmd.ExecuteNonQuery();
    C2_Wipe_Message.Text = "Storing success";
    C2_Wipe_Message.ForeColor = Color.FromArgb(0, 0, 255, 0);

  }
  catch
  {
    C2_Wipe_Message.Text = "An error occured..";
    C2_Wipe_Message.ForeColor = Color.FromArgb(0, 255, 0, 0);
  }
  finally
  {
    if (conn.State == System.Data.ConnectionState.Open)
    {
      //Close connection IF open
      conn.Close();
    }
  }
}

我以字符串形式发送，以纯二进制形式发送，以十六进制字节数组形式发送，等等.

I've send it as a string, I've send it as plain binary, i've send it as a hexadecimal byte array, etc.

我的假设是在sql中使用while循环来存储它，但这并不能解释为什么总是保存最后一个字节而不是字节数组的第一个字节，请告诉我，因为这很令人生气.

My assumption is to use a while loop in sql, to store it, but that doesn't explain why the last byte is always saved instead of the first byte of the byte array, Please enlighten me cause this is infuriating..

* SQL SP

@A varchar(10),
@B varbinary(1728)

    AS  

UPDATE Invenotry
SET A = @B
WHERE (Name = @A)

推荐答案

您的sql应该是这样的:

Your sql should be this:

UPDATE Invenotry
SET B = @B
WHERE A = @A

您也可以尝试完整版本的参数构造函数:

You can also try the full version of a parameter constructor:

SqlParamter param = new SqlParameter("@B", SqlDbType.VarBinary, 1728, ParameterDirection.Input, 
     // we have these parameters but they are ignored for input types
     false, 0, 0, null, DataRowVersion.Current, 
     // the data
     to_store);

cmd.Parameters.Add(param);

这篇关于通过C#中调用的存储过程将Bytearray插入SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！