[译]构造一个简单的LINQ数据库应用程序
By robot-v1.0
本文链接 https://www.kyfws.com/applications/constructing-a-simple-linq-database-application-zh/
版权声明 本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!
- 7 分钟阅读 - 3442 个词 阅读量 0[译]构造一个简单的LINQ数据库应用程序
原文地址:https://www.codeproject.com/Articles/20674/Constructing-a-Simple-LINQ-Database-Application
原文作者:Matthew Hazlett
译文由本站 robot-v1.0 翻译
前言
More about the technology, but the demo app is ASP.NET
有关该技术的更多信息,但演示应用程序为ASP.NET
目录(Table of Contents)
数据库(The Database) 应用程序(The Application) 创建LINQ类(Creating the LINQ Class) 包装类(The Wrapper Class) 应用程序(The Application) SQL查询(SQL Queries) 演示申请(Demo Application)
更新(Updates)
-
2007年12月2日(Dec, 02 2007)
- 已更新以与.NET Framework 3.5发行版一起使用.(Updated to work with the release of .NET Framework 3.5.)
- **Web.config(Web.config)**已更新以反映新发布的程序集.(was updated to reflect the newly released assemblies.)
- **default.aspx.cs(default.aspx.cs)**103行用途(line 103 uses)
DeleteOnSubmit
代替(instead of)Remove
.(.) - **default.aspx.cs(default.aspx.cs)**第153行的用途(line 153 uses)
InsertOnSubmit
代替(instead of)Add
.(.) - **_Addressbook.dbml(_Addressbook.dbml)**已重新生成以符合新格式.(has been regenerated to bring compliance with the new format.)
-
2007年10月1日(Oct, 01 2007)
- 通过使用动态LINQ,向打包的应用程序以及在线演示中添加了分页和排序功能.(Added paging and sorting to the packaged application, as well as the online demo, by utilizing dynamic LINQ.)
数据库(The Database)
在此示例应用程序中,我使用的是SQL 2005数据库,但您可以轻松地将XML文件用作数据源.我们将从创建数据库开始(In this sample application I am using a SQL 2005 database, but you could just as easily use an XML file as a data source. We will start off by creating the database) AddressBook
.(.)
我建了一张桌子(I built a table called) Addresses
在该数据库中.(inside that database.)
这张桌子很标准.您唯一要做的就是(This table is pretty standard; the only thing you must do is)将ID字段设置为带有自动递增标志的主键(make the ID field a primary key with an auto-increment flag).如果您不将ID作为主键,则您的应用程序将(. If you don’t make the ID a primary key, your application will be)只读(READONLY).(.)
应用程序(The Application)
创建LINQ类(Creating the LINQ Class)
-
首先,我们仅使用Visual Studio 2008中的漂亮生成器来创建LINQ to SQL类.我叫课(First, we simply use the nice little generator in Visual Studio 2008 to create our LINQ to SQL class. I called the class)
_AddressBook.dbml
.(.) -
当设计图面出现时,拖动(When the design surface comes up, drag the)
Addresses
表格放到设计窗格中.现在将表重命名为(table onto the design pane. Now rename the table to)Addresses
.通过单击设计窗格中蓝色框顶部的名称来执行此操作.(. Do this by clicking the name in the top of the blue box in the design pane.) -
转到"属性"窗口并编辑以下属性(Go over to the Properties window and edit the properties for)
_AddressBookDataContext
.确保使用正确的命名空间设置它们,并从中提取连接字符串(. Make sure they are set with the proper namespace and that the connection string is pulled from)web.config(web.config).(.) -
接下来,编辑(Next, edit the properties of the)
Address
数据类.默认属性应该可以.(data class. The default properties should be ok.) -
现在保存并关闭设计器.(Now save and close the designer.)
包装类(The Wrapper Class)
创建一个名为(Create a class called) AddressBook
.这基本上是一个包装类(. This is basically a wrapper class to wrap around) _AddressBook
.这个课很简单.它所做的只是初始化对象并提供对表的轻松访问.唯一的麻烦是确保名称空间与您告诉的名称空间相同(. This class is so simple; all it does is initialize the object and provide easy access to your table. The only gotcha(s) are to make sure the namespace is the same one you told) _AddressBook
使用并确保(to use and to make sure) AddressBookConnectionString
在(is in)web.config(web.config).(.)
using System.Configuration;
/// <span class="code-SummaryComment"><summary></span>
/// Summary description for AddressBook
/// <span class="code-SummaryComment"></summary></span>
namespace Clarity.Database
{
public class AddressBook
{
private _AddressBookDataContext _AddressBook;
public AddressBook()
{
string conn = ConfigurationManager.ConnectionStrings[
"AddressBookConnectionString"].ToString();
_AddressBook = new _AddressBookDataContext(conn);
}
public System.Data.LINQ.Table<addresses> Addresses
{
get { return _AddressBook.Addresses; }
}
}
}
应用程序(The Application)
我不会讨论如何构建应用程序.这不是重点.但是,我将向您展示如何通过LINQ轻松访问您的表.如果您需要完整的应用程序,请参阅本文的结尾.现在进行初始化和访问数据库的繁琐步骤.坚持,稍等;这有点混乱.(I’m not going to go into how to build an application; that’s not the point here. I will, however, show you how to easily access your table via LINQ. If you want the full application, it’s at the end of the article. Now for the tedious step of initializing and accessing the database. Hold on; this gets a little messy.)
private AddressBook thisAddressBook = new AddressBook();
而已!现在,您可以使用(That’s it! You now have complete access to your database by using the) thisAddressBook
目的.使用该对象,您可以通过操作一个简单的通用类来查询,插入,更新和删除数据库表中的项目.(object. With this object you can query, insert, update and delete items from the database table by manipulating a simple generic class.)
将数据加载到网格中(To Load the Data into the Grid)
private void LoadGrid()
{
GridView1.DataSource = thisAddressBook.Addresses.OrderBy(c =>
该查询输出的数据按以下顺序排序(The data output by this query is ordered by) LastName
.在lambda表达式中(. In the lambda expression)(c => c.LastName)((c => c.LastName)),c代表您的对象,很容易成为obj(, c represents your object and could just as easily have been obj),(,)如(as in)(obj => obj.LastName)((obj => obj.LastName)).您选择的任何对象名称都可以使用;您要做的就是在整个表达式中保持一致.(. Whatever object name you choose will work; all you have to do is be consistent throughout the expression.)
筛选网格中的数据(To Filter Data in the Grid)
private void LoadGrid(string filter)
{
GridView1.DataSource = thisAddressBook.Addresses.Where(
c =>
这会将经过过滤的数据子集从表中加载到数据源中,其中(This loads a filtered subset of data from your table into your data source, where the) LastName
栏位必须以(field must begin with what is specified by the) filter
变量.(variable.)
删除记录(Deleting a record)
protected void GridView1_RowDeleting(object sender,
System.Web.UI.WebControls.GridViewDeleteEventArgs e)
{
int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value);
Addresses thisAddress = thisAddressBook.Addresses.First(x =>
第四行的作业((The assignment on the fourth line () Address thisAddress
…)在数据库中查询第一次出现的指定ID,并将其分配给(…) queries the database for the first occurrence of the specified ID and assigns it to) thisAddress
.然后(. Then the) thisAddressBook
通知对象(我们的表)从表中删除条目并保存更改.(object (our table) is told to remove the entry from the table and save the changes.)
插入/更新记录(Inserting/Updating a Record)
protected void AddressUpdated(object sender, UserData.UpdateEvent e)
{
Addresses thisAddress;
int id = e.Address.id;
// If this value is 0 then add a record
if (id != 0)
thisAddress = thisAddressBook.Addresses.First(x =>
此功能需要更改或更新(This function takes an altered or new) Addresses
对象并将其更新或插入到表中.(object and updates or inserts it into the table.)
SQL查询(SQL Queries)
我对查询实际上命令数据库执行的操作很感兴趣.它是否加载了所有记录,然后对其进行过滤?它是在调整查询以仅获取所请求的信息吗?可以说我对结果真的很满意…(I was interested in what the queries were actually commanding the database to do. Was it loading in all the records and then filtering them afterwards? Was it tailoring the queries to only get the info that was requested? Let’s just say that I was really happy with the results…)
LoadQuery的结果(Results from LoadQuery)
SELECT [t0].[id], [t0].[FirstName],
[t0].[LastName], [t0].[Address1], [t0].[City], [t0].[State], [t0].[Zip],
[t0].[Email] FROM [dbo].[Addresses] AS [t0] ORDER BY [t0].[LastName]
好的,这里没有什么不寻常的.(Ok, there’s nothing out of the ordinary here.)
筛选查询的结果(Results from the Filtered Query)
exec sp_executesql N'SELECT
[t0].[id], [t0].[FirstName], [t0].[LastName], [t0].[Address1], [t0].[City],
[t0].[State], [t0].[Zip], [t0].[Email] FROM [dbo].[Addresses] AS [t0] WHERE
[t0].[LastName] LIKE @p0 ORDER BY [t0].[LastName]',N'@p0 nvarchar(2)',@p0=N'H%'
啊,它使用SP作为一种安全措施来抵御SQL注入攻击.(Ahh, it’s using an SP as a security measure to ward off SQL injection attacks.)
删除查询的结果(Results from the Delete Query)
exec sp_executesql N'DELETE FROM
[dbo].[Addresses] WHERE ([id] = @p0) AND ([FirstName] = @p1) AND ([LastName] =
@p2) AND ([Address1] = @p3) AND ([City] = @p4) AND ([State] = @p5) AND ([Zip]
= @p6) AND ([Email] = @p7)',N'@p0 int,@p1 nvarchar(5),@p2 nvarchar(5),@p3
nvarchar(15),@p4 nvarchar(10),@p5 nvarchar(2),@p6 nvarchar(5),
@p7 nvarchar(15)',@p0=4,@p1=N'Kelly',@p2=N'Smith',@p3=N'123
Fake Street',@p4=N'Manchester',@p5=N'NH',@p6=N'03102',@p7=N'kelly@gmail.com'
这和上面一样.(This is the same as above.)
插入查询的结果(Results from the Insert Query)
exec sp_executesql N'INSERT INTO
[dbo].[Addresses]([FirstName], [LastName], [Address1], [City], [State], [Zip],
[Email]) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6)
SELECT [t0].[id] FROM
[dbo].[Addresses] AS [t0] WHERE [t0].[id] = (SCOPE_IDENTITY()) ',N'@p0
varchar(7),@p1 varchar(6),@p2 varchar(15),@p3 varchar(9),@p4 varchar(2),@p5
varchar(5),@p6 varchar(18)',@p0='John',@p1='Smith',@p2='123 Fake
Street',@p3='Somewhere',@p4='CT',@p5='03102',@p6='nobody@nowhere.com'
似乎通过插入操作,他们可以重新查询数据以检索新创建的主键的值.(It seems that with an insert, they re-query the data to retrieve the newly created primary key’s value.)
演示申请(Demo Application)
该演示应用程序是在Visual Studio 2008中构建的,并使用C#编写.这绝不是成品;这只是您可以使用LINQ进行的快速演示.(The demo application was built in Visual Studio 2008 and written in C#. By no means is this a finished product; it is just a quick demonstration of what you can do with LINQ.)
历史(History)
-
2007年9月30日-发布原始版本(30 September, 2007 – Original version posted)
-
2007年11月8日-文章内容已更新(8 November, 2007 – Article content updated)
-
2007年12月14日-下载已更新;文章已编辑并移至CodePoroject.com的主要文章库(14 December, 2007 – Download updated; article edited and moved to the main CodePoroject.com article base)
- 已更新以与.NET Framework 3.5发行版一起使用(Updated to work with the release of .NET Framework 3.5)
- **Web.config(Web.config)**已更新以反映新发布的程序集.(was updated to reflect the new released assemblies.)
- **default.aspx.cs(default.aspx.cs)**第103行使用DeleteOnSubmit而不是Remove.(line 103 uses DeleteOnSubmit instead of Remove.)
- **default.aspx.cs(default.aspx.cs)**第153行使用nsertOnSubmit而不是Add.(line 153 uses nsertOnSubmit instead of Add.)
- **_Addressbook.dbml(_Addressbook.dbml)**重新生成以符合新格式.(regenerated to bring into compliance with the new format.)
许可
本文以及所有相关的源代码和文件均已获得The Code Project Open License (CPOL)的许可。
C#3.0 C# C#2.0 .NET1.1 .NET2.0 .NET1.0 VS2005 VS2008 VS.NET2003 Dev 新闻 翻译