数据库及ADO.NET学习（四）

sql注入

对于登录项目，任意输入用户名后加**’ or 1=1 --**，然后输入任意密码也可以登录成功。
sql语句就变成了：

select count(*) from UserLogin where userName='张三' or 1=1 --' and userPwd='123'

这种方式叫sql注入攻击，注入自己的代码，可以写任何sql代码
解决办法：不使用sql拼接，使用带参数的sql语句或者是存储过程。

解决办法

代码：

string constr = "Data Source=LAPTOP-CELEUP2E;Initial Catalog=TestSchool;User ID=sa;Pwd=123456";
            using (SqlConnection conn = new SqlConnection(constr))
            {

                string sql ="select count(*) from UserLogin where userName=@userName and userPwd=@userPwd";
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    //1、sql语句中需要有参数 2、Command对象需要为参数指定值
                    //创建参数对象，指定和哪个参数相关联
                    SqlParameter paramUserName = new SqlParameter("@userName",SqlDbType.VarChar,50);
                    paramUserName.Value=textBox1.Text.Trim();
                    /*也可以使用对象初始化器
                    SqlParameter paramUserName = new SqlParameter
                        ("@userName", SqlDbType.VarChar, 50)
                    {Value=textBox1.Text.Trim() };*/
                    SqlParameter paramUserPwd = new SqlParameter("@userPwd", SqlDbType.VarChar, 50);
                    paramUserPwd.Value = textBox2.Text;
                    //将这两个对象加到command中
                    cmd.Parameters.Add(paramUserName);
                    cmd.Parameters.Add(paramUserPwd);
                    conn.Open();
                    int a = Convert.ToInt32(cmd.ExecuteScalar());
                    conn.Close();
                    if (a == 0) MessageBox.Show("登录失败");
                    else MessageBox.Show("登录成功");
                }
            }

这样就不会出现上述情况了。
一次创建多个对象并添加：
（推荐这种写法）
也可以不写类型，直接把值写到第二个参数

//一次创建多个对象
                    SqlParameter[] parameters = new SqlParameter[]{
                        new SqlParameter("@userName",SqlDbType.VarChar,50){Value=textBox1.Text.Trim() },
                        new SqlParameter("@userPwd",SqlDbType.VarChar,50){Value=textBox2.Text },
                    };
                    cmd.Parameters.AddRange(parameters);

另外一种方式：

cmd.Parameters.AddWithValue("@userName", textBox1.Text.Trim());
cmd.Parameters.AddWithValue("@userPwd", textBox2.Text);

但是不推荐，因为AddWithValue内部还是new了对象，而且如果值比较特殊可能会出现问题
实质上，带参数的sql语句调用的就是存储过程
但是，使用带参数的sql语句有一个问题，如果new SqlParameter(“@a”,0)进行赋值时，会出错，将0前加一个object，写0的话，会调用Parameter的另一个重载，需要写(object)0

数据库信息文本文件导入导出

表信息导出到文本文件

string constr = "Data Source=LAPTOP-CELEUP2E;Initial Catalog=TestSchool;User ID=sa;Password=123456";
using(SqlConnection con=new SqlConnection(constr))
{
  string sql = "select * from UserLogin";
  using(SqlCommand cmd = new SqlCommand(sql, con))
  {
      con.Open();
      using (SqlDataReader reader = cmd.ExecuteReader())
      {
          if (reader.HasRows)
          {//查询出数据，创建写入文件流写入到文本文件
              using(StreamWriter sw=new StreamWriter(@"a.txt"))
              {
                  while (reader.Read())
                  {
                      object objAutoId = reader.GetInt32(0);
                      object objUserName = reader.GetString(1);
                      object objPassword = reader.GetString(2);
                      //也可以定义为对应的类型，但是需要处理空值的情况，使用IsDBNull和正则表达式
                      //string p=reader.IsDBNull(2)?"NULL" : reader.GetString(2);
                      string l = string.Format("{0}\t{1}\t{2}", objAutoId, objUserName, objPassword);
                      sw.WriteLine(l);
                  }
                  MessageBox.Show("导出数据完毕！");
              }
          }
          else
          {
              MessageBox.Show("表中没有数据");
          }
      }
  }
}

执行结果：

将文本文件数据导入数据库

代码：
如下两种方法解决cmd的参数重复问题。

//先读取文本文件，然后插入到表中
using (StreamReader sr=new StreamReader(@"a.txt"))
{
  string constr = "Data Source=LAPTOP-CELEUP2E;Initial Catalog=TestSchool;User ID=sa;Password=123456";
  using (SqlConnection con = new SqlConnection(constr))
  {
      string sql = "insert into UserLogin values(@userName,@userPwd)";
      using (SqlCommand cmd = new SqlCommand(sql, con))
      {
          SqlParameter[] pms = new SqlParameter[]
          {
                  new SqlParameter("@userName",SqlDbType.VarChar,50),
                  new SqlParameter("@userPwd",SqlDbType.VarChar,50),
          };
          cmd.Parameters.AddRange(pms);
          con.Open();
          while (!sr.EndOfStream)
          {
              string line = sr.ReadLine();
              string[] columns = line.Split('\t');
              string userName = columns[1];
              string password = columns[2];
              /*SqlParameter[] pms = new SqlParameter[]
              {
                      new SqlParameter("@userName",userName),
                      new SqlParameter("@userPwd",password),
              };*/
              pms[0].Value = userName;
              pms[1].Value = password;
              //cmd.Parameters.AddRange(pms);
              cmd.ExecuteNonQuery();
              //清空cmd
              //cmd.Parameters.Clear();

          }
      }
  }                
}
MessageBox.Show("插入完毕！");

省市联动小程序

实现

1、配置文件App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
    </startup>
	<connectionStrings>
		<add name="sql" connectionString="Data Source=LAPTOP-CELEUP2E;Initial Catalog=callcenter;User ID=sa;Password=123456"/>
	</connectionStrings>
</configuration>

2、加载省名到下拉列表

//获取配置文件中的连接字符串
//以下两种是等价的，通过字符串或索引获取
 string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
// string constr = ConfigurationManager.ConnectionStrings[0].ConnectionString;
//这个索引0是错的，所以还是直接通过字符串获取吧
 using(SqlConnection conn=new SqlConnection(constr))
 {
     string sql = "select * from area where pid=0";
     using(SqlCommand cmd=new SqlCommand(sql, conn))
     {
         conn.Open();
         using (SqlDataReader reader = cmd.ExecuteReader())
         {
             if (reader.HasRows)
             {
                 while (reader.Read())
                 {
                     int aid=reader.GetInt32(0);
                     string aname= reader.GetString(1);
                     int pid=reader.GetInt32(2);
                     Area a = new Area() { id = aid,name=aname };
                     //不能把实体对象声明在循环外面，要每次创建一个对象
                     comboBox1.Items.Add(a);
                 }
             }
         }           
     }
 }
 Area a1 = new Area() { id = -1, name = "请选择" };
 comboBox1.Items.Insert(0, a1);
 comboBox1.SelectedIndex = 0;

3、Area类
编写实体类一定要用属性而不是字段，因为大多数控件数据绑定的时候只认属性不认字段。

public class Area
{
   public int id { get; set; }
   public string name { get; set; }
   public override string ToString()
   {
       return name;
   }
}

4、数据表
id name pid
2 北京 0
3 天津 0
4 山西 0
5 河北 0
6 保定 5
7 沧州 5
8 衡水 5
9 承德 5
11 大同 4
12 晋城 4
13 晋中 4
5、界面

6、显示省份函数

//获取选中项的id name
Area a = (Area)comboBox1.SelectedItem;
MessageBox.Show(a.id + " " + a.name);

7、加载城市
comboBox1_SelectedIndexChanged函数

if (comboBox1.SelectedIndex == -1) { }
else
{
comboBox2.Items.Clear();//注意要清空哦~~~~~~~
string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
using (SqlConnection conn = new SqlConnection(constr))
{
string sql = "select * from area where pid=@uid";
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
 Area a = (Area)comboBox1.SelectedItem;
 SqlParameter p1 = new SqlParameter("@uid", a.id);
 cmd.Parameters.Add(p1);
 conn.Open();
 using (SqlDataReader reader = cmd.ExecuteReader())
 {
     if (reader.HasRows)
     {
         while (reader.Read())
         {
             int aid = reader.GetInt32(0);
             string aname = reader.GetString(1);
             Area a1 = new Area() { id = aid, name = aname };
             //不能把实体对象声明在循环外面，要每次创建一个对象
             comboBox2.Items.Add(a1);
         }
     }
 }
}
}
if (comboBox2.Items.Count > 0) comboBox2.SelectedIndex = 0;
}

数据源绑定实现

List<Area> list = new List<Area>();
//定义一个集合
string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
using(SqlConnection conn=new SqlConnection(constr))
{
    string sql = "select * from area where pid=0";
    using(SqlCommand cmd=new SqlCommand(sql, conn))
    {
        conn.Open();
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int aid = reader.GetInt32(0);
                    string aname = reader.GetString(1);
                    Area a = new Area() { id = aid, name = aname };
                    //不能把实体对象声明在循环外面，要每次创建一个对象
                    list.Add(a);
                }
            }
        }           
    }
}
comboBox1.DisplayMember = "name";
comboBox1.ValueMember = "id";
comboBox1.DataSource = list;
dataGridView1.DataSource = list;

dataGridView1，如果Area定义的字段，就显示不出来。

资料管理器小程序

1、数据库
创建代码：

create table Category
(
	tId int identity(1,1) primary key,
	tName nvarchar(100) not null,
	tParentId int not null,				--父ID
	tNote nvarchar(1000)				--备注
)
create table ContentInfo
(
	dId int identity(1,1) primary key,
	dTId int not null foreign key references Category(tId),	--外键 类型ID
	dName nvarchar(100) not null,			--资料名称
	dContent varchar(max) ,
	dInTime datetime constraint DF_data_dIntime default(getdate()),		--添加时间
	dEditTime datetime constraint DF_data_dEditTime default(getdate()),	--修改时间
	dIsDeleted bit constraint DF_data_dIsDeleted default(0)			--是否被删，是未删，是删除
)
insert into Category (tName, tParentId, tNote) values('软件开发',-1,'关于代码的那些鸟事儿')
insert into Category (tName, tParentId, tNote) values('休闲娱乐',-1,'累了吧，来休息会')
insert into Category (tName, tParentId, tNote) values('C#',1,'C Sharp')
insert into Category (tName, tParentId, tNote) values('Java',1,'java 学习')
insert into Category (tName, tParentId, tNote) values('Php',1,'php 学习')
insert into Category (tName, tParentId, tNote) values('笑话',2,'笑话')
insert into Category (tName, tParentId, tNote) values('糗百',2,'糗事百科')
insert into Category (tName, tParentId, tNote) values('cnbeta',2,'it资讯')
insert into ContentInfo (dTId, dName, dContent) values(3,'C#操作文件','IO操作....')
insert into ContentInfo (dTId, dName, dContent) values(3,'C#操数据库','数据库操作....')
insert into ContentInfo (dTId, dName, dContent) values(3,'C#加密解密','加密解密....')
insert into ContentInfo (dTId, dName, dContent) values(3,'C#...','C#操作....')

2、界面
使用到的控件：
SplitContainer：将窗体上下或左右进行分隔
左面是treeview，右侧上方是listbox，下方是textbox

3、窗体加载时，将category中的文章类别到treeview

private void LoadCategoryToTree()
{
    string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
    using(SqlConnection conn=new SqlConnection(constr))
    {
        string sql = "select tId,tName from category where tParentId=-1";
        using(SqlCommand cmd=new SqlCommand(sql, conn))
        {
            conn.Open();
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        int tId = reader.GetInt32(0);
                        string tName=reader.GetString(1);
                        //加载到TreeView上
                        TreeNode n=treeView1.Nodes.Add(tName);
                        n.Tag = tId;
                        n.ContextMenuStrip = contextMenuStrip1;
                        //加载该类别下的所有子类别
                        LoadChildCategory(tId,n);
                    }
                }
            }
        }
    }
}

private void LoadChildCategory(int tId,TreeNode node)
{
    string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(constr))
    {
        //我认为打开的连接有两个，因为一个在使用，一直两个，但是递归一直在使用，最好不用reader
        //直接最开始一次性把数据加载到list集合
        string sql = "select tId,tName from category where tParentId=@tId";
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            SqlParameter pms = new SqlParameter("@tId", tId);
            cmd.Parameters.Add(pms);
            conn.Open();
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        int tId1 = reader.GetInt32(0);
                        string tName1 = reader.GetString(1);
                        //加载到TreeView上节点标签为TId的节点下
                        TreeNode n = node.Nodes.Add(tName1);
                        n.Tag=tId1;
                        //多级类别需要递归，现在就两级
                    }
                }
            }
        }
    }
}

4、treeView选中结点后的事件加载到listbox中

private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
{
    if (e.Node.Level == 1)
    {
        listBox1.Items.Clear();
        //e.Node表示当前选中的节点，为二级节点时level为1
        object tId = e.Node.Tag;
        LoadContentInfo(tId);
    }
    /*TreeNode node= treeView1.SelectedNode;
    int tId = Convert.ToInt32( node.Tag);*/
}

private void LoadContentInfo(object tId)
{
    string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(constr))
    {
        //我认为打开的连接有两个，因为一个在使用，一直两个，但是递归一直在使用，最好不用reader
        //直接最开始一次性把数据加载到list集合
        string sql = "select dId,dName from contentinfo where dTId=@tId";
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            SqlParameter pms = new SqlParameter("@tId", tId);
            cmd.Parameters.Add(pms);
            conn.Open();
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        int dId=reader.GetInt32(0);
                        string dName = reader.GetString(1);
                        Info info = new Info() { dId = dId,dName=dName };
                        listBox1.Items.Add(info);
                    }
                }
            }
        }
    }
}

5、选中listbox中的数据详细信息显示在textbox中

private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
{
    if (listBox1.SelectedItem != null)
    {
        Info info = listBox1.SelectedItem as Info;
        int dId = info.dId;
        string dContent = GetContent(dId);
        textBox1.Text = dContent;
    }
}
private string GetContent(int dId)
{
    string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
    using (SqlConnection conn = new SqlConnection(constr))
    { 
        string sql = "select dContent from contentinfo where dId=@tId";
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            SqlParameter pms = new SqlParameter("@tId", dId);
            cmd.Parameters.Add(pms);
            conn.Open();
            string x = cmd.ExecuteScalar().ToString();
            return x;
        }
    }
}

6、父节点点击增加类别

n.ContextMenuStrip = contextMenuStrip1;

6-1、显示菜单，即创建Form2窗体并在点击时显示

int id = Convert.ToInt32(treeView1.SelectedNode.Tag);
Form2 frm=new Form2(id);
frm.updateDelegate = UpdateTreeView;
frm.ShowDialog();

6-2、Form2窗体

添加函数：

//增加子类别
string name=textBox1.Text.Trim();
string content=textBox2.Text.Trim();
string sql = "insert into Category values (@tName,@tParentId,@tNote)";
SqlParameter[] pms = new SqlParameter[]
{
    new SqlParameter("@tName",name),
    new SqlParameter("@tParentId",_id),
    new SqlParameter("@tNote",content)
};
SqlHelper.ExecuteNonQuery(sql, pms);
if (updateDelegate != null) updateDelegate();
this.Close();

6-3、另：添加时要传递parentId，添加完后需要刷新form1中的treeview
前者定义字段、构造函数；
后者使用委托；

private int _id;
public UpdateTreeViewDelegate updateDelegate;
public Form2(int id):this()
{
    _id = id;
}
if (updateDelegate != null) updateDelegate();
form1中：
private void UpdateTreeView()
{
    treeView1.Nodes.Clear();
    LoadCategoryToTree();
}

7、子节点点击删除类别
ContextMenuStrip 控件

n.ContextMenuStrip = contextMenuStrip2;
函数：
//获取选中的类别，删除
int id= Convert.ToInt32(treeView1.SelectedNode.Tag);
string sql = "delete from Category where tId=@tId";
SqlParameter[] pms = new SqlParameter[]{
    new SqlParameter("@tId",id)
};
treeView1.SelectedNode.Remove();
SqlHelper.ExecuteNonQuery(sql, pms);

8、子节点点击导入文章
使用openFileDialog控件

openFileDialog1.Multiselect = true;
openFileDialog1.Filter = "txt|*.txt";
DialogResult result = openFileDialog1.ShowDialog();
if(result == DialogResult.OK)
{
    string[]files=openFileDialog1.FileNames;//每个文本文件的路径
    foreach(string filePath in files)
    {
        string content= File.ReadAllText(filePath,Encoding.UTF8);
        string title=Path.GetFileNameWithoutExtension(filePath);
        int id = Convert.ToInt32( treeView1.SelectedNode.Tag);
        string sql = "insert into ContentInfo(dTId,dName,dContent) values(@dTId,@dName,@dContent)";
        SqlParameter[] pms = new SqlParameter[]{
            new SqlParameter("@dTId",id),
            new SqlParameter("@dName",title),
            new SqlParameter("@dContent",content),
        };
        SqlHelper.ExecuteNonQuery(sql, pms);
    }
}

DataSet

是数据的集合、临时数据库、内存数据库

创建与循环输出

代码：

#region DataSet创建
//1、创建一个临时数据库
DataSet ds = new DataSet("School");
//2、创建一个临时表
DataTable dt = new DataTable("Student");
//2-1、为dt中增加列
DataColumn dtAutoId = new DataColumn("autoId");
dtAutoId.AutoIncrement = true;
dtAutoId.AutoIncrementSeed = 1;
dtAutoId.AutoIncrementStep = 1;
dt.Columns.Add(dtAutoId);
//再增加一列
dt.Columns.Add("loginId", typeof(string));
dt.Columns[0].Unique = true;
//设置唯一
//DataRelation设置主外键关系
dt.Columns.Add("loginPwd", typeof(string));
//2-2、创建行，注意，不能new，需要调用方法创建
for (int i = 0; i < 10; i++)
{
    DataRow dr = dt.NewRow();
    dr[1] = "a" + i;//第一列：用户名
    dr[2] = "123456";//第二列：密码
    dt.Rows.Add(dr);
}
//3、将表加入数据库
ds.Tables.Add(dt);
//dataGridView1.DataSource=ds.Tables[0];
//绑定在控件上并显示
#endregion
#region DataSet内容循环输出
//遍历tables
foreach(DataTable table in ds.Tables)
{
    Console.WriteLine("=============={0}============",table.TableName);
    //遍历每一行
    foreach(DataRow dr in table.Rows)
    {
        //遍历每一列
        for(int i = 0; i < table.Columns.Count; i++)
        {
            Console.Write(dr[i].ToString()+"\t");
        }
        Console.WriteLine();
    }

}
#endregion

Console.WriteLine("ok");
Console.ReadKey();

运行结果：

自动填充DataSet

string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
string sql = "select * from CallRecords";
SqlDataAdapter adapter = new SqlDataAdapter(sql,constr) ;
//SqlDataAdapter内部封装了SqlConnection、SqlCommand、SqlDataReader
DataSet ds = new DataSet();
adapter.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
// //SqlDataAdapter分页
DataTable dt =new DataTable();
adapter.Fill(0,3,dt);
dataGridView1.DataSource = dt;

SqlDataAdapter被称为适配器，把数据库的数据转换为DataTable，通过它可以快速创建数据
b/s模式下使用较少，因为客户端访问服务器，服务器读取数据库的数据，DataSet在服务器中，服务器承载量太大，b/s一般用缓存。

SqlHelper

封装的SqlHelper

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Data;
using System.Threading.Tasks;

namespace WindowsFormsApp2
{
    public class SqlHelper
    {
        //获取配置文件中的字符串
        private static readonly string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
        /// <summary>
        /// 执行insert delete update的方法
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql,params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using(SqlCommand cmd = new SqlCommand(sql, con))
                {
                    if(pms != null) cmd.Parameters.AddRange(pms);
                    con.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        
        /// <summary>
        /// 执行sql语句，返回单个值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    if (pms != null) cmd.Parameters.AddRange(pms);
                    con.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 执行sql语句返回DataReader
        /// 注意：Connection和DataReader都不能关闭，执行ExecuteReader
        /// 需要传递参数
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms)
        {
            SqlConnection con = new SqlConnection(constr);
            try
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    if (pms != null) cmd.Parameters.AddRange(pms);
                    con.Open();
                    SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                    //关闭reader的时候自动关闭连接
                    return reader;
                }
            }
            catch
            {
                if (con != null) { con.Close(); con.Dispose(); }
                throw;
            }        
        }
        /// <summary>
        /// 封装返回DataTable的方法
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static DataTable ExecuteDateTable(string sql, params SqlParameter[] pms)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(sql,constr);
            if(pms!=null)
            {
                adapter.SelectCommand.Parameters.AddRange(pms);
            }
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            return dt;
        }
    }
}

使用封装的SqlHelper实现插入

string userName = textBox1.Text.Trim();
string userPwd=textBox2.Text;
string sql = "insert into UserLogin values(@userName,@UserPwd)";
SqlParameter[] pms = new SqlParameter[]
{
    new SqlParameter("@userName",userName),
    new SqlParameter("@UserPwd",userPwd)
};
int r=SqlHelper.ExecuteNonQuery(sql, pms);
MessageBox.Show("成功插入" + r + "行。");

小总结