Linq To Sql CRUD操作示例

　　昨天简要的看了一下Linq To Sql 的操作方式，写了一个简单的CRUD操作的例子。去掉繁琐的理论，直接贴代码。【源代码下载】

　　我是新建了一个本地数据库，数据库字段如下：

 

根据这个结构，设计了一个实体类Book：

    [Table]
    public class Book
    {
        /// <summary>
        /// 图书ID
        /// </summary>
        [Column(IsPrimaryKey=true,DbType=("bigint"), IsDbGenerated=true)]
        public Int64 bId { get; set; }
        /// <summary>
        /// 图书名称
        /// </summary>
        [Column]
        public string Title { get; set; }
        /// <summary>
        /// 单价
        /// </summary>
        [Column(DbType = "float")]
        public float Price { get; set; }
        /// <summary>
        /// 作者
        /// </summary>
        [Column]
        public string Author { get; set; }
        /// <summary>
        /// ISBN号
        /// </summary>
        [Column]
        public string ISBN { get; set; }
        /// <summary>
        /// 记录的版本号
        /// </summary>
        [Column(DbType = "rowversion", IsVersion=true)]
        public byte[] Stamp { get; set; }
    }

 

下面就是对它的CRUD操作：

　　　　　#region Linq To Sql [CRUD操作]

        private static void LinqToSql_Select()
        {
            DataContext context = new DataContext(string.Format(@"Data Source={0}DB.sdf", AppDomain.CurrentDomain.BaseDirectory));
            context.Log = Console.Out;  //输出Sql的执行过程，方便跟踪调试

            var result = from book in context.GetTable<Book>()
                         where book.Price > 50 && book.Title.Contains("C#")
                         orderby book.Price descending
                         select book;

            Console.WriteLine(result.Count());

            Console.WriteLine("单价大于50元的书：");
            foreach (var item in result)    //在遍历的时候，才正真的去执行查询
            {
                Console.WriteLine(item.Title);
            }
        }

        private static void LinqToSql_Insert()
        {
            DataContext context = new DataContext(string.Format(@"Data Source={0}DB.sdf", AppDomain.CurrentDomain.BaseDirectory));
            context.Log = Console.Out;  //输出Sql的执行过程，方便跟踪调试

            ITable books = context.GetTable<Book>();

            Book book = new Book() { Title = "C#高级编程", Author = "Wrox", ISBN = "1235=23-233", Price = 138 };
            books.InsertOnSubmit(book);
            context.SubmitChanges();

            Console.WriteLine(book.bId);
        }

        private static void LinqToSql_Update()
        {
            DataContext context = new DataContext(string.Format(@"Data Source={0}DB.sdf", AppDomain.CurrentDomain.BaseDirectory));
            context.Log = Console.Out;  //输出Sql的执行过程，方便跟踪调试

            var books = from b in context.GetTable<Book>()
                       where b.bId == 5
                       select b;

            Book book = books.First<Book>();
            book.Title = "C#高级编程（第五版）";

            context.SubmitChanges();
        }

        private static void LinqToSql_Delete()
        {
            DataContext context = new DataContext(string.Format(@"Data Source={0}DB.sdf", AppDomain.CurrentDomain.BaseDirectory));
            context.Log = Console.Out;  //输出Sql的执行过程，方便跟踪调试

            Table<Book> books = context.GetTable<Book>();

            var query = from b in books
                        where b.bId == 5
                        select b;
            Book book = query.First<Book>();

            books.DeleteOnSubmit(book);
            context.SubmitChanges();
        }

        #endregion