SqlHelper + winfrom + datagridview操作图片

datagridview中单元格显示图片，图片的增删改查

点击单元格选择图片

 1、Employer

public class Employer
{
    public int Id;
    public string Name;
    public string PhoneNum;
    public string Address;
    public string Photo;
}

2、ObjectConvertUtil处理工具类，转换图片与字节

public class ObjectConvertUtil
{
    public static Image StringConvertToImage(string str)
    {
        byte[] byts = Convert.FromBase64String(str);
        MemoryStream ms = new MemoryStream(byts);
        Image img = System.Drawing.Image.FromStream(ms);
        return img;
    }

    public static string BitmapConvertToBase64(System.Drawing.Image bitmap)
    {
        MemoryStream ms1 = new MemoryStream();
        bitmap.Save(ms1, System.Drawing.Imaging.ImageFormat.Jpeg);
        byte[] arr1 = new byte[ms1.Length];
        ms1.Position = 0;
        ms1.Read(arr1, 0, (int)ms1.Length);
        ms1.Close();
        return Convert.ToBase64String(arr1);
    }
}

3、SqlHelper

public class SQLHelper
{
    //连接字符串
    private static readonly string connStr = "Data Source=DESKTOP-NJ2V6IC;Initial Catalog=Study;Integrated Security=True";

    //1.执行增、删、改的方法：ExecuteNonQuery
    public static int ExecuteNonQuery(string sql, params SqlParameter[] pms)
    {
        using (SqlConnection con = new SqlConnection(connStr))
        {
            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                if (pms != null)
                {
                    cmd.Parameters.AddRange(pms);
                }
                con.Open();
                return cmd.ExecuteNonQuery();
            }
        }
    }

    //2.封装一个执行返回单个对象的方法：ExecuteScalar()
    public static object ExecuteScalar(string sql, params SqlParameter[] pms)
    {
        using (SqlConnection con = new SqlConnection(connStr))
        {
            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                if (pms != null)
                {
                    cmd.Parameters.AddRange(pms);
                }
                con.Open();
                return cmd.ExecuteScalar();
            }
        }
    }

    //3.执行查询多行多列的数据的方法：ExecuteReader
    public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms)
    {
        SqlConnection con = new SqlConnection(connStr);
        using (SqlCommand cmd = new SqlCommand(sql, con))
        {
            if (pms != null)
            {
                cmd.Parameters.AddRange(pms);
            }
            try
            {
                con.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception)
            {
                con.Close();
                con.Dispose();
                throw;
            }
        }
    }

    //4.执行返回DataTable的方法
    public static DataTable ExecuteDataTable(string sql, params SqlParameter[] pms)
    {
        DataTable dt = new DataTable();
        using (SqlDataAdapter adapter = new SqlDataAdapter(sql, connStr))
        {
            if (pms != null)
            {
                adapter.SelectCommand.Parameters.AddRange(pms);
            }
            adapter.Fill(dt);
        }
        return dt;
    }
}

4、页面

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    void LoadData()
    {
        try
        {
            dataGridView1.Rows.Clear();
            string sql = "select * from Employer";
            SqlDataReader sdr = SQLHelper.ExecuteReader(sql);
            if (sdr.HasRows)
            {
                while (sdr.Read())
                {
                    int index = dataGridView1.Rows.Add();
                    dataGridView1.Rows[index].Cells[0].Value = sdr[0].ToString();
                    dataGridView1.Rows[index].Cells[1].Value = sdr[1].ToString();
                    dataGridView1.Rows[index].Cells[2].Value = sdr[2].ToString();
                    dataGridView1.Rows[index].Cells[3].Value = sdr[3].ToString();
                    dataGridView1.Rows[index].Cells[4].Value = ObjectConvertUtil.StringConvertToImage(sdr[4].ToString());
                }
            }
            sdr.Close();
        }
        catch (Exception ex)
        {

        }
    }

    private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
    {
        if (this.dataGridView1.Columns[this.dataGridView1.CurrentCell.ColumnIndex].HeaderText == "头像")
        {
            OpenFileDialog open = new OpenFileDialog();
            if (open.ShowDialog() == DialogResult.OK)
            {
                string openfile = open.FileName;
                FileStream fs = new FileStream(openfile.Trim(), FileMode.Open);
                byte[] imageBytes = new byte[fs.Length];
                BinaryReader br = new BinaryReader(fs);
                imageBytes = br.ReadBytes(Convert.ToInt32(fs.Length));//图片转换成二进制流
                MemoryStream ms = new MemoryStream(imageBytes);
                Image img = System.Drawing.Image.FromStream(ms);

                this.dataGridView1.CurrentCell.Value = img;
            }
        }
    }

    /// <summary>
    /// 插入
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void button1_Click(object sender, EventArgs e)
    {
        try
        {
            int index = dataGridView1.CurrentRow.Index;
            if (index != -1)
            {
                Employer employer = new Employer();
                employer.Name = dataGridView1.Rows[index].Cells[1].Value.ToString();
                employer.PhoneNum = dataGridView1.Rows[index].Cells[2].Value.ToString();
                employer.Address = dataGridView1.Rows[index].Cells[3].Value.ToString();

                Image image = (Image)dataGridView1.Rows[index].Cells[4].Value;
                string phontoStr = ObjectConvertUtil.BitmapConvertToBase64(image);

                string sql = "insert into Employer(name,phoneNum,address,photo) values('" + employer.Name + "','" + employer.PhoneNum + "','" + employer.Address + "','" + phontoStr + "')";
                int i = SQLHelper.ExecuteNonQuery(sql);
                MessageBox.Show("添加成功");
                LoadData();
            }
        }
        catch (Exception ex)
        {

        }
    }

    /// <summary>
    /// 读取
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void button3_Click(object sender, EventArgs e)
    {
        LoadData();
    }
}