C#数据库操作之LINQ to SQL技术详解_C#

一、linq to sql技术概述

linq to sql 是 .net framework version 3.5 的一个组件，提供了用于将关系数据作为对象管理的运行时基础结构。

在 linq to sql 中，将关系数据库的数据模型，映射到一种对象模型中，该对象模型省指开发人员使用的编程语言表示的对象。通俗来讲，就是将数据库中的关系型数据，映射到代码里的实例对象。当应用程序运行时，linq to sql 会将对象模型中的语言集成查询转换为 sql语句，然后将它们发送到数据库进行执行。当数据库返回结果时，linq to sql 会将它们转换回你可以用你自己的编程语言处理的对象。

【tips】使用visual studio的开发者可以使用对象关系设计器the object relational designer，它为实现linq to sql的许多特性提供了一个用户界面。

二、使用linq to sql 进行简单操作.

通过使用 linq to sql，使得访问 sql 数据库，就像访问内存中的集合一样。

以下代码，创建一个northwnd对象，代表northwnd数据库，并从customers表中查询出城市为“london”的数据，返回一个集合，赋值给companynamequery，之后通过循环，输出集合内的元素。

// northwnd inherits from system.data.linq.datacontext.
northwnd nw = new northwnd(@"northwnd.mdf");
// or, if you are not using sql server express
// northwnd nw = new northwnd("database=northwind;server=server_name;integrated security=sspi");

var companynamequery =
    from cust in nw.customers
    where cust.city == "london"
    select cust.companyname;

foreach (var customer in companynamequery)
{
    console.writeline(customer);
}

通过上述例子中，相必您已经体会到了linq to sql技术的强大之处了，接下来，我将带领你们逐步的吸收linq to sql技术。

三、如何动态创建数据库

在linq to sql技术中，对象模型和关系型数据库之间是映射关系。要想启用映射关系，一般可以通过使用基于特性的映射，或者是使用描述关系数据库结构的外部映射文件。在这两种情况下，如果你有足够的关系型数据库的信息，那么就可以通过使用datacontext.createdatabase()方法来创建数据库实例。

datacontext.createdatabase()方法，仅在对象模型中被编码信息的范围内创建数据库的副本。对象模型中的映射文件和特性，不会对现有数据库结构的所有内容进行编码。映射信息不表示用户自定义函数、存储过程、触发器或检查约束的内容。这种行为对于各种数据库来说已经足够了。

如果已知的数据提供者是可用的(如sql server 2008)，那么你可以在任何情况下使用datacontext.createdatabase()方法。

→构建一个自动安装在客户系统上的应用程序。
→构建一个客户机应用程序，该应用程序需要一个本地数据库来保存其脱机状态。

您还可以使用datacontext.createdatabase()方法来使用sql server，通过创建.mdf文件或目录名，具体是哪种取决于您的连接字符串。linq to sql 使用连接字符串来定义将要创建的数据库，以及要创建数据连接的服务器。

【tips】尽可能的，请使用windows集成安全性连接到数据库，以便在连接字符串中不需要密码。

案例一：

该例展示了一个如何创建一个名为mydvds.mdf数据库的方式。

//在使用该例展示的方法之前，必须进行以下两个步骤：
//1、添加引用system.data.linq；
//2、使用using语句，添加命名空间，否则报错
//   using tableattribute = system.data.linq.mapping.tableattribute;
//   using columnattribute = system.data.linq.mapping.columnattribute;

//table特性，创建了数据库中的名为dvdtable的表与该类之间的映射关系。
[table(name = "myorders")]
public class order
{
	//column特性，创建了dvdtable表中同名列与该类成员属性之间的映射关系。
	[column(isprimarykey = true)]
	public int id;
	[column]
	public string name;
	[column]
	public datetime date;

	public override string tostring()
	{
		return "order对象:{" + id + "，" + name + "," + date + "}";
	}
}

//自定义类 ，继承datacontext类。
public class myorders : datacontext
{
    public myorders(string connection) : base(connection)
    {
    }

    public table<order> orders;
}

//创建一个myorders 类的对象db，该类继承自datacontext类。
myorders myorders = new myorders(@"d:\myfirstorder.mdf");

public void createdatabase()
{
    if (myorders.databaseexists())
    {
        //先判断该路径中，是否存在该数据库文件，如果存在，则删除。
        messagebox.show("deleting old database...");
        myorders.deletedatabase();
    }
    //myorders对象创建数据库
    myorders.createdatabase();
    //释放datacontext实例，所使用的的所有资源
    //myorders.dispose();
}

【注意】

myorders myorders = new myorders(@"d:\myfirstorder4.mdf");

语句中的myorders对象，最好设置成全局变量，如果设置成局部变量，后续在重复的调用方法时，可能会出现“该数据库已存在，请选择其他数据库名称”异常。

在构建自动安装在客户系统上的应用程序时，请先查看数据库是否已经存在，并在创建新数据库之前删除它。datacontext类提供databaseexists()和deletedatabase()方法来帮助您完成这个过程。

四、如何向数据库中insert数据

通过向关联的linq to sql里的table<tentity>集合添加对象，然后向数据库提交更改，您就可以将行插入到数据库中。link to sql将您提交的更改，翻译成适当的sql insert语句，然后提交到数据库执行的。

【tips】您也可以为linq to sql里默认的insert数据库、update数据库、delete数据库等操作进行重载。

以下步骤假设有一个连接到northwind数据库的datacontext连接。

1、创建一个新对象，包含要提交的列数据。
2、将新对象添加到与数据库中目标表相关联的linq to sql table集合中。
3、提交更改到数据库中。

下面代码展示了创建一个新的order对象（order是用户自定义类），然后为它填充适当的属性值。之后将新的order对象添加到order集合里。最后，将更改提交到数据库，作为orders表中一个新的行。

// create a new order object.order class is user defined class.
order ord = new order
{
    id = 12000,
    name = "harry potty",
    orderdate = datetime.now
    // …
};

// add the new object to the orders collection.
system.data.linq.table<order> table = myorders.gettable<order>();
table.insertonsubmit(ord);

// submit the change to the database.
try
{
    myorders.submitchanges();
}
catch (exception e)
{
    messagebox.show(e.message);
    // make some adjustments.
    // ...
    // try again.
    myorders.submitchanges();
}
//释放资源
//myorders.dispose();

【注意】在向数据库中的表里，insert数据或者后续的update、delete操作时，无论您对对象做了多少项更改，都只是在更改内存中的副本。您并未对数据库中的实际数据做任何更改。直到您对显式调用datacontext里的submitchanges()方法，您所做的更改才会传输到服务器。

五、如何从数据库中query数据

linq to sql 中的查询使用与 linq 中的查询相同的语法。唯一的差异是 linq to sql 查询中引用的对象映射到数据库中的元素。有兴趣的朋友，可以看下 linq 查询知识。

下面的代码，展示了一个简单的查询请求，查询出id等于12000的订单，并将查询结果输出。

myorders myorders = new myorders(@"c:\northwnd.mdf");
system.data.linq.table<order> table = myorders.gettable<order>();

// query for customers in london.
iqueryable<customer> custquery =
    from order in myorders.orders
    where order.id == 12000
    select order;

//将查询结果输出
foreach( var o in result)
{
	console.writeline(o.tostring());
}

从数据库中查询数据，还有更多复杂的操作，后续我会继续整理更为详细的数据库查询操作，可以先关注下。

六、如何update表中的数据

通过修改与linq to sql 的table集合相关联的对象的成员值，然后将更改提交给数据库，那么就可以更新数据库中的某行数据。linq to sql 将你的改变转换为相对应的sql update语句。

将某行数据更新到数据库中的步骤如下：

1、在数据库中查询要更新的行。

2、对第1步的linq to sql 查询结果里的成员值进行所需的更改。

3、将更改提交到数据库里。

下面的示例在数据库中查询订单#11000，然后更改结果order对象中的name和date的值。最后，对这些成员值的更改作为name和date列中的更改提交给数据库。

// query the database for the row to be updated.
var query =
    from ord in myorders.orders
    where ord.id== 11000
    select ord;

// execute the query, and change the column values
// you want to change.
foreach (order ord in query)
{
    ord.name = "hong lou meng";
    ord.date = datetime.now;
    // insert any additional changes to column values.
}

// submit the changes to the database.
try
{
    myorders.submitchanges();
}
catch (exception e)
{
    messagebox.show(console.writeline(e));
    // provide for exceptions.
}
//释放datacontext对象占用的资源。
//myorders.dispose();

七、如何如何delete表中的数据

可以通过从与表相关联的集合中删除相应的linq to sql对象，来完成对数据库中某行的删除。linq to sql 会将删除操作转变为适当的sql delete语句。

linq to sql不支持或不识别级联删除操作。如果你想删除表中有约束的一行，你必须完成以下其中一项条件：

1、在数据库的外键约束中设置on delete cascade规则。

2、使用您自己的代码先删除阻止父对象被删除的子对象。

如果无法保证上述至少一个条件成立，则将抛出异常。请参阅本主题的第二个代码示例。

删除数据库中某一行的操作如下：

1、在数据库中查询需要删除的行。
2、调用deleteonsubmit方法。
3、将删除操作提交到数据库。

下面的第1个代码示例向数据库查询属于order# 11000的订单详细信息，将这些订单详细信息标记为删除，并将这些更改提交给数据库。

table<order> orders = myorders.gettable<order>();
// query the database for the rows to be deleted.

var deleteresult =
    from ord in orders
    where ord.id == 11000
    select ord;

foreach (var ord in deleteresult )
{
    orders.deleteonsubmit(ord );
}

try
{
    myorders.submitchanges();
}
catch (exception e)
{
    messagbox.show(e.message);
    // provide for exceptions.
}

在第二个代码示例中，目标是删除一个订单(#10250)。代码首先检查orderdetails表，以查看要删除的订单在那里是否有子订单。如果订单有子订单，则首先将子订单标记为要删除，然后将订单标记为要删除。datacontext将实际的删除按正确的顺序排列，以便发送到数据库的删除命令遵守数据库约束。

//northwnd myorders = new northwnd(@"c:\northwnd.mdf");

myorders myorders = new myorders(@"d:\myfirstorder.mdf");

myorders.log = console.out;

// specify order to be removed from database
int reqorder = 10250;

// fetch orderdetails for requested order.
var orddetailquery =
    from odq in myorders.orderdetails
    where odq.orderid == reqorder
    select odq;

foreach (var selecteddetail in orddetailquery)
{
    messagebox.show(selecteddetail.tostring());
    myorders.orderdetails.deleteonsubmit(selecteddetail);
}

// display progress.
messagebox.show("detail section finished.");

// determine from detail collection whether parent exists.
if (orddetailquery.any())
{
    messagebox.show("the parent is present in the orders collection.");
    // fetch order.
    try
    {
        var ordfetch =
            (from ofetch in myorders.orders
             where ofetch.orderid == reqorder
             select ofetch).first();
        myorders.orders.deleteonsubmit(ordfetch);
        messagebox.show("{0} orderid is marked for deletion.", ordfetch.orderid);
    }
    catch (exception e)
    {
        messagebox.show(e.message);
    }
}
else
{
    messagebox.show("there was no parent in the orders collection.");
}

// display progress.
messagebox.show("order section finished.");

try
{
    myorders.submitchanges();
}
catch (exception e)
{
    messagebox.show(e.message);
}

// display progress.
messagebox.show("submit finished.");