做一下mssql的连接，和赋值的测试

机器环境：

前言

最近实体框架很流行，不过缺点也多，我列一下：

1.不支持sql2000

2.增删改很麻烦，增加，带多个外键的，要查数据库，修改与删除也要查，这样对性能影响大打折扣，而且编码也麻烦，至少比代码生成麻烦

3.linq to entity中，有些语句是不支持的，如intArr.Contains等方法，必需用e-sql query.Where("it.ID IN {1,2,3,4}")才行

4.要支持外键值的话，要用ef2.0，即是.net4.0的平台，.net4.0太庞大了，近两三年应该也用不起来。

所以我还是用普通的ado.net算了。时间很紧迫，项目很乱，而且我还只有一个星期对住一堆超乱的代码进行重构，还不知做不做得来。所以为了解决这个难题，要一步一步验证自己未能确定的问题。

现在我的思路是，代码效率+性能+维护方便

如果牺牲10%-20%的性能，能增加1-2倍的生产效率的话，那我很乐意去做。

ef1.0，好像在100万条数据的情况下，不能满足吧。这种查法，不过现在放弃使用，就暂不做相关的测试了。

正题

最近在研究属于自己的架构，所以有必要做一下这方面的性能测试

主要有两个问题要搞清楚

1.在DAL层里，是否需要共用一个Connection?

2.在赋值过程中，能否通过一个规范来实现接口，然后拿数据，像DataReader赋值的操作，用循环就搞店，不用一行一行属性来赋值。

所以就有两个问题

1.用连接池开关连接与不用连接池开关连接，时间上有多大差异？

2.用索引器的方式赋值，与普通手写代码赋值，时间上又有多大差异？

于是模拟最实际的环境（我想是挺实际的）就用以下在代码来做测试：

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Data; using System.IO; namespace ConsoleApplication52 { class Program { static void Main(string[] args) { string poolStr = "data source=127.0.0.1//sql2005;initial catalog=examjuhai;user id=sa;password=juhaisoft;min pool size=50;max pool size=200;pooling=true;"; string notPoolStr = "data source=127.0.0.1//sql2005;initial catalog=examjuhai;user id=sa;password=juhaisoft;pooling=false"; int baseCount = 1000; StreamWriter log = new StreamWriter("E://testlog.txt", false, Encoding.Default); log.WriteLine("测试用连接池与不用连接池开关连接所用时间:"); log.WriteLine("------------------------------------------------"); log.WriteLine("次数/t连接池/t不用连接池"); for (int i = 1; i <= 20; i++) { int count = baseCount * i; TimeSpan time = TestConn(count, poolStr); TimeSpan time2 = TestConn(count, notPoolStr); log.WriteLine(count + "/t" + time + "/t" + (time2 == TimeSpan.MaxValue ? "连接出错" : time2.ToString())); log.Flush(); } log.WriteLine("------------------------------------------------"); log.WriteLine("------------------------------------------------"); log.WriteLine("------------------------------------------------"); log.WriteLine("测试字符串判断赋值与直接赋值时间比较"); log.WriteLine("------------------------------------------------"); log.WriteLine("次数/t判断赋值/t赋值 "); for (int i = 1; i <= 100; i++) { int count = baseCount * i; TimeSpan time = TestAssign1(count); TimeSpan time2 = TestAssign2(count); log.WriteLine(count + "/t" + time + "/t" + time2); log.Flush(); } log.WriteLine("------------------------------------------------"); log.Close(); } /// <summary> /// 测试开关连接所用时间 /// </summary> /// <param name="count">开关次数</param> /// <param name="connStr">连接字符串</param> /// <returns>所用时间</returns> private static TimeSpan TestConn(int count, string connStr) { //计算数数所用时间 DateTime now = DateTime.Now; for (int i = 0; i < count; i++) { } TimeSpan t1 = DateTime.Now - now; for (int i = 0; i < count; i++) { SqlConnection conn = new SqlConnection(connStr); try { conn.Open(); conn.Close(); } catch { return TimeSpan.MaxValue; } finally { conn.Dispose(); } } TimeSpan t2 = DateTime.Now - now; //减去数数的时间才是正确的时间 return t2 - t1; } /// <summary> /// 用判断的方式赋值 /// </summary> /// <returns></returns> public static TimeSpan TestAssign1(int dataCount) { DataTable dt = PreparedTable(dataCount); DateTime now = DateTime.Now; List<Model> list = new List<Model>(); foreach (DataRow row in dt.Rows) { Model m = new Model(); foreach (DataColumn col in dt.Columns) { m[col.ColumnName] = row[col]; } list.Add(m); } return DateTime.Now - now; } /// <summary> /// 赋值 /// </summary> /// <returns></returns> public static TimeSpan TestAssign2(int dataCount) { DataTable dt = PreparedTable(dataCount); DateTime now = DateTime.Now; List<Model> list = new List<Model>(); foreach (DataRow row in dt.Rows) { Model m = new Model(); m.ID = Convert.ToInt32(row["ID"]); m.C1abcdefghijkl = Convert.ToString(row["C1abcdefghijkl"]); m.C2abcdefghijkl = Convert.ToSingle(row["C2abcdefghijkl"]); m.C3abcdefghijkl = Convert.ToDouble(row["C3abcdefghijkl"]); m.C4abcdefghijkl = Convert.ToDecimal(row["C4abcdefghijkl"]); m.C5abcdefghijkl = Convert.ToInt64(row["C5abcdefghijkl"]); m.C6abcdefghijkl = Convert.ToDateTime(row["C6abcdefghijkl"]); m.C7abcdefghijkl = Convert.ToString(row["C7abcdefghijkl"]); list.Add(m); } return DateTime.Now - now; } /// <summary> /// 正常的表，一般有八个列 /// </summary> /// <param name="dataRowCount">数据的数量</param> static DataTable PreparedTable(int dataRowCount) { DataTable dt = new DataTable(); dt.Columns.Add("ID", typeof(int)); dt.Columns.Add("C1abcdefghijkl", typeof(string)); dt.Columns.Add("C2abcdefghijkl", typeof(float)); dt.Columns.Add("C3abcdefghijkl", typeof(double)); dt.Columns.Add("C4abcdefghijkl", typeof(decimal)); dt.Columns.Add("C5abcdefghijkl", typeof(long)); dt.Columns.Add("C6abcdefghijkl", typeof(DateTime)); dt.Columns.Add("C7abcdefghijkl", typeof(string)); for (int i = 0; i < dataRowCount; i++) { DataRow row = dt.NewRow(); row[0] = rnd.Next(); row[1] = rnd.Next().ToString(); row[2] = (float)rnd.NextDouble(); row[3] = rnd.NextDouble(); row[4] = rnd.Next(); row[5] = rnd.Next(); row[6] = DateTime.Now.AddSeconds(rnd.Next(int.MinValue, int.MaxValue)); row[7] = DateTime.Now.Ticks.ToString() + "_" + rnd.Next() + "_" + rnd.NextDouble(); dt.Rows.Add(row); } return dt; } static Random rnd = new Random(); /// <summary> /// 测试字符串判断赋值比较 /// </summary> class Model { public int ID { get; set; } public string C1abcdefghijkl { get; set; } public float C2abcdefghijkl { get; set; } public double C3abcdefghijkl { get; set; } public decimal C4abcdefghijkl { get; set; } public long C5abcdefghijkl { get; set; } public DateTime? C6abcdefghijkl { get; set; } public string C7abcdefghijkl { get; set; } public object this[string propertyName] { get { switch (propertyName) { case "ID": return ID; case "C1abcdefghijkl": return C1abcdefghijkl; case "C2abcdefghijkl": return C2abcdefghijkl; case "C3abcdefghijkl": return C3abcdefghijkl; case "C4abcdefghijkl": return C4abcdefghijkl; case "C5abcdefghijkl": return C5abcdefghijkl; case "C6abcdefghijkl": return C6abcdefghijkl; case "C7abcdefghijkl": return C7abcdefghijkl; default: return null; } } set { //value不为空时 if (value != null && !(value is DBNull)) { switch (propertyName) { case "ID": ID = Convert.ToInt32(value); break; case "C1abcdefghijkl": C1abcdefghijkl = Convert.ToString(value); break; case "C2abcdefghijkl": C2abcdefghijkl = Convert.ToSingle(value); break; case "C3abcdefghijkl": C3abcdefghijkl = Convert.ToDouble(value); break; case "C4abcdefghijkl": C4abcdefghijkl = Convert.ToDecimal(value); break; case "C5abcdefghijkl": C5abcdefghijkl = Convert.ToInt64(value); break; case "C6abcdefghijkl": C6abcdefghijkl = Convert.ToDateTime(value); break; case "C7abcdefghijkl": C7abcdefghijkl = Convert.ToString(value); break; } } } } } } }

是一个控制台应用程序，连接sql2005数据库（开始向sql2005转了，不过现在还在用sql2000，客户要求，没办法的事）