利用LINQ to SQL 增删改查本地数据库

最近趁项目空隙，学习了LINQ，其强大高效易读的数据操作方式令人印象深刻。特别是LINQ to SQL，让用户可以像操作内存对象一样操作SQL数据库。在阅读了《深入理解c#》和参考以下四篇博文后，自己摸索，完成了创建本地SQL数据库和数据表，VS连接数据库，到利用LINQ to SQL增删改查数据库表中信息。

SQL服务器安装：http://www.downcc.com/tech/4135.html

SQL数据库和数据表格创建：http://blog.csdn.net/justdb/article/details/7012675

利用LINQ　to SQL增删改查：http://www.cnblogs.com/yukaizhao/archive/2010/05/13/linq_to_sql_1.html

http://developer.51cto.com/art/200912/166240.htm

SQL服务器和数据库的安装以及表格创建基本是按照前两篇博文来完成，这两篇博文讲的十分详细，是我等小白之福音啊！我在这两步中在本地计算机中创建了一个ExampleDB数据库，在该库中创建了一张PeopleInfoTable表格，表格中有Names,Ages,Addresses,Phones,ID,Nations列，添加了7行数据

完成上述步骤之后，开始利用VS连接数据库

打开VS2010新建控制台应用程序，然后添加LINQ to SQL Class，命名为DbApp.dbml，新建dbml文件之后，可以打开server explorer
在server exploer的窗口中，右击"Data Connections"，选择“Add Connections”

在Add Connection对话框中选择“Data source”为"Microsoft SQL Server(sqlClient)"，“Server name"为"DESKTOP-JOS2U2P”(之前创建的服务器名称)，填写"Use SQL Server Authentication"，在下拉选项框中选择需要操作的数据库名称。

在server exploer的窗口中，点击树形结构中的+以展开PeopleInfoTable表

选中PeopleInfoTable表将其拖到DbApp.dbml的编辑页面中。如果有警示的对话框出现，点击是即可

保存当前的项目，编译项目查看是否存在错误
使用LINQ to SQL增删改查数据库

注意：执行Create、Update或Delete操作需要添加主键，在DbApp.designer.cs中DPeopleInfoTable的属性上添加

LINQ to SQL查询数据库
复制代码
//查询数据库

using (DbAppDataContext context = new DbAppDataContext())
{
       context.Log = Console.Out;
       var query = from p in context.PeopleInfoTables
                   let length = p.Names.Length
                   orderby length
                   select new { Name = p.Names, Length = length };
       foreach (var entry in query)
       {
             Console.WriteLine("{0}:{1}", entry.Name, entry.Length);
       }
}

输出结果

复制代码
LINQ to SQL增加数据库信息
复制代码
//往数据库增加信息

using (DbAppDataContext context = new DbAppDataContext())
{
     context.Log = Console.Out;
     context.PeopleInfoTables.InsertOnSubmit(new PeopleInfoTable
     {
         Names = "Qinan",
         Addresses = "Shaoxing",
         Ages = 24,
         ID = "1231231",
         Nations = "China",
         Phones = "18868875132"
      });
      context.SubmitChanges();
      var query = from p in context.PeopleInfoTables
          select new { Name = p.Names, Address = p.Addresses };
      foreach (var entry in query)
      {
          Console.WriteLine("{0}:{1}", entry.Name, entry.Address);
      }
}

输出结果

复制代码
LINQ to SQL删除数据库信息
复制代码
//删除数据库某条信息

using (DbAppDataContext context = new DbAppDataContext())
{
      context.Log = Console.Out;
      var people = context.PeopleInfoTables.SingleOrDefault(p => p.Names == "Qinan");
      if (people != null)
      {
          context.PeopleInfoTables.DeleteOnSubmit(people);
          context.SubmitChanges();
      }
      var query = from p in context.PeopleInfoTables
           select new { Name = p.Names, Address = p.Addresses };
      foreach (var entry in query)
      {
           Console.WriteLine("{0}:{1}", entry.Name, entry.Address);
      }
}

输出结果

复制代码
LINQ to SQL编辑数据库
复制代码
//编辑数据库中的信息

using (DbAppDataContext context = new DbAppDataContext())
{
    context.Log = Console.Out;
    var people = context.PeopleInfoTables.SingleOrDefault(p => p.Names == "Yuan");
    if (people != null)
    {
        people.Ages = 50;
        people.Addresses = "tanggu";
        context.SubmitChanges();
    }
    var query = from p in context.PeopleInfoTables
         select new {Name = p.Names, Age = p.Ages, Address = p.Addresses};
    foreach (var entry in query)
    {
         Console.WriteLine("{0}:{1},{2}", entry.Name, entry.Age, entry.Address);
    }
}

输出结果

LINQ to SQL增加数据库信息
复制代码
//往数据库增加信息

using (DbAppDataContext context = new DbAppDataContext())
{
     context.Log = Console.Out;
     context.PeopleInfoTables.InsertOnSubmit(new PeopleInfoTable
     {
         Names = "Qinan",
         Addresses = "Shaoxing",
         Ages = 24,
         ID = "1231231",
         Nations = "China",
         Phones = "18868875132"
      });
      context.SubmitChanges();
      var query = from p in context.PeopleInfoTables
          select new { Name = p.Names, Address = p.Addresses };
      foreach (var entry in query)
      {
          Console.WriteLine("{0}:{1}", entry.Name, entry.Address);
      }
}

输出结果

LINQ to SQL删除数据库信息
复制代码
//删除数据库某条信息

using (DbAppDataContext context = new DbAppDataContext())
{
      context.Log = Console.Out;
      var people = context.PeopleInfoTables.SingleOrDefault(p => p.Names == "Qinan");
      if (people != null)
      {
          context.PeopleInfoTables.DeleteOnSubmit(people);
          context.SubmitChanges();
      }
      var query = from p in context.PeopleInfoTables
           select new { Name = p.Names, Address = p.Addresses };
      foreach (var entry in query)
      {
           Console.WriteLine("{0}:{1}", entry.Name, entry.Address);
      }
}

输出结果

LINQ to SQL编辑数据库
复制代码
//编辑数据库中的信息

using (DbAppDataContext context = new DbAppDataContext())
{
    context.Log = Console.Out;
    var people = context.PeopleInfoTables.SingleOrDefault(p => p.Names == "Yuan");
    if (people != null)
    {
        people.Ages = 50;
        people.Addresses = "tanggu";
        context.SubmitChanges();
    }
    var query = from p in context.PeopleInfoTables
         select new {Name = p.Names, Age = p.Ages, Address = p.Addresses};
    foreach (var entry in query)
    {
         Console.WriteLine("{0}:{1},{2}", entry.Name, entry.Age, entry.Address);
    }
}