数据访问
对应命名空间：System.Data.SqlClient;
SqlConnection:连接对象
SqlCommand:命令对象
SqlDataReader:读取器对象
CommandText：命令文本
增删改、查分以下几步：
1、造连接字符串
stringconnstring= "server=.;database=mydb;user=sa;pwd=123";
connstring:造的字符串名
server指服务器：一般是IP地址，本机可以使用点;
database指数据库名称：要访问的数据库名称
user数据库的用户名：一般是sa
pwd数据库的密码：自己设置的
2、造连接对象
SqlConnectionconn = new SqlConnection(connstring);
conn：造的连接对象名
3、创建命令对象
SqlCommandcmd = conn.CreateCommand();
cmd：造的命令对象名
4、写要执行的SQL语句
4-1：查询
cmd.CommandText = "select * from Info";
//4-2：添加
cmd.CommandText = "Insert into Info values(‘p032‘,‘毒哥‘,‘True‘,‘n001‘,‘1987-02-02‘)";
//4-3：删除
cmd.CommandText = "delete from Info where Code=‘p032‘;
//4-4：更改
cmd.CommandText = "update Info set name=‘情方方‘ where Code=‘p032‘;
5、打开连接
conn.Open(); //可放在执行之前的任意位置
6、执行操作
5-1：（读取操作，返回读取器对象）
SqlDataReader dr = cmd.ExecuteReader();
//5-2.执行操作（增删改操作，返回行数）
cmd.ExecuteNonQuery();
7、处理数据
6-1:查询一条数据
if (dr.HasRows) //HasRows 判断是否有行数据 bool型，返回true/false
{
dr.Read(); //dr.Read() 是数据库数据访问指针，每执行一次都会向下走一行，如果有内容则返回true，同时dr访问为当前行数据集合，可以使用索引或是列名来访问相对应的数据
Console.WriteLine(dr[0]);
Console.ReadLine();
}
else
{
Console.WriteLine("读取失败！");
}
//6-2.查询多条数据
if (dr.HasRows)
{
while(dr.Read()) //使用while循环读取所有数据 一行数据是一个数组，一行数据里有多少列就有多少个索引
{
Console.WriteLine(dr[0]+"----"+dr[1]);
}
Console.ReadLine();
}
else
{
Console.WriteLine("没有读到数据");
Console.ReadLine();
}
8、关闭连接
conn.Close();
例1：根据用户输入一个条件查询数据
static void Main1(string[] args)
???????{
???????????//用户输入内容
???????????Console.WriteLine("请输入要查询的名称：");
???????????string str = Console.ReadLine();
???????????//造连接字符串
???????????string connstring = "server=.;database=mydb;user=sa;pwd=123";
???????????//造连接对象
???????????SqlConnection conn = new SqlConnection(connstring);
???????????//造命令对象
???????????SqlCommand cmd = conn.CreateCommand();
???????????//准备一条SQL语句
???????????cmd.CommandText = "select * from Info where Name like ‘%"+str+"%‘";
???????????//打开连接
???????????conn.Open();
???????????//执行SQL语句
???????????SqlDataReader dr = cmd.ExecuteReader();
???????????//读取数据
???????????if (dr.HasRows)
???????????{
???????????????while (dr.Read())
???????????????{
???????????????????int n = 0;
???????????????????while ( n <dr.FieldCount ) //.FieldCount获取当前行的列数
???????????????????{
???????????????????????Console.Write(dr[n]+"\t");
???????????????????????n++;
???????????????????}
???????????????????Console.WriteLine();
???????????????}
???????????}
???????????else
???????????{
???????????????Console.WriteLine("没有查到满足条件的数据");
???????????}
???????????//关闭连接
???????????conn.Close();
???????????Console.ReadLine();
???????}
让用户输入要删除的数据主键值（此方法不安全）
static void Main4(string[] args) ???????{ ???????????//用户输入要删除的数据主键值 ???????????Console.WriteLine("请输入要删除的代号："); ???????????string code = Console.ReadLine(); ???????????//判断该数据存不存在 ???????????SqlConnection conn = new SqlConnection("server=.;database=mydb;user=sa;pwd=123"); ???????????SqlCommand cmd = conn.CreateCommand(); ???????????cmd.CommandText = "select * from Info where Code=‘"+code+"‘"; ???????????conn.Open(); ???????????SqlDataReader dr = cmd.ExecuteReader(); ???????????????????????if (dr.HasRows) ???????????{ ???????????????//说明该数据存在 ???????????????Console.WriteLine("查到该数据，是否要执行删除操作，如果要删除请输入：1"); ???????????????int sc = Convert.ToInt32(Console.ReadLine()); ???????????????if (sc == 1) ???????????????{ ???????????????????//删除 ???????????????????dr.Close(); //关闭读取器 ???????????????????cmd.CommandText = "delete from Info where Code=‘"+code+"‘"; ???????????????????cmd.ExecuteNonQuery(); ???????????????????Console.WriteLine("删除成功！"); ???????????????????????????????????} ???????????????else ???????????????{ ???????????????????//不删除 ???????????????????dr.Read(); ???????????????????string sex = Convert.ToBoolean(dr[2])?"男":"女"; ???????????????????string nation = MinZu(dr[3].ToString()); ???????????????????string str = "代号："+dr[0]+"\t姓名："+dr[1]+"\t性别："+sex+"\t民族："+nation+"\t生日："+dr[4]; ???????????????????Console.WriteLine(str); ???????????????} ???????????} ???????????else ???????????{ ???????????????//数据不存在 ???????????????Console.WriteLine("输入的代号错误！"); ???????????} ???????????conn.Close(); ???????????Console.ReadLine(); ???????}让用户输入要添加的内容 ???????static string MinZu(string code) ???????{ ???????????string name=""; ???????????SqlConnection conn = new SqlConnection("server=.;database=mydb;user=sa;pwd=123"); ???????????SqlCommand cmd = conn.CreateCommand(); ???????????cmd.CommandText = "select Name from Nation where Code = ‘" + code + "‘"; ???????????conn.Open(); ???????????SqlDataReader dr = cmd.ExecuteReader(); ???????????if (dr.HasRows) ???????????{ ???????????????dr.Read(); ???????????????name = dr[0].ToString(); ???????????} ???????????conn.Close(); ???????????return name; ???????}
static void Main3(string[] args) ???????{ ???????????//让用户输入要添加的内容 ???????????Console.WriteLine("请输入要添加的代号："); ???????????string code = Console.ReadLine(); ???????????Console.WriteLine("请输入姓名："); ???????????string name = Console.ReadLine(); ???????????Console.WriteLine("请输入性别："); ???????????bool sex = Console.ReadLine()=="男"?true:false; ???????????Console.WriteLine("请输入民族："); ???????????string nation = Console.ReadLine(); ???????????Console.WriteLine("请输入生日："); ???????????string birthday = Console.ReadLine(); ???????????string nationcode = "n001"; ???????????//将民族名称转为名族代号 ???????????SqlConnection conn = new SqlConnection("server=.;database=mydb;user=sa;pwd=123"); ???????????SqlCommand cmd = conn.CreateCommand(); ???????????cmd.CommandText = "select Code from Nation where Name = ‘"+nation+"‘"; ???????????conn.Open(); ???????????SqlDataReader dr = cmd.ExecuteReader(); ???????????if (dr.HasRows) ???????????{ ???????????????dr.Read(); ???????????????nationcode = dr[0].ToString(); ???????????} ???????????conn.Close(); ???????????//往Info表添加数据 ???????????cmd.CommandText = "insert into Info values(‘"+code+"‘,‘"+name+"‘,‘"+sex+"‘,‘"+nationcode+"‘,‘"+birthday+"‘)"; ???????????conn.Open(); ???????????cmd.ExecuteNonQuery(); ???????????conn.Close(); ???????????Console.WriteLine("添加成功！"); ???????????Console.ReadLine(); ???????}ADO.Net（一）——增、删、改、查
原文地址：https://www.cnblogs.com/asdyzh/p/9756362.html