如果把照片直接保存在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);
} } } }