C#操作varbinary(MAX)欄位

如果把照片直接儲存在SQL Server資料庫中，微軟推薦用varbinary(MAX)欄位。下面的代碼示範了用C#操作varbinary(MAX)欄位的基本方法。

1、新增記錄

        private void btnBrowse_Click(object sender, EventArgs e)//瀏覽照片
        {
            OpenFileDialog dlg = new OpenFileDialog();
            dlg.Filter = "*.jpg(jpg檔案)|*.jpg|*.gif|*.gif";
            dlg.FilterIndex = 1;
            if (dlg.ShowDialog()==DialogResult.OK)
            {
                textBox3.Text = dlg.FileName;
                pictureBox1.Image = Image.FromFile(dlg.FileName);
            }
        }

　　　　//新增記錄

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString))
            {
                String sql = "insert into emp(name,age,photo) values(@name,@age,@photo)";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@name", textBox1.Text);
                cmd.Parameters.AddWithValue("@age", Convert.ToInt32(textBox2.Text));
                //byte[] b;
                //using(FileStream fs=new FileStream(textBox3.Text,FileMode.Open,FileAccess.Read))
                //{
                //    b = new byte[fs.Length];
                //    fs.Read(b, 0, (int)fs.Length);
                //}
                byte[] b;
                if (textBox3.Text != "")
                {
                    b = File.ReadAllBytes(textBox3.Text);
                    cmd.Parameters.AddWithValue("@photo", b);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@photo", System.Data.SqlTypes.SqlBinary.Null);
                }
                conn.Open();
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {

                    MessageBox.Show(ex.Message);
                }

            }

2、顯示記錄資訊並提供修改功能

        private void FormDetail_Load(object sender, EventArgs e)//顯示記錄詳細資料
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString))
            {
                String sql = "select * from emp where id=@id";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@id", id);
                conn.Open();
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    if(dr.Read())
                    {
                        textBox1.Text = dr[1].ToString();
                        textBox2.Text = dr[2].ToString();
                        if (!dr.IsDBNull(3))//防止照片欄位為空白
                        {
                            System.Data.SqlTypes.SqlBytes bytes = dr.GetSqlBytes(3);
                            pictureBox1.Image=Image.FromStream(bytes.Stream);//顯示照片
                        }
                    }
                }
            }
        }

        private void btnSave_Click(object sender, EventArgs e)//更新記錄
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString))
            {
                byte[] b;
                if (textBox3.Text != "")//需要更新照片
                {
                    String sql = "update emp set name=@name,age=@age,photo=@photo where id=@id";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.Parameters.AddWithValue("@name", textBox1.Text);
                    cmd.Parameters.AddWithValue("@age", Convert.ToInt32(textBox2.Text));
                    cmd.Parameters.AddWithValue("@id", id);
                    b = File.ReadAllBytes(textBox3.Text);
                    cmd.Parameters.AddWithValue("@photo", b);
                    conn.Open();
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {

                        MessageBox.Show(ex.Message);
                    }
                }
                else//不需要更新照片
                {
                    String sql = "update emp set name=@name,age=@age where id=@id";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.Parameters.AddWithValue("@name", textBox1.Text);
                    cmd.Parameters.AddWithValue("@age", Convert.ToInt32(textBox2.Text));
                    cmd.Parameters.AddWithValue("@id", id);
                    conn.Open();
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {

                        MessageBox.Show(ex.Message);
                    }
                }              
            }
        }