解析如何正确使用SqlConnection的实现方法
程序员文章站
2023-12-16 23:35:46
以前曾见过有人这样写代码:复制代码 代码如下:public class service1 : iservice1 { &nbs...
以前曾见过有人这样写代码:
public class service1 : iservice1
{
private sqlconnection conn = new sqlconnection();
public void method1()
{
//do something with conn;
}
public void method2()
{
//do something with conn;
}
public void method3()
{
//do something with conn;
}
public void method4()
{
//do something with conn;
}
}
在服务类中,新建一个全局的conn对象,然后使用conn对象来操作数据库。
当然,还有一些不同的版本,比如:
private sqlconnection conn = new sqlconnection();
private static sqlconnection sconn = new sqlconnection();
private sqlconnection conn
{
get { return new sqlconnection(); }
}
如果有人问你哪种方式比较好,你会怎么回答?
首先验证下在多线程环境下使用一个connection的方式:
创建控制台程序:
main代码如下:
public static void main()
{
string connectionstring = @"data source=.\sqlexpress;
attachdbfilename=""e:\db\northwnd.mdf"";
integrated security=true;
connect timeout=30;user instance=true";
string connectionstringnopooling = connectionstring + " ;pooling='false' ";
sqlconnection conn = new sqlconnection(connectionstring);
new thread(() => { executecommand(conn); }) { name = "t1" }.start();
new thread(() => { executecommand(conn); }) { name = "t2" }.start();
}
public static void executecommand(sqlconnection conn)
{
console.writeline("thread:{0},{1}", thread.currentthread.name, datetime.now);
conn.open();
sqlcommand command = new sqlcommand("select * from customers", conn);
command.executenonquery();
command.dispose();
thread.sleep(5000); //模拟耗时的查询
conn.close();
console.writeline("thread:{0} 执行完毕,{1}", thread.currentthread.name, datetime.now);
}
public static void main()
{
string connectionstring = @"data source=.\sqlexpress;
attachdbfilename=""e:\db\northwnd.mdf"";
integrated security=true;
connect timeout=30;user instance=true";
string connectionstringnopooling = connectionstring + " ;pooling='false' ";
//sqlconnection conn = new sqlconnection(connectionstring);
//new thread(() => { executecommand(conn); }) { name = "t1" }.start();
//new thread(() => { executecommand(conn); }) { name = "t2" }.start();
sqlconnection conn1 = new sqlconnection(connectionstring);
sqlconnection conn2 = new sqlconnection(connectionstring);
new thread(() => { executecommand(conn1); }) { name = "t1" }.start();
new thread(() => { executecommand(conn2); }) { name = "t2" }.start();
console.readline();
}
运行结果如下:
public sqlconnection connection
{
get
{
return new sqlconnection(@"...");
}
}
而不要
private sqlconnection conn = new sqlconnection(connectionstring);
复制代码 代码如下:
public class service1 : iservice1
{
private sqlconnection conn = new sqlconnection();
public void method1()
{
//do something with conn;
}
public void method2()
{
//do something with conn;
}
public void method3()
{
//do something with conn;
}
public void method4()
{
//do something with conn;
}
}
在服务类中,新建一个全局的conn对象,然后使用conn对象来操作数据库。
当然,还有一些不同的版本,比如:
复制代码 代码如下:
private sqlconnection conn = new sqlconnection();
private static sqlconnection sconn = new sqlconnection();
private sqlconnection conn
{
get { return new sqlconnection(); }
}
如果有人问你哪种方式比较好,你会怎么回答?
首先验证下在多线程环境下使用一个connection的方式:
创建控制台程序:
main代码如下:
复制代码 代码如下:
public static void main()
{
string connectionstring = @"data source=.\sqlexpress;
attachdbfilename=""e:\db\northwnd.mdf"";
integrated security=true;
connect timeout=30;user instance=true";
string connectionstringnopooling = connectionstring + " ;pooling='false' ";
sqlconnection conn = new sqlconnection(connectionstring);
new thread(() => { executecommand(conn); }) { name = "t1" }.start();
new thread(() => { executecommand(conn); }) { name = "t2" }.start();
}
public static void executecommand(sqlconnection conn)
{
console.writeline("thread:{0},{1}", thread.currentthread.name, datetime.now);
conn.open();
sqlcommand command = new sqlcommand("select * from customers", conn);
command.executenonquery();
command.dispose();
thread.sleep(5000); //模拟耗时的查询
conn.close();
console.writeline("thread:{0} 执行完毕,{1}", thread.currentthread.name, datetime.now);
}
代码很简单,模拟两个线程同时执行executecommand.方法。结果如下:
可以知道在多线程环境下使用一个connection来执行sql语句是不安全的,
修改main函数如下:将一个connection,改为多个connection
复制代码 代码如下:
public static void main()
{
string connectionstring = @"data source=.\sqlexpress;
attachdbfilename=""e:\db\northwnd.mdf"";
integrated security=true;
connect timeout=30;user instance=true";
string connectionstringnopooling = connectionstring + " ;pooling='false' ";
//sqlconnection conn = new sqlconnection(connectionstring);
//new thread(() => { executecommand(conn); }) { name = "t1" }.start();
//new thread(() => { executecommand(conn); }) { name = "t2" }.start();
sqlconnection conn1 = new sqlconnection(connectionstring);
sqlconnection conn2 = new sqlconnection(connectionstring);
new thread(() => { executecommand(conn1); }) { name = "t1" }.start();
new thread(() => { executecommand(conn2); }) { name = "t2" }.start();
console.readline();
}
运行结果如下:
既然多个connection比一个connection要好,
为什么还是有人使用上面的那种写法来创建connection呢?
我认为他们可能会认为创建多个connection比较耗时,而且多个connection会占用内存,影响性能等等。。
在这一点上可以使用测试数据来说明:
测试数据来自:connection-pooling vs. reusing one connection
run # |
ncp |
cp |
oc |
1 |
4073 |
374 |
237 |
2 |
4032 |
341 |
298 |
3 |
3985 |
353 |
242 |
4 |
4085 |
348 |
269 |
5 |
3964 |
369 |
256 |
6 |
4203 |
330 |
207 |
7 |
4055 |
341 |
359 |
8 |
4071 |
357 |
286 |
9 |
3968 |
363 |
356 |
10 |
4023 |
349 |
359 |
avg |
4046 |
353 |
287 |
run #:1代表1000次查询,2代表2000次查询
ncp :not connection pool ,未启用数据库连接池
cp :connection pool,启用数据库连接池
oc :one connection,一个连接对象
从图表可以发现启用了连接池的方式并不比重用一个连接慢多少。
但是从稳定性,程序的健壮性来说,cp的方式明显的好于oc。
所以下次实现服务,或者是查询的时候完全可以使用
复制代码 代码如下:
public sqlconnection connection
{
get
{
return new sqlconnection(@"...");
}
}
而不要
private sqlconnection conn = new sqlconnection(connectionstring);