3-25 练习

Profile Picture
@yiluo1991
- Published on Mar 25, 2020🌏 Public

ADO.NET

  • 进行数据库连接
  • 执行数据库SQL命令
  • 数据查询

1. 建立数据库连接

DbConnection类，是数据库连接的基类，不同的数据库，会继承该类型，创建他们自己的连接类。

比如:

  • SQLSERVER -> SQLConnection
  • MySQL -> MySqlConnection

1.1. MySQL数据库连接

Server 服务器地址
Port 数据库的连接端口
Database 要连接的数据库名
User Id 用户名
Password 密码
Server=127.0.0.1;Port=3306;Database=northwindcn;User Id=root;Password=1234;
//1. 创建MySQL数据库连接实例，并且设定数据库连接字符串
MySqlConnection conn = new MySqlConnection();
conn.ConnectionString = "Server=127.0.0.1;Port=3306;Database=northwindcn;User Id=root;Password=12345;";
//2. 打开数据库连接
conn.Open();
//...其他数据库操作

//3.关闭数据库连接
conn.Close();
Server=127.0.0.1;Port=3306;Database=northwindcn2;User Id=root;Password=1234;

数据库不存在错误提示:Unknown database 'northwindcn2'
Server=177.0.0.1;Port=3306;Database=northwindcn2;User Id=root;Password=1234;

服务器连接失败错误提示:Unable to connect to any of the specified MySQL hosts
Server=127.0.0.1;Port=3306;Database=northwindcn;User Id=root;Password=12345;

账号或密码错误的错误提示：Access denied for user 'root'@'localhost' (using password: YES)

SqlServer数据库连接

//1. 创建SQLServer数据库连接实例，并且设定数据库连接字符串
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Server=127.0.0.1;Database=Test;User Id=sa;Password=lh1234;";
//2. 打开数据库连接
conn.Open();
//3.关闭数据库连接
conn.Close();

想要更换到另一款数据库，改动比较少，连接的数据类型换成其他的数据库连接类型就可以了。

2. 执行SQL命令

2.1 对于增删改语句，下面的方式执行。

调用的是命令的ExecuteNonQuery方法去执行SQL语句并且返回受影响的行数。

  public JsonResult Index()
        {

            //1. 创建MySQL数据库连接实例，并且设定数据库连接字符串
            DbConnection conn = new MySqlConnection();
            conn.ConnectionString = "Server=127.0.0.1;Port=3306;Database=abc;User Id=root;Password=1234;";
            //2. 建立SQL命令并且设置要执行的SQL语句和连接通道
            //MySqlCommand cmd = new MySqlCommand("insert into log (UserId,LoginTime,Ip,Result) values (3,'2020-1-1','128.8.8.8',1)", conn);
            DbCommand cmd = new MySqlCommand();
            cmd.CommandText = "insert into log (UserId,LoginTime,Ip,Result) values (3,'2020-1-1','128.8.8.8',1)";
            cmd.Connection = conn;
            // 3. 打开连接
            conn.Open();
            // 4. 执行SQL命令
            int count= cmd.ExecuteNonQuery(); // 会返回受影响的行数
            // 5. 关闭连接
            conn.Close();
            return Json(count,JsonRequestBehavior.AllowGet);
        }

其他增删改语句只需要更换SQL命令即可。

2.2. ExecuteScalar返回查询结果第一行第一列值

//1. 创建MySQL数据库连接实例，并且设定数据库连接字符串
DbConnection conn = new MySqlConnection();
conn.ConnectionString = "Server=127.0.0.1;Port=3306;Database=abc;User Id=root;Password=1234;";
//2. 建立SQL命令并且设置要执行的SQL语句和连接通道
//MySqlCommand cmd = new MySqlCommand("insert into log (UserId,LoginTime,Ip,Result) values (3,'2020-1-1','128.8.8.8',1)", conn);
DbCommand cmd = new MySqlCommand();
cmd.CommandText = "SELECT sum(ip) FROM abc.log;";
cmd.Connection = conn;
// 3. 打开连接
conn.Open();
// 4. 执行SQL命令
int count= Convert.ToInt32(cmd.ExecuteScalar());  // 返回查询结果的第一行第一列数据
// 5. 关闭连接
conn.Close();
return Json(count,JsonRequestBehavior.AllowGet);

3. 查询表格

使用数据适配器去获取SQL语句执行之后查询到的表。

//1. 创建MySQL数据库连接实例，并且设定数据库连接字符串
DbConnection conn = new MySqlConnection();
conn.ConnectionString = "Server=127.0.0.1;Port=3306;Database=abc;User Id=root;Password=1234;";
// 2. 建立数据适配器，设定执行的查询语句，设定使用的数据库连接
DbDataAdapter adp = new MySqlDataAdapter("select * from log", (MySqlConnection)conn);
// 3.执行查询，将查询到的表填充到DataTable中
DataTable table = new DataTable();
//数据适配器执行命令时会自动检测连接是否开启，没有开启会自动打开，查询完毕后还原会原始状态。
adp.Fill(table);

要遍历查询到的表格，或者取具体某个单元格的值：

foreach (DataRow row in table.Rows)
{
    var id= row["Id"];
}
for (int i = 0; i < table.Rows.Count; i++)
{
    var id = table.Rows[i]["Id"];
}
return Json(table.Rows[2]["Id"], JsonRequestBehavior.AllowGet);

关于SQL注入攻击问题： https://www.cnblogs.com/zhangyuanbo12358/p/3959924.html

练习

  1. 能够根据Id，读取用户数据，显示到界面上
public JsonResult GetUserInfo(int id)
{
    MySqlConnection conn = new MySqlConnection("Server=127.0.0.1;Port=3306;Database=abc;User Id=root;Password=1234;");
    MySqlDataAdapter adp = new MySqlDataAdapter("select * from `user` where id= " + id,conn);
    DataTable table = new DataTable();
    adp.Fill(table);
    if (table.Rows.Count == 0)
    {
        return Json(new { 
            success=false,
            message="用户未找到"
        },JsonRequestBehavior.AllowGet);
    }
    else
    {
        return Json(new
        {
            success=true,
            mesage="请求成功",
            data=  TableToList(table)
        }, JsonRequestBehavior.AllowGet);
    }
}

/// <summary>
///  将Table转为List
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
private List<Dictionary<string,object>> TableToList(DataTable table)
{
    List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
    foreach (DataRow row in table.Rows)
    {
        Dictionary<string, object> dic = new Dictionary<string, object>();
        foreach (DataColumn column in table.Columns)
        {
            dic.Add(column.ColumnName, row[column.ColumnName]);
        }
        list.Add(dic);
    }
    return list;
}
    填写Name，点击添加，根据Name加一条新的用户数据，并且弹框显示是否添加成功

    填写Id，执行删除按钮功能，根据Id删除数据，返回受影响的函数

    填写Id和Name，根据Id去修改Name，返回受影响函数
public JsonResult AddUserInfo(string name)
{
    var count = ExecuteSQL("insert into `user` (`Name`) values ('" + name + "')");
    if (count == 1)
    {
        return Json(new
        {
            success = true,
            mesage = "请求成功"
        }, JsonRequestBehavior.AllowGet);
    }
    else
    {
        return Json(new
        {
            success = false,
            mesage = "插入失败"
        }, JsonRequestBehavior.AllowGet);
    }
}

public JsonResult RemoveUserInfo(int id) {
    var count = ExecuteSQL("delete from  `user` where id="+id);
    if (count == 1)
    {
        return Json(new
        {
            success = true,
            mesage = "请求成功"
        }, JsonRequestBehavior.AllowGet);
    }
    else
    {
        return Json(new
        {
            success = false,
            mesage = "没有找到用户"
        }, JsonRequestBehavior.AllowGet);
    }
}

public JsonResult EditUserInfo(int id ,string name)
{
    var count = ExecuteSQL("update   `user` set Name='"+name+"'  where id=" + id);
    if (count == 1)
    {
        return Json(new
        {
            success = true,
            mesage = "请求成功"
        }, JsonRequestBehavior.AllowGet);
    }
    else
    {
        return Json(new
        {
            success = false,
            mesage = "更新失败，没有找到用户"
        }, JsonRequestBehavior.AllowGet);
    }
}

/// <summary>
///     执行SQL增删改，返回受影响行数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
private int ExecuteSQL(string sql) {
    MySqlConnection conn = new MySqlConnection("Server=127.0.0.1;Port=3306;Database=abc;User Id=root;Password=1234;");
    conn.Open();
    var count = 0;
    try
    {
        MySqlCommand cmd = new MySqlCommand(sql, conn);
        count = cmd.ExecuteNonQuery();
    }
    catch (Exception)
    {
    }
    finally {
        conn.Close();
    }
    return count;
}
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <title></title>
    <script src="Scripts/jquery-3.4.1.min.js"></script>
</head>
<body>
    <p>
        <label>ID：<input id="inputId" value="" /></label>
    </p>
    <p>
        <label>Name：<input id="inputName" value="" /></label>
    </p>
    <button onclick="search1()"> 查询</button>
    <button onclick="add()"> 添加</button>
    <button onclick="del()"> 删除</button>
    <button onclick="edit()"> 修改</button>
    <script>
        function search1() {
            $.post('/user/getuserinfo', { id: inputId.value }, function (data) {
                if (data.success) {
                    inputName.value = data.data[0].Name
                } else {
                    alert(data.message)
                }
            })
        }
        function add() {
            $.post('/user/adduserinfo', { name: inputName.value }, function (data) {
                alert(data.message)
            })
        }
        function edit() {
            $.post('/user/edituserinfo', { name: inputName.value, id: inputId.value }, function (data) {
                alert(data.message)
            })
        }
        function del() {
            $.post('/user/removeuserinfo', {  id: inputId.value }, function (data) {
                alert(data.message)
            })
        }
    </script>
</body>
</html>

More coding videos

3月6日 面向对象编程-继承（1）

Profile Picture@yiluo1991 ·4 years ago

2月13日-vue-cli 路由前置守卫(2) 全局守卫

Profile Picture@yiluo1991 ·4 years ago

2月4日-组件基础（5）. 插槽

Profile Picture@yiluo1991 ·4 years ago

workbench 建立设计数据关系图

Profile Picture@yiluo1991 ·4 years ago

3月4日 方法

Profile Picture@yiluo1991 ·4 years ago

EntityFramework - 1.从数据库创建模型和数据上下文

Profile Picture@yiluo1991 ·4 years ago