使用winforms在db中存储图像

c# dapper sql-server winforms

我想使用c#winforms和dapper micro orm在SqlServer中保存Image。

db中的Photo字段是VARBINARY(MAX)类型

Inside Book实体我有类型为byte []的Photo属性。

public Book 
{
   ... 
   public byte[] Photo { get; set; }
}

在winforms窗口我有

  OpenFileDialog open = new OpenFileDialog() { Filter = "Image Files(*.jpeg;*.bmp;*.png;*.jpg)|*.jpeg;*.bmp;*.png;*.jpg" };
   if (open.ShowDialog() == DialogResult.OK)
   {
       txtPhoto.Text = open.FileName;
   }

   string image = txtPhoto.Text;
   Bitmap bmp = new Bitmap(image);
   FileStream fs = new FileStream(image, FileMode.Open, FileAccess.Read);
   byte[] bimage = new byte[fs.Length];
   fs.Read(bimage, 0, Convert.ToInt32(fs.Length));
   fs.Close();

   byte[] Photo = bimage;

//在我的存储库中我在第一行Photo = @Photo上保存对象时Photo = @Photo

var sql = "UPDATE Book " +
          "SET Title = @Title, " +
          "    Language = @Language, " +
          ....
          "    Photo = @Photo" +
          "WHERE Id = @Id";

          this.db.Execute(sql, book); // error occures
          return book;

错误是

System.Data.dll中发生了'System.Data.SqlClient.SqlException'类型的第一次机会异常

附加信息:“照片”附近的语法不正确。

我错过了什么吗?

谢谢

一般承认的答案

WHERE关键字之前缺少空格:

      "    Photo = @Photo" + // no space at the end here
      "WHERE Id = @Id";      // and no space before WHERE here

另外我建议你使用多行字符串(即逐字字符串文字 )用于sql查询文本(这使查询更具可读性):

var sql = @"UPDATE Book 
            SET Title = @Title, 
                Language = @Language, 
                Photo = @Photo
            WHERE Id = @Id";

还有一件事 - 最好将Stream用法包装成using块(以便在发生异常时释放文件句柄):

byte[] photo;
using(var stream = File.OpenRead(txtPhoto.Text)
{
    photo = new byte[stream.Length];
    stream.Read(photo, 0, photo.Length);
}

// db query with dapper is OK


许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因
许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因