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

posted @   zhouhb  阅读(15015)  评论(2编辑  收藏  举报
编辑推荐:
· 智能桌面机器人:用.NET IoT库控制舵机并多方法播放表情
· Linux glibc自带哈希表的用例及性能测试
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
阅读排行:
· 手把手教你在本地部署DeepSeek R1,搭建web-ui ,建议收藏!
· 新年开篇:在本地部署DeepSeek大模型实现联网增强的AI应用
· Janus Pro:DeepSeek 开源革新,多模态 AI 的未来
· 互联网不景气了那就玩玩嵌入式吧,用纯.NET开发并制作一个智能桌面机器人(三):用.NET IoT库
· 【非技术】说说2024年我都干了些啥
点击右上角即可分享
微信分享提示