整理C#LinQ2012-10-09

手动使用linq对象

1,创建model对象,studentDB.cs

#region Using directives
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq.Mapping;
using System.Data.Linq;
using System.Configuration;
#endregion


namespace CSLinqToSQL.Manual
{
    [Table(Name = "Person")]
    public class Student
    {
        public Student()
        {
            this._Grades = new EntitySet<CourseGrade>();

            // Set the default value of the column PersonCategory
            this.PersonCategory = 1;
        }

        // Refer to the ID column in database.
        [Column(IsPrimaryKey = true, Name = "PersonID", IsDbGenerated = true,
            AutoSync = AutoSync.OnInsert, DbType = "Int NOT NULL IDENTITY")]
        public int PersonID
        {
            get;
            set;
        }

        // Refer to the LastName column in database
        [Column]
        public string LastName
        {
            get;
            set;
        }

        // Refer to the FirstName column in database
        [Column]
        public string FirstName
        {
            get;
            set;
        }

        // Refer to the EnrollmentDate column in database
        [Column]
        public System.Nullable<System.DateTime> EnrollmentDate
        {
            get;
            set;
        }

        // Refer to the PersonCategory column in database
        [Column]
        public short PersonCategory
        {
            get;
            set;
        }

        // Refer to the FK of FK_CourseGrade_Student 
        private EntitySet<CourseGrade> _Grades;
        [Association(Storage = "_Grades", OtherKey = "StudentID")]
        public EntitySet<CourseGrade> CourseGrades
        {
            get { return this._Grades; }
            set { this._Grades.Assign(value); }
        }

        // Refer to the image column in database
        [Column(Name = "Picture")]
        public Byte[] Picture
        {
            get;
            set;
        }
    }

    [Table(Name = "Course")]
    public class Course
    {
        public Course()
        {
            this._Grades = new EntitySet<CourseGrade>();
        }

        // Refer to the CourseID column in database
        [Column(IsPrimaryKey = true, Name = "CourseID")]
        public int CourseID
        {
            get;
            set;
        }

        // Refer to the Title column in database
        [Column(Name = "Title")]
        public string Title
        {
            get;
            set;
        }

        // Refer to the Credits column in database
        [Column]
        public int Credits
        {
            get;
            set;
        }

        // Refer to the FK for FK_CourseGrade_Course
        private EntitySet<CourseGrade> _Grades;
        [Association(Storage = "_Grades", OtherKey = "CourseID")]
        public EntitySet<CourseGrade> Grades
        {
            get { return this._Grades; }
            set { this._Grades.Assign(value); }
        }
    }

    [Table(Name = "CourseGrade")]
    public class CourseGrade
    {
        public CourseGrade()
        {
            this._Course = default(EntityRef<Course>);
            this._Student = default(EntityRef<Student>);
        }

        // Refer to the EnrollmentID column in database
        [Column(IsPrimaryKey = true, Name = "EnrollmentID", IsDbGenerated = true)]
        public int EnrollmentID
        {
            get;
            set;
        }

        // Refer to the CourseID column in database
        [Column(Name = "CourseID")]
        public int CourseID
        {
            get;
            set;
        }

        // Refer to the StudentID column in database
        [Column(Name = "StudentID")]
        public int StudentID
        {
            get;
            set;
        }

        // Refer to the Grade column in database
        [Column]
        public decimal Grade
        {
            get;
            set;
        }

        // Refer to the FK of FK_CourseGrade_Course
        private EntityRef<Course> _Course;
        [Association(Storage = "_Course", ThisKey = "CourseID")]
        public Course Course
        {
            get { return this._Course.Entity; }
            set { this._Course.Entity = value; }
        }

        // Refer to the FK of FK_CourseGrade_Student 
        private EntityRef<Student> _Student;
        [Association(Storage = "_Student", ThisKey = "StudentID")]
        public Student Student
        {
            get { return this._Student.Entity; }
            set { this._Student.Entity = value; }
        }
    }

    public class SchoolDataContext : DataContext
    {
        public Table<Student> Students;
        public Table<Course> Courses;
        public Table<CourseGrade> CourseGrades;
        public SchoolDataContext(string connection) : base(connection) { }
    }
}

2,手动操作

/// <summary>
    /// 读取图片路径转换成字节数
    /// </summary>
    /// <param name="path">The path of the image file.</param>
    /// <returns>The output of the array.</returns>
    private static byte[] ReadImage(string path)
    {
        try
        {
            // Open the image file
            using (FileStream fs = new FileStream(path, FileMode.Open,
                FileAccess.Read))
            {
                // Create an array of bytes
                byte[] bPicture = new byte[fs.Length];

                // Read the image file
                fs.Read(bPicture, 0, Convert.ToInt32(fs.Length));

                return bPicture;
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(
                "The application throws the error: {0}", ex.Message);
            return null;
        }
    }
 /// <summary>
    /// 下面的方法适用于手动创建的类
    /// </summary>
    private static void QueryDBByManuallyCreatedClasses()
    {
        Console.WriteLine("Query using the manually-created classes.");
        try
        {
            string connStr = ConfigurationManager.ConnectionStrings[
                "SQLServer2005DB"].ConnectionString;

            // DataContext takes a connection string
            CSLinqToSQL.Manual.SchoolDataContext db = new
                CSLinqToSQL.Manual.SchoolDataContext(connStr);
            Console.WriteLine("\nAdd a new student to the Person table");


            // 创建一个新学生。
            // 我们不需要在此处设置的 PersonCategory 值，如果我们想要
            // 默认值将设置为此数据库中的列。
            CSLinqToSQL.Manual.Student student = new CSLinqToSQL.Manual.Student
            {
                FirstName = "Rongchun",
                LastName = "Zhang",
                EnrollmentDate = DateTime.Now,
                Picture = ReadImage(@"MSDN.jpg"),
            };

            // 添加一个新对象到学生表
            db.Students.InsertOnSubmit(student);

            // 提交数据
            db.SubmitChanges();

            // 查询学生
            Console.WriteLine("\nQuery students whose first name is Roger:");
            var students = from p in db.Students
                           where p.FirstName == "Roger"
                           select p;

            foreach (var p in students)
                Console.WriteLine("ID = {0}, Name = {1} {2}", p.PersonID,
                    p.FirstName, p.LastName);

            // 连表查询
            Console.WriteLine("\nQuery max. grade of each course:");
            var courses =
                from grade in db.CourseGrades
                group grade by grade.CourseID into g
                join cur in db.Courses on g.Key equals cur.CourseID
                select new
                {
                    CourseID = g.Key,
                    Title = cur.Title,
                    TopGrade = g.Max(gra => gra.Grade)
                };

            foreach (var c in courses)
                Console.WriteLine("Course = {0}, TopGrade = {1}",
                    c.Title, c.TopGrade);

            Console.WriteLine("\nQuery all grades that Nino got");

            var grades =
               from stu in db.Students
               from gra in stu.CourseGrades
               join cur in db.Courses on gra.CourseID equals cur.CourseID
               where stu.FirstName == "Nino"
               select new
               {
                   Stduent = stu,
                   Course = cur,
                   CourseGrade = gra
               };

            foreach (var grade in grades)
            {
                Console.WriteLine("FirstName = {0}, Course= {1}, Grade= {2}",
                    grade.Stduent.FirstName,
                    grade.Course.Title,
                    grade.CourseGrade.Grade);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine("The application throws the error: {0}", e.Message);
        }
    }