Image To SQL Server

/*
use master
go
if DB_ID('UserImage') is not null
   drop database UserImage
create database UserImage
go
use UserImage
go
create table Images
(
 Image_Name nvarchar(255) primary key,
 Image_Data Image not null
)
go
create proc InsertImage
(
 @Image_Name nvarchar(255),
 @Image_Data Image
)
as
insert into Images values(@Image_Name, @Image_Data)
go
create proc DeleteImage
(
 @Image_Name nvarchar(255)
)
as
delete from Images where Image_Name = @Image_Name
go
create proc SelectImage
(
 @Image_Name nvarchar(255)
)
as
select Image_Data from Images where Image_Name = @Image_Name
go
*/

 

using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Windows.Forms;

namespace ImageDB
{
    public partial class FormImageSQL : Form
    {
        #region
        private SqlConnection sql;
        private DataTable table;
        #endregion

        public FormImageSQL()
        {
            #region
            InitializeComponent();
            openFileImage.Filter = "图像文件|*.bmp;*.gif;*.jpg;*.jpeg;*.png|所有文件|*.*";
            pictureBox1.SizeMode = PictureBoxSizeMode.Zoom; // 图像大小按其原有的大小比例被增加或减小。
            table = new DataTable();
            DataColumn column = table.Columns.Add("Name", typeof(String));
            table.Columns.Add("Image", typeof(Image));
            table.Constraints.Add("PK", column, true);
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = Environment.MachineName; // @"./SQLEXPRESS"
            builder.InitialCatalog = "UserImage";
            builder.IntegratedSecurity = true;
            builder.TypeSystemVersion = "SQL Server 2000";
            sql = new SqlConnection(builder.ConnectionString);
            try
            {
                if (sql.State == ConnectionState.Closed)
                    sql.Open();
                using (SqlCommand cmd = new SqlCommand("Select * from Images", sql))
                using (SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    int name = sdr.GetOrdinal("Image_Name");
                    int img = sdr.GetOrdinal("Image_Data");
                    while (sdr.Read())
                    {
                        using (Stream ms = sdr.GetSqlBytes(img).Stream)
                        {
                            table.Rows.Add(sdr.GetString(name), new Bitmap(ms));
                            ms.Flush();
                        }
                    }
                }
            }
            catch (Exception se)
            {
                if (sql.State == ConnectionState.Open)
                    sql.Close();
                MessageBox.Show(this, se.Message);
            }
            comboBoxImage.DrawMode = DrawMode.OwnerDrawFixed; // 编码绘制所有元素，并且元素大小都相等。
            comboBoxImage.DropDownStyle = ComboBoxStyle.DropDownList;
            comboBoxImage.Sorted = false; // 绑定数据时，禁用排序。
            comboBoxImage.ValueMember = "Image"; // 绑定数据之前赋值。
            comboBoxImage.DataSource = table;
            comboBoxImage.DisplayMember = "Name";
            buttonDelete.Enabled = (table.Rows.Count > 0);
            #endregion
        }

        #region AddImage
        private void buttonAdd_Click(object sender, EventArgs e)
        {
            if (openFileImage.ShowDialog(this) != DialogResult.OK)
                return;
            string name = openFileImage.SafeFileName;
            if (table.Rows.Contains(name))
            {
                MessageBox.Show(this, string.Format("图像名称“{0}”已存在！", name), "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            try
            {
                using (SqlCommand cmd = new SqlCommand("InsertImage", sql))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@Image_Name", SqlDbType.NVarChar, name.Length).Value = name;
                    byte[] bytes = File.ReadAllBytes(openFileImage.FileName);
                    cmd.Parameters.Add("@Image_Data", SqlDbType.Image, bytes.Length).SqlValue = bytes;
                    if (sql.State == ConnectionState.Closed)
                        sql.Open();
                    cmd.ExecuteNonQuery();
                    DataRow row = table.NewRow();
                    row.SetField<String>("Name", name);
                    row.SetField<Image>("Image", new Bitmap(openFileImage.FileName));
                    table.Rows.Add(row);
                    comboBoxImage.SelectedIndex = comboBoxImage.Items.Count - 1;
                    buttonDelete.Enabled = true;
                }
            }
            catch (Exception se)
            {
                MessageBox.Show(this, se.Message);
            }
            finally
            {
                if (sql.State == ConnectionState.Open)
                    sql.Close();
            }
        }
        #endregion

        #region DeleteImage
        private void buttonDelete_Click(object sender, EventArgs e)
        {
            try
            {
                string name = comboBoxImage.Text;
                if (MessageBox.Show(this, string.Format("确实要删除图像“{0}”吗？", name), "系统提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK)
                    using (SqlCommand cmd = new SqlCommand("DeleteImage", sql))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@Image_Name", SqlDbType.NVarChar, name.Length).Value = name;
                        if (sql.State == ConnectionState.Closed)
                            sql.Open();
                        cmd.ExecuteNonQuery();
                        table.Rows.RemoveAt(comboBoxImage.SelectedIndex);
                        buttonDelete.Enabled = (table.Rows.Count > 0);
                    }
            }
            catch (Exception se)
            {
                MessageBox.Show(this, se.Message);
            }
            finally
            {
                if (sql.State == ConnectionState.Open)
                    sql.Close();
            }
        }
        #endregion

        #region SelectImage
        private void comboBoxImage_SelectedIndexChanged(object sender, EventArgs e)
        {
            pictureBox1.Image = comboBoxImage.SelectedValue as Image;
        }

        private void comboBoxImage_DrawItem(object sender, DrawItemEventArgs e)
        {
            if (e.Index < 0)
                return;
            using (Graphics g = e.Graphics)
            using (SolidBrush newBrush = new SolidBrush(e.BackColor))
            {
                if ((e.State & DrawItemState.Selected) != 0) // 取交集。
                    newBrush.Color = Color.Cyan;
                Rectangle newClip = e.Bounds;
                g.FillRectangle(newBrush, newClip);
                newClip.Offset(1, 1);
                newClip.Width = 36;
                newClip.Height -= 3;
                g.DrawRectangle(Pens.Black, newClip);
                newClip.Offset(1, 1);
                newClip.Width -= 1;
                newClip.Height -= 1;
                DataRow row = table.Rows[e.Index];
                g.DrawImage(row.Field<Image>("Image"), newClip);
                newBrush.Color = e.ForeColor;
                g.DrawString(row.Field<String>("Name"), e.Font, newBrush, newClip.X + 72, newClip.Y);
            }
        }
        #endregion
    }
}