SQL 数据库学习04 ADB.NET

ADO.NET

一种数据访问技术，应用程序可以连接到数据库，并以各种方式来操作其中的数据。

组成：

1. DataSet 非连接的核心组件。独立于任何数据源的数据访问，多种不同的数据源。
2. Data Provider(数据提供程序)：用于连接数据库、执行命令、检索结果。

数据库连接示例

using System.Data.SqlClient;


namespace AdoNetCourse
{ 
    class Program
    {
        static void Main()
        {
            SqlConnection conn = new SqlConnection();

            //conn.ConnectionString = "server=.;database=TestBase;uid=sa;pwd='123456'";
            //初始化目录
            //conn.ConnectionString = "server=.;Initial Catalog=TestBase;uid=sa;pwd='123456'";

            //windows 身份验证                                              综合安全   SSPI/TRUE
            //conn.ConnectionString = "Data Source=.;Initial Catalog=TestBase;Integrated Security=SSPI";
            conn.ConnectionString = "Data Source=.;Initial Catalog=TestBase;Trusted_Connection=SSPI";

            //conn.Database;//要连接的数据库名称
            //conn.DataSource //数据源
            //conn.State//连接状态
            //conn.ConnectionTimeout//超时时间


            conn.Open();

            conn.Close();
            conn.Open();   //这里仍可重新执行打开

            conn.Dispose();//释放连接
            conn.Open();  //由于conStr被释放所以报错

            //Close关闭后还可以再打开
            //Dispose后，连接字符串也被清空，要重新设置连接字符串
        }
    }
}

构造连接字符串的其他方法 SqlConnectionStringBuilder/读取配置文件

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

namespace ConsoleApp1
{
    internal class Program
    {
        static void Main(string[] args)
        {
            SqlConnection conn = new SqlConnection();

            #region 方法1：SqlConnectionStringBuilder
            //SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

            //builder.DataSource = ".";
            //builder.InitialCatalog = "DataBase";
            //builder.UserID = "sa";
            //builder.Password = "123456";
            //builder.Pooling = false;//禁用连接池

            //conn.ConnectionString = builder.ConnectionString;
            #endregion

            #region 方法2：读取配置文件
            //引用：System.Configuration
            //conn.ConnectionString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

            //conn.ConnectionString = ConfigurationManager.AppSettings["connStr"].ToString();
            #endregion

            conn.Open();

            conn.Close();
        }
    }
}

Try…catch/Using

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

namespace ConsoleApp3
{
    internal class Program
    {
        static void Main(string[] args)
        {

            SqlConnection conn = null;
           
            //使用try catch 不终止程序
            try
            {
                string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

                conn = new SqlConnection(connStr);

                conn.Open();

            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            //using 对象自动释放
            //避免忘记对象被释放
            //using 使用的对象必须是继承IDisposable（提供一种用于释放非托管资源的机制）
            //string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
            //using (SqlConnection conn1 = new SqlConnection(connStr))
            //{
            //    conn1.Open();
            //}
        }
    }
}

连接池

原理：
为数据库连接建立一个“缓冲池”,预先在池中放入一定数量的数据库连接管道,需要时,从池子中取出管道进行使用,操作完毕后,在将管道放入池子中,从而避免了频繁的向数据库申请资源,释放资源带来的性能损耗。

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

namespace 线程池
{
    internal class Program
    {
        static void Main(string[] args)
        {
            #region 线程池默认打开 所以只打开了5个
            Stopwatch sw = new Stopwatch();
            sw.Start();
            string connStr = "server=.;database=TestBase;uid=ou;password=123456;Max Pool Size=5;";

            for (int i = 0; i < 100; i++)
            {
                SqlConnection conn = new SqlConnection(connStr);
                conn.Open();
                //Console.WriteLine($"第{i + 1}个连接已打开");
                conn.Close();
            }
            sw.Stop();
            Console.WriteLine(sw.ElapsedMilliseconds);
            #endregion

            #region 不使用连接池耗时测试
            Stopwatch sw1 = new Stopwatch();
            sw1.Start();
            string connStr1 = "server=.;database=TestBase;uid=ou;password=123456;Max Pool Size=5;Pooling=false";

            for (int i = 0; i < 100; i++)
            {
                SqlConnection conn1 = new SqlConnection(connStr1);
                conn1.Open();
                conn1.Close();
            }
            sw1.Stop();
            Console.WriteLine(sw1.ElapsedMilliseconds);
           
            #endregion

            #region 连接池类别区分测试
            string connStr2 = "server=.;database=TestBase;uid=ou;password=123456;Max Pool Size=5;";
            string connStr3 = "server=.;database=TestBase;uid=ou;password=123456; Max Pool Size=5;";
            string connStr4 = "server=.;database=TestBase;uid=ou;password=123456;Max Pool Size=5;";

            //下面会报错，2，4共用资源，3多了个空格
            for (int i = 0; i < 100; i++)
            {
                SqlConnection conn2 = new SqlConnection(connStr2);
                conn2.Open();
                Console.WriteLine($"conn2第{i + 1}个连接已打开");
                SqlConnection conn3 = new SqlConnection(connStr3);
                conn3.Open();
                Console.WriteLine($"conn3第{i + 1}个连接已打开");
                SqlConnection conn4 = new SqlConnection(connStr4);
                conn4.Open();
                Console.WriteLine($"conn4第{i + 1}个连接已打开");

            }

            //可得，连接字符串区分不同的连接池

            Console.ReadKey();
            #endregion
        }
    }
}

执行命令，增删改查

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

namespace SQL命令
{
    internal class Program
    {
        static void Main(string[] args)
        {

            try
            {
                using (SqlConnection conn = new SqlConnection
                (ConfigurationManager.ConnectionStrings
                ["connStr"].ConnectionString))
                {
                    conn.Open();

                    #region 创建命令的多种方法，一般用第四种
                    //创建命令，执行命令的对象 执行命令
                    //SqlCommand 对SQL数据库执行的一个T-SQL语句或存储过程
                    //string cmdStr = "select* from UserInfo";
                    //SqlCommand cmd = conn.CreateCommand();
                    //cmd.Connection = conn;
                    //cmd.CommandText = cmdStr;

                    可忽略
                    //cmd.CommandType = CommandType.Text;
                    //#region 如果是存储过程  则必须设置
                    cmd.CommandType = CommandType.StoredProcedure;
                    //#endregion

                    //SqlCommand cmd1 = new SqlCommand(cmdStr);
                    //cmd1.Connection = conn;


                    //SqlCommand cmd2 = new SqlCommand(cmdStr, conn);

                    //SqlCommand cmd3 = conn.CreateCommand();
                    //cmd3.CommandText = cmdStr;

                    //SqlCommand cmd4 = new SqlCommand(cmdStr, conn, null);
                    #endregion

                    #region ----------------增删改-----------
                    string uName = "jason";
                    string uPwd = "1234";
                    int age = 25;
                    int deptId = 3;

                    string cmdStr1 = "select* from UserInfos";

                    //insert UserInfos(UserName,UserPwd,CreateTime,DeptId,Age) values('4444', '4444', GETDATE(), 4, 4)
                    string cmdStr2 = "insert UserInfos(UserName,UserPwd,CreateTime,DeptId,Age) values(" + "'" + uName + "'" + ", " + "'" + uPwd + "'" + ", GETDATE()," + "'" + age + "'" + "," + "'" + deptId + "'" + ")";
                    SqlCommand cmd5 = new SqlCommand(cmdStr2, conn, null);

                    //执行SQL命令，并返回受影响的行数
                    //Console.WriteLine(cmd5.ExecuteNonQuery());
                    #endregion

                    #region ----------------查询-------------
                    object o = null;

                    string cmdStr3 = "select * from UserInfos ";

                    //返回一个表的数据行数
                    string cmdStr4 = "select count(1) from UserInfos where age>10";

                    //@@获得刚刚生成的标识列的值
                    string cmdStr5 = "insert into DepInfos(DepName)values('快乐部');select @@identity";

                    SqlCommand cmd6 = new SqlCommand(cmdStr5, conn, null);

                    //执行SQL语句或存储过程，并返回查询结果中的第一格(第一行第一列)的值，忽略其他行列
                    //适用于执行查询返回一个值
                   // o = cmd6.ExecuteScalar();

                    if(o != null)
                    {
                        Console.WriteLine(o.ToString());
                    }
                    #endregion

                    #region MyRegion
                    string cmdStr6 = "select UserId,UserName,Age from UserInfos";
                    SqlCommand cmd7 = new SqlCommand(cmdStr6, conn, null);


                    SqlDataReader dr = null;
                    dr = cmd7.ExecuteReader();

                    //数据要及时保存，读一条丢一条
                    while(dr.Read())
                    {
                       int userId = int.Parse(dr["UserId"].ToString());
                        string userName = dr["UserName"].ToString();
                        int _age = int.Parse(dr["Age"].ToString());
                        Console.WriteLine($"UserId:{ userId} UserName:{userName} Age:{_age}");
                    }

                    #endregion

                    Console.ReadKey();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.ReadKey();
            }
            
                
        }

      
    }
}

SqlParameter

存储过程

--创建一个无参数的存储过程
create proc SearchUserInfo
as
begin
 select UserId,UserName,Age From UserInfos
end

exec SearchUserInfo

--修改
alter proc SearchUserInfo
as
begin
 select UserId,UserName,Age From UserInfos
 select * From UserInfos
end

exec SearchUserInfo

--带参存储过程
alter proc AddUserInfo
@UserName nvarchar(50),
@userPwd varchar(50),
@Age int,
@DepId int
as
begin
--定义变量
	declare @time datetime
	set @time = getdate()
	insert into UserInfos(UserName,UserPwd,CreateTime,Age,DeptId)
	values(@UserName,@userPwd,@time,@Age,@DepId)
	delete from UserInfos where UserId=1003
	select * from UserInfos
end

exec AddUserInfo '2022' , '123' , 99 , 4 

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

namespace SQLparameter
{
    internal class Program
    {
        static void Main(string[] args)
        {
            #region 参数的构造方法,不看也行
            //1.
            SqlParameter par1 = new SqlParameter();
            par1.ParameterName = "@userName";//参数名
            par1.SqlDbType = System.Data.SqlDbType.VarChar;//数据类型
            par1.Value = "admin";//参数值
            par1.Size = 20;//大小

            //2.参数名，值
            SqlParameter par2 = new SqlParameter("@Age", 24);

            //3.
            SqlParameter par3 = new SqlParameter("@DepId", System.Data.SqlDbType.Int);
            par3.Size = 4;
            par3.Value = 3;

            //4.参数名称 类型 大小 
            SqlParameter par4 = new SqlParameter("@UserPwd", System.Data.SqlDbType.VarChar, 50);
            par4.Value = "123456";

            //5.参数名称 类型 大小 源列名
            SqlParameter par5 = new SqlParameter("@UserName", System.Data.SqlDbType.VarChar, 20, "UName");
            par4.Value = "123456";
            SqlCommand command = new SqlCommand();
            #endregion


            using (SqlConnection conn = new SqlConnection
              (ConfigurationManager.ConnectionStrings
              ["connStr"].ConnectionString))
            {
                conn.Open();

                #region 默认输入值 使用SqlParameter进行参数设置
                //string sql = "select Age from UserInfos where UserName = @userName";
                //SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@userName", "jason"));

                传入一个数组
                //SqlParameter[] paras =
                //{
                //    new SqlParameter("@userName", "jason")
                //};
                //cmd.Parameters.AddRange(paras);

                //object o = cmd.ExecuteScalar();
                //conn.Close();
                //Console.WriteLine(o.ToString());
                #endregion

                #region 输出值 使用SqlParameter进行参数接收，接收存储过程返回的值
                //创建存储过程                   存储过程名
                //SqlCommand cmd = new SqlCommand("GetDeptName", conn);
                //cmd.CommandType = System.Data.CommandType.StoredProcedure;

                //SqlParameter parId = new SqlParameter("@DepId", 3);
                //cmd.Parameters.Add(parId);

                //SqlParameter parName = new SqlParameter("@DepName", System.Data.SqlDbType.NVarChar, 50);
                //parName.Direction = System.Data.ParameterDirection.Output;
                //cmd.Parameters.Add(parName);

                //cmd.ExecuteScalar();
                执行后，接收到存储过程output 的值
                //Console.WriteLine(parName.Value.ToString());

                #endregion

                #region 既输入值也输出值
                //SqlCommand cmd = new SqlCommand("GetDeptNameNew", conn);
                //cmd.CommandType = System.Data.CommandType.StoredProcedure;

                //SqlParameter parName = new SqlParameter("@DepName", System.Data.SqlDbType.NVarChar, 50);

                1.传入值
                //parName.Value = "牌";
                //parName.Direction = System.Data.ParameterDirection.InputOutput;
                //cmd.Parameters.Add(parName);

                //cmd.ExecuteScalar();
                2.接收值，执行后，接收到存储过程output 的值
                //Console.WriteLine(parName.Value.ToString());

                #endregion

                #region 返回值 只可以返回int类型
                SqlCommand cmd = new SqlCommand("GetUserAge", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                //1.传入值
                SqlParameter[] paras =
                {
                     new SqlParameter("@UserId", "3001"),
                     //新定义一个变量接收返回值
                     new SqlParameter("@reValue", System.Data.SqlDbType.Int,4),
                };
                paras[1].Direction = System.Data.ParameterDirection.ReturnValue;
                cmd.Parameters.AddRange(paras);

                cmd.ExecuteScalar();
                //2.接收值，执行后，接收到存储过程output 的值
                Console.WriteLine(paras[1].Value.ToString());
                #endregion



                Console.ReadKey();

            }
        }
    }
}

DataReader 数据读取流

提供一种从数据库中读取行的方式。

单方向，单行读取，不能向后读取，不能修改数据。

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

namespace _08_SqlDataReader_
{
    internal class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection conn = new SqlConnection
            (ConfigurationManager.ConnectionStrings
            ["connStr"].ConnectionString))
            {
                conn.Open();
                string sql = "select UserId,UserName,Age from UserInfos";
                SqlCommand cmd = new SqlCommand(sql, conn);

                //不能直接构造，只能通过cmd.---来创建   ↓关闭reader对象，自动关闭连接
                SqlDataReader dr =  cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

                //DataTable dt = new DataTable();
                //dt.Load(dr);

                List<UserInfosModel> list = new List<UserInfosModel>();

                if (dr.HasRows)
                {
                    int indexId = dr.GetOrdinal("UserId");//获取指定列序号
                    int indexName = dr.GetOrdinal("UserName");
                    int indexAge = dr.GetOrdinal("Age");

                    string idName = dr.GetName(0); //获取指定列名


                    //检测是否有数据
                    while (dr.Read())
                    {
                        //dr[0] 第一列 列序号读取
                        //int userId = (int)dr[0];
                        //string userName = dr["UserName"].ToString();

                        //int userId = dr.GetInt32(indexId);
                        //string userName = dr.GetString(indexName);
                        //int age = dr.GetInt32(indexAge);

                        UserInfosModel model = new UserInfosModel();
                        model.UserId = dr.GetInt32(indexId);
                        model.UserName = dr.GetString(indexName);
                        model.Age = dr.GetByte(indexAge);
                        list.Add(model);    
                    }
                }
                dr.Close();
            }
        }
    }
}

DataTable 临时表

内存中的表

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

namespace _09_DataTable_
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //定义临时表存放数据
            //                           表名
            DataTable dt = new DataTable("UserInfo");

            //定义列
            List<DataColumn> listDc = new List<DataColumn>
            {
                new DataColumn("UserId",typeof(int)),
                new DataColumn("UserName",typeof(string)),
                new DataColumn("Age",typeof(byte)),
            };

            dt.Columns.AddRange(listDc.ToArray());

            //设置主键
            dt.PrimaryKey = new DataColumn[]{ dt.Columns[0]};
            //唯一约束
            dt.Constraints.Add(new UniqueConstraint(dt.Columns[1]));

            
            //行数据
            DataRow dataRow = dt.NewRow();
            dataRow[0] = 1;
            dataRow["UserName"] = "admin";
            dataRow["Age"] = 24;

            dt.Rows.Add(dataRow);

            //DataRowState
            
            dt.AcceptChanges();//提交修改 UnChanged

            dataRow["Age"] = 27;//已修改 Modified 

            dt.AcceptChanges();//提交修改 UnChanged

            //dataRow.Delete();
            //dt.AcceptChanges();

            //dt.RejectChanges();//回滚
            //dt.Clear();//清除数据
            //dt.Copy();//复制表

            //DataTable t2 = dt.Copy();//复制所有
            //DataTable t3 = dt.Clone();//复制列等结构，不复制数据
            //t3.Merge(t2);//合并并自动去重

            DataRow[] rows = dt.Select();
            DataRow[] rows1 = dt.Select("UserId>1","UserId desc");

            //RowState :Detached Added UnChanged Modified Deleted Detached

        }
    }
}

DataSet

数据在内存中的缓存，内存中的数据库
DataTable内存数据库中的表
DataSet由一组DataTabe 组成

应用

1. DataAdapter 将数据填充到DataSet中
2. DataAdapter 将DataSet中的更改提交到数据库
3. XML文档或文本加载到DataSet中

不依赖于不同类型数据库，存在内存中

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

namespace _10_DataSet
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //默认名称 NewDataSet
            //创建数据库
            DataSet ds = new DataSet("ds1");

            //创建表
            DataTable dt = new DataTable();

            //向数据库添加表
            ds.Tables.Add(dt);

            //获取刚刚添加的表
            DataTable dt2 = ds.Tables[0];

            //ds.Relations.Add();//添加关系

            //ds.AcceptChanges();//提交
            //ds.RejectChanges();//回滚
            //ds.Clear();
            //ds.Copy();
            //ds.Clone();//不复制数据
            //ds.Merge();//合并
            //ds.Reset();//重置
            //ds.Load();//将数据源的值填充到DataSet里面

            Console.ReadKey();
        }
    }
}

Relation

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

namespace _11_Relation
{
    internal class Program
    {
        static void Main(string[] args)
        {
            //关系：
            //1. 一对一，一个表列数太多，拆分成两张表，以主键来联系
            //2. 一对多
            //3. 多对多 使用中间表来维护，权限分配

            DataSet ds = new DataSet("ds");
            DataTable[] dts =
            {
               new DataTable("User"),
               new DataTable("Dept")
            };
  
            ds.Tables.AddRange(dts);

            DataColumn[] dc =
            {
                new DataColumn("UserId",typeof(int)),
                 new DataColumn("UserName",typeof(string)),
                 new DataColumn("Age",typeof(int)),
                 new DataColumn("DepId",typeof(int)),
            };

            DataColumn[] dc2 =
            {
                new DataColumn("DepId",typeof(int)),
                 new DataColumn("DepName",typeof(string)),
            };

            ds.Tables[0].Columns.AddRange(dc);
            ds.Tables[1].Columns.AddRange(dc2);

                              列数据数组类型 
            //dts[0].PrimaryKey = new DataColumn[] { dc[0] };//主键

            //dts[0].Constraints.Add(new UniqueConstraint("uc", dc2[1]));//唯一性约束

            //dts[0].Constraints.Add(new ForeignKeyConstraint("fk",dc2[0], dc[3]));//外键约束

            //自动为父表中的列建立唯一约束，子表中外键列建立外键约束
            DataRelation relation = new DataRelation("relation", ds.Tables[1].Columns[0], ds.Tables[0].Columns[3], true);

            //添加到ds.Relation中
            ds.Relations.Add(relation);

            InitData(ds.Tables[0], ds.Tables[1]);

            //通过父表读取子表数据
            foreach (DataRow dr in ds.Tables[1].Rows)
            {
                DataRow[] rows = dr.GetChildRows(relation);
                foreach (DataRow row in rows)
                {
                    Console.WriteLine(row[0].ToString()+" "+row[1].ToString() + " "+row[2].ToString() + " "
                        +row[3].ToString() + " ");
                }
            }

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                DataRow[] rows = dr.GetParentRows(relation);
                foreach (DataRow row in rows)
                {
                    Console.WriteLine(row[0].ToString() + " " + row[1].ToString());
                }
            }
            Console.ReadKey();

        }

        static void InitData(DataTable dt1,DataTable dt2)
        {
            DataRow dr2 = dt2.NewRow();
            dr2["DepId"] = 1;
            dr2["DepName"] = "人事部";
            dt2.Rows.Add(dr2);

            dr2 = dt2.NewRow();
            dr2["DepId"] = 2;
            dr2["DepName"] = "管理部";
            dt2.Rows.Add(dr2);

           dr2 = dt2.NewRow();
            dr2["DepId"] = 3;
            dr2["DepName"] = "销售部";
            dt2.Rows.Add(dr2);

            DataRow dr1 = dt1.NewRow();
            dr1["UserId"] = 1;
            dr1["UserName"] = "李明";
            dr1["Age"] = 22;
            dr1["DepId"] = 3;
            dt1.Rows.Add(dr1);

            dr1 = dt1.NewRow();
            dr1["UserId"] = 2;
            dr1["UserName"] = "刘丽";
            dr1["Age"] = 23;
            dr1["DepId"] = 1;
            dt1.Rows.Add(dr1);

            dr1 = dt1.NewRow();
            dr1["UserId"] = 2;
            dr1["UserName"] = "王力";
            dr1["Age"] = 23;
            dr1["DepId"] = 3;
            dt1.Rows.Add(dr1);

        }
    }
}

SqlDataAdapter 适配器

DataSet和SQLServer之间的桥接器

通过fill把数据从SQL里取出来加载到DataSet里面，然后通过update将修改后的数据提交到数据库

填充DataSet以及更新数据源的一组数据库命令和数据库连接。

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

namespace _11_DataAdapter
{
    internal class Program
    {
        static void Main(string[] args)
        {

            SqlConnection conn =  new SqlConnection
            (ConfigurationManager.ConnectionStrings
            ["connStr"].ConnectionString);

            string sql = "select * from UserInfos;select * from DepInfos";

            #region 创建方式
            #region 1.
            SqlDataAdapter adapter = new SqlDataAdapter();

            adapter.SelectCommand = new SqlCommand(sql, conn);
            #endregion

            #region 2.参数 查询语句和连接对象
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, conn);
            #endregion


            #region 3.参数 查询语句和连接字符串
            SqlDataAdapter sqlDataAdapter2 = new SqlDataAdapter(sql, ConfigurationManager.ConnectionStrings
            ["connStr"].ConnectionString);
            #endregion
            #endregion

            DataSet ds = new DataSet();
            
            //映射，重命名表显示在查看器中
            sqlDataAdapter2.TableMappings.Add("Table","Users");
            sqlDataAdapter2.TableMappings.Add("Table1","Dept");
            //得到多个结果集，表名 Table Table1......
            sqlDataAdapter2.Fill(ds);

           // ds.Tables[0].Rows[2]["UserName"] = "oo123";

            //新增行
            DataRow dr = ds.Tables[0].NewRow();
            dr["UserName"] = "bb";
            dr["UserPwd"] = "44123";
            dr["Age"] = 23;
            dr["DeptId"] = 3;

            ds.Tables[0].Rows.Add(dr);

            //自动生成对应SQL命令
            //SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter2);

            //手动配置builder
            SqlCommand insertCmd = new SqlCommand("insert into UserInfos (UserName,UserPwd,Age,DeptId)values (@UserName,@UserPwd,@Age,@DeptId)",conn);

            SqlParameter[] parameters = {
                //                                                源列名 DataTable对应列的列名 
                new SqlParameter("@UserName",SqlDbType.NVarChar,50,"UserName"),
                new SqlParameter("@UserPwd",SqlDbType.VarChar,20,"UserPwd"),
                new SqlParameter("@Age",SqlDbType.TinyInt,4,"Age"),
                 new SqlParameter("@DeptId",SqlDbType.Int,4,"DeptId"),
            };

            insertCmd.Parameters.Clear();
            insertCmd.Parameters.AddRange(parameters);
            sqlDataAdapter2.InsertCommand = insertCmd;
            //sqlDataAdapter2.Update(ds.Tables[0]);
            
            //增删改查命令配置同理
            sqlDataAdapter2.Update(ds);
        }
    }
}

不要求随意读取，不修改，数据量小–Reader

数据库事务

一致性提交，中途失败则会回滚

--事务，一致性提交 回滚

create proc AddUserByTran
@UserName varchar(50),
@UserPwd varchar(50),
@Age int,
@DepName nvarchar(50)
as
begin
begin tran
begin try
	--插入部门信息
	insert into DepInfos(DepName)values(@DepName);
	declare @depId int 
	select @depId=@@IDENTITY
	--插入用户信息
	insert into UserInfos(UserName,UserPwd,Age,DeptId)
	values(@UserName,@UserPwd,@Age,@depId)
commit tran--提交
	return 1
end try

begin catch
	rollback tran--回滚
	return 0
end catch
end

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

namespace _13_Transaction
{
    internal class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection conn = new SqlConnection
          (ConfigurationManager.ConnectionStrings
          ["connStr"].ConnectionString))
            {
                conn.Open();
                SqlCommand cmd =new SqlCommand("AddUserByTran",conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                SqlParameter[] sp =
                {
                    new SqlParameter("@UserName","Lily"),
                    new SqlParameter("@UserPwd","12345"),
                    new SqlParameter("@Age",32),
                    new SqlParameter("@DepName","欧欧部"),
                    new SqlParameter ("@reValue",System.Data.SqlDbType.Int,4)
                };
                sp[4].Direction = System.Data.ParameterDirection.ReturnValue;

                cmd.Parameters.AddRange(sp);
            
                cmd.ExecuteNonQuery();

                Console.WriteLine(sp[4].Value.ToString());
                Console.ReadKey(true);
            }
        }
    }
}