Esempio n. 1
0
    /// <summary>
    /// This method works with the strong typed DataSet
    /// </summary>
    private static void QueryDBByStrongTypedDataSet()
    {
        Console.WriteLine("Use LINQ to query strong typed DataSet...");

        /////////////////////////////////////////////////////////////////////
        // Fill the strong typed DataSet && Insert data into database
        //

        // Create a strong typed DataSet object
        SQLServer2005DBDataSet dsSQLServer = new SQLServer2005DBDataSet();

        // Create the PersonTableAdapter and fill the Person DataTable
        PersonTableAdapter taPerson = new PersonTableAdapter();

        SQLServer2005DBDataSet.PersonDataTable tblPerson = dsSQLServer.
                                                           Person;
        taPerson.Fill(tblPerson);

        // Create a new row into the Person DataTable
        // We don't set the PersonCategory value because we have set the
        // default value property of the DataSet PersonCategory column.
        SQLServer2005DBDataSet.PersonRow rowPerson = tblPerson.NewPersonRow();
        rowPerson.LastName  = "Sun";
        rowPerson.FirstName = "Hongye";
        rowPerson.SetHireDateNull();
        rowPerson.EnrollmentDate = DateTime.Now;
        rowPerson.Picture        = ReadImage(@"MSDN.jpg");
        tblPerson.AddPersonRow(rowPerson);

        // Update the database
        taPerson.Update(tblPerson);

        // Use PersonTableAdapter to insert new record into the database
        // directly
        taPerson.Insert("Ge", "Jialiang", DateTime.Now, null,
                        ReadImage(@"MSDN.jpg"), 2);

        // Create the CourseTableAdapter and fill the Course DataTable
        CourseTableAdapter taCourse = new CourseTableAdapter();

        SQLServer2005DBDataSet.CourseDataTable tblCourse = dsSQLServer.Course;
        taCourse.Fill(tblCourse);

        // Create the CourseGradeTableAdapter and fill the CourseGrade
        // DataTable
        CourseGradeTableAdapter taCourseGrade = new CourseGradeTableAdapter();

        SQLServer2005DBDataSet.CourseGradeDataTable tblCourseGrade =
            dsSQLServer.CourseGrade;
        taCourseGrade.Fill(tblCourseGrade);


        /////////////////////////////////////////////////////////////////////
        // Perform the query operation in one DataTable
        //

        Console.WriteLine("\nQuery people whose first name is Roger:");

        // Person the query
        var query = from p in tblPerson.AsEnumerable()
                    where p.FirstName == "Roger"
                    select p;

        // Display the query results
        foreach (var p in query)
        {
            Console.WriteLine("ID = {0}, Name = {1} {2}", p.PersonID,
                              p.FirstName, p.LastName);
        }


        /////////////////////////////////////////////////////////////////////
        // Perform the query operation across multiple DataTables
        //

        Console.WriteLine("\nQuery the max grade of each course:");

        // Perform the query and get a collection of hte anonymous type,
        // new { int CourseID, string Title, decimal TopGrade }
        var courses = from grade in tblCourseGrade.AsEnumerable()
                      group grade by grade.CourseID into g
                      join cur in tblCourse.AsEnumerable()
                      on g.Key equals cur.CourseID
                      select new
        {
            CourseID = g.Key,
            Title    = cur.Title,
            TopGrade = g.Max(gra => gra.IsGradeNull() ?
                             decimal.Zero : gra.Grade)
        };

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


        /////////////////////////////////////////////////////////////////////
        // Perform the query operation across multiple related DataTables
        //

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

        // Perform the query betweem related DataTables and get the anonymous
        // type, new { PersonRow Person, CourseRow Course, CourseGradeRow
        // CourseGrade}
        var grades = from per in tblPerson.AsEnumerable()
                     from gra in per.GetCourseGradeRows()
                     join cur in tblCourse.AsEnumerable()
                     on gra.CourseID equals cur.CourseID
                     where per.FirstName == "Nino"
                     select new
        {
            Person      = per,
            Course      = cur,
            CourseGrade = gra
        };

        // Display the query results
        foreach (var grade in grades)
        {
            Console.WriteLine("FirstName = {0}, Course= {1}, Grade= {2}",
                              grade.Person.FirstName, grade.Course.Title,
                              grade.CourseGrade.Grade);
        }
    }
Esempio n. 2
0
    /// <summary>
    /// Insert and Select data using Strong Typed DataSet
    /// </summary>
    private static void InsertSelectUsingStrongTypedDataSet()
    {
        Console.WriteLine("\r\nInsert and Select data using Strong Typed "
                          + "DataSet...");

        // 1. Create a Strong Typed DataSet object and fill its corresponding
        //    data tables
        // Create a Strong Typed DataSet object
        SQLServer2005DBDataSet dsSQLServer = new SQLServer2005DBDataSet();

        // Use the PersonTableAdapter to fill the Person table
        PersonTableAdapter taPerson = new PersonTableAdapter();

        SQLServer2005DBDataSet.PersonDataTable tblPerson = dsSQLServer.
                                                           Person;
        taPerson.Fill(tblPerson);

        // Use the CourseTableAdapter to fill the Course table
        CourseTableAdapter taCourse = new CourseTableAdapter();

        SQLServer2005DBDataSet.CourseDataTable tblCourse = dsSQLServer.
                                                           Course;
        taCourse.Fill(tblCourse);

        // Use the DepartmentTableAdapter to fill the Department table
        DepartmentTableAdapter taDepartment = new DepartmentTableAdapter();

        SQLServer2005DBDataSet.DepartmentDataTable tblDepartment =
            dsSQLServer.Department;
        taDepartment.Fill(tblDepartment);


        // 2. Insert a record into the Person table
        //--- (Strong Typed DataSet)
        // We don't set the PersonCategory value because we have set the
        // default value property of the DataSet PersonCategory column.
        SQLServer2005DBDataSet.PersonRow addRowPerson = tblPerson.
                                                        NewPersonRow();
        addRowPerson.LastName  = "Ge";
        addRowPerson.FirstName = "Jialiang";
        addRowPerson.SetHireDateNull();
        addRowPerson.EnrollmentDate = DateTime.Now;
        addRowPerson.Picture        = ReadImage(@"MSDN.jpg");
        tblPerson.AddPersonRow(addRowPerson);

        // ---Insert a record into the Person table
        // ---(Untyped DataSet)
        //DataRow addRowPerson = tblPerson.NewRow();
        //addRowPerson["LastName"] = "Ge";
        //addRowPerson["FirstName"] = "Jialiang";
        //addRowPerson["HireDate"] = DBNull.Value;
        //addRowPerson["EnrollmentDate"] = DateTime.Now;
        //addRowPerson["Picture"] = ReadImage(@"MSDN.jpg");
        //tblPerson.Rows.Add(addRowPerson);

        // Update the Person table
        taPerson.Update(tblPerson);

        // 3. Insert a record into the Person table using PersionTableAdapter
        taPerson.Insert("Sun", "Hongye", DateTime.Now, null,
                        ReadImage(@"MSDN.jpg"), 2);

        // 4. Find a certain record in the Person table by primary key
        // ---(Strong Typed DataSet)
        SQLServer2005DBDataSet.PersonRow findRowPerson = tblPerson.
                                                         FindByPersonID(1);

        // ---Find a certain record in the Person table by primary key
        // ---(Untyped DataSet)
        //DataRow findRowPerson = tblPerson.Rows.Find(1);

        // Display the result record
        if (findRowPerson != null)
        {
            // Use IsColumnNameNull method to check the DBNull value
            Console.WriteLine("{0}\t{1} {2}\t{3}", findRowPerson.PersonID,
                              findRowPerson.FirstName, findRowPerson.LastName,
                              findRowPerson.IsEnrollmentDateNull() ? "(DBNull)" :
                              findRowPerson.EnrollmentDate.ToShortDateString());
        }

        // 5. Display data in two related data tables
        foreach (SQLServer2005DBDataSet.DepartmentRow rowDepartment in
                 dsSQLServer.Department)
        {
            Console.WriteLine("\r\nCourses for Department({0})",
                              rowDepartment.DepartmentID);
            foreach (SQLServer2005DBDataSet.CourseRow rowCourse
                     in rowDepartment.GetCourseRows())
            {
                Console.WriteLine("{0} - {1}", rowCourse.CourseID,
                                  rowCourse.Title);
            }
        }
    }