ADO.Net 基础

string connString = "Server=MATEBOOK16S\\SQLEXPRESS;DataBase=MISDB;Uid=sa;Pwd=123456";

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

SqlConnection conn = new SqlConnection(connString); //
//打开连接
conn.Open();
if (conn.State == ConnectionState.Open)
Console.WriteLine("连接已经打开！");
//3.管理连接
conn.Close();
if (conn.State == ConnectionState.Closed)
Console.WriteLine("连接已经关闭");
}

//新增操作
void Insert()
{
//1.创建连接对象
SqlConnection con = new SqlConnection(connString);
//2.定义Sql语句
string sql = "insert into Empyloyee(EmployeeName, Gender, NowAddress, IdNo, WeiXinNumber, PhoneNumber, OtherWork, EntryDate, PostId, DepartmentId)";
sql += " values('韩老师', '男', '天津', '120223199001091217', 'thinger007', '18500000007', '开发', '2014-01-01', 10, 10)";

//3.创建Command对象
//SqlCommand cmd = new SqlCommand();
//cmd.Connection = con;
//cmd.CommandText = sql;
SqlCommand cmd = new SqlCommand(sql,con);

//4.打开连接
con.Open();
//5.执行操作
int result = cmd.ExecuteNonQuery(); //此方法，可以执行intsert, update ,delete类型的Sql语句，不能执行select
Console.WriteLine("受影响的行数："+result);
//6.管理连接
con.Close();
}

//修改操作
void Update()
{
SqlConnection con = new SqlConnection( connString);
string sql = "update Employee set EmployeeName ='王老师',Gender='女' where EmployeeId=1010";
SqlCommand cmd = new SqlCommand(sql,con);
con.Open();
int result = cmd.ExecuteNonQuery();
con.Close();
Console.WriteLine("受影响的行数："+result);
}

//删除操作
void Delete()
{
SqlConnection con = new SqlConnection(connString);
string sql = "delete from Employee where EmployeeId =1010";
SqlCommand cmd = new SqlCommand(sql,con);
con.Open();
int result = cmd.ExecuteNonQuery();
con.Close();
Console.WriteLine("受影响的行数"+result);
}

//查询操作 单一结果的查询
void GetSingleResult1()
{
SqlConnection con = new SqlConnection(connString);
string sql = "select count(*) as 员工总数 from employee";
SqlCommand cmd = new SqlCommand(sql,con);
con.Open();
Object result = cmd.ExecuteScalar();
con.Close();
Console.WriteLine(result);
}

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
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();
}

//查询操作 返回一个结果集的查询（只读）
void GetResaderList1()
{
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(); //一定要在读取器关闭后，再关闭，否则出问题
}

//查询操作 返回多个结果集 返回关联的表数据
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>
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"]}");
}
}

转载：

https://zhuanlan.zhihu.com/p/619487466