高效访问数据库

• 第一种方法(占位符):

  • 一. 最简单的写法执行格式化的SQL语句(使用占位符)

  • // 1. 创建sql执行语句使用占位符进行拼接
    StringBuilder sql = new StringBuilder();
    sql.Append("select StudentId,StudentName,Age, from Students where Students.ClassId = {0}");
    string s1 = string.Format(sql.ToString(), 1);
    
    // 2. 连接数据库
    string connstr= @"Server=192.168.113.65,51187\SQLEXPRESS;DataBase=StudentManageDB;Uid=sa;Pwd=123456";
    SqlConnection conn = new SqlConnection(connstr);
    SqlCommand cmd = new SqlCommand(sql,conn);
    conn.Open();
    
    // 3. 执行sql语句
    // System.Data.CommandBehavior.CloseConnection 数据读取完之后关闭链接
    return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

• 第二种方法(设置变量):

  • 二. 执行带参数的SQL语句(类似存储过程参数, 效率和安全性较高) SqlParameter

  • 二. 执行带参数的SQL语句(类似存储过程参数, 效率和安全性较高)  SqlParameter 
    // 设置sql语句，参数
    string sql =("select StudentId,StudentName,Agefrom Students  where Students.ClassId = @i1");
    SqlParameter[] ps = new SqlParameter[]
    {
        new SqlParameter("@i1",1)
    };
    
    // 2 连接数据库
    SqlConnection conn = new SqlConnection(connstr);
    SqlCommand cmd = new SqlCommand(sql, conn);
    conn.Open();
    
    // 3 将参数添加到cmd指令中
    
    
    cmd.Parameters.AddRange(ps);
    
    // 4 执行sql语句
    cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

• 第三种方法(存储过程):

  • 三. 执行带参数的存储过程(效率最高, 安全性最高)

  • 三. 执行带参数的存储过程(效率最高, 安全性最高)
    // 1. 先在数据库中设置存储过程
    create procedure StudentData
    	@a int = 1
    as
    	select StudentId,StudentName,Age from Students  where Students.ClassId = @a
    go
    
    // 2. 在C#中添加存储过程和参数
    // 设置存储过程
    string s = "StudentData";
    // 设置参数
    SqlParameter csharp = new SqlParameter()
    {
        ParameterName = "@a",
        Direction = System.Data.ParameterDirection.Input,
        Value = classId,
        SqlDbType = System.Data.SqlDbType.Int,
    
    };
    
    // 3.连接数据库
     SqlConnection conn = new SqlConnection(connstr);
     // 创建指令对象
     SqlCommand cmd = new SqlCommand(sql, conn);
    conn.Open();
    
    // 4 设置cmd指令的执行类型和添加参数
    // 执行类型
    cmd.CommandType = CommandType.StoredProcedure;
    // 把参数添加到指令里
    cmd.Parameters.Add(csharp);
    
    // 5 执行sql语句
    cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

• 第四种方法(事务):

  • 四. 启用事务执行更新(保证数据的一致性)

  • 四. 启用事务执行更新(保证数据的一致性)
    // 1.连接数据库
    string connstring = @"Server = 李昊轩-212\MSSQLSERVER11;Database = Student;uid = sa;Pwd= 123456";
    SqlConnection conn = new SqlConnection(connstring);
    conn.Open();
    // 2.创建多个sql语句
    List<string> list = new List<string>() {
        "delete from XueSheng where StudentId = 1004",
        "delete from XueSheng where StudentId = 100"
    };
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    try
    {
        // 3.开启事务
        cmd.Transaction = conn.BeginTransaction();
        foreach (string s in list)
        {
            // 4.设置执行的sql
            cmd.CommandText = s;
            cmd.ExecuteNonQuery();
        }
        // 5. 如果没有出错 提交事务
        cmd.Transaction.Commit();
    }
    // 6. 如果执行错误 跳转到catch里面 在catch执行回滚
    catch (Exception e)
    {
        if (cmd.Transaction != null)
        {
            cmd.Transaction.Rollback(); // 执行sql语句有错误的情况 执行回滚操作
        }
        throw new Exception("执行删除sql事务出错:" + e.Message);
    }
    finally
    {
        // 7.不管是否执行有误 把事务设置为null 清除事务
        if(cmd.Transaction!= null)
        {
            cmd.Transaction = null;
        }
        conn.Close();
    }

• 第五种方法(数据池)

  • 五. 用连接池 ，最大程度优化执行的效率

  •  连接池介绍 :

  •     连接池是Data Provider提供的一个机制，使得应用程序使用的连接保存在连接池里，而避免每次都要完成建立/关闭物理连接的完整过程。

        当我们执行 `conn.Open()` 时实际上从连接池里面取了一个连接供你使用，而不是真的建立一个新的连接。

        当我们 `conn.Close()` 时它也不是真的关闭断开了连接，而是放回到了数据库的连接池里面。

        pooling = true; 开启连接池

        Max pool size=10; 

  • 设置连接池 

  • connectionString="Server=李昊轩-212\MSSQLSERVER11;DataBase=SMDB;Uid=sa;Pwd=123456;pooling=true;Max pool size=10;Min pool size=5;"