黑马程序员-自学笔记-SQLServer与ADO.Net（四）

SQLServer与ADO.Net（四）

---------------------- ASP.Net+Android+IOS开发、.Net培训、期待与您交流！ ----------------------

1、ADO.NET

l  为什么要学ADO.NET

•    之前我们所学只能在查询分析器里查看数据，操作数据，我们不能让普通用户去学sql，所以我们搭建一个界面(Web Winform)让用户方便的操作数据库中的数据

l  什么是ADO.NET

•    ADO.NET就是一组类库，这组类库可以让我们通过程序的方式访问数据库，就像System.IO下的类用类操作文件一样， System.Data.这组类是用来操作数据库(不光是MSSql Server)，它提供了统一的编程接口让操作其它数据库(Access、Oracle等)的方式和操作MSSql Server一致

 

2、ADO.NET组成（*）

l  数据提供程序

•    ***Connection，用来连接数据库

•    ***Command，用来执行SQL语句

•    DataReader只读只进的结果集，一条一条读取数据

•    DataAdapter，一个封装了上面3个对象的对象。

l  数据集(DataSet)，临时数据库。

•    断开时连接的数据库操作

 

 

3、ADO.NET与数据库

l  Ado.net访问数据库的步骤。

•    1

•    1.连接数据用Connection

•    2.执行SQL语句Command

•    3.执行完毕之后将结果一条一条返回。DataReader

•    2

•    使用DataAdapter+DataSet,这种方法本质还是通过Connection、Command、DataReader将数据全部取出来然后放到了DataSet中。

 

4、如何连接数据库

l  在管理工具里面

•    连接数据库

•    新建查询

•    写SQL语句

•    执行SQL语句

l  在ADO.Net中

•    创建连接通道（SqlConnection）

•    创建SQL命令对象（SqlCommand）

•    开始查询

 

5、连接SQLServer

l  SqlConnection

•    构造方法提供连接字符串

•    属性提供连接字符串

l  连接字符串

•    data source=实例名;initial catalog=数据库名;integrated security=true/user id=…;password=…;

•    server=实例名;database=数据库名;integrated security=true/uid=…;pwd=…;

l  打开连接open()

l  连接都需要Dispose()

l  State属性可以查看连接状态

附：随机练习代码

static void Main(string[] args)

       {

           /*1、要连接数据库，就要创建连接对象

            *  数据库实例名  （Local）or T1417SMIS

            *  数据库         UsersInfo

            *  用户名和密码 （Windows身份验证）    sa 123456

            */

 

           string strConn =@"server=(local);database=UsersInfo;uid=sa;pwd=123456";

           //连接字符串，需要确定连接哪个数据库管理软件，即连接哪个实例名，用Server表示

           //需要了解连接哪一个数据库文件，用Database表示

           //身份验证

           //      Windows身份验证：integratedsecurity=true

           //      SqlServer验证：uid pwd

           

           // Data Source=T1417SMIS;Initial Catalog=UsersInfo;IntegratedSecurity=True

           // Data Source=t1417smis;Initial Catalog=UsersInfo;UserID=sa;Password=123456;

 

           #region try-catch-finally手动释放资源

           创建连接通道

           //SqlConnection conn = new SqlConnection(strConn);

            需要连接

           //try

           //{

           //    //开始就执行这里，如果出现异常，在异常处停下，并跳至Catch中代码

           //    conn.Open();

           //    Console.WriteLine("我成功连接啦！");

           //}

           //catch(Exception ex)

           //{

           //    //平时不执行，当try出现异常时，才执行其中代码

           //    Console.WriteLine(ex.Message);

           //}

           //finally

           //{

           //    //无论何时均执行,一般常用作释放资源

           //    conn.Close();  //关闭后可重新开启

           //    conn.Dispose();  //销毁后需重新连接才能开启

           //    Console.WriteLine("我成功关闭啦！");

           //}           

           #endregion

 

           #region 使用using自动释放资源，推荐使用

           using (SqlConnection conn = new SqlConnection(strConn))

           {

                // 需要连接

                try

                {

                    //开始就执行这里，如果出现异常，在异常处停下，并跳至Catch中代码

                    if (conn.State==System.Data.ConnectionState.Closed)

                    {

                        //?    State属性可以查看连接状态，数据库重复打开会报异常

                        conn.Open();

                    }                   

                    Console.WriteLine("我成功连接啦！");

                }

                catch (Exception ex)

                {

                    //平时不执行，当try出现异常时，才执行其中代码

                   Console.WriteLine(ex.Message);

                }

           }

           Console.WriteLine("我成功关闭啦！");

           Console.ReadKey();

           #endregion

       }

 

6、准备执行SQL语句

l  执行SQL语句使用SqlCommand对象

l  提供SQL语句

•    构造方法提供

•    属性提供

l  -> 执行SQL语句需要知道在哪儿执行

•    SqlCommand需要提供SQL语句和连接通道

l  -> 常用三个方法

•    ExecuteNonQuery()      处理增、删、改，返回受影响行数

•    ExecuteScalar()    处理查询，返回首行首列

•    ExecuteReader()  处理查询，返回DataReader对象

 

7、执行简单的增删改语句

l  SqlCommand的ExecuteNonQuery执行增删改操作

l  ExecuteNonQuery返回值是执行的影响行数

•    //执行数据库操作3步

•    //打开连接

•    conn.Open();

•    //执行操作

•    result = cmd.ExecuteNonQuery();

•    //关闭连接

•    conn.Close();

l  使用using语句即使释放连接资源，不用再考虑是否关闭连接，推荐使用

 

8、异常处理

l  可以使用try…catch…finally来捕获异常

l  使用异常处理可以保证一个功能出错不影响另一个功能，比如添加操作失败，不影响查询的操作

 

9、ExecuteScalar

l  使用ExecuteScalar()执行SQL语句，返回第一行第一列，object类型

•    -> 转换使用Convert，不要使用强转

l  使用ExecuteScalar()一般执行聚合查询

l  使用ExecuteScalar()亦可查询selectgetdate()

l  在添加数据的时候，得到自动增长id

•    -> 在value前使用output inserted.主键字段

l  使用详注：

1）、SqlCommand的ExecuteScalar方法用于执行查询，并返回查询所返回的结果集中第一行的第一列，因为不能确定返回值的类型，所以返回值是object类型。

cmd.CommandText = "selectcount(*) from student";int i = Convert.ToInt32(cmd.ExecuteScalar())

cmd.CommandText = "selectgetdate()"; DateTime dt = Convert.ToDateTime(cmd.ExecuteScalar());

2）、得到自动增长字段的主键值，在values关键词前加上output inserted.Id，其中Id为主键字段名。执行结果就试插入的主键值，用ExecuteScalar执行最方便。

cmd.CommandText =“insert intoclass(cName,cDescription) output inserted.Id values(‘高三一班’,‘描述’)”;

int i =Convert.ToInt32(cmd.ExecuteScalar());

 

10、ExecuteReader

l  查询多行数据，返回一个DataReader对象

•    HasRows属性表示查询结果

•    提供连接通道用来持续得到数据

•    并未将数据全部倒入到内存

l  -> 如何处理DataReader

•    使用while循环，调用Read方法

•    调一次，查一次

•     GetString()、GetInt32()、GetOrdinal()方法

•    使用索引，索引器有重载

l  DataReader必须保证SqlConnection处于连接状态

 

附：随机练习代码

//1、ExecuteNonQuery()

static void Main(string[] args)

       {

           //ADO.NET能执行什么，你能写什么SQL语句，它就能给你执行什么

           SqlConnection conn = new SqlConnection(@"DataSource=T1417SMIS;Initial Catalog=UsersInfo;Integrated Security=True");

           string sql = "create table Tbl(id int,name nvarchar(10));insertinto Tbl values(1,'张洋洋');";

           using (conn)

           {

                using (SqlCommand cmd = newSqlCommand(sql, conn))

                {

                   if (conn.State==System.Data.ConnectionState.Closed)

                    {

                        conn.Open();

                    }

 

                    cmd.ExecuteNonQuery();

                    //ExecuteNonQuery()对于Update、Insert和Delete语句，返回值为该命令所唱响的行数。对于其他所有类型的语句，返回值为-1;

                }

           }

           Console.WriteLine("OK");

           Console.ReadKey();

       }

 

#region 2、ExecuteScalar()

       static void Main(string[] args)

       {

           SqlConnection conn = new SqlConnection(@"DataSource=T1417SMIS;Initial Catalog=UsersInfo;Integrated Security=True");

           string sql = @"select [name] from Tbl;";

           using (conn)

           {

                using (SqlCommand cmd = newSqlCommand(sql, conn))

                {

                    if (conn.State ==System.Data.ConnectionState.Closed)

                    {

                        conn.Open();

                    }

                    //ExecuteScalar() 返回结果集中第一行的第一列

                    object o =cmd.ExecuteScalar();

                    //int res =Convert.ToInt32(o);

                   Console.WriteLine(o.ToString());

                   //Console.WriteLine(res);                

                }

           }

           Console.ReadKey();

       }

       #endregion

 

       #region 3、ExecuteReader

       static void Main(string[] args)

       {

           SqlConnection conn = new SqlConnection(@"Data Source=T1417SMIS;InitialCatalog=UsersInfo;Integrated Security=True");

           string sql = @"select * from Student;";

           using (conn)

           {

                using (SqlCommand cmd = newSqlCommand(sql, conn))

                {

                    if (conn.State ==System.Data.ConnectionState.Closed)

                    {

                        conn.Open();

                    }

                    //在这里读取

                    SqlDataReader reader =cmd.ExecuteReader();

                    using (reader)

                    {

                        // 判断是否有数据

                        if (reader.HasRows)

                        {

                            //循环读取

                            while(reader.Read())

                            {

                               Console.Write(reader.IsDBNull(0) ? "0" : reader.GetInt32(0) +"\t");

                                Console.Write(reader.IsDBNull(1)? "0" : reader.GetString(1));

                               Console.WriteLine();

                            }

                        }

                    }

                }

           }

           Console.ReadKey();

       }

       #endregion

 

11、SQL注入漏洞攻击

l  对于用户登录的实现，提供SQL语句

•    select * from 表名 where uid=… and pwd=…

•    使用字符串拼接

l  提供密码为：’or’1’=’1

l  防范注入漏洞攻击的方法就是使用参数查询

 

12、查询参数

l  提供SqlParameter对象处理参数比较

l  SqlParameter提供两个参数

•    参数别名

•    参数值

l  SqlCommand提供一个Parameter属性

•    该属性表示SqlParameter的集合

•    使用Add方法添加（记得new）

•    AddWithValue方法

•    不使用要清空

l  SQL语句中直接使用参数别名

•    - 参数在SQLServer内部不是简单的字符串替换

•    SQLServer直接用添加的值进行数据比较

l  使用详注：

1）对于查询，可以使用SQL Server Profiler查看

2）SQL语句使用@UserName表示“此处用参数代替”，向SqlCommand的Parameters中添加参数

cmd.CommandText ="select * from [user] where uUserName=@UserName and uPwd=@Password";

cmd.Parameters.Add(newSqlParameter(“@ UserName ","admin"));

cmd.Parameters.Add(newSqlParameter(“@ Password ",password));

3）参数在SQLServer内部不是简单的字符串替换，SQLServer直接用添加的值进行数据比较，因此不会有注入漏洞攻击。

 

13、DataSet(ado.net断开式数据访问)

l  DataSet是数据集，是一个临时数据库

•    多层数据中常用

•    现在多用List<T>强类型

l  建数据集 -> 建表 -> 建列结构 -> 附加

•    设置列属性：自增、是否允许为空

l  添加数据（创建行对象）

•     创建行 -> 加行到DataSet

l  遍历数据集（循环与集合与索引结构）

l  显示在DataGrideView（DataSource属性）

附：随机练习代码：

//手动创建一个DataSet

           DataSet ds = new DataSet("MyDataSet");           

           //数据集中有表的集合，所有手动创建一张表

           DataTable dt = new DataTable("MyTable");

           //对表中的列进行初始化

           DataColumn dcId = new DataColumn("id", typeof(int));

           DataColumn dcName = new DataColumn("name",typeof(string));

           DataColumn dcAge = new DataColumn("age",typeof(int));

           DataColumn dcGender = new DataColumn("gender",typeof(string));

           //将初始化的列加到表中

           dt.Columns.Add(dcId);

           dt.Columns.Add(dcName);

           dt.Columns.Add(dcAge);

           dt.Columns.Add(dcGender);

           //手动创建一个行

           DataRow dr = dt.NewRow();

           DataRow dr1 = dt.NewRow();

           //往行里面添加数据，或读取数据

           dr["id"] = 1;

           dr["name"] = "张三";

           dr["age"] = 18;

           dr["gender"] = "男";

           //该行数据加在dt这张表中

           dt.Rows.Add(dr);

           //将创建的表加到DataSet中

           ds.Tables.Add(dt);

 

14、SqlDataAdapter

l   将数据库中的数据一次性提出放至DataSet

•    创建一个DataSet

•    创建一个SqlDataAdapter

•    Fill方法取数据

l  保存修改数据

•    SqlCommandBuilder对象（以主键定位）

•    Update方法

附：随机练习代码：

//创建一个SqlDataAdapter对象

           SqlDataAdapter sa = new SqlDataAdapter(@"selectcityID,cityName,proID from city",@"server=(local);database=DBPromary;uid=sa;pwd=123456");

           //创建一个DataSet

           DataSet ds = new DataSet();

           //使用using以便及时释放资源

           using (sa)

           {

                sa.Fill(ds);

           }

           //将数据保存

           ds.Tables[0].WriteXml("456.xml");

-----------------------------------------------------------------------------------------------------------

//通过dataGridView控件更新数据

           //根据操作生成动态的SQL语句等

           SqlCommandBuilder scb = new SqlCommandBuilder(sa);

           //执行刚刚生成的SQL语句

           sa.Update(ds.Tables["myTable"]);  

                            //Update时填充ds.Tables["myTable"]的SqlDataAdapter资源需保持，不能Dispose掉

 

 ---------------------- ASP.Net+Android+IOS开发、.Net培训、期待与您交流！ ----------------------