Question

c# 数据库操作类reader读取数据的方法

public static SqlDataReader MyReader(string strSQL)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(strSQL, connection);
try
{
connection.Open();
SqlDataReader myReader = cmd.ExecuteReader();
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}

}

这是一个reader的方法。
如果我想从数据库中读出网站配置信息，然后显示在aspx文件的文本框内。该怎么写？
我现在是这么写的：
string sqlcmd = "select * from system_config";
SqlDataReader reader = myData.MyReader(sqlcmd);
if (reader.Read())
{
sitename.Value = reader["sitename"].ToString();
en_sitename.Value = reader["en_sitename"].ToString();
company_name.Value = reader["company_name"].ToString();
en_company_name.Value = reader["en_company_name"].ToString();
company_tel.Value = reader["company_tel"].ToString();
company_fax.Value = reader["company_fax"].ToString();
}

运行后在if (reader.Read()) 这行代码提示阅读器已经关闭。我知道那个reader方法在执行完sql语句后，就关闭连接了。我下面再取的时候就出错了。我在想我这种模式应该怎么实现？
如果我把方法中finally
{
cmd.Dispose();
connection.Close();
}
去掉的话，就需要自己在程序中手工关闭连接。但是我在想是否有更好的实现模式？想了很久了没有解决。

还有这个方法和上面那个有什么区别
/// <summary>
/// 执行查询语句，返回SqlDataReader
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteReader (string SQLString, params SqlParameter[] cmdParms)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
SqlDataReader myReader = cmd.ExecuteReader();
cmd.Parameters.Clear();
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}

}

Answer 1

finally
{
cmd.Dispose();
connection.Close();
}
连接都关闭了，还怎么读取
把connection.Close();去掉，
读取数据之后，关闭reader就可以了

string sqlcmd = "select * from system_config";
SqlDataReader reader = myData.MyReader(sqlcmd);
if (reader.Read())
{
sitename.Value = reader["sitename"].ToString();
en_sitename.Value = reader["en_sitename"].ToString();
company_name.Value = reader["company_name"].ToString();
en_company_name.Value = reader["en_company_name"].ToString();
company_tel.Value = reader["company_tel"].ToString();
company_fax.Value = reader["company_fax"].ToString();
}
if(!reader.IsClosed){reader.Closed();}

Answer 2

1.提取单条记录
//using System.Data;
//using System.Data.SqlClient;
using (SqlConnection cn = new SqlConnection(%%1))
//ConfigurationManager.ConnectionStrings["db2ConnectionString"].ConnectionString
//Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
{
SqlCommand cmd = new SqlCommand("Select Count(*) From jobs",cn);
cn.Open();
%%2=cmd.ExecuteScalar(); //Message.InnerHtml
}

3.显示表格
//using System.Data;
//using System.Data.SqlClient;
DataSet ds=null;
using (SqlConnection cn = new SqlConnection(%%1))
//ConfigurationManager.ConnectionStrings["db2ConnectionString"].ConnectionString
//Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
{
cn.Open();
cmd=new SqlDataAdapter("Select * From Author",cn);
ds=new DataSet();
cmd.Fill(ds,%%2); //"作者"
MyDataGrid.DataSource=ds.Tables(%%2).DefaultView; //"作者"
MyDataGrid.DataBind();
}

5.数值范围查询
//using System.Data;
//using System.Data.SqlClient;
using (SqlConnection cn = new SqlConnection(%%1))
//ConfigurationManager.ConnectionStrings["db2ConnectionString"].ConnectionString
//Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
{
cn.Open();
int min = Int32.Parse(jcb1.selectedItem);
int max = Int32.Parse(jcb2.selectedItem);
cmd=new SqlDataAdapter("Select count(*) as pro_count From ProPrice where price between "
+ min + " and " + max,cn);
ds=new DataSet();
cmd.Fill(ds,%%2); //"作者"
MyDataGrid.DataSource=ds.Tables(%%2).DefaultView; //"作者"
MyDataGrid.DataBind();
}

6.关闭时断开连接
//using System.Data;
//using System.Data.SqlClient;
using (SqlConnection cn = new SqlConnection(%%1))
//ConfigurationManager.ConnectionStrings["db2ConnectionString"].ConnectionString
//Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
{
}

7.执行命令
//using System.Data;
//using System.Data.SqlClient;
using (SqlConnection cn = new SqlConnection(%%1))
//ConfigurationManager.ConnectionStrings["db2ConnectionString"].ConnectionString
//Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
{
SqlCommand cmd = new SqlCommand("insert userRegister_t values('"
+ TextBox1.Text + "','" + TextBox2.Text + "')",cn);
cn.Open();
cmd.ExecuteNonQuery();
}

Answer 3

public static SqlDataReader MyReader(string strSQL)
你可不可把这个函数改成这种
public static SqlDataReader MyReader(string strSQL，SqlConnection connection )
public static SqlDataReader MyReader(string strSQL，SqlConnection connection )

{

SqlCommand cmd = new SqlCommand(strSQL, connection);
try
{
connection.Open();
SqlDataReader myReader = cmd.ExecuteReader();
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
}

然后在
try{
SqlConnection connection = new SqlConnection(connectionString);

SqlDataReader reader = myData.MyReader(sqlcmd，connection);
if (reader.Read())
{
sitename.Value = reader["sitename"].ToString();
en_sitename.Value = reader["en_sitename"].ToString();
company_name.Value = reader["company_name"].ToString();
en_company_name.Value = reader["en_company_name"].ToString();
company_tel.Value = reader["company_tel"].ToString();
company_fax.Value = reader["company_fax"].ToString();
}
}catch（）{
}finally{
connection.Close();//运行之后再调用finally关闭连接
}

Answer 4

将
cmd.ExecuteReader()
改成
cmd.ExecuteReader(CommandBehavior.CloseConnection)
来返回DataReader，这样在使用完DataReader后关闭DataReader，相关的连接就会关闭。
把finally中的关闭连接代码删掉

Answer 5

string sqlcmd = "select * from system_config";
SqlDataReader reader = myData.MyReader(sqlcmd);
if (reader.Read())
{
sitename.Value = reader["sitename"].ToString();
en_sitename.Value = reader["en_sitename"].ToString();
company_name.Value = reader["company_name"].ToString();
en_company_name.Value = reader["en_company_name"].ToString();
company_tel.Value = reader["company_tel"].ToString();
company_fax.Value = reader["company_fax"].ToString();
}
if(!reader.IsClosed){reader.Closed();}