封装一个获取SqlDataReader的方法,在调用时报错:“阅读器关闭时尝试调用 Read 无效”

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/qq_40245756/article/details/79968225

哇,这个问题真的好烦啊,在出现这个问题后查了好久的资料,最终在ID为“zhang_wj123”的大佬博客中发现了思路,在此感谢大佬

首先贴代码

{
        SqlConnection conn = new SqlConnection();
        string connStr = @"Data Source = TAOR\TAOR;Initial Catalog = 02.车辆; Integrated Security = True";
        //string connStr = "Data Source=.;Initial Catalog=02.车辆;Integrated Security=True";

        //方法一:只读查询所需数据
        public SqlDataReader GetReader(string sql_a, SqlParameter[] para)
        {
            conn = new SqlConnection(connStr);
            conn.Open();
            SqlCommand com_a = new SqlCommand(sql_a, conn);

            foreach (SqlParameter parameter in para)
            {
                com_a.Parameters.Add(parameter);
            }

            /*另一种思路
            for(int i=1;i<=para.Length;i++)
            {com_a.Parameters.AddRange(para);}
            */

            /*第三种思路
            com_a.Parameters.AddRange(para);//添加参数组
            */

            SqlDataReader RD_a = com_a.ExecuteReader();
            RD_a.Close();
            conn.Close();
            return RD_a;//此处出现问题
        }
}

RT,觉得上面啰嗦的话,核心代码即

conn = new SqlConnection(connStr);
            conn.Open();
            SqlCommand com_a = new SqlCommand(sql_a, conn);

            foreach (SqlParameter parameter in para)
            {
                com_a.Parameters.Add(parameter);
            }

            SqlDataReader RD_a = com_a.ExecuteReader();
            RD_a.Close();
            conn.Close();
            return RD_a;//此处出现问题

这是用GetReader 来获取一个SqlDataReader。但是在调用运行后会显示“阅读器关闭时尝试调用 Read 无效”

这是因为Reader与DataSet不同,DataSet已经将数据保存在本地内存中,而Reader并没有将数据保存在本地内存中。

出了封装的范围后,con.close()生效后,返回的sqldataReader毫无用处,其只能在连接范围内使用

此时不能关闭连接,也不能保持连接打开状态。很多系统为了解决这样两难的境地,只能放弃使用Reader模式的数据源,或者把连接对象交给方法调用者,以便进行关闭。


解决这个问题的方法很简单

CommandBehavior.CloseConnection

通过这句代码,即可以完成我们想要的功能。

这句代码的作用是能够保证当SqlDataReader对象被关闭时,其依赖的连接也会被自动关闭。

即只有在调用的sqldataReader关闭后,连接才会关闭

因此代码修改如下,即可以解决问题

SqlDataReader RD_a = com_a.ExecuteReader(CommandBehavior.CloseConnection);
            return RD_a;
            
            /*修改为上述代码
            RD_a.Close();
            conn.Close();
            return RD_a;//此处出现问题
            */
RT。


附:附带主函数部分代码如下:

private void button1_Click(object sender, EventArgs e)
        {
            double miles;
            string ChePai;
            string result=null;
            
            miles = Convert.ToDouble(textBox2.Text);
            ChePai = textBox1.Text;

            string str1 = null;
            str1 = "select LeiXing from Vehicle where ChePai=@chepai";

            //这里的地方用于循环添加新的sqlparameter的值
            SqlParameter[] str_sqlPara =
            {
                new SqlParameter("@chepai",SqlDbType.NChar,10),
            };
            str_sqlPara[0].Value = ChePai;
            
            SQLDataHelper reResult = new SQLDataHelper();//实例化一个对象为自建的sqldatahelper类
            SqlDataReader dr = reResult.GetReader(str1, str_sqlPara);
            
            while (dr.Read())
            {
                result = dr[0].ToString();
            }
            
            Vehicle v = null;
            //设定汽车 公交 卡车 依次为1 2 3 
            if (result == "1") v = new Car();
            if (result == "2") v = new Bus();
            if (result == "3") v = new Truck();
            label4.Text = ChePai + "号 收费" + v.JinE(miles) + "元";

            dr.Close();
        }


阅读更多

阅读器关闭尝试调用 Read 无效

11-05

现在提示我这段的我并没有关闭rs1啊,为什么?rnrn而且是在触发第二次:rn if (e.KeyCode == Keys.Space)rn rn if(lst.Count>1)rn rn timer1.Enabled = true;rn rn elsern rn MessageBox.Show("奖品数量不足", "抽奖信息出错");rn rn //开始抽奖rn rn才出现错误rnrn[code=C#]rn SqlConnection con = new SqlConnection(conn_str);rn SqlCommand command1 = new SqlCommand(sql_str, con);rn con.Open();rn SqlDataReader rs1 = command1.ExecuteReader();rn while (rs1.Read())rn rn tryrn rn if (File.Exists(rs1[1].ToString()))rn rn cj_names = rs1[1].ToString();rn cj_pic_path = rs1[3].ToString();rn cj_id = rs1[0].ToString();rn names.Add(cj_names);rn id.Add(cj_id);rn lst.Add(Image.FromFile(cj_pic_path));rn //重新从数据库装载LIST图片信息rn rn rnrn catch (FileNotFoundException)rn rn //忽略错误地址图片继续装入下一条图片记录rn rn con.Close();rn rnrn[/code]rnwhile (rs1.Read()),阅读器关闭时尝试调用 Read 无效。rnrnrn[code=C#]rnnamespace cj_sysrnrn public partial class Form1 : Formrn rn public Form1()rn rn InitializeComponent();rn rn int i = 0;rn List lst = new List();rn List names = new List();rn List id = new List();rn string conn_str = "Server=*;Database=*;uid=sa;pwd=*";rn string sql_str = "select * from cj_main where cj_show_counts>0";rn string sql_update;rn string cj_pic_path;rn string cj_names;rn string cj_id;rnrn private void Form1_KeyDown(object sender, KeyEventArgs e)rn rn if (e.KeyCode == Keys.Space)rn rn if(lst.Count>0)rn rn timer1.Enabled = true;rn rn elsern rn MessageBox.Show("奖品数量不足", "抽奖信息出错");rn rn //开始抽奖rn rn if (e.KeyCode == Keys.Enter)rn rn timer1.Enabled = false;rn if (lst.Count > 1)rn rn string ids = label3.Text;rn //确定抽奖结果rnrn sql_update = "update cj_main set cj_show_counts = rn cj_show_counts-1 where cj_id='" + Convert.ToInt32(ids) + "'";rn SqlConnection conn = new SqlConnection(conn_str);rn SqlCommand update = new SqlCommand(sql_update, conn);rn conn.Open();rn update.ExecuteNonQuery();rn conn.Close();rn lst.Clear();rn names.Clear();rn id.Clear();rn //更新抽中图片记录的次数rnrn SqlConnection con = new SqlConnection(conn_str);rn SqlCommand command1 = new SqlCommand(sql_str, con);rn con.Open();rn SqlDataReader rs1 = command1.ExecuteReader();rn while (rs1.Read())rn rn tryrn rn if (File.Exists(rs1[1].ToString()))rn rn cj_names = rs1[1].ToString();rn cj_pic_path = rs1[3].ToString();rn cj_id = rs1[0].ToString();rn names.Add(cj_names);rn id.Add(cj_id);rn lst.Add(Image.FromFile(cj_pic_path));rn //重新从数据库装载LIST图片信息rn rn rnrn catch (FileNotFoundException)rn rn //忽略错误地址图片继续装入下一条图片记录rn rn con.Close();rn rn rn elsern rn timer1.Enabled = false;rn lst.Clear();rn names.Clear();rn id.Clear();rn MessageBox.Show("奖品数量不足","抽奖信息出错");rn //处理剩余奖品数量不足以继续抽奖,终止。rn rn rn if (e.KeyData == Keys.Escape) rn rn rn Application.Exit();rn //退出程序rn rn rnrn private void Form1_Load(object sender, EventArgs e)rn rn this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.None;rn this.WindowState = FormWindowState.Maximized;rnrn SqlConnection conn = new SqlConnection(conn_str);rn SqlCommand command = new SqlCommand(sql_str, conn);rn conn.Open();rn SqlDataReader rs = command.ExecuteReader();rn tryrn rn while (rs.Read())rn rn if (File.Exists(rs[3].ToString()))rn rn cj_names = rs[1].ToString();rn cj_pic_path = rs[3].ToString();rn cj_id = rs[0].ToString();rn names.Add(cj_names);rn id.Add(cj_id);rn lst.Add(Image.FromFile(cj_pic_path));rn //从数据库中取得图片信息rn rn rn rn catch (FileNotFoundException)rn rn //如果找不到图片继续寻找下一条记录rn rn conn.Close(); rn rnrn private void timer1_Tick(object sender, EventArgs e)rn rn if (lst.Count > 1)rn rn pictureBox1.Image = lst[i];rn label1.Text = names[i];rn label3.Text = id[i];rn i++;rn if (i >= lst.Count)rn rn i = 0;rn rn //窗体初始装载LIST图片rn rn elsern rn rn lst.Clear();rn names.Clear();rn id.Clear(); rn timer1.Enabled = false;rn MessageBox.Show("奖品数量不足", "抽奖信息出错");rn //数据库中已没有符合条件的记录rn rnrn rnrn rnrnrn[/code]

求助! 阅读器关闭Read尝试无效

10-12

问题提示:1.阅读器关闭时 Read 的尝试无效。2.未将对象引用设置到对象的实例。3.内部连接致命错误。4.不允许更改“ConnectionString”属性。连接的当前状态为正在连接。rn以上错误都会出现。rnrn一般运行程序时都没问题就是偶尔来一下。(程序是C#)rnrnrn错误源码:rnrn行 166: catch (Exception e)rn行 167: rn行 168: throw new Exception(e.Message); //提示这行有问题rn行 169: rn行 170: finallyrnrn完整源码:rnrn /// rn /// 执行Sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -> Unbox rn /// rn /// 传入的Sql语句 rn /// object 返回值 rn public static object ExecuteScalar(string sqlstr)rn rn object obj = new object();rn SqlCommand comm = new SqlCommand();rn tryrn rn openConnection();rn comm.Connection = conn;rn comm.CommandType = CommandType.Text;rn comm.CommandText = sqlstr;rn obj = comm.ExecuteScalar();rn rn catch (Exception e)rn rn throw new Exception(e.Message);rn rn finallyrn rn comm.Dispose(); rn closeConnection();rn rn return obj;rn rnrnrn调用的源码:rnrn public string Get_SQL_OnlyRecord(string sql)rn rn return PS_Conn.ExecuteScalar(sql).ToString();rn rn rn public string Get_Focus_Img() rn rn rn string dataSet = Get_SQL_OnlyRecord("select FocusImg from PS_WebSet where ID = 1"); rn rn string FocusList = "\r\n";rn int Order = 0;rn string[] sArray = Regex.Split(dataSet, ",", RegexOptions.IgnoreCase);rn foreach (string i in sArray)rn rn Order++;rn if (Order <= 4)rn rn string BookID = i.ToString();rn string BookImg = Get_SQL_OnlyRecord("select BookCover from ps_books where bookid = " + BookID + "");rn string BookName = Get_SQL_OnlyRecord("select BookName from ps_books where bookid = " + BookID + "");rn string BookIntro = Get_SQL_OnlyRecord("select BookIntro from ps_books where bookid = " + BookID + "");rn rn FocusList += " imgUrl[" + Order + "]='/uploadfiles/" + BookImg + "';\r\n";rn FocusList += " imgSUrl[" + Order +"]='/uploadfiles/" + BookImg + "';\r\n";rn FocusList += " imgtext[" + Order + "]='" + BookName + "';\r\n";rn FocusList += " imgLink[" + Order + "]='/book/" + BookID + "/';\r\n";rn FocusList += " imgAlt[" + Order + "]='" + BookName + "';\r\n";rn FocusList += " imgIntro[" + Order + "]='" + PS_Site.Substring(PS_Site.NoAllHTML(BookIntro), 60) + "[详细]';\r\n";rn rn rn rn return FocusList;rn rnrn在上面rn string dataSet = Get_SQL_OnlyRecord("select FocusImg from PS_WebSet where ID = 1"); rn这句读取数据库时有时正确,有时错误。rnrn一直找不到原因,请大家帮帮忙![code=C#][/code][code=C#][/code][code=C#][/code]

没有更多推荐了,返回首页