ADO.NET笔记——存储二进制大对象（BLOB）

相关知识

上传二进制大对象（Binary Large Object）（如图片、视频等）的基本编程步骤是：

• 在数据库中使用varbinary(MAX)、varchar(MAX)或者nvarchar(MAX)等数据类型记录BLOB
• 使用INSERT INTO语句，在表中建立一个新行
• 使用 UPDATE xxx.WRITE 语句，逐段将BLOB写入大数据字段中

代码示例

1. 数据库：沿用AccountDBforSQLInjection，使用下列的SQL语句建立一个包含varbinary(MAX)字段类型的表：

       CREATE TABLE [dbo].[Photo](
           [PhotoID] [int] IDENTITY(1,1) NOT NULL,
           [PhotoName] [nvarchar](50) NULL,
           [PhotoData] [varbinary](max) NULL,
        CONSTRAINT [PK_Photo] PRIMARY KEY CLUSTERED 
       (
           [PhotoID] ASC
       )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
       ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

   该表的主键PhotoID是自增长列

2. SQL语句定义：

   static string strConn = @"server=Joe-PC;database=AccountDBforSQLInjection;uid=sa;pwd=root";

   //以下SQL命令在Photo表中创建一个新行，并且返回新行的PhotoID(该字段是自增长的)
   //PhotoData不能初始化为NULL，因为.WRITE字句无法在NULL字段上操作
   static string strInsertCmd = @"INSERT INTO Photo(PhotoName, PhotoData) Values(@PhotoID, 1);SELECT @PhotoID=@@IDENTITY";
   //@@identity的作用是返回最后插入的标识值，使用它来获取插入数据后的标识符。
   //但有一点是需要注意的，@@identity返回的是最后的标识符，所以，要想正确的返回插入后的标识符，那么就必须保证，你想要的结果是最后的标识符，否则就会隐藏bug。

   //以下SQL语句用于更新PhotoData字段中的一部分数据
   //@data表示待写入的数据，@offset表示在该字段的哪个位置开始写起，@length表示写入多少个字节
   static string strUploadCmd = @"UPDATE Photo SET PhotoData.WRITE(@data, @offset, @length) WHERE PhotoID=@PhotoID";

3. 创建新行：

           // 在图片表中创建新行
           static int InsertPhoto()
           {
               using (SqlConnection conn = new SqlConnection(strConn))
               {
                   conn.Open();
                   SqlCommand cmd = new SqlCommand(strInsertCmd, conn);
                   cmd.Parameters.Add("@PhotoName", SqlDbType.NVarChar, 50).Value = "flower.jpg";
                   SqlParameter id = cmd.Parameters.Add("@PhotoID", SqlDbType.Int);
                   id.Direction = ParameterDirection.Output;
                   cmd.ExecuteNonQuery();
                   return Convert.ToInt32(id.Value);
               }
           }

4. 向新行中写入图片数据：

           //从磁盘文件读入图片数据，然后逐段写入数据库
           static void UploadPhoto(int photoID)
           {
               using (SqlConnection conn = new SqlConnection(strConn))
               {
                   SqlCommand cmd = new SqlCommand(strUploadCmd, conn);
                   cmd.Parameters.Add("@PhotoID", SqlDbType.Int).Value = photoID;
                   SqlParameter data = cmd.Parameters.Add("@data", SqlDbType.VarBinary, -1);
                   SqlParameter offset = cmd.Parameters.Add("@offset", SqlDbType.Int);
                   offset.Value = 0;
                   SqlParameter length = cmd.Parameters.Add("@length", SqlDbType.Int);
   
                   int BUF_SIZE = 1024;
                   string fileName = "..\..\picture.jpg";
   
                   FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                   byte[] buf = new byte[BUF_SIZE];
                   int read = fs.Read(buf, 0, BUF_SIZE);
                   conn.Open();
   
                   while (read > 0)
                   {
                       length.Value = read;
                       data.Value = buf;
                       cmd.ExecuteNonQuery();
                       offset.Value = Convert.ToInt32(offset.Value) + read;//偏移量逐次递进
                       read = fs.Read(buf, 0, BUF_SIZE);
                   }
   
                   fs.Close();
               }
           }

5. 下载图片数据，检测上传是否成功：

           //将前述写入的图片从数据库读出来，保存到文件中
           static void DownloadPhoto(int photoID)
           {
               using (SqlConnection conn = new SqlConnection(strConn))
               {
                   string strCmd = "select * From photo where PhotoID=" + photoID.ToString();
                   SqlCommand cmd = new SqlCommand(strCmd, conn);
                   conn.Open();
                   SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
                   dr.Read();
                   FileStream fs = new FileStream("..\..\savedpicture.jpg", FileMode.OpenOrCreate, FileAccess.Write);
                   byte[] buf = new byte[1024];
                   long bytesRead = 0;
                   long startIndex = 0;
                   while ((bytesRead = dr.GetBytes(2, startIndex, buf, 0, 1024)) > 0)
                   {
                       fs.Write(buf, 0, (int)bytesRead);
                       startIndex += bytesRead;
                   }
                   fs.Close();
               }
           }

6. 主函数：

           static void Main(string[] args)
           {
               int photoID = InsertPhoto();
               UploadPhoto(photoID);
               DownloadPhoto(photoID);
           }

程序说明

本程序将从项目文件夹上传picture.jpg文件至数据库，然后又从数据库中下载数据保存为savedpicture.jpg文件，以便检查上传是否成功