从SQL Server中读写大数据列。

56 阅读 0 评论 37 点赞

我是靠谱客的博主粗暴金毛，最近开发中收集的这篇文章主要介绍从SQL Server中读写大数据列。，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

/*
Author:Wu Xiuxiang;
Email:imessage@126.com

public static void Main()
{
   //写入大对象到SqlServer
   FileStream fs   = new FileStream("C://test.bmp",FileMode.OPen,FileAccess.Read);
   BinaryReader br = new BinaryReader(fs);

   SqlConnection conn = new SqlConnection("server=localhost;uid=sa;pwd=sa;database=northwind");

   string cmdText = "UPDATE EMPLOYEES" +
         "SET Photo=@image where EmployeeId=1";

   SqlCommand cmd = new SqlCommand(cmdText,conn);
   cmd.Parameters.Add("@image",SqlDbType.Image);

   cmd.Parameters["@image"].Value = br.ReadBytes((int)br.BaseStream.Length);

   conn.Open();
   int i=cmd.ExecuteNoQuery();

   //从SQL Server中读取大对象
   string cmdtext = "SELECT employeeid,photo" +
          " from employees where employeeid = 1";


  SqlCommand cmd2 = new SqlCommand(cmdtext,conn);

  FileStream rfs;
  BinaryWriter rbw;

  long numread;
  long startIndex;
  int buffSize=4096;
  byte[] buff = new byte[buffSize];

  conn.Open();
  SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess);

  if(rdr.Read())
  {
     int empid = rdr.GetInt32(0);

     fs = new FileStream("c://mypic.bmp",FileMode.OpenOrCreate,FileAccess.Write);
     bw = new BinaryWrite(fs);

     startIndex=0;

     numread = rdr.GetBytes(1,startIndex,buff,0,buffSize);

     while(numread==buffSize)
     {
         bw.Write(buff);
         bw.Flush();
         startIndex+=buffSize;
         numread = rdr.GetBytes(1,startIndex,buff,buffSize);
     }
     bw.Write(buff);
     bw.Flush();

     bw.Close();
     fs.Close();
  }
  rdr.Close();
  conn.Close();

}