C#利用DataTable存取数据，

DataTable使用介绍：http://www.dotnetperls.com/datatable

取数据

SQL结果存到DataTable例子：http://stackoverflow.com/questions/6073382/read-sql-table-into-c-sharp-datatable

简要代码例子：

            String conStr = @"server=stcvm-130;integrated security=true;database=RAADB";
            SqlConnection con = new SqlConnection(conStr);
            SqlParameter catName = new SqlParameter("@CatName", "洗发水");
            SqlCommand com = new SqlCommand();
            com.CommandType = System.Data.CommandType.StoredProcedure;
            com.Connection = con;
            com.CommandText = "dbo.prc_GetCatPriceRange";
            com.CommandTimeout = 90;
            com.Parameters.Add(catName);
            com.Parameters.Add("RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

            DataTable dataTable = new DataTable();
            //The following two can also be replaced by 'SqlDataAdapter da = new SqlDataAdapter(com);'
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = com;
            //This will query your database and return the result to your datatable
            da.Fill(dataTable);
            da.Dispose();

            Console.WriteLine(dataTable.Rows.Count);
            foreach (DataRow dr in dataTable.Rows)
                Console.WriteLine(dr["minPrice"].ToString() + " " + dr[1].ToString());

这里可以看出，其关键点是SqlDataAdapter类。它的构造函数有很多，给定了关键参数即可。例如：

SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM table",constring);

这里在使用相关的代码的时候，遇到的一个问题是，执行完后有一个存储过程返回正常的结果，另外一个则返回0行，事实是它也应该有一行返回结果。尝试了很长时间，结果是using部分虽然没有编译出错，但是加上一些solution包后居然就好了。这个真是奇怪！

存数据

目前能够采用的办法是借助于SqlBulkCopy和Transaction来实现，下面是可用的参考代码：

        public class Person
        {
            public int PersonId { get; set; }
            public string LastName { get; set; }
            public string FirstName { get; set; }
            public string Adress { get; set; }
            public string City { get; set; }

            public Person(int id, string ln, string fn, string ad, string ci)
            {
                PersonId = id;
                LastName = ln;
                FirstName = fn;
                Adress = ad;
                City = ci;
            }
        }

                List<Tuple<string, Type, string>> COL_DEFs = new List<Tuple<string, Type, string>>()
                {
                    new Tuple<string, Type, string>("PersonId", typeof(int), "Id_P"),
                    new Tuple<string, Type, string>("LastName", typeof(string), "LastName"),
                    new Tuple<string, Type, string>("FirstName", typeof(string), "FirstName"),
                    new Tuple<string, Type, string>("Adress", typeof(string), "Adress"),
                    new Tuple<string, Type, string>("City", typeof(string), "City")
                };
                string tableName = "dbo.Persons";
                DataTable dt = new DataTable(tableName);
                foreach (var col in COL_DEFs)
                {
                    // The column format: <ObjectAttributeName, Type, DBColumnName>
                    dt.Columns.Add(col.Item3, col.Item2);
                }
                List<Person> records = new List<Person>();
                records.Add(new Person(30,"Jim","Gree","Nanjing","JS"));
                records.Add(new Person(40, "Jimf", "Gree", "Nanjing", "GF"));
                foreach (var rec in records)
                {
                    DataRow dr = dt.NewRow();
                    foreach (var col in COL_DEFs)
                    {
                        dr[col.Item3] = rec.GetType().GetProperty(col.Item1).GetValue(rec, null);
                    }
                    dt.Rows.Add(dr);
                }

                var transaction = con.BeginTransaction();
                SqlBulkCopy bulkCopy = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, transaction);
                bulkCopy.BatchSize = 10000;
                bulkCopy.BulkCopyTimeout = 36000;
                bulkCopy.DestinationTableName = dt.TableName;

                bulkCopy.WriteToServer(dt);

                transaction.Commit();