在SQL Server数据库实现图片文件的存取

　　CREATE TABLE [dbo].[StudentInfo]
　　(
　　[ID] [int] IDENTITY(1,1) NOT NULL,
　　[Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
　　[Age] [int] NULL,
　　[Sex] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
　　[Class] [varchar](15) COLLATE Chinese_PRC_CI_AS NULL,
　　[Hobby] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
　　[Picture] [image] NULL
　　)

 

　　Create procedure [dbo].[proc_UploadPicture]
　　@ID int,
　　@Picture image
　　AS
　　update StudentInfo set Picture = @Picture
　　where ID = @ID

 

　　Create procedure [dbo].[proc_DownloadPicture]
　　@ID int
　　as
　　select Picture
　　from StudentInfo
　　where ID = @ID 

     

 
        /// <summary>
        /// 上传图片到数据库
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnUpload_Click(object sender, EventArgs e)
        {
            OpenFileDialog openDlg = new OpenFileDialog();
            openDlg.Filter = "图片文件(*.jpg)|*.jpg";
            string filePath = "";
            if (openDlg.ShowDialog() == DialogResult.OK)
            {
                filePath = openDlg.FileName;
                this.txtFilePath.Text = filePath;
                this.picShow.ImageLocation = filePath;
                //打开文件流，用来读取图片文件中的数据
                FileStream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                //将文件流中的数据存入内存字节组中
                byte[] buffer = new byte[stream.Length];
                stream.Read(buffer, 0, (int)stream.Length);
                stream.Close();
                try
                {
                    //调用存储图片数据的存取过程
                    string strName = Path.GetFileName(filePath);
                    string connString = "Data Source=.;Initial Catalog=test;Integrated Security=True";
                    SqlConnection conn = new SqlConnection(connString);
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("proc_UploadPicture", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ID", SqlDbType.Int).Value = 1;
                    cmd.Parameters.Add("@Picture", SqlDbType.Image).Value = buffer;
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }
        /// <summary>
        /// 从数据库读取图片
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnDownload_Click(object sender, EventArgs e)
        {
            try
            {
                byte[] imageBytes;
                string connString = "Data Source=.;Initial Catalog=test;Integrated Security=True";
                SqlConnection conn = new SqlConnection(connString);
                conn.Open();
                SqlCommand cmd = new SqlCommand("proc_DownloadPicture", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ID", SqlDbType.Int).Value = 1;
                SqlDataReader dataReader = cmd.ExecuteReader();
                if (dataReader.Read())
                {
                    //获取图片数据
                    imageBytes = (byte[])dataReader["Picture"];
                    //将内存流格式化为位图
                    MemoryStream stream = new MemoryStream(imageBytes);
                    Bitmap bmap = new Bitmap(stream);
                    stream.Close();
                    //将位图显示在界面的PictureBox控件中
                    this.picShow.Image = bmap;
                }
                dataReader.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }