#region 基础CRUD方法编写
static void ConnectDB()
{
//创建数据库连接对象
string connString = "Server=.;DataBase=CourseManageDB;Uid=sa;Pwd=a123456";
SqlConnection conn = new SqlConnection(connString);
//打开连接
conn.Open();
if (conn.State == ConnectionState.Open) Console.WriteLine("连接成功!");
//关闭连接
conn.Close();
if (conn.State == ConnectionState.Closed) Console.WriteLine("关闭成功!");
}
static void ExecuteInsert()
{
//【1】创建连接对象
string connString = "Server=.;DataBase=CourseManageDB;Uid=sa;Pwd=a123456";
SqlConnection conn = new SqlConnection(connString);
//定义sql语句
string sql = " insert into Course(CourseName, CourseContent, ClassHour, Credit, CategoryId, TeacherId)";
sql += "values ('.Net全栈开发', 'C#基础', 500, 12, 10, 1000)";
//【2】创建Command对象
//SqlCommand cmd = new SqlCommand();
//cmd.CommandText = sql;
//cmd.Connection = conn;
SqlCommand cmd = new SqlCommand(sql, conn);
//【3】打开数据库连接
conn.Open();
//【4】执行操作(下面这个方法,只能用于执行insert、update、delete操作,不能执行select)
int result = cmd.ExecuteNonQuery();
Console.WriteLine("受影响的行数=" + result);
//【5】关闭连接
conn.Close();
}
static void ExecuteUpdate()
{
//【1】创建连接对象
string connString = "Server=.;DataBase=CourseManageDB;Uid=sa;Pwd=a123456";
SqlConnection conn = new SqlConnection(connString);
//定义sql语句
string sql = "update Course set CourseName='.Net学习', CourseContent='.Net开发基础',";
sql += "ClassHour=20, CategoryId=10, TeacherId=1001";
sql += " where CourseId=1042";
//【2】创建Command对象
SqlCommand cmd = new SqlCommand(sql, conn);
//【3】打开数据库连接
conn.Open();
//【4】执行操作(下面这个方法,只能用于执行insert、update、delete操作,不能执行select)
int result = cmd.ExecuteNonQuery();
Console.WriteLine("受影响的行数=" + result);
//【5】关闭连接
conn.Close();
}
static void ExecuteDelete()
{
//【1】创建连接对象
string connString = "Server=.;DataBase=CourseManageDB;Uid=sa;Pwd=a123456";
SqlConnection conn = new SqlConnection(connString);
//定义sql语句
string sql = "delete from Course where CourseId=1042";
//【2】创建Command对象
SqlCommand cmd = new SqlCommand(sql, conn);
//【3】打开数据库连接
conn.Open();
//【4】执行操作(下面这个方法,只能用于执行insert、update、delete操作,不能执行select)
int result = cmd.ExecuteNonQuery();
Console.WriteLine("受影响的行数=" + result);
//【5】关闭连接
conn.Close();
}
//执行单一结果的查询
static void ExecuteSingleResult()
{
//【1】创建连接对象
string connString = "Server=.;DataBase=CourseManageDB;Uid=sa;Pwd=a123456";
SqlConnection conn = new SqlConnection(connString);
//定义sql语句
string sql = "select Count(*) as 课程总数 from Course";
//【2】创建Command对象
SqlCommand cmd = new SqlCommand(sql, conn);
//【3】打开数据库连接
conn.Open();
//【4】执行查询(下面这个方法,一般执行的都是查询,但是有时候也可以同时执行insert、update、delete操作)
object result = cmd.ExecuteScalar();
Console.WriteLine("查询结果=" + result);
//【5】关闭连接
conn.Close();
}
static void ExecuteReader()
{
//【1】创建连接对象
string connString = "Server=.;DataBase=CourseManageDB;Uid=sa;Pwd=a123456";
SqlConnection conn = new SqlConnection(connString);
//定义sql语句
string sql = "select CourseName, CourseContent, ClassHour from Course where CourseId<1020";
//【2】创建Command对象
SqlCommand cmd = new SqlCommand(sql, conn);
//【3】打开数据库连接
conn.Open();
//【4】执行查询
SqlDataReader reader = cmd.ExecuteReader();
//判断是否有查询结果,来决定读取数据
while (reader.Read())
{
Console.WriteLine($"{reader["CourseName"]}\t{reader[1]}\t{reader["ClassHour"]}");
}
reader.Close();//关闭读取器对象(千万不要忘记)
//【5】关闭连接
conn.Close();
}
#endregion
#region 通过通用SQLHelper类简化CRUD操作
static void ExecuteInsertByHelper()
{
//定义sql语句
string sql = " insert into Course(CourseName, CourseContent, ClassHour, Credit, CategoryId, TeacherId)";
sql += "values ('.Net/C#学习', 'C#学习', 200, 12, 10, 1000)";
int result = SQLHelper.Update(sql);
Console.WriteLine(result);
}
static void ExecuteSingleResultByHelper()
{
string sql = "select Count(*) as 课程总数 from Course";
object result = SQLHelper.GetSingleResult(sql);
Console.WriteLine(result);
}
static void ExecuteReaderByHelper()
{
string sql = "select CourseName, CourseContent, ClassHour from Course where CourseId<1020";
SqlDataReader reader = SQLHelper.GetReader(sql);
//判断是否有查询结果,来决定读取数据
while (reader.Read())
{
Console.WriteLine($"{reader["CourseName"]}\t{reader[1]}\t{reader["ClassHour"]}");
}
reader.Close();//关闭读取器对象(千万不要忘记)在这个地方执行关闭时,会首先自动把它使用的链接对象关闭!
}
#endregion
/// <summary>
/// 通用数据访问类
///
/// 封装变化的,抽取不变的。变化的作为参数,不变的作为方法体!
/// </summary>
public class SQLHelper
{
// private static string connString = "Server=.;DataBase=CourseManageDB;Uid=sa;Pwd=a123456";
private static string connString = ConfigurationManager.ConnectionStrings["connString"].ToString();
/ <summary>
/ 执行增删改操作的方法
/ </summary>
/ <param name="sql"></param>
/ <returns></returns>
//public static int Update(string sql)
//{
// //【1】创建连接对象
// SqlConnection conn = new SqlConnection(connString);
// //【2】创建Command对象
// SqlCommand cmd = new SqlCommand(sql, conn);
// //【3】打开数据库连接
// conn.Open();
// //【4】执行操作(下面这个方法,只能用于执行insert、update、delete操作,不能执行select)
// int result = cmd.ExecuteNonQuery();
// //【5】关闭连接
// conn.Close();
// return result;
//}
/// <summary>
/// 执行增删改操作的方法
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int Update(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//可以在这个地方捕获ex对象相关信息,然后保存到日志文件中...
throw new Exception("执行方法public static int Update(string sql)发生异常:" + ex.Message);
}
finally //不管前面是否发生异常都要执行的代码
{
conn.Close();
}
}
/// <summary>
/// 执行单一结果返回的查询方法
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static object GetSingleResult(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
//可以在这个地方捕获ex对象相关信息,然后保存到日志文件中...
throw new Exception("执行方法 public static object GetSingleResult(string sql)发生异常:" + ex.Message);
}
finally //不管前面是否发生异常都要执行的代码
{
conn.Close();
}
}
/// <summary>
/// 执行返回结果集的查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static SqlDataReader GetReader(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
//我们添加枚举CommandBehavior.CloseConnection之后,将来reader对象的链接会跟随reader对象的关闭自动关闭
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
//可以在这个地方捕获ex对象相关信息,然后保存到日志文件中...
throw new Exception("执行方法 public static object GetSingleResult(string sql)发生异常:" + ex.Message);
}
//finally //在这个方法里面,绝对不能直接把链接关掉,否则出错
//{
// conn.Close();
//}
}
}