Sql只为多个读者打开一次连接 [英] Sql open connection just once for multiple readers
问题描述
我在C＃asp.net中创建了一个网站。我在代码中经常使用OOP。因此，每种方法都有一个读者。数据库被多次打开，它工作正常，但我知道我应该有一个更有效的方式。对于这篇文章，我只展示了我的一种方法，只是为了保持简单。
我问的是什么？有人可以告诉我如何打开数据库一次并使用多种方法连接。
在我的页面加载中查看我的LoadSchedule（）。然后在我的LoadSchedule方法中，我必须打开数据库才能正常工作。
必须有一个更好的方法来做到这一点，对吗？
先谢谢。
我尝试过的事情：
I created a web site in C# asp.net. I use OOP a lot in my code. Because of this there is a reader in each method. The database is opened multiple times and it works fine but I know there should be a more efficient way I doing this. For this post I only show 1 of my methods just to keep it simple.
What I'm asking? Could someone please show me how to open the database once and use the connection in multiple methods.
Take a look at my LoadSchedule() in my page load. Then in my LoadSchedule method I have to open database for it work.
There has to be a better way of doing this, right?
Thanks in advanced.
What I have tried:
using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
public partial class PickAllPool : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection conn;
SqlCommand comm;
SqlDataReader reader;
string connectionString = ConfigurationManager.ConnectionStrings["Name of connection string"].ConnectionString;
conn = new SqlConnection(connectionString);
comm = new SqlCommand("GetSchedule_v2", conn);
comm.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
reader = comm.ExecuteReader();
if (reader.Read())
{
DateTime localDate = DateTime.Now.AddHours(2);
DateTime PoolStatusActive = Convert.ToDateTime(reader["PoolActive"]);
DateTime PoolStatusDisabled = Convert.ToDateTime(reader["PoolDisabled"]);
if (localDate > PoolStatusActive && localDate < PoolStatusDisabled)
{
if (!IsPostBack)
{
reader.Close();
LoadSchedule();
}
}
else
{
poolStatus.Visible = false;
timerMsg.Visible = true;
}
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("Database error message: " + ex.Message +
"<br>" + ex.Source +
"<br>" + ex.TargetSite +
"<br>" + ex.StackTrace);
}
finally
{
// comm.Dispose();
conn.Close();
}
}
protected void LoadSchedule()
{
SqlConnection conn;
SqlCommand comm;
SqlDataReader reader;
string connectionString = ConfigurationManager.ConnectionStrings["PhillyPickAllConnection"].ConnectionString;
conn = new SqlConnection(connectionString);
comm = new SqlCommand("GetSchedule_v2", conn);
comm.CommandType = CommandType.StoredProcedure;
try
{
conn.Open();
reader = comm.ExecuteReader();
myRepeater.DataSource = reader;
myRepeater.DataBind();
reader.Read();
reader.Close();
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("Database error message: " + ex.Message +
"<br>" + ex.Source +
"<br>" + ex.TargetSite +
"<br>" + ex.StackTrace);
}
finally
{
comm.Dispose();
conn.Close();
}
}
}
推荐答案
你遇到的问题比你想象的要大。
使用
You have bigger problems than you think.
The use of
if (!IsPostBack)和
protected void Page_Load(object sender, EventArgs e)说这是一个网站 - 这意味着你的代码将无法在生产中使用。
C＃代码在服务器上执行，永远不会客户端 - 只在客户端上执行Javascript代码。因此，当您使用MessageBox时，它会在服务器上显示一个对话框，而不是客户端 - 用户无法看到它。这意味着服务器上的代码执行将停止并等待数千英里之外的用户点击他无法看到的确定按钮，甚至不知道是否存在。他不会收到任何错误信息;所有他得到的都是一个冻结的网站...
它在开发中工作，因为服务器和客户端是同一台物理机 - 所以当服务器显示一个对话框时你在服务器上看到它，看起来它的工作原理。但它不是，不是真的 - 一旦你投入生产，它就会灾难性地失败。
鉴于这是一个网站，你不应该是甚至试图共享一个SqlConnection - 你必须在每次使用它时重新创建它，因为它们是稀缺资源，如果你创建一个并保持它，它可能会持续太长时间 - 即使客户端关闭了他的机器然后回家了！鉴于网站被设计为浏览但是多个用户，这可以很快地耗尽与SQL的连接，然后用户得到无法连接到服务器的消息。或者，如果您的代码没有使用MessageBoxes来显示它们......：叹息：
进行数据库访问的正确方式：
says "this is a website" - and that means your code will not work in production.
C# code is executed on the Server, never the Client - only Javascript code is ever executed on the client. So when you use MessageBox it shows a dialog on the Server, not the Client - where the user cannot see it. That means the execution of code on the Server will stop and wait for a use several thousand miles away to click an "OK" button that he can't see, and doesn't even know is there. He will not get any error message; all he gets is a frozen website...
It works in development because the Server and the Client are the same physical machine - so when the server shows a dialog you see it it on the Server and it looks like it works. But it doesn't, not realy - and as soon as you get to production, it fails catastrophically.
And given that this is a website, you should not be even trying to "share" an SqlConnection - you must create it it anew each time you use it because they are scarce resources and if you create one and hold it, it can last far too long - even if the client has turned off his machine and gone home! Given that websites are designed to be browsed but multiple users, this can exhaust the supply of connections to SQL pretty quickly, and then users get "Can't connect to server" messages. Or would, if your code didn't use MessageBoxes to display them ... :sigh:
The "Proper" way to do DB access:
using (SqlConnection con = new SqlConnection(strConnect))
{
con.Open();
using (SqlDataAdapter da = new SqlDataAdapter("SELECT MyColumn1, MyColumn2 FROM myTable WHERE mySearchColumn = @SEARCH", con))
{
da.SelectCommand.Parameters.AddWithValue("@SEARCH", myTextBox.Text);
DataTable dt = new DataTable();
da.Fill(dt);
myDataGridView.DataSource = dt;
}
}
或者
Or
using (SqlConnection con = new SqlConnection(strConnect))
{
con.Open();
using (SqlCommand cmd = new SqlCommand("SELECT MyColumn1, MyColumn2 FROM myTable WHERE mySearchColumn = @SEARCH", con))
{
cmd.Parameters.AddWithValue("@SEARCH", myTextBox.Text);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int id = (int) reader["Id"];
string desc = (string) reader["description"];
...
}
}
}
}
使用 block确保对象自动处理，无论发生什么。
The using block ensures that the objects are Disposed automatically, regardless of what happens.
这篇关于Sql只为多个读者打开一次连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
