C#之数据库编程：从入门到精通

时间：2021-10-18 20:00:47

引子

这是我在阅读《Beginning C# 2008 Database: From Novice to Professional》一书的过程中，在书页上所做注解的整理和汇总，权作温故知新之用。

关于本书

《Beginning C# 2008 Database: From Novice to Professional》是一本关于C#操作数据库的书籍，适合入门级的读者。

全书的内容涵盖了简单查询、存储过程、事务、ADO.NET基础类、异常处理、LINQ初步等内容。

该书以Visual Studio 2008与SQL Server 2005为平台，以举例－解释－总结这样一种循序渐进、循循善诱的方式，对上述每个主题进行了讲解。

笔记正文

准备工作
P7 启动SQL Server服务

在安装了SQL Server Express的Windows下，如果实例没有自动启动，可以使用下列命令手动启动SQL Server实例：

net start mssql$sqlexpress
P11 准备AdventureWorks数据库

注意下载对应SQL Server 2005的Northwind版本：http://msftdbprodsamples.codeplex.com/

这是一个.MDF文件，直接以物理文件形式被以Attach的方式加入SQL Server。

P23 SQL Server系统数据库的作用

　·master：管理SQL Server的全局信息

　·model：新数据库的模板

　·msdb：为SQL Server代理Agent的调度和警报服务

　·tmpdb：临时数据库

以下章节，最好找一本类似《T-SQL语法参考》这样的书，因为SQL语法基础不是简单的几章就能解决的。
P27 重要的一章：关系数据库

信息 经过 组织化+结构化 成为 数据库。

健壮的生命周期模型有益于良好数据库的构建：需求分析－逻辑设计－物理设计－实现－改进－监测。

Key－良好设计的关键，是row的标识。

映射基数包括1:1，1:M与M:M三种基本模式。

M:M模式，通常利用一个中间表Junction Table以1:M:1的方式实现。

1:M模式，子表的Foreign Key对应父表的Primary Key，子表的Foreign Key属于父表Primary Key的子集。

P36：1-2-3范式

“Vice Versa”：反之亦然

P44 Group by子句

Group by子句类似“分类汇总”的“分类”部分，其“汇总”部分则在select子句中实现。

P46 PIVOT操作符

pivot用以实现“行转列”或“列转行”，包括三个步骤：准备原始数据，设定要转置的字段与方式，用pivot操作符确定的方式实现转置并显示结果。

select CardType, [2006] as Year2006, [2007] as Year2007
from ( 
select CardType, Expyear from Sales.CreditCard) as piv 
pivot( 
count (ExpYear) for ExpYear in ([2006],[2007]) as carddetail 
order by CardType

1-2行是转置后要显示的结果，3-4是查询出的源数据，5是转置的方式，6的排序可有可无。 其中3与5的关键字 as 可省略，as后是别名。

1的字段[2006]与[2007]与5的for...in(...) 的值域（集合）一一对应。5中包括完整的分类汇总：count(ExpYear) 是汇总，可以换作其他函数；for ExpYear in ( [2006],[2007] ) 是分类，其中的每个值将成为转置时的一个分类项。每个分类项，必须用[]括上。而在第1行中，2006与2007将被解释为整数值而被直接输出，因此要加上[]与第5行中的分类项对应。

P48 Row_Number()
select SalesPersonID, Bonus, Row_Number() over (order by SalesPersonID) as indexno
from Sales.SalesPerson

按order by子句定义的顺序，对返回的结果从1开始编号，并显示为一个字段indexno。

P48 Partition By子句
select CustomerID, TerritoryID,
Row_Number() over (partition by TerritoryID order by CustomerID) as indexno
from Sales.Customer
where (TerritoryID in (1, 2, 3, 4, 5)) and (CustomerID between 1 and 75)

假设分区partition的字段为A，行号Row_Number()的字段为B。则是在原本用Row_Number()与where子句返回的B的 1...x行中，再根据A的不同值，以where子句的前半句A in (x1 , x2 , x3 , ..., xn ) 将B之（1...x ）分解为1...x1 ，1...x2 ，..., 1...xn 这个若干个小区段，即在按B值排序的基础上，优先以A值进行分组。其中，partition by子句与where子句要相呼应，必须共存。

在上面的例句里，就是先显示所有Territory＝1的CustomerID，编号从1到n。然后显示Territory＝2的CustomerID，编号又重新从1到m。依此类推。在每个小节里，按CustomerID进行排序。

P50 通配符

%: 若干个任意字符

_: 任意单个字符

[]: 其中之一

[^]: 不包括xxxx

P61 表间连接Join

内部join：只有两边都匹配条件的记录才被合并

外部join：在内部join基础上，选择性加入左表或右表的非完全匹配记录

P83 select into与临时表
select OrderID, EmployeeID into #TempOrder from Orders

发生意外：”未将实例引用设置到对象的实例“。初以为是当前打开的数据库原因，后发现是临时数据库与SSMS（SQL Server Management Studio）的查询窗口同生存期。

select OrderID, EmployeeID into #TempOrder from Orders where 0=1

利用Magic condition "where 0=1"拷贝一个空的表结构。

P89 Insert

自动增量的字段由数据库管理，不能显式地赋值，不能也不会出现在insert的值列里。删除某行，会出现空缺，数据库不会自动回收或者填补。

在Foreign Key所在的表中进行insert操作，须在其父表中存在该值。

P98 存储过程

尽量别用前缀sp_来创建存储过程，这关乎系统默认的搜索顺序，因为系统总是优先查找系统库中的存储过程。

"--" 是SQL Server的单行注释符。

存储过程有input、output和return三种参量，其中@return_value 由系统提供，可被抛弃。声明用的变参和实际的变量不需要同名。但是在调用格式上，有一个约束：第一个参数使用了“参数名＝变量名或值”的方式，则后续的所有参数也必须采取类似方式。

declare @rcount int
execute order_by_employeeid @employeeid=5, @ordercount = @rcount output
execute order _by_employeeid 5, @rcount output

ADO.NET中的SqlCommand可以通过设置CommandType为StoredProcedure，来映射一个存储过程。 SqlParameter类则对应于各种参数。SqlParameter的属性中包括参数名称、数据类型、传递方向、当前值等。通过 SqlCommand.Parameters.Add()与特定的某个SqlCommand对象建立联系。

P121 XML

XML，一切以元素Element为中心。

P137 事务

事务的ACID属性：

A：原子性，不可再分

C：一致性

I：独立性

D：耐久性

设计高效的事务：准备充分的环境信息，浏览数据时不要使用事务，言简意赅，并发操作，尽可能少的数据访问。

利用ADO.NET实现事务的模型：

try
{
    SqlTransaction.BeginTransaction();
    // operations of the transaction.
    SqlTransaction.Commit();
}
catch
{
    SqlTransaction.RollBack();
}
以下章节开始进入ADO.NET世界
P157 ADO.NET

ADO.NET是一组提供数据访问功能的松耦合的类。

以程序员的视角，操作SQL Server、OLE DB、ODBC三种DataProvider没有本质差异，仅在连接串（描述子）上不同。特别是ODBC，其连接需要手工在DSN里添加。

string sql = @"server=./sqlexpress; integrated security=true; database=northwind";
string oledb = @"provider=sqloledb; data source=./sqlexpress; integrated security=sspi; initial catalog=northwind";
string odbc = @"dsn=northwindodbc";
P192 Connection

连接串，总是以“key=value;"这样的若干小节组合而成。对于本地的SQL Server，使用"server = localhost;"能适应更多的场合。

string connection1 = @"sever = ./sqlexpress";
string connection1 = @"sever = (local)/sqlexpress";

SqlConnection类的成员，可以反映许多连接的相关信息。

P225 Command

Command支持3种执行方式：返回单个值的ExecuteScalar()、以DataReader方式返回查询结果的ExecuteReader()、面向Insert-Update-Delete和存储过程操作的ExecuteNonQuery()。

SqlCommand cmdScalar = new SqlCommand(strScalar, conn);
SqlCommand cmdQuery = new SqlCommand(strQuery, conn);

SqlDataReader reader = cmdQuery.ExecuteReader();
while (reader.Read())
    Console.WriteLine("{0},{1}", reader["employeeid"], reader["firstname"]);
reader.Close();

Console.WriteLine(cmdScalar.ExecuteScalar());

上例中，第7行是必须的，因为DataReader是只读的、面向连接的。否则所有与该DataReader使用的Connection关联的Command都会在Execute时触发异常：“已有打开的与此命令关联的DataReader，必须首先将它关闭。“

Command支持类似存储过程中使用的参数，并用类Parameter予以对应，同时提供了一个方法Prepare()为命令参数预备空间。这会提高命令的执行效率。一个Command对象，可以反复使用，只要更换CommandType、CommandText等。 在执行存储过程时，直接将Command对象看作过程本身，不需要在SQL语句中添加execute命令。

P235 DataReader

每个Connection只能有一个活动的DataReader。

访问DataReader的字段有3种方式：reader[0]，reader[“employeeid”]和reader.GetInt32(0)。其中方式2的字段名，对大小写不敏感。

DataReader除了常用的Read()方法，还有一系列的方法反映查询结果的相关信息，甚至可用以还原一张Table。其GetSchemaTable()方法返回的DataTable中，每Row描述查询结果的一个字段的相关信息。

DataReader支持类似批处理的方式，同时执行多条SQL查询。通过构造多条平行的select查询语句，可以将每个查询的结果作为一个子集，返回给DataReader。其中，利用NextResult()方法在子集内遍历。

string query1 = @"select * from employees";
string query2 = @"select * from customers";
string bat = query1 + query2;

SqlCommand cmd = new SqlCommand(bat, conn);
SqlDataReader reader = cmd.ExecuteReader();

do
{
    while (reader.Read())
    {
        // to do something
    }
} while (reader.NextResult());

这样划线实在是效率，早没想到呢？

Console.WriteLine("".PadLeft(60, '='));
P265  DataSet与DataAdapter

DataProvider是水池（我更愿意是湖），DataAdapter是水管，DataSet是水桶。其实，水管DataAdapter并不是必须的。

DataSet是一个本地的Cache，对它的所有操作不会直接地、立即地反馈给DataProvider。一次连接、重复使用、确认提交是DataSet的主要特点。

关于DataAdapter.Fill()，它可以自动打开和关闭Connection。Fill的对象，可以是DataSet、DataTable或者DataRow[]。在使用时要注意：

不给名的方式，默认表名依次为Table、Table1、Table2、...

如果DataSet中已经有同名表或只有唯一的一个大小写不敏感的同名表（如"aaa”与"Aaa"），则直接填充该表。

如果有表名的完全匹配与大小写不敏感的匹配，则选择完全匹配的表进行填充。

MSDN上有一个例子：

dataset.Tables.Add("aaa");
dataset.Tables.Add("AAA");

adapter.Fill(dataset, "aaa");   // fill table aaa
adapter.Fill(dataset, "Aaa");   // create a new table Aaa
//-------------------------------------------------------------------
dataset.Tables.Add("aaa");
adapter.Fill(dataset, "AAA");   // fill table aaa

DataAdapter也支持批处理查询，但是在Fill()时要注意。假设有2条分别对应表Customers与Products的查询语句，则 Fill(ds, "customers")后，在DataSet中会依据select语句的顺序生成2张表：Customers与Customers1，而不是 Customers与Products！所以ds.Tables[“customers”]可以，而ds.Tables[“products”]会报错，后者改为ds.Tables[1]即可。

select * from Customers where country = lower('Germany')

上述语句，在传递给Command或者Adapter时会报错，但在SSMS中使用则是正常的。不知道其他运算是否也不支持？

P281 DataView

蛋疼的DataView，类似DataSet，也是Cache，但可以自己改变表的结构。

DataRowView－DataViewRow，这是玩笑？

P309 利用DataSet与DataAdapter实现数据更新

DataSet维护了表中各行Row的状态。在调用AcceptChanges()后，会将所有Row的状态而不是值复位为"Unchanged"（MSDN使用了"Commint"这个词作表述）。但这只影响DataSet本身，并不会直接导致数据源的数据更新。数据的更新，需借助Command实现。无论此Command是直接调用Command对象，或是经由Adapter.Update()调用的。

DataAdapter.Update()会根据Row的不同RowState调用InsertCommand、DeleteCommand 或UpdateCommand，以实现对应的更新操作。DataSet作为一个Cache，其中每一行的值作为相应Command的参数值。 Update()的内部执行机制如下：

　·将DataRow的值移至参数值；

　·触发OnRowUpdating事件；

　·执行对应Command；

　·若上述Command被设置为FirstReturnedRecord，则将返回的第一条结果放入DataRow中。若存在输出参数，则将输出值放入DataRow中。然后触发OnRowUpdated事件；

　·在DataSet中，调用AcceptChanges()，隐式调用EndEdit()，并将RowState设置为"unchanged"或删除该行。

在DataSet的列Column与Command中Parameter的对应关系如下：

cmd.Parameters.Add(
                "@city",               // Command中参数名
                SqlDbType.NVarChar,    // Command中参数类型
                15,                    // Command参数长度
                "city");               // 映射到DataSet表中的某字段

要注意作为Primary Key的列，在Update时可能需要保留原值，因此要设置Parameter.SourceVersion = DataRowVersion.Original。

CommandBuilder只需声明并new之，不用引用。它仅仅是按照CommandText的对应关系，构造好InsertCommand、UpdateCommand与DeleteCommand。

CommandBuilder仅限于单表的使用，与构造Adapter的sql语句或其SelectCommand相对应，自动以select的列为框架生成对应的insert/update/delete语句，并侦听RowUpdateing事件。

Adapter的SelectCommand要求返回主键或带唯一属性的列，否则会触发异常。

关于AcceptChanges()与Update()的关系，通过注释来发掘Command、Adapter、DataSet间的关系，自己设计了一个简单的例子：

adapter.Fill(dataset, "employees");
SqlCommandBuilder cmdbuilder = new SqlCommandBuilder(adapter);    // 1
dataset.AcceptChanges();                                          // 2 
adapter.Update(dataset, "employees");                             // 3

搭配结果：

1+2

 无异常，未更新源

2+3

 无异常，未更新源

3+2

 异常：需要添加有效的Command

1+2+3

 无异常，未更新

1+3+2

 成功更新

1+3

 成功更新
P312 DataSet与XML

援引自己的Blog：重复使用ReadXml()会触发异常；第一次添加数据时不用ReadXml()，直接WriteXml()生成.xml文件即可；尽量不要尝试手动建立空白的.xml文件。

P335  Dock与Anchor

Dock：以何种方式依附到容器。选择了哪个方向，则除其相反方向外的其余3个方向都会随容器边界变化。

Anchor：与容器在哪个方向上保持固定距离。选择了哪个方向，则此方向上会与容器边界变化。

P349 ASP.NET

Windows 7中的IIS默认未安装，需开启。

Web＝请求－回应－解释

P403 存取大量文本与二进制数据（比如图片）

SQL Server提供了三种数据类型，用以处理大量文本和二进制数据。

VARCHAR(MAX)：非Unicode编码的文本

NVARCHAR(MAX)：Unicode编码的文本

VARBINARY(MAX)：类似图像这样的二进制数据

存入二进制数据，就是把数据转换为byte[]这样的流（stream），然后作为参数传递给Command。而取二进制数据，则是一个相反的过程，把Command返回的byte[]类型的值，转换为MemoryStream，再转换为Bitmap等其他类型。在整个过程中，主要的对象和数据类型包括：byte[]、Stream、StreamReader。

需要注意的是，对于varchar、varbinary这样变长的类型，Command的参数需要指定最大长度，否则Command.Prepare()方法会出错。

LINQ, bridges the gap and strengthens the connection between relational data and the object-oriented world.
P431 LINQ：Language Integrated Query

LINQ，突破传统数据存取机制的伟大创举。 但本章草草结束，仅作初识LINQ之用。

LINQ的组成包括：LINQ to Objects，LINQ to Relational，LINQ to XML。LINQ能接纳的对象，都需要实现IEnumerable或者IQueryable接口。

P449 ADO.NET 3.5

ADO.NET 3.5区别以往的核心在于Entity Framework和Entity Data Model的引入。本章仍是草草结束，重点是了解EF和EDM的概念。

（全书完）

标签：

相关文章