从SQL Server中读写大数据列。

/*
Author:Wu Xiuxiang;
Email:[email protected]

*/

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();

}