ADO.NET学习之SqlConnection,SqlCommand（第二周）

ADO.NET学习之SqlConnection,SqlCommand

一、ADO.NET的两种类型的对象：

①基于连接的对象。
它们是数据提供程序对象，如Connection、Command、DataReader和DataAdapter。它们连接到数据库，执行SQL语句，遍历只读结果集或者填充DataSet。基于连接的对象是针对具体数据源类型的，并且可以在提供程序制定的命令空间中找到
SQL Server为例，调用引用：

using System.Data.SqlClient

②基于内容的对象。
这些对象其实是数据的”包”，包括DataSet、DataColumn、DataRow、DataRelation等。它们完全和数据源独立，出现在System.Data命令空间里

using System.Data

二、SqlConnection的应用：

Connection：用于建立和数据库的连接

1、构造方法

①无参构造

public SqlConnection();

②有参构造

public SqlConnection(string connectionString);

2、创建对象

①第一种方式：

SqlConnection connection = new SqlConnection("data source=.; database=SampleDB;integrated security=SSPI");

②第二种方式：

SqlConnection connection = new SqlConnection();
connection.ConnectionString = "data source=.; database=SampleDB; integrated security=SSPI";

3、两种不同连接方法

①windows连接法
在字符串变量中，描述连接字符串所需的服务器地址、数据库名称、集成安全性（即是否使用Windows验证）

sqlConnection.ConnectionString ="Server=(Local);Database=EduBaseDemo;Integrated Security=sspi";

②SQL Server身份验证连接法
在字符串变量中，描述连接字符串所需的服务器地址、数据库名称、用户名Uid与密码Pwd

sqlConnection.ConnectionStrin=”Server=localhost;Database=the medicine;Uid=root;Pwd=xxxxxx”

如果连接的是本地，直接使用一个点(.)或(Local)即可

4、使用SQL连接字符串构造器

SqlConnectionStringBuilder sqlConnectionStringBuilder = new SqlConnectionStringBuilder()    //声明并实例化SQL连接字符串构造器；
 {                                                                                           
                DataSource = YOURDATASOURCE                                                      //数据源（即服务器）；                                      
                , InitialCatalog = YOURDATABASE                                            //初始化条目（即数据库）；
                , IntegratedSecurity = this.ckb_IsWindowsAuthentication.Checked                         //集成安全性（即是否Windows验证）；
            };
 sqlConnection.ConnectionString=sqlConnectionStringBuilder.ConnectionString;               //SQL连接字符串构造器的连接字符串属性包含了SQL连接所需的连接字符串；

5、在配置文件中保存connection string

①configuration string可保存在APP.config文件中:
（添加了名为Sql的指定的连接字符串）
新建项-应用程序配置文件

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="Sql" connectionString="Server=(local);Database=EduBaseDemo;Integrated Security=sspi" providerName="System.Data.SqlClient"/>
  </connectionStrings>
<startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1"/></startup></configuration>

②配置管理器从配置文件读取连接字符串
注意调用引用

using System.Configuration;    

将配置文件中保存的连接字符串赋予SQL连接的连接字符串属性；

SqlConnection sqlConnection = new SqlConnection();                                                                  
sqlConnection.ConnectionString =
ConfigurationManager.ConnectionStrings["Sql"].ConnectionString; 

三、SqlCommand的应用：

Sqlcommand对象用于执行具体的SQL语句，如增删改查等操作

1、创建对象

①第一种方法：
指定SQL命令的命令文本；命令文本由字符串拼接而成；

  sqlCommand.Connection = sqlConnection;                                  
            sqlCommand.CommandText ="SELECT COUNT(1) FROM tb_User"+ " WHERE No='" + this.txb_UserNo.Text.Trim() + "'"+ " AND Password=HASHBYTES('MD5','" + this.txb_Password.Text.Trim() + "');"; 

【注】形如this.txb_UserNo.Text.Trim()中的.Trim()将文本框的文本清除首尾的空格后，拼接至命令文本中；
②第二种方法
带参数创建，第一个参数为命令文本字符串，第二个参数为使用的connection连接

SqlCommand cmd = new SqlCommand("SELECT COUNT(1) FROM tb_User"+ " WHERE No='" + this.txb_UserNo.Text.Trim() + "'"+ " AND Password=HASHBYTES('MD5','" + this.txb_Password.Text.Trim() + "');", sqlConnection);

2、方法ExecuteScalar

①用于计算单个值
②执行select查询，并返回命令生成的记录集的第一行第一列的字段。
③该方法常用来执行使用COUNT() 、SUM()等函数的聚合select语句

下面以用户登录查询为例：

 sqlConnection.Open(); //调用sqlCommand的方法前先打开SQL连接
 int rowCount = (int)sqlCommand.ExecuteScalar(); //执行标量的返回结果类型为object，可通过强制类型转换，转为整型；
sqlConnection.Close();
            if (rowCount == 1)//若查得所输用户号相应的1行记录；
            {
                MessageBox.Show("登录成功。"); //给出正确提示；
            }
            else                                                                        
            {
                MessageBox.Show("用户号/密码有误，请重新输入！"); //给出错误提示
            }

【注入式攻击危险】
在密码一栏填入“‘) or 1=1;–’”

将原命令码成功修改为：

SELECT COUNT(1) FROM tb_User WHERE No=3140707001 AND Password= ‘’ or 1=1;– 

由于OR的优先级比AND 低，所以最终结果是恒为1；

3、包含参数的命令文本

该方法可有效抵御注入式攻击

SqlCommand sqlCommand = sqlConnection.CreateCommand(); //调用SQL连接的方法CreateCommand来创建SQL命令；                                  
	sqlCommand.CommandText =sqlCommand.CommandText = "SELECT COUNT(1) FROM tb_User WHERE No=@No AND Password=HASHBYTES('MD5',@Password);";   //指定SQL命令的命令文本；命令文本包含参数； 

            sqlCommand.Parameters.AddWithValue("@Password", this.txb_Password.Text.Trim()); //直接调用方法AddWithValue向SQL命令的参数集合添加参数的名称、值；
            sqlCommand.Parameters["@Password"].SqlDbType = SqlDbType.VarChar;                           //通过参数名称访问SQL参数，并将密码参数的类型设为变长字符串；

【注意】
①SQL参数能自动识别类型；若参数值为字符串，则类型自动设为NVARCHAR，且可在执行时自动转换；

②但对于相同密码，VARCHAR/NVARCHAR类型所获得的散列值不同，故需手动将SQL参数类型统一设为VARCHAR;

四、思维导图