将LINQ TO DataSet查询的对象集保存到真实数据库新建的表中

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Text;
using System.Reflection;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            AddTableToDB();
            Console.ReadKey();
        }
        static public void AddTableToDB()
        {//将查询结果添加到真实数据库中
            CreateNewTable();
            DataTable dt = new  DataTable();
            DataSet ds = new DataSet();
            DoQuery(ref dt);
            try
            {
                SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=True");
                using (conn)
                {
                    string StrSqlCommand = "SELECT * FROM FindOutEmployees";
                    SqlCommand com = new SqlCommand(StrSqlCommand,conn);
                    using(com)
                    {
                        SqlDataAdapter ad = new SqlDataAdapter(com);
                        using (ad)
                        {
                            SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(ad); //ad更新起效必备语句
                            ad.Fill(ds, "FindOutEmployees");
                            foreach (DataRow dr in dt.Rows)
                            {
                                DataRow RowWillAdd = ds.Tables["FindOutEmployees"].NewRow();
                                RowWillAdd["ID"] = dr["ID"];
                                RowWillAdd["Name"] = dr["Name"];
                                ds.Tables["FindOutEmployees"].Rows.Add(RowWillAdd);
                            }
                            ad.Update(ds, "FindOutEmployees");
                        }  
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("SQL exception occurred: " + ex.Message);
            }
        }


        static public void FillDataSet(DataSet ds, string DataTableName)
        {//从真实数据库中下载一个表，填充到DataSet中
            try
            {
                SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=True");
                using (conn)
                {
                    string StrSqlCommand = "SELECT * FROM " + DataTableName;
                    SqlCommand com = new SqlCommand(StrSqlCommand,conn);
                    using(com)
                    {
                        SqlDataAdapter ad = new SqlDataAdapter(com);
                        using (ad)
                        { ad.Fill(ds, DataTableName); }  
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("SQL exception occurred: " + ex.Message);
            }
        }

        static public void  DoQuery(ref DataTable dt)
        {//LINQ TO DataSet查询，转换后返回DataTable
            DataSet ds = new DataSet();
            FillDataSet(ds, "Employees");
            var result = from s1 in ds.Tables["Employees"].AsEnumerable()
                        where s1.Field<int>("EmployeeID") < 8
                        select new
                        {
                            ID = s1.Field<int>("EmployeeID"),
                            Name = s1.Field<string>("FirstName")
                        };
            dt= LinqToDataTable(result);
        }

        static public void CreateNewTable()
        {//在真实数据库中创建一个新表
            try
            {
                SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Northwind;Integrated Security=True");
                using (conn)
                {
                    conn.Open();
                    string strSqlCommand = "CREATE TABLE FindOutEmployees( ID int  NOT NULL, Name nvarchar(20) NOT NULL)";
                    SqlCommand com1 = new SqlCommand(strSqlCommand, conn);
                    using (com1)
                    {
                        com1.ExecuteNonQuery();//执行创建表的SQL语句
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("SQL exception occurred: " + ex.Message);
            }
        }

        static DataTable LinqToDataTable(IEnumerable list)
        {
            DataTable table = new DataTable();
            bool schemaIsBuild = false;
            PropertyInfo[] props = null;
            foreach (object item in list)
            {
                if (!schemaIsBuild)
                {
                    props = item.GetType().GetProperties();
                    foreach (var pi in props)
                        table.Columns.Add(new DataColumn(pi.Name, pi.PropertyType));
                    schemaIsBuild = true;
                }
                var row = table.NewRow();
                foreach (var pi in props)
                    row[pi.Name] = pi.GetValue(item, null);
                table.Rows.Add(row);
            }
            table.AcceptChanges();
            return table;
        }

    }
}