1、Ado.Net组成
2、SqlConnection 介绍
//1、创建连接
SqlConnection connn = new SqlConnection();
//打开们---需要钥匙,连接字符串---就是钥匙
connn.ConnectionString = "server=.;database=TestBase;uid=root;pwd=123456";//连接字符串
// connn.Database; 要连接的数据库名称
//connn.DataSource //要连接的数据源 local . IP,端口号
//connn.State //连接的1状态
//connn.ConnectionTimeout // 默认15s
//2、打开连接
connn.Open();
//3、创建执行命令的对象
connn.CreateCommand();//创建一个与conn关联的sqlCommand对象
//4、执行命令
//5、关闭连接
connn.Close();//关闭连接
connn.Dispose();//释放连接
3、连接字符串
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data;
namespace AdoNetCourse
{
class Program
{
static void Main(string[] args)
{
//1、创建连接
SqlConnection connn = new SqlConnection();
//打开们---需要钥匙,连接字符串---就是钥匙
//第一种方式:连接字符串
//string connstr = "server=.;database=TestBase;uid=sa;pwd=123456";
//第二种方式:连接字符串
SqlConnectionStringBuilder sqlConnectionStringBuilder = new SqlConnectionStringBuilder();
sqlConnectionStringBuilder.DataSource = ".";
sqlConnectionStringBuilder.InitialCatalog = "TestBase";
sqlConnectionStringBuilder.UserID = "sa";
sqlConnectionStringBuilder.Password = "123456";
string conStr = sqlConnectionStringBuilder.ConnectionString;
connn.ConnectionString = conStr;//连接字符串
//2、打开连接
connn.Open();
//3、创建执行命令的对象
connn.CreateCommand();//创建一个与conn关联的sqlCommand对象
Console.WriteLine("123213123");
Console.ReadKey();
//4、执行命令
//5、关闭连接
connn.Close();//关闭连接
// connn.Dispose();//释放连接
}
}
}
4、构建字符串及其配置
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
</startup>
<!--推荐这种方法-->
<connectionStrings>
<add name="connStr" connectionString="server=.;database=TestBase;uid=sa;pwd=123456"
providerName="System.Data.SqlClient"/>
</connectionStrings>
<!--这种方法也可以,但是不推荐-->
<appSettings>
<add key="connStr" value="server=.;database=TestBase;uid=sa;pwd=123456"/>
</appSettings>
</configuration>
4.1读取连接字符串
//读取配置文件里面的连接字符串
//读取连接字符串
string conStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
connn.ConnectionString = conStr;//连接字符串
5、连接池介绍
5.1连接池的使用
6.SqlCommand的介绍
6-1.SqlCommand对象的方法ExecuteNonQuery
用于增删改查
6-2.SqlCommand对象的方法ExecutScalar方法
6-3.SqlCommand对象的方法ExecuteReader方法
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Data;
using System.Configuration;
namespace AdoNetCourse
{
class Program
{
static void Main(string[] args)
{
// TestExecuteNonQuery();
TestExecuteReader();
Console.ReadKey();
}
//ExecuteReader查询 返回一个对象:SqlDataReader
//SqlDataReader 实时读取 类似于sql中的游标。指针
private static void TestExecuteReader()
{
SqlConnection conn = null;
SqlDataReader sdr = null;
try
{
string conStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
//Console.WriteLine(conStr);
//1、创建SqlConnection对象
conn = new SqlConnection(conStr);
//获取sql语句
string sql = "select * from Student ";
SqlCommand cmd = new SqlCommand(sql, conn);
//2、打开连接,进行与数据库的交互,操作数据
conn.Open();
//sdr读取数据整个过程 SqlDataReader这个对象是一个数据流
//SqlDataReader 读取数据要及时保存,都一条丢一条
sdr = cmd.ExecuteReader();
//开始读取数据
while (sdr.Read()) //是否可以前进到一条记录
{
int id = int.Parse(sdr["id"].ToString());
string name = sdr["name"].ToString();
Console.WriteLine("id:" + id + ",name:" + name + "\n");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
sdr.Close();
conn.Close(); //关闭连接
}
Console.ReadKey();
}
public static void TestExecuteNonQuery()
{
SqlConnection conn = null;
try
{
string conStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
//Console.WriteLine(conStr);
//1、创建SqlConnection对象
conn = new SqlConnection(conStr);
//获取sql语句
string sql = "insert into Student values (6,'xixi') ";
SqlCommand cmd = new SqlCommand(sql, conn);
//2、打开连接,进行与数据库的交互,操作数据
conn.Open();
int count = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
conn.Close(); //关闭连接
}
Console.ReadKey();
}
}
}
6-3.SqlCommand对象的方法SqlParmeter方法
//参数的构造方法
//1、参数
SqlParameter pra1 = new SqlParameter();
pra1.ParameterName = "@name"; //参数名
pra1.SqlDbType = SqlDbType.VarChar;//数据库的数据类型
pra1.Value = "admin";
//2、带参数 参数名,参数值
SqlParameter pra2 = new SqlParameter("@id",1);
//3、带参数 参数名,参数值 参数类型
SqlParameter pra3 = new SqlParameter("@id",SqlDbType.Int);
pra3.Value = 2;
//4、带参数 参数名,参数类型,大小,源列名
//public SqlParameter(string parameterName, SqlDbType dbType, int size, string sourceColumn);
SqlParameter pra24= new SqlParameter("@id", SqlDbType.Int,20,"id");
6-4.sqlCommand 添加参数
static void Main(string[] args)
{
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
string sql = "select * from Student where 1 = 1 and id =@id and name=@name";
//string sql = "select * from Student where 1 = 1 and id =@id";
SqlCommand cmd = new SqlCommand(sql, conn);
// cmd.Parameters.AddWithValue("@id",100);
SqlParameter[] values = { new SqlParameter("@id",10),new SqlParameter("@name","陈陈陈10")};
cmd.Parameters.AddRange(values);
conn.Open();
object o = cmd.ExecuteScalar();
Console.WriteLine(o.ToString());
// SqlDataReader dr = cmd.ExecuteReader();
//dr.Read();
//while (dr.Read())
//{
// int id = int.Parse(dr["id"].ToString());
// string name = dr["name"].ToString();
// Console.WriteLine("id=" + id + "name="+ name);
// Console.WriteLine("111111");
//}
conn.Close();
//Console.WriteLine(o.ToString());
Console.ReadKey();
}
6-5.SqlParameter表示参数的输入输出
static void Main(string[] args)
{
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlCommand cmd = new SqlCommand("GetStudentName", conn);
cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("@id",10);
//输入参数
SqlParameter paraId = new SqlParameter("@id", 100);
cmd.Parameters.Add(paraId);
//输出参数
SqlParameter paraName = new SqlParameter("@name", SqlDbType.NChar,10);
paraName.Direction = ParameterDirection.Output;
cmd.Parameters.Add(paraName); //添加单个参数
conn.Open();
object o = cmd.ExecuteScalar();
conn.Close();
Console.WriteLine(paraName.Value.ToString());
Console.ReadKey();
}
6-6.SqlDataReader读取数据
static void Main(string[] args)
{
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connStr))
{
string sql = "select * from Student";
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//DataTable dt = new DataTable();
// dt.Load(dr);
List<Student> list = new List<Student>();
if (dr.HasRows)
{
int indexId = dr.GetOrdinal("id");
int indexName = dr.GetOrdinal("name");
string idName = dr.GetName(0);
while (dr.Read())//检测是否有数据
{
//int id = (int)dr[0];//列序号读取
//string name = dr["name"].ToString();//列名读取
int id = dr.GetInt32(indexId);
string name = dr.GetString(indexName);
Student student = new Student(id,name);
list.Add(student);
// Console.WriteLine("id:" + id + ",name:" + name + "\n");
}
}
Console.WriteLine(list.Count);
dr.Close();
}
Console.ReadKey();
6-7.DataTable详解
6-7-1.DataTable使用
//1、创建表
DataTable dt = new DataTable("Student");
//2、表示空的,没有架构 列 约束 主键
DataColumn dc = new DataColumn("id",typeof(int));
dt.Columns.Add(dc); //添加一列
dt.Columns.Add("name", typeof(string)); //推荐用这种方式 添加一列
dt.PrimaryKey =new DataColumn[]{dt.Columns[0] }; //设置主键
dt.Constraints.Add(new UniqueConstraint(dt.Columns[1]));//添加唯一约束
//架构定义好了,添加数据
DataRow dr = dt.NewRow();
dr["id"] = 1;
dr["name"] = "admin";
//这行数据数据并没有添加到表里面
dt.Rows.Add(dr); //添加到dt里
DataRow dr1 = dt.NewRow();
dr1["id"] = 2;
dr1["name"] = "admin2";
dt.Rows.Add(dr1);
// dt.AcceptChanges();
DataRow[] rows = dt.Select(); //获取所有的行
DataRow[] rows1 = dt.Select("id >= 2 ","id desc"); //按照条件查询
6-8.DataSet详解
//1、创建DataSet 就是数据库
DataSet ds = new DataSet("ds1");
//2、常用属性
DataTable dt1 = new DataTable("Student");
//Tables Datable集合
ds.Tables.Add(dt1);
DataTable dt = ds.Tables[0];
Console.WriteLine(dt);
Console.ReadKey();
6-9.约束和关系
class Program
{
static void Main(string[] args)
{
//string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
//using (SqlConnection conn = new SqlConnection(connStr))
//{
//}
DataSet ds = new DataSet("ds");
DataTable dt1 = new DataTable("User");
DataTable dt2 = new DataTable("Dept");
ds.Tables.Add(dt1);
ds.Tables.Add(dt2);
dt1.Columns.Add("UserId",typeof(int));
dt1.Columns.Add("UserName", typeof(string));
dt1.Columns.Add("Age",typeof(int));
dt1.Columns.Add("DeptId",typeof(int));
dt2.Columns.Add("DeptId", typeof(int));
dt2.Columns.Add("DeptName", typeof(string));
//dt1.PrimaryKey = new DataColumn[] { dt1.Columns[0] }; //设置UserId为主键 --主键约束
//dt2.Constraints.Add(new UniqueConstraint("uc",dt2.Columns[1])); //添加一个唯一性约束
//dt1.Constraints.Add("fk",dt2.Columns[0],dt1.Columns[3]); //外键约束
//默认情况下,建立关系,就自动为父表中列建立唯一约束,子表中外键建立一个外键约束
DataRelation relation = new DataRelation("relation",dt2.Columns[0],dt1.Columns[3]);
ds.Relations.Add(relation); //添加关系到DataRelation中
InitData(dt1,dt2); //准备一些数据
///使用关系
//foreach (DataRow dr in dt2.Rows)
//{
// DataRow[] rows = dr.GetChildRows(relation);
// foreach (DataRow r in rows)
// {
// Console.WriteLine($"UserId:{r[0].ToString()},UserName:{r[1]},Age:{r[2]},DeptId:{r[3]}");
// }
//}
//通过父表读取子表中的数据
DataRow[] rows = dt2.Rows[0].GetChildRows(relation);
foreach (DataRow r in rows)
{
Console.WriteLine($"UserId:{r[0].ToString()},UserName:{r[1]},Age:{r[2]},DeptId:{r[3]}");
}
Console.ReadKey();
}
//初始化数据
private static void InitData(DataTable dt1,DataTable dt2)
{
//dt2添加数据
DataRow dr2 = dt2.NewRow();
dr2["DeptId"] = 1;
dr2["DeptName"] = "人事部";
dt2.Rows.Add(dr2);
dr2 = dt2.NewRow();
dr2["DeptId"] = 2;
dr2["DeptName"] = "管理部";
dt2.Rows.Add(dr2);
dr2 = dt2.NewRow();
dr2["DeptId"] = 3;
dr2["DeptName"] = "销售部";
dt2.Rows.Add(dr2);
//dt1添加数据
DataRow dr1 = dt1.NewRow();
dr1["UserId"] = 1;
dr1["UserName"] = "黎明";
dr1["Age"] = 20;
dr1["DeptId"] = 1;
dt1.Rows.Add(dr1);
dr1 = dt1.NewRow();
dr1["UserId"] = 2;
dr1["UserName"] = "德华";
dr1["Age"] = 22;
dr1["DeptId"] = 2;
dt1.Rows.Add(dr1);
dr1 = dt1.NewRow();
dr1["UserId"] = 3;
dr1["UserName"] = "陈陈";
dr1["Age"] = 23;
dr1["DeptId"] = 3;
dt1.Rows.Add(dr1);
dr1 = dt1.NewRow();
dr1["UserId"] = 4;
dr1["UserName"] = "王丽";
dr1["Age"] = 33;
dr1["DeptId"] = 1;
dt1.Rows.Add(dr1);
}
6-10.SqlDataAdapter 介绍与创建
6-10-1.SqlDataAdapter填充数据
string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
string sql = "select * from Student";
using (SqlConnection conn = new SqlConnection(connStr))
{
//1、第一种:设置SelectCommand
SqlDataAdapter adapter = new SqlDataAdapter();
// adapter.SelectCommand = new SqlCommand(sql,conn);
//2、第二种:通过一个SqlCommand 对象来实例化一个adapter
// SqlCommand cmd = new SqlCommand(sql,conn);
// SqlDataAdapter adapter1 = new SqlDataAdapter(cmd);
//3、查询语句和连接对象来实例化一个adapter
// SqlDataAdapter adapter2 = new SqlDataAdapter(sql,conn);
//4、查询语句和连接字符串,也可以构建一个adapter
// SqlDataAdapter adapter3 = new SqlDataAdapter(sql,connStr);
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
// da.TableMappings.Add("Table11", "Student");
int i = da.Fill(ds,"Student");
Console.WriteLine(i);
Console.ReadKey();
}
7.SqlDataAdapter 和SqlDataReader
8、Ado.Net如何调用事务
9、封装DBHelper
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace AdoNetDBHelper
{
public class DBHelper
{
//获取配置文件里面的连接字符串
private static readonly string ConnStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
//建立连接
SqlConnection conn = null;
//增对INSERT UPDATE DELETE 所封装的方法
private static int ExecuteNonQuery(string sql,int cmdType,params SqlParameter[] paras)
{
int count = 0;
using(SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
if(cmdType == 2)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (paras != null && paras.Length > 0)
{
cmd.Parameters.AddRange(paras);
}
conn.Open();
count = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
conn.Close();
}
return count;
}
//增对查询返回一个结果的值(第一行第一列) 所封装的方法
private static object ExecuteScalar(string sql, int cmdType, params SqlParameter[] paras)
{
object o = null;
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand(sql, conn);
if (cmdType == 2)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (paras != null && paras.Length > 0)
{
cmd.Parameters.AddRange(paras);
}
conn.Open();
o = cmd.ExecuteScalar();
cmd.Parameters.Clear();
conn.Close();
}
return o;
}
// 执行查询返回全部结果SqlDataReader
private static SqlDataReader ExecuteReader(string sql, int cmdType, params SqlParameter[] paras)
{
SqlDataReader dr;
SqlConnection conn = new SqlConnection(ConnStr);
SqlCommand cmd = new SqlCommand(sql, conn);
if (cmdType == 2)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (paras != null && paras.Length > 0)
{
cmd.Parameters.AddRange(paras);
}
try
{
conn.Open();
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
}
catch (Exception ex)
{
conn.Close();
throw new Exception("执行查询异常",ex);
}
return dr;
}
//填充DataSET 针对多个结果集 或者一个结果集
public static DataSet GetDataSet(string sql, int cmdType, params SqlParameter[] paras)
{
DataSet ds = null;
using(SqlConnection conn = new SqlConnection(ConnStr))
{
//SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommand cmd = new SqlCommand(sql,conn);
if (cmdType == 2)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (paras != null && paras.Length > 0)
{
cmd.Parameters.AddRange(paras);
SqlDataAdapter da = new SqlDataAdapter(cmd);
conn.Open();
da.Fill(ds);
conn.Close();
}
}
return ds;
}
///填充DataTable 针对一个结果集
public static DataTable GetDataTable(string sql, int cmdType, params SqlParameter[] paras)
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
//SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlCommand cmd = new SqlCommand(sql, conn);
if (cmdType == 2)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (paras != null && paras.Length > 0)
{
cmd.Parameters.AddRange(paras);
SqlDataAdapter da = new SqlDataAdapter(cmd);
conn.Open();
da.Fill(dt);
conn.Close();
}
}
return dt;
}
//事务操作 一系列的SQL语句 针对的是insert update delete
public static bool ExecuteTrans(List<string> listSQL)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
SqlCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
try
{
for (int i = 0; i < listSQL.Count; i++)
{
cmd.CommandText = listSQL[i];
cmd.ExecuteNonQuery();
}
trans.Commit();
return true;
}
catch (Exception ex)
{
trans.Rollback();
throw new Exception("执行事务出现异常",ex);
}
}
return false;
}
//执行事务 每个操作封装到CmdInfo
public static bool ExecuteTrans(List<cmdInfo> listCmd)
{
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
SqlCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
try
{
for (int i = 0; i < listCmd.Count; i++)
{
cmd.CommandText = listCmd[i].CommandText;
if (listCmd[i].CmdType == 2)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (listCmd[i].Parameters != null && listCmd[i].Parameters.Length>0)
{
cmd.Parameters.AddRange(listCmd[i].Parameters);
}
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();//很重要这一句
}
trans.Commit();
return true;
}
catch (Exception ex)
{
trans.Rollback();
throw new Exception("执行事务出现异常", ex);
}
finally
{
trans.Dispose();
cmd.Dispose();
conn.Close();
}
}
return false;
}
}
}
cmdInfo类的消息
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace AdoNetDBHelper
{
public class cmdInfo
{
public string CommandText;//sql语句或者存储过程名
public SqlParameter[] Parameters; // 参数列表
public int CmdType;//是存储过程还是T-SQL语句
public cmdInfo()
{
}
public cmdInfo(string CommandText, int CmdType)
{
this.CommandText = CommandText;
this.CmdType = CmdType;
}
public cmdInfo(string CommandText, int CmdType, SqlParameter[] Parameters)
{
this.CommandText = CommandText;
this.CmdType = CmdType;
this.Parameters = Parameters;
}
}
}