C#利用DataTable存取資料，

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();