手动使用linq对象

1,创建model对象,studentDB.cs

View Code 
  1 #region Using directives
  2 using System;
  3 using System.Collections.Generic;
  4 using System.Linq;
  5 using System.Text;
  6 using System.Data.Linq.Mapping;
  7 using System.Data.Linq;
  8 using System.Configuration;
  9 #endregion
 10 
 11 
 12 namespace CSLinqToSQL.Manual
 13 {
 14     [Table(Name = "Person")]
 15     public class Student
 16     {
 17         public Student()
 18         {
 19             this._Grades = new EntitySet<CourseGrade>();
 20 
 21             // Set the default value of the column PersonCategory
 22             this.PersonCategory = 1;
 23         }
 24 
 25         // Refer to the ID column in database.
 26         [Column(IsPrimaryKey = true, Name = "PersonID", IsDbGenerated = true,
 27             AutoSync = AutoSync.OnInsert, DbType = "Int NOT NULL IDENTITY")]
 28         public int PersonID
 29         {
 30             get;
 31             set;
 32         }
 33 
 34         // Refer to the LastName column in database
 35         [Column]
 36         public string LastName
 37         {
 38             get;
 39             set;
 40         }
 41 
 42         // Refer to the FirstName column in database
 43         [Column]
 44         public string FirstName
 45         {
 46             get;
 47             set;
 48         }
 49 
 50         // Refer to the EnrollmentDate column in database
 51         [Column]
 52         public System.Nullable<System.DateTime> EnrollmentDate
 53         {
 54             get;
 55             set;
 56         }
 57 
 58         // Refer to the PersonCategory column in database
 59         [Column]
 60         public short PersonCategory
 61         {
 62             get;
 63             set;
 64         }
 65 
 66         // Refer to the FK of FK_CourseGrade_Student 
 67         private EntitySet<CourseGrade> _Grades;
 68         [Association(Storage = "_Grades", OtherKey = "StudentID")]
 69         public EntitySet<CourseGrade> CourseGrades
 70         {
 71             get { return this._Grades; }
 72             set { this._Grades.Assign(value); }
 73         }
 74 
 75         // Refer to the image column in database
 76         [Column(Name = "Picture")]
 77         public Byte[] Picture
 78         {
 79             get;
 80             set;
 81         }
 82     }
 83 
 84     [Table(Name = "Course")]
 85     public class Course
 86     {
 87         public Course()
 88         {
 89             this._Grades = new EntitySet<CourseGrade>();
 90         }
 91 
 92         // Refer to the CourseID column in database
 93         [Column(IsPrimaryKey = true, Name = "CourseID")]
 94         public int CourseID
 95         {
 96             get;
 97             set;
 98         }
 99 
100         // Refer to the Title column in database
101         [Column(Name = "Title")]
102         public string Title
103         {
104             get;
105             set;
106         }
107 
108         // Refer to the Credits column in database
109         [Column]
110         public int Credits
111         {
112             get;
113             set;
114         }
115 
116         // Refer to the FK for FK_CourseGrade_Course
117         private EntitySet<CourseGrade> _Grades;
118         [Association(Storage = "_Grades", OtherKey = "CourseID")]
119         public EntitySet<CourseGrade> Grades
120         {
121             get { return this._Grades; }
122             set { this._Grades.Assign(value); }
123         }
124     }
125 
126     [Table(Name = "CourseGrade")]
127     public class CourseGrade
128     {
129         public CourseGrade()
130         {
131             this._Course = default(EntityRef<Course>);
132             this._Student = default(EntityRef<Student>);
133         }
134 
135         // Refer to the EnrollmentID column in database
136         [Column(IsPrimaryKey = true, Name = "EnrollmentID", IsDbGenerated = true)]
137         public int EnrollmentID
138         {
139             get;
140             set;
141         }
142 
143         // Refer to the CourseID column in database
144         [Column(Name = "CourseID")]
145         public int CourseID
146         {
147             get;
148             set;
149         }
150 
151         // Refer to the StudentID column in database
152         [Column(Name = "StudentID")]
153         public int StudentID
154         {
155             get;
156             set;
157         }
158 
159         // Refer to the Grade column in database
160         [Column]
161         public decimal Grade
162         {
163             get;
164             set;
165         }
166 
167         // Refer to the FK of FK_CourseGrade_Course
168         private EntityRef<Course> _Course;
169         [Association(Storage = "_Course", ThisKey = "CourseID")]
170         public Course Course
171         {
172             get { return this._Course.Entity; }
173             set { this._Course.Entity = value; }
174         }
175 
176         // Refer to the FK of FK_CourseGrade_Student 
177         private EntityRef<Student> _Student;
178         [Association(Storage = "_Student", ThisKey = "StudentID")]
179         public Student Student
180         {
181             get { return this._Student.Entity; }
182             set { this._Student.Entity = value; }
183         }
184     }
185 
186     public class SchoolDataContext : DataContext
187     {
188         public Table<Student> Students;
189         public Table<Course> Courses;
190         public Table<CourseGrade> CourseGrades;
191         public SchoolDataContext(string connection) : base(connection) { }
192     }
193 }

2,手动操作

View Code 
  1 /// <summary>
  2     /// 读取图片路径转换成字节数
  3     /// </summary>
  4     /// <param name="path">The path of the image file.</param>
  5     /// <returns>The output of the array.</returns>
  6     private static byte[] ReadImage(string path)
  7     {
  8         try
  9         {
 10             // Open the image file
 11             using (FileStream fs = new FileStream(path, FileMode.Open,
 12                 FileAccess.Read))
 13             {
 14                 // Create an array of bytes
 15                 byte[] bPicture = new byte[fs.Length];
 16 
 17                 // Read the image file
 18                 fs.Read(bPicture, 0, Convert.ToInt32(fs.Length));
 19 
 20                 return bPicture;
 21             }
 22         }
 23         catch (Exception ex)
 24         {
 25             Console.WriteLine(
 26                 "The application throws the error: {0}", ex.Message);
 27             return null;
 28         }
 29     }
 30  /// <summary>
 31     /// 下面的方法适用于手动创建的类
 32     /// </summary>
 33     private static void QueryDBByManuallyCreatedClasses()
 34     {
 35         Console.WriteLine("Query using the manually-created classes.");
 36         try
 37         {
 38             string connStr = ConfigurationManager.ConnectionStrings[
 39                 "SQLServer2005DB"].ConnectionString;
 40 
 41             // DataContext takes a connection string
 42             CSLinqToSQL.Manual.SchoolDataContext db = new
 43                 CSLinqToSQL.Manual.SchoolDataContext(connStr);
 44             Console.WriteLine("\nAdd a new student to the Person table");
 45 
 46 
 47             // 创建一个新学生。
 48             // 我们不需要在此处设置的 PersonCategory 值，如果我们想要
 49             // 默认值将设置为此数据库中的列。
 50             CSLinqToSQL.Manual.Student student = new CSLinqToSQL.Manual.Student
 51             {
 52                 FirstName = "Rongchun",
 53                 LastName = "Zhang",
 54                 EnrollmentDate = DateTime.Now,
 55                 Picture = ReadImage(@"MSDN.jpg"),
 56             };
 57 
 58             // 添加一个新对象到学生表
 59             db.Students.InsertOnSubmit(student);
 60 
 61             // 提交数据
 62             db.SubmitChanges();
 63 
 64             // 查询学生
 65             Console.WriteLine("\nQuery students whose first name is Roger:");
 66             var students = from p in db.Students
 67                            where p.FirstName == "Roger"
 68                            select p;
 69 
 70             foreach (var p in students)
 71                 Console.WriteLine("ID = {0}, Name = {1} {2}", p.PersonID,
 72                     p.FirstName, p.LastName);
 73 
 74             // 连表查询
 75             Console.WriteLine("\nQuery max. grade of each course:");
 76             var courses =
 77                 from grade in db.CourseGrades
 78                 group grade by grade.CourseID into g
 79                 join cur in db.Courses on g.Key equals cur.CourseID
 80                 select new
 81                 {
 82                     CourseID = g.Key,
 83                     Title = cur.Title,
 84                     TopGrade = g.Max(gra => gra.Grade)
 85                 };
 86 
 87             foreach (var c in courses)
 88                 Console.WriteLine("Course = {0}, TopGrade = {1}",
 89                     c.Title, c.TopGrade);
 90 
 91             Console.WriteLine("\nQuery all grades that Nino got");
 92 
 93             var grades =
 94                from stu in db.Students
 95                from gra in stu.CourseGrades
 96                join cur in db.Courses on gra.CourseID equals cur.CourseID
 97                where stu.FirstName == "Nino"
 98                select new
 99                {
100                    Stduent = stu,
101                    Course = cur,
102                    CourseGrade = gra
103                };
104 
105             foreach (var grade in grades)
106             {
107                 Console.WriteLine("FirstName = {0}, Course= {1}, Grade= {2}",
108                     grade.Stduent.FirstName,
109                     grade.Course.Title,
110                     grade.CourseGrade.Grade);
111             }
112         }
113         catch (Exception e)
114         {
115             Console.WriteLine("The application throws the error: {0}", e.Message);
116         }
117     }