ADO.NET增、删、改、查

增：

 

//展示方法

public void ZhanShi()
{
SqlConnection conn = new SqlConnection(DBHelper.strConn);
try
{
conn.Open();
DataTable dt = new DataTable();
string sql = @"SELECT [Id],[danwei],[name],[jiguan],[junxian],[ruwu],[liduitime],[guiduitime],[beizhu],[hunfou],[laidui] FROM [dbo].[Body]";
SqlDataAdapter ds = new SqlDataAdapter(sql, conn);
ds.Fill(dt);
this.skinDataGridView1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show("出现异常：" + ex);
}
finally
{
DBHelper.CloseConnection();
}
}

 

//增:
　　　　　　　SqlConnection conn = new SqlConnection(DBHelper.strConn);
            try
            {
                conn.Open();
                string sql = string.Format(@"INSERT INTO [dbo].[Body]([danwei],[name],[junxian],[ruwu],[beizhu],[jiguan],[hunfou],[laidui]) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", danwei, name, junxian, ruwu, beizhu, jiguan,hunfou,laidui);
                SqlCommand comm = new SqlCommand(sql, conn);
                int count = comm.ExecuteNonQuery();
                if (count < 0)
                {
                    MessageBox.Show("添加失败", "消息提示");
                }
                else
                {
                    MessageBox.Show("添加成功", "消息提示");
                    this.Close();
                    
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("异常" + ex.Message);
            }
            finally
            {
                DBHelper.CloseConnection();
            }

删：

DialogResult result = MessageBox.Show("确定删除该条数据吗？","消息提示",MessageBoxButtons.YesNo,MessageBoxIcon.Error);
            if (result != DialogResult.Yes)
            {
                return;
            }
            int id = Convert.ToInt32(this.skinDataGridView1.SelectedRows[0].Cells["cid"].Value);
            string sql = @"delete Body where Id = {0}";
            sql = string.Format(sql, id);
            SqlConnection conn = new SqlConnection(DBHelper.strConn);
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand(sql, conn);
                int count = comm.ExecuteNonQuery();
                if (count > 0)
                {
                    ZhanShi();
                }
                else
                {
                    MessageBox.Show("删除失败", "消息提示");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("异常：" + ex);
            }
            finally
            {
                DBHelper.CloseConnection();
            }

改：

try
            {
                //获取输入的内容
                string sql = string.Format(@"update [dbo].[Body] SET [danwei]='{1}',[name]='{2}',[junxian]='{3}',[ruwu]='{4}',[type]='{5}',[liduitime]='{6}',[guiduitime]='{7}',[jiguan]='{8}',[lucheng]='{9}',[zongday]='{10}',[hunfou]='{11}',[laidui]='{12}',[beizhu]='{13}' WHERE [Id]={0}", id, danwei, name, junxian, ruwu, type, liduitime, guiduitime, jiguan, lucheng, zongday, hunfou, laidui, beizhu);
                int count = DBHelper.ExecuteNonQuery(sql);
                if (count > 0)
                {
                    MessageBox.Show("修改成功!");
                }
                else
                {
                    MessageBox.Show("修改失败!");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.StackTrace,ex.Message);
            }
            finally
            {
                DBHelper.CloseConnection();
            }

查：

 SqlConnection conn = new SqlConnection(DBHelper.strConn);
            try
            {
                conn.Open();
                DataTable dt = new DataTable();
                string sql = @"SELECT [Id],[danwei],[name],[jiguan],[junxian],[ruwu],[liduitime],[guiduitime],[beizhu],[hunfou],[laidui] FROM [dbo].[Body]";
                SqlDataAdapter ds = new SqlDataAdapter(sql, conn);
                ds.Fill(dt);
                this.skinDataGridView1.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show("异常：" + ex);
            }
            finally
            {
                DBHelper.CloseConnection();
            }

DBHelper类（很多没有用到，练习原始方法）：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace XiuJia
{
    class DBHelper
    {
        public readonly static string strConn = @"Data Source=.;Initial Catalog=Vacation;User ID=领航鲸团队;Password=13904581665";

        private static SqlConnection conn = null;
        //创建一个待执行的SqlCommand对象
        private static SqlCommand PrepareCommand(string sql)
        {
            conn = new SqlConnection(strConn);
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            return comm;
        }
        //通过执行SQL查询，将首行首列结果返回
        public static object ExecuteScaLar(string sql)
        {
            SqlCommand comm = PrepareCommand(sql);
            return comm.ExecuteScalar();
        }
        //关闭连接对象
        public static void CloseConnection()
        {
            if (conn != null && conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }
        }
        //执行目标SQL语句，并返回SqlDataReader读取对象
        public static SqlDataReader ExecuteReader(string sql)
        {
            SqlCommand comm = PrepareCommand(sql);
            SqlDataReader reader = comm.ExecuteReader();
            return reader;
        }
        //执行目标SQL语句，返回DATATABLE，用于绑定数据源
        public static DataTable ExecuteForDataTable(string sql)
        {
            DataTable dt = new DataTable();
            SqlCommand comm = PrepareCommand(sql);//创建一个待执行的对象
            SqlDataAdapter sda = new SqlDataAdapter(sql, strConn);//创建适配器
            sda.Fill(dt);//将数据填充到DataTable中
            return dt;
        }
        //执行目标SQL语句，返回受影响行数，用于增删改等更新操作
        public static int ExecuteNonQuery(string sql)
        {
            SqlCommand comm = PrepareCommand(sql);
            int count = comm.ExecuteNonQuery();
            return count;
        }
    }
}