C#——数据访问技术

1.数据库基础

数据库是按照数据结构来组织、存储和管理数据的仓库，是存储在一起的相关数据的集合。使用数据库可以减少数据的冗余度，节省数据的存储空间。其具有较高的数据独立性和易扩充性，实现了数据资源的充分共享。计算机系统中只能存储二进制的数据，而数据存在的形式却是多种多样的。数据库可以将多样化的数据转换成二进制的形式，使其能够被计算机识别。同时，可以将存储在数据库中的二进制数据以合理的方式转换为人们可以识别的逻辑数据。

SQL是一种数据库查询和程序设计语言，用于存储数据以及查询、更新和管理关系型数据库系统。SQL的含义是“结构化查询语言（Structured Query Language）”。

（1）数据库的创建

数据库主要用于存储数据及数据库对象（如表、索引）。

在Windows10操作系统的开始界面中找到SQL Server的SQL Server Management Studio，单击打开“连接到服务器”对话框，在该对话框中选择登录的服务器名称和身份验证方式，然后输入登录用户名和登录密码。

单击“连接”按钮，连接到指定的SQL Server服务器，然后展开服务器节点，选中“数据库”节点，右击，在弹出的快捷菜单中选择“新建数据库”命令。

打开“新建数据库”对话框，在该对话框中输入新建的数据库的名称，选择数据库所有者和存放路径，这里的数据库所有者一般为默认。

单击“确定”按钮，即可新建一个数据库。

（2）数据库的删除

只需要在要删除的数据库上右击，在弹出的快捷菜单中选择“删除”命令即可。

（3）数据表的创建

单击数据库名左侧的“+”，打开该数据库的子项目，在子项目中的“表”项上右击，在弹出的快捷菜单中选择“新建表”命令。

在SQL Server管理器中的右边显示一个新表，这里输入要创建的表中所需要的字段，并设置主键。

单击“保存”按钮，弹出“选择名称”对话框。输入要新建的数据表的名称，单击“确定”按钮，即可在数据库中添加一个数据表。

（4）删除数据表

如果要删除数据库中的某个数据表，只需要右击数据表，在弹出的快捷菜单中选择“删除”命令即可。

2.简单SQL语句的应用

（1）查询数据

通常使用Select语句查询数据，Select语句是从数据库中检索数据并查询，并将查询结果以表格的形式返回。

SELECT select_list

[INFO new_table]

FROM table_source

[WHERE search_condition]

[GROUP BY group_by_expression]

[HAVING search_condition]

[ORDER BY order_expression [ASC|DESC]]

其中，

①Select_list：指定由查询返回的列，它是一个逗号分分隔的表达式列表。每个表达式同时定义格式（数据类型和大小）和结果集列的数据来源。每个选择列表表达式通常是对从中获取数据的源表或视图的列的引用，但也可能是其他表达式，列入常量或T-SQL函数。在选择列表中使用“*”表达式指定返回源表中的所有列。

②INTO new_table_name：创建新表并将查询行从查询插入新表中。new_table_name指定新表的名称。

③FROM table_source：指定从其中检索行的表，这些来源可能包括基表、视图和链接表。From子句还可包含连接说明，该说明定义了SQL Server用来在表之间进行导航的特定路径。From子句还可以用在Delete和Update语句中，已定义要修改的表。

④WHERE search_condition：Where子句指定用于限制返回的行的搜索条件。Where子句还用在Delete和Update语句中以定义目标表中要修改的行。

⑤GROUP BY group_by_expression：Group By子句根据group_by_list列中的值将结果集分成组。例如，student表在“性别”中有两个值。Group By Ship Via子句将结果集分成两组，每组对应于ShipVia的一个值。

⑥HAVING search_condition：Having子句是指定组或聚合的搜索条件。逻辑上讲，Having子句从中间结果集对行进行筛选，这些中间结果集是用Select语句中的From、Where或Group By子句创建。Having子句通常与Group By子句一起使用，尽管Having子句前面不必有Group By子句。

⑦ORDER BY order_expression [ASC|DESC]：Order by子句定义结果集中的行排列的顺序。order_list指定组成排序列表的结果集的列。ASC和DESC关键字用于指定行是按升序还是降序排序。Order By之所以重要，是因为关系理论规定除非已经指定Order By，否则不能假设结果集中的行带有任何序列。如果结果集行的顺序对于Select语句来说很重要，那么在该语句中就必须使用Order By子句。

 例：数据库db_CSharp的数据表tb_test中存储了一些商品的信息，使用Select语句查询数据表tb_test中商品的新旧程度为“二手”的数据

代码如下

select * from tb_test where 新旧程度=’二手’

如果想要在数据库中查找空值，那么其条件必须为where 字段名=’’ or字段名=null

（2）添加数据

使用Insert语句向数据表中添加数据，语法如下：

INSERT[INTO]

{table_name WITH(<table_hint_limited>[…n])

|view_name

|rowswt_function_limited

}

{[(column)list]

{VALUES

      ({DEFAULT|NULL|expression}[,..n])

      |derived_table

      |execute_statement

}

}

|DEFAULT VALUES

其中，

①[INTO]：一个可选的关键字可以将它用在INSERT和目标表之间。

②table_name：将要接收数据的表或table变量的名称

③view_name：视图的名称及可选的别名。通过view_name来引用的视图必须使可更新的

④（column_list）：要在其中插入数据的一列或多列的列表。必须用圆括号将clumn_list括起来，并且用逗号进行分隔

⑤VALUES：引入要插入的数值的列表，对于column_list（如果已经指定）中或者表中的每个列，都必须有一个数据值。必须用圆括号将值的列表括起来。如果VALUES列表中耳朵值、表中的值与表中列的顺序不相同，或者未包含表中所有列的值，那么必须使用column_list明确地指定存储每个传入值的列

⑥DEFAULT：前置SQL Server装载为列定义的默认值。如果杜宇某列并不存在默认值，并且该列允许NULL，那么就插入NULL。

⑦expression：一个常量、变量或表达式。表达式不能包含Select或Execute语句

⑧derived_table：任何有效的Select语句，它将返回装载到表中的数据行

需要注意以下几点：

a.插入项的顺序和数据类型必须与表或视图中列的顺序和数据类型相对应。

b.如果表中某列定义为不允许NULL，则插入数据时，该列必须存在合法值。

c.如果某列是字符型或日期型数据类型，则插入的数据应该加上单引号。

例子：使用Insert语句，向数据表tb_test中添加一条新的商品信息，代码如下：

insert into tb_test(商品名称,商品价格,商品类型,商品产地,新旧程度) values(‘洗衣机’,890,‘家电’,’进口’,’全新’)

（3）更新数据

使用Update语句更新数据，可以修改一个列或者几个列中的值，但一次只能修改一个表。

语法如下：

UPDATE

{table_name WITH(<table_hint_limited>[,…n])

|view_name

|rowset_function_limited

}

SET

{column_name={expression|DEFAULT|NULL}

|@variable=expression

|@variable=column=expression}[,...n]

{{[FROM{<table_source>}[,…n]]

      [WHERE

<search_condition>]}

|

[WHERE CURRENT OF

{{[GLOBAL]cursor_name}|cursor_variable_name}

]}

[OPTION(<query_hint>[,…n])]

其中，

①table_name：需要更新的表的名称。如果该表不在当前服务器或数据库中，或不为当前用户所有，那么这个名称可用链接服务器、数据库和所有者名称来限定

②WITH(<table_hint_limited>[,…n])：指定目标表所允许的一个或多个表的提示。需要有WITH关键字和圆括号。不允许有READPAST、NOLOCK和READUNCOMMITTED

③view_name：要更新的视图的名称。通过view_name来引用的视图必须是可更新的。用UPDATE语句进行的修改，之多只能影响视图的FROM子句所引用的基表中的一个

④rowset_function_limited：OPENQUERY或OPENROWSET函数，视提供程序功能而定

⑤SET：指定要更新的列或者变量名称的列表

⑥column_name：含有要更改数据的列的名称。column_name必须驻留于UPDATE子句中所指定的表或视图中。标识列不能进行更新

⑦expression：变量、字面值、表达式或加上括弧的返回单个值的subSELECT语句。expression返回的值将替换column_name或@variable中的现有值。

⑧DEFAULT：指定使用对列定义的默认值替换列中的现有值。如果该列没有默认值并且定义为允许控空值，也可用来将列更改为NULL

⑨@variable：已声明的变量，该变量将设置为expression所返回的值

⑩FROM<table_source>：指定用表来为更新操作提供准则

⑪WHERE：指定条件来限定所更新的行

⑫<search_condition>：为要更新行指定需满足的条件。搜索条件也可以是连续所基于的条件。对搜索条件中可以包含的谓词数量没有限制

⑬CURRENT OF：指定更新在指定游标的当前位置进行

⑭GLOBAL：指定cursor_name指的是全局游标

⑮cursor_name：要从中进行提取的开放游标的名称。如果同时存在名为cursor_name的全局游标和局部游标；如果未指定GLOBAL，则cursor_name指局部游标。游标必须允许更新。

⑯cursor_variable_name：游标变量的名称，cursor_variable_name必须引用允许更新的游标

⑰OPTION(<query_hint>[,…n])：指定优化程序提示用于自定义SQL Server的语句处理

例子：由于进口商品价格上调，所以洗衣机的价格随之上调，使用Update语句更新数据表tb_test中洗衣机的商品价格，代码如下：

//Update 语句更新数据表tb_test中洗衣机的商品价格

update tb_test set 商品价格=1500 where 商品名称=’洗衣机’

（4）删除数据

使用DELETE语句删除数据，可以使用一个单一的Delete语句删除一行或多行。当表中没有行满足Where子句中指定的条件时，就没有行会被删除，也没有错误产生。

语法如下：

DELETE

[FROM]

       {table_name WITH(<table_hint_limited>[,…n])

       |view_name

       |rowset_function_limited

}

[FROM{<table_source>}[,…n]]

[WHERE

{<search_condition>

|{[CURRENT OF

              {{[GLOBAL]cursor_name}

              |cursor_variable_name}

]}

}

]

[OPTION(<query_hint>[,…n])]

其中，

①table_name：需要更新的表的名称，如果该表不在当前服务器或数据库中，或不为当前用户所有，那么这个名称可用链接服务器、数据库和所有者名称来限定

②WITH(<table_hint_limited>[,…n])：指定目标表所允许的一个或多个表提示。需要有WITH关键字和圆括号。不允许有READPAST、NOLOCK和READUNCOMMITTED

③view_name：要更新的视图的名称。通过view_name来引用的视图必须是可更新的。用UPDATE语句进行的修改，至多只能影响视图的FROM子句所引用的基表中的一个

④rowset_function_limited：OPENQUERY或OPENROWSET函数，视提供程序功能而定

⑤FROM<table_source>：指定用表来为更新操作提供准则

⑥WHERE：指定条件来限定所更新的行

⑦<search_condition>：为要更新行指定需满足的条件。搜索条件额可以是连接所基于的条件。对搜索条件中可以包含的谓词数量没有限制

⑧CURRENT OF：指定更新在指定游标的当前位置进行

⑨GLOBAL：指定cursor_name指的是全局游标

⑩cursor_name：要从中进行提取的开放游标的名称。如果同时存在名为cursor_name的全局游标和局部游标，在指定了GLOBAL时，则cursor_name指的是全局游标；如果未指定GLOBAL，则cursor_variable_name必须引用允许更新的游标

⑪cursor_variable_name：游标变量的名称。cursor_variable_name必须引用允许更新的游标

⑫OPTION(<query_hint>[,…n])：指定优化程序提示用于自定义SQL Server的语句chuli

例子：删除数据表tb_test中商品名称为“洗衣机”，并且商品产地是“进口”的商品信息，代码如下：

delete from tb_test where 商品名称=’洗衣机’ and 商品产地=’进口’

3.ADO.NET简介

ADO.NET是一组向.NET程序员公开数据访问服务的类。ADO.NET为创建分布式数据共享应用程序提供了一组丰富的额组件。它提供了一系列的方法，用于支持对Microsoft SQL Server和XML等数据源进行访问，还提供了通过OLE DB和XML公开的数据源提供一直访问的方法。数据客户端应用程序可以使用ADO.NET来连接到这些数据源，并查询、添加、删除和更新多包含的数据。

ADO.NET支持两种访问数据的模型：无线连接模型和连接模型。无线连接模型将数据下载到客户机器上，并在客户机上将数据封装到内存中，然后可以像访问本地关系数据库一样访问内存中的数据。连接模型依赖于逐记录的访问，这种访问要求打开并保持与数据源的连接。

4.连接数据库：Connection对象

Connection对象是一个连接对象，主要功能是建立与物理数据库的对象类。主要包括4中访问数据库的对象类：

þSQL Server数据提供程序，位于System.Data.SqlClient命名空间。

þODBC数据提供程序，位于System.Data.Odbc命名空间。

þOLEDB数据提供程序，位于System.Data.Oledb命名空间。

þOracle数据提供程序，位于System.Data.OracleClient命名空间。

①打开数据库，输入服务器名称

②.在数据库中创建一个新的数据库表

③. VS中项目右击，添加->类

④添加ADO.NET实体数据模型

⑤点击下一步

⑥填写服务器名和选择或输入数据库名称，其中服务器名要与sql的对应。然后点击确定。
⑦点击下一步

⑧选择对应的数据库表，然后点击完成。

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace Connection1

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            if (textBox1.Text == "")

            {

                MessageBox.Show("请输入要连接的数据库名称");

            }

            else

            {

                try

                {

                    //声明一个字符串用于存储连接数据库字符串

                    string ConStr = "server=.;database=" + textBox1.Text.Trim() + ";uid=sa;pwd=自己密码"; //.Trim()函数主要用于移除字符串两侧的空白字符或其他预定义的字符

                    //创建一个SqlConnection对象

                    SqlConnection conn = new SqlConnection(ConStr);

                    conn.Open();

                    if (conn.State == ConnectionState.Open)

                    {

                        //显示状态信息

                        label2.Text = "数据库【" + textBox1.Text.Trim() + "】已经连接并打开";

                    }

                }

                catch

                {

                    MessageBox.Show("连接数据库失败");

                }

            }

    }

}

}

5.关闭连接

当数据库操作完毕后，要关闭与数据库的连接，释放占用的资源。通过调用SQLConnection对象的Close方法或Dispose方法关闭与数据库的连接，这两种方法的区别是：Close方法用于关闭一个连接，而Dispose方法不仅关闭一个连接，而且还清理连接所占用的资源。当使用Close方法关闭连接后，可以再次调用Open方法打开连接，不会产生任何错误；当使用Dispose方法关闭连接，就不可以再次调用Open方法打开连接，必须再次重新初始化连接再打开。

例子：

namespace CLoseConnection

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void Form1_Load(object sender, EventArgs e)

        {

        }

        SqlConnection conn; //声明一个SqlConnection对象

        private void button1_Click(object sender, EventArgs e)

        {

            if (textBox1.Text == "") //判断是否输入数据库名称

            {

                MessageBox.Show("请输入数据库的名称"); //没有输入则弹出提示

            }

            else

            {

                try

                {

                    string str = "server=.;database=" + textBox1.Text.Trim() + ";uid=sa;pwd=自己密码";

                    conn = new SqlConnection(str); //创建一个SqlConnection对象

                    conn.Open(); //打开连接

                    if (conn.State == ConnectionState.Open) //判断当前连接状态

                    {

                        MessageBox.Show("连接成功"); //弹出提示

                    }

                 }

                catch (Exception ex)

                {

                    MessageBox.Show(ex.Message); //出现异常弹出错误信息

                    textBox1.Text = ""; //清空文本框

                }

               

            }

        }

        private void button2_Click(object sender, EventArgs e)

        {

            try

            {

                string str1 = ""; //声明一个字符串变量

                conn.Close(); //使用Close方法关闭连接

                if (conn.State == ConnectionState.Closed) //判断当前的连接是否关闭

                {

                    str1 = "数据库已成功关闭\n"; //如果关闭则弹出提示

                }

                conn.Open(); //重新打开连接

                if (conn.State == ConnectionState.Open) //判断连接是否打开

                {

                    str1 += "数据库已经成功打开"; //弹出提示

                }

                richTextBox1.Text = str1; //向RichTextBox1中添加提示信息

            }

            catch (Exception ex)

            {

                richTextBox1.Text = ex.Message; //出现异常，将异常添加到RichTextBox1中

            }

        }

        private void button3_Click(object sender, EventArgs e)

        {

            try

            {

                conn.Dispose(); //使用Dispose方法关闭连接

                conn.Open(); //重新使用Open方法打开会出现异常

            }

            catch (Exception ex)

            {

                richTextBox1.Text = ex.Message; //将异常显示在RichTextBox控件中

            }

        }

    }

}

6.执行SQL语句：Command对象

Command对象是一个数据命令对象，主要功能是像数据库发送查询、更新、删除、修改操作的SQL语句。

①设置数据源类型

Command对象有3个重要的属性，分别是Connection属性、CommandText属性和CommandType属性。

Connection属性用于设置SqlCommand使用的SqlConnection。

CommandText属性用于设置要对数据源执行的SQL语句或存储过程。

CommandType属性用于设置指定CommandText类型。

CommandType属性的值是CommandType枚举值，CommandType枚举有3个枚举成员。分别为：StoredProcedure存储过程的名称；TableDirect表的名称；Text：SQL文本命令。  

例子：

namespace Command

{

    public partial class Form1 : Form

    {

        SqlConnection conn;

        public Form1()

        {

            InitializeComponent();

        }

        private void Form1_Load(object sender, EventArgs e)

        {

            //实例化SqlConnection变量conn

            conn = new SqlConnection("server=.;database=test;uid=sa;pwd=自己密码");

            conn.Open();

            if(conn.State==ConnectionState.Open)

            {

                MessageBox.Show("连接成功");

            }

        }

        private void button1_Click(object sender, EventArgs e)

        {

            try

            {

                //判断是否打开连接或者文本框不为空

                if (conn.State == ConnectionState.Open || textBox1.Text != "")

                {

                    SqlCommand cmd = new SqlCommand(); //创建一个SqlCommand对象

                    cmd.Connection = conn; //设置Connection属性

                    //设置CommandText属性，设置SQL语句

                    cmd.CommandText = "select count(*)from " + textBox1.Text.Trim(); //注意黄色部分的空格，多和少都不行

                    //设置CommandType属性为Text，使其只执行SQL语句文本形式

                    cmd.CommandType = CommandType.Text;

                    //使用ExecuteScalar()方法获取指定数据表中的数据数量

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

                    label3.Text = i.ToString();

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

       

    }

}

右击选择编辑前200行可以对表格进行编写。

7.执行SQL语句

Command对象需要取得将要执行的SQL语句，通过调用该类提供的多种方法，向数据库提交SQL语句。

①ExecuteNonQuery()方法

执行SQL语句，并返回受影响的行数，在使用SqlCommand向数据库发送增、删、改命令时，通常使用ExecuteNonQuery()方法执行发送的SQL语句。

例子：

创建一个Windows应用程序，在38妇女节那天给每位女员工颁发50元。

namespace SQL

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void Form1_Load(object sender, EventArgs e)

        {

        }

        private void label2_Click(object sender, EventArgs e)

        {

        }

        SqlConnection conn;

        private void button1_Click(object sender, EventArgs e)

        {

            //实例化Sqlconnection变量conn

            conn = new SqlConnection("server=.;database=MS;uid=sa;pwd=自己密码");

            conn.Open(); //打开连接

            SqlCommand cmd = conn.CreateCommand(); //创建一个SqlCommand对象

            //CommandText 　　获取或设置要对数据源执行的 Transact-SQL 语句、表名或存储过程。

　　        //CommandTimeout 获取或设置在终止执行命令的尝试并生成错误之前的等待时间。

        　　//CommandType 　　获取或设置一个值，该值指示释 CommandText 是SQL语句，存储过程还是表操作。

        　　//Connection 　　　　获取或设置 SqlCommand 的此实例使用的 SqlConnection。

　       　//Parameters 　　　　获取 SqlParameterCollection。

　       　//Transaction 　　　 获取或设置将在其中执行 SqlCommand 的 SqlTransaction。

            cmd.Connection = conn;  //设置Connection属性，指定其使用conn连接数据库

            cmd.CommandText = "update MSS set 奖金=50 where 性别='女'"; //设置CommandText属性，设置其执行的SQL语句

            cmd.CommandType = CommandType.Text; //设置CommandType属性为Text，使其只执行SQL语句文本形式

            int i=Convert.ToInt32(cmd.ExecuteNonQuery()); //使用ExecuteQuery()方法执行SQL语句

            label2.Text = "共有" + i.ToString() + "名女员工获得奖金50元";

        }

    }

}