ADO.NET基础（C#访问SQLServer数据库进行增删改查）

火锅哥哥

http://ADO.NET基础

数据访问组件、常用对象与CRUD基础操作





数据库准备
use master  --表示下面的操作是针对master数据库完成的
go
--判断当前数据库是否在master数据库中已经存在
if exists(select * from sysdatabases where name='MISDB')
drop database MISDB   --删除数据库
go

--创建数据库
create database MISDB
on primary  
(
      name='MISDB_Mdata',  --数据库文件的逻辑名称（数据库服务内部使用的）
          filename='D:\C#study\DB\MISDB_Mdata.mdf',  --数据库物理文件名（绝对路径）
          size=30MB, --数据库文件初始大小
          filegrowth=1MB  --当数据超过文件大小的时候自动增长量
),
(   --次要数据文件（可以有多个，也可以没有）
      name='MISDB_ndata',  
          filename='D:\C#study\DB\MISDB_ndata.ndf',
          size=20MB,
          filegrowth=1MB
)
log on
(
      name='MISDB_log1',  
          filename='D:\C#study\DB\MISDB_log1.ldf',
          size=2MB,
          filegrowth=1MB
),
(
      name='MISDB_log2',  
          filename='D:\C#study\DB\MISDB_log2.ldf',
          size=2MB,
          filegrowth=1MB
)
go
--添加数据表
use MISDB
go
--部门信息表
if exists(select * from sysobjects where name='Department')
drop table Department
go
create table Department
(
    DepartmentId int identity(10,1) primary key,  --主键、标识列（从10开始，每次递增1）
        DepartmentName varchar(50) not null,  --部门名称，非空
)
go
--职位信息表
if exists(select * from sysobjects where name='Post')
drop table Post
go
create table Post
(
     PostId int  identity(10,1) primary key,
         PostName varchar(50) not null
)
go
--员工信息表
if exists(select * from sysobjects where name='Employee')
drop table Employee
go
create table Employee
(
      EmployeeID int identity(1000,1) primary key,   --主键约束
          EmployeeName varchar(50) not null,    --非空约束
          Gender char(2) not null,
          NowAddress nvarchar(100) default('地址不详'),  --默认约束
          IdNo char(18) not null check(len(IdNo)=18),  --检查约束
          WeiXinNumber varchar(20) not null,
          PhoneNumber varchar(50) not null,
          OtherWork nvarchar(50) null,
          EntryDate datetime not null,
          PostId int references Post(PostId) ,--外键约束（职位编号）
          DepartmentId int references Department(DepartmentId)  --外键（部门编号）
)
go
--插入测试数据   （常见操作：insert update delete select ）

--经验：首先要插入没有《外键约束》的主键表的数据。
use MISDB
go
insert into Department(DepartmentName) values('开发部'),('测试部'),('实施部'),('财务部'),('人事部')
--insert into Department(DepartmentName) values('测试部')
--insert into Department(DepartmentName) values('实施部')
--insert into Department(DepartmentName) values('财务部')
--insert into Department(DepartmentName) values('人事部')


--select DepartmentId,DepartmentName from Department
insert into Post(PostName) values('软件工程师'),('测试工程师'),('实施工程师'),('财务经理'),('人事经理')



insert into Employee(EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber, PhoneNumber, OtherWork, EntryDate, PostId, DepartmentId)
values
('Kiter10','男','天津','123226199012111510','thinger01','13600001201','暂无','2025-01-01',10,10),
('Kiter11','男','北京','123226199012111511','thinger02','13600001202','暂无','2020-01-02',11,11),
('Kiter12','女','上海','123226199012111512','thinger03','13600001203','暂无','2020-01-03',12,13),
('Kiter13','男','广州','123226199012111513','thinger04','13600001204','暂无','2020-01-04',10,10),
('Kiter14','男','苏州','123226199012111514','thinger05','13600001205','暂无','2020-01-05',12,12),
('Kiter15','女','南宁','123226199012111515','thinger06','13600001206','暂无','2020-01-06',10,10),
('Kiter16','男','德州','123226199012111516','thinger07','13600001207','暂无','2020-01-07',11,11),
('Kiter17','男','衢州','123226199012111517','thinger08','13600001208','暂无','2020-01-08',12,12),
('Kiter18','女','西安','123226199012111518','thinger09','13600001209','暂无','2020-01-09',10,10),
('Kiter19','男','咸阳','123226199012111519','thinger10','13600001210','暂无','2020-01-10',11,11),
('Kiter20','女','沈阳','123226199012111520','thinger11','13600001211','暂无','2020-01-11',14,14)


select * from Employee
select * from Department
select * from Post

--select EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber,PostId from Employee
-- where PostId=10 and DepartmentId=10

-- select EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber,PostId from Employee
-- where PostId>10  

--  select EmployeeId,EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber,PostId from Employee
-- where Gender='男'

--更新的话，一定要注意写where
--update Employee set  EmployeeName='王老师' ,NowAddress='天津' where EmployeeId=1001

--删除的话，一定要注意写where
--delete from Employee where EmployeeId=1002

-- delete from Employee where Gender='男'

-- delete from Employee

-- 这个无法执行删除（因为主键值，已经被外键表使用）
-- delete from Department where DepartmentId=11

-- 如果要强制删除，可以在确保安全的情况下，先删除外键表数据
-- delete from Employee where DepartmentId=11

  --连接查询和汇总查询
--  select EmployeeName, Gender, NowAddress, IdNo,PostName, DepartmentName,Post.PostId from Employee
--  inner join Post on Employee.PostId=Post.PostId
--  inner join Department on Employee.DepartmentId=Department.DepartmentId

--  --改变列的名称
--    select 员工姓名=EmployeeName, 性别=Gender, NowAddress as 住址, IdNo,PostName, DepartmentName,Post.PostId from Employee
--  inner join Post on Employee.PostId=Post.PostId
--  inner join Department on Employee.DepartmentId=Department.DepartmentId

--  --聚合查询
--select count(*) as 员工总数 from Employee
--select 最大编号=max(EmployeeId)  from Employee
--select 最小编号=min(EmployeeId)  from Employee

--select  员工总数=count(*) ,最大编号=max(EmployeeId),最小编号=min(EmployeeId)  from Employee

--select 平均编号=avg(EmployeeId)  from Employee

编写连接数据库类
注意连接数据库的名称和密码


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

//访问不同的数据库，需要使用不同命名空间中的类
using System.Data.SqlClient;
using System.Data.OleDb;

using System.Data;  //一般都要同时引入


//SQLServer   Accesss   

namespace thinger.ADONETTeach
{
    /// <summary>
    /// 主要练习基础的数据库连接、insert、update、delete、select
    /// </summary>
    public class CRUDBase
    {
        //封装连接字符串  MATEBOOK16S\\SQLEXPRESS 表示本地数据库名称  正常应该使用IP地址
        private string connString = "Server=MATEBOOK16S\\SQLEXPRESS;DataBase=MISDB;Uid=sa;Pwd=123456";

        //连接数据库
        public void ConnectDB()
        {
            //【1】创建Connection对象
            //SqlConnection conn = new SqlConnection();
            //conn.ConnectionString = connString;

            SqlConnection conn = new SqlConnection(connString); //建议使用有参数构造方法
            //【2】打开连接
            conn.Open();

            if (conn.State == ConnectionState.Open)
            {
                Console.WriteLine("连接已经打开！");
            }

            //【3】关闭连接
            conn.Close();
            if (conn.State == ConnectionState.Closed)
            {
                Console.WriteLine("连接已经关闭！");
            }
        }
    }
}
主程序
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace thinger.ADONETTeach
{
    class Program
    {
        static void Main(string[] args)
        {
            //测试连接和关闭
            CRUDBase crudBase = new CRUDBase();
            crudBase.ConnectDB();
            Console.Read();
        }


    }
}
运行结果


成功与数据库建立连接  并关闭数据库
增删改查代码

新增操作
public void Insert()
        {
            //【1】创建连接对象
            SqlConnection conn = new SqlConnection(connString);
            //【2】定义SQL语句
            string sql = " insert into Employee (EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber, PhoneNumber, OtherWork, EntryDate, PostId, DepartmentId)";
            sql += " values('韩老师', '男', '天津', '120223199001091217', 'thinger007', '18500000007', '开发', '2014-01-01', 10, 10)";
            //【2】创建Command对象
            //SqlCommand cmd = new SqlCommand();
            //cmd.CommandText = sql;
            //cmd.Connection = conn;
            SqlCommand cmd = new SqlCommand(sql, conn);
            //【3】打开连接
            conn.Open();
            //【4】执行操作
            int result = cmd.ExecuteNonQuery(); //此方法，可以执行insert、update、delete类型的sql语句，不能执行select

            Console.WriteLine("受影响的行数：" + result);
            //【5】关闭连接
            conn.Close();
        }
修改操作
public void Update()
        {
            SqlConnection conn = new SqlConnection(connString);
            string sql = "update Employee set EmployeeName ='王老师',Gender='女' where EmployeeId=1010";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            int result = cmd.ExecuteNonQuery();
            conn.Close();
            Console.WriteLine("受影响的行数：" + result);
        }
删除操作
public void Delete()
        {
            SqlConnection conn = new SqlConnection(connString);
            string sql = "delete from  Employee where EmployeeId=1010";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            int result = cmd.ExecuteNonQuery();
            conn.Close();
            Console.WriteLine("受影响的行数：" + result);
        }
        //insert 和update、delete类型的sql语句执行的过程步骤是完全一样的！
查询操作  单一结果的查询
        //单一结果的查询
        public void GetSingleResult1()
        {
            SqlConnection conn = new SqlConnection(connString);
            string sql = "select count(*) as 员工总数 from employee";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            object result = cmd.ExecuteScalar();
            conn.Close();

            Console.WriteLine(result);
        }
        public void GetSingleResult2()
        {
            SqlConnection conn = new SqlConnection(connString);
            string sql = "select EmployeeName from Employee where EmployeeId=1016";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            object result = cmd.ExecuteScalar();
            conn.Close();
            Console.WriteLine(result);
        }
查询操作 返回当前最大id
  public void GetSingleResult3()
        {
            SqlConnection conn = new SqlConnection(connString);
            string sql = " insert into Employee (EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber, PhoneNumber, OtherWork, EntryDate, PostId, DepartmentId)";
            sql += " values('张老师', '女', '天津', '120223199001091218', 'thinger008', '18500000008', '开发', '2014-01-01', 10, 10)";
            sql += ";select @@identity";

            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            object result = cmd.ExecuteScalar();
            Console.WriteLine("当前最大值：" + result);
            conn.Close();
        }
查询操作  返回一个结果集的查询（只读）
/// <summary>
        /// 返回一个结果集的查询（只读）
        /// </summary>
        public void GetReaderList1()
        {
            SqlConnection conn = new SqlConnection(connString);
            string sql = "select EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber, PhoneNumber from Employee";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            //提交查询返回一个只读数据集
            SqlDataReader reader = cmd.ExecuteReader();
            //使用循环读取
            while (reader.Read())
            {
                Console.WriteLine($"{reader["EmployeeName"]}\t{reader["Gender"]}\t" +
                    $"{reader["NowAddress"]}\t{reader[3]}\t{reader["WeiXinNumber"]}\t{reader["PhoneNumber"]}");
            }
            reader.Close();//关闭读取器（必须在读取完毕后首先关闭）                        
            conn.Close();  //一定要在读取器关闭后，再关闭，否则出问题
        }
查询操作  返回多个结果集 返回关联的表数据
/// <summary>
        /// 返回多个结果集
        /// </summary>
        public void GetReaderList2()
        {
            SqlConnection conn = new SqlConnection(connString);
            string sql = "select EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber, PhoneNumber from Employee";
            sql += ";select DepartmentId,DepartmentName from Department";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            //使用循环读取
            while (reader.Read())
            {
                Console.WriteLine($"{reader["EmployeeName"]}\t{reader["Gender"]}\t" +
                    $"{reader["NowAddress"]}\t{reader[3]}\t{reader["WeiXinNumber"]}\t{reader["PhoneNumber"]}");
            }
            Console.WriteLine("**************************************************");
            //跳转到下一个结果集
            if (reader.NextResult())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"{reader["DepartmentId"]}\t{reader["DepartmentName"]}");
                }
            }
            reader.Close();
            conn.Close();
        }
查询操作   返回一个数据集
        /// <summary>
        /// 返回一个数据集
        /// </summary>
        public void GetDataSet1()
        {
            SqlConnection conn = new SqlConnection(connString);
            string sql = "select EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber, PhoneNumber from Employee";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();

            //创建一个数据适配器对象（SqlDataAdapter）
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            //创建数据集对象DataSet（内存数据库）DataTable
            DataSet ds = new DataSet();
            //填充数据
            da.Fill(ds);
            conn.Close();

            //遍历DataTable的数据行
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Console.WriteLine($"{row["EmployeeName"]}\t{row["Gender"]}\t{row["NowAddress"]}\t{row["IdNo"]}");
            }
        }
查询操作 返回一个数据集包含多张数据表
/// <summary>
        /// 返回一个数据集包含多张数据表
        /// </summary>
        public void GetDataSet2()
        {
            SqlConnection conn = new SqlConnection(connString);
            string sql = "select EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber, PhoneNumber from Employee";
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();

            //填充数据【第一个查询结果】
            da.Fill(ds, "Employee");

            //封装第二个查询
            cmd.CommandText = "select DepartmentId,DepartmentName from Department";
            da.Fill(ds, "Department");

            //封装第三个查询
            cmd.CommandText = "select PostId,PostName from Post";
            da.Fill(ds, "Post");
            conn.Close();

            //遍历DataTable
            foreach (DataTable dt in ds.Tables)
            {
                Console.WriteLine($"{dt.TableName}\t{dt.Rows.Count}");
            }
            Console.WriteLine("*********************");

            //遍历第一个表的DataTable的数据行
            foreach (DataRow row in ds.Tables["Employee"].Rows)
            {
                Console.WriteLine($"{row["EmployeeName"]}\t{row["Gender"]}\t{row["NowAddress"]}\t{row["IdNo"]}");
            }
            Console.WriteLine("*********************");
            foreach (DataRow row in ds.Tables["Department"].Rows)
            {
                Console.WriteLine($"{row["DepartmentId"]}\t{row["DepartmentName"]}");
            }
            Console.WriteLine("*********************");
            foreach (DataRow row in ds.Tables["Post"].Rows)
            {
                Console.WriteLine($"{row["PostId"]}\t{row["PostName"]}");
            }
        }
主程序
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace thinger.ADONETTeach
{
    class Program
    {
        static void Main(string[] args)
        {
            //测试连接和关闭
            CRUDBase crudBase = new CRUDBase();
            //crudBase.ConnectDB();

            crudBase.Insert();

            //crudBase.Update();

            //crudBase.Delete();

            //crudBase.GetSingleResult1();
            //crudBase.GetSingleResult2();
            //crudBase.GetSingleResult3();

            //crudBase.GetReaderList1();
            //crudBase.GetReaderList2();
            //crudBase.GetDataSet1();

            //crudBase.GetDataSet2();

            Console.Read();
        }


    }
}