cclbc37
cclbc37
2020-12-30 15:23
采纳率: 16.7%
浏览 60

c#读取数据库中数据出现错误。

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using WindowsFormsApplication1;
using System.Data.OleDb;

//DataGridView双击那个个,那个就可以修改。 前提DataGridView有双击方法,可以对单个格子设置是否能修改。


namespace Supermarket_warehouse_management_system
{
    public partial class supplier : Form
    {
        OleDbOperations oleDbOperations = new OleDbOperations();
        public string sql;
        public supplier()
        {
            InitializeComponent();
        }

        private void supplier_Load(object sender, EventArgs e)          //加载项
        {
            sql = "select * from Supplier where 1=1";
            dataGridView1.DataSource = oleDbOperations.GetDataViewValue(sql);
        }

        private void button3_Click(object sender, EventArgs e)          //采购
            //供应表减少,超市表增加
        {
            string id = Convert.ToString(dataGridView1.CurrentRow.Cells[0].Value);      
            dataGridView1.ReadOnly = true; 
            //获取供应表信息,商品名,价格,数量。
             sql = "select * from Supplier where id='"+id+"'";
            OleDbDataReader dr = oleDbOperations.GetDataReaderValue(sql);           //获得流    
            int number=0;  //获取数量
            int num = Convert.ToInt32(dataGridView1.CurrentRow.Cells[4].Value);
            int PurchasePrice=0;         //获取价格
            string goods=null;         //获取商品名
            string suppliers=null;        //获取供应商
                        //获取ID
            while(dr.Read())
            {
                number = dr.GetInt32(dr.GetOrdinal("number"));          //获取数量
                PurchasePrice = dr.GetInt32(dr.GetOrdinal("PurchasePrice"));           //获取价格
                goods = dr.GetString(dr.GetOrdinal("goods"));          //获取商品名
                suppliers = dr.GetString(dr.GetOrdinal(" Supplier"));         //获取供应商
            }
            if (number - Convert.ToInt32(dataGridView1.CurrentRow.Cells[4].Value) > 0)      //数量足够
            {
                sql = "UPDATE Supermarket SET Supplier.[number] = " + num + " WHERE((id = '" + id + "')); ";
                Console.WriteLine(oleDbOperations.ExecuteNonQueryCount(sql));
                Console.WriteLine("足够");

                //超市
                //如果有该商品就修改,没有则增加。

                /*如何确定id值
                * X;遍历所有行,行数+1。前提ID值=行数 
                */
                //id=;
                sql = "select * from Supermarket where goods='" + goods + "' and PurchasePrice= '" + PurchasePrice + "'";
                int count = oleDbOperations.ExecuteNonQueryCount(sql);
                if (count > 0)
                {           //修改
                    sql = "UPDATE Supermarket Supermarket.[number] = " + num + " WHERE((id = '" + id + "')); ";
                    Console.WriteLine(oleDbOperations.ExecuteNonQueryCount(sql));
                    Console.WriteLine("修改");

                }
                else
                {           //增加
                    sql = "insert into Supermarket(id,goods,number,PurchasePrice,Supplier) values ('"+id+"','" + goods + "','" + num + "','" + PurchasePrice + "','" + suppliers + "')";
                    Console.WriteLine(oleDbOperations.ExecuteNonQueryCount(sql));
                    Console.WriteLine("增加");
                }

            }
            else 
            {
                MessageBox.Show("数量不足无法操作");
            }
            supplier_Load(this,null);

            dataGridView1.ReadOnly = false;  

        }
    }
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
//先传表
namespace WindowsFormsApplication1
{
    class OleDbOperations
    {
        private string path;
        private static string strconn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=";
        public static OleDbConnection oledb;


        //构造函数
        public OleDbOperations()
        {
            strconn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Application.StartupPath.ToString() + "\\Simulation of the supermarket.mdb";
            Console.WriteLine("strconn = "+strconn);
        }
        public OleDbOperations(string path)
        {
            this.path = path;
            strconn += path;
        }
        //打开数据库连接
        public void open()
        {
            string SqlCon = strconn;//数据库连接字符串
            Console.WriteLine("sqlcon"+SqlCon);
            oledb = new OleDbConnection(SqlCon);
            if (oledb.State == ConnectionState.Closed)          //如果关闭则打开,已打开无操作
                oledb.Open();          
        }
        //关闭数据库连接,已关闭误操作
        public void close()
        {
            //如果打开则关闭
            if (oledb.State.ToString().ToLower() == "open")
            {
                oledb.Close();
                oledb.Dispose();
            }
        }
        //执行语句
        public bool perform(string sql)
        {
            bool judge;
            OleDbCommand oledbmm = new OleDbCommand(sql, oledb);
            judge = Convert.ToBoolean(oledbmm.ExecuteScalar());
            if (judge)
                return true;
            else
                return false;
        }
        //返回DataSe  tableName映射源表名称
        public DataSet GetDataSetValue(string sql, string tableName)
        {
            open();
            OleDbDataAdapter da = new OleDbDataAdapter(sql, oledb);
            DataSet ds = new DataSet();
            //da.Fill(ds);
            da.Fill(ds, tableName);         //table虚表名,将查询到的数据放入ds中,这张表叫table可以通过ds["usertable"]访问到
            close();
            return ds;
        }
        //读取数据
        public  OleDbDataReader GetDataReaderValue(string sql)
        {
            open();
            OleDbCommand cmd = new OleDbCommand(sql, oledb);                //执行
            OleDbDataReader dr = cmd.ExecuteReader();
            close();
            return dr;
        }
        //返回DataView
        public  DataView GetDataViewValue(string sql)
        {
            open();
            OleDbDataAdapter da;
            DataSet ds = new DataSet();
            da = new OleDbDataAdapter(sql, oledb);
            da.Fill(ds, "temp");
            close();
            return ds.Tables[0].DefaultView;
        }
        //返回DataTable
        public  DataTable GetDataTableValue(string sql)
        {
            open();
            OleDbDataAdapter da = new OleDbDataAdapter(sql, oledb);
            DataTable dt = new DataTable();
            da.Fill(dt);
            close();
            return dt;
        }
        //执行一个Sql操作:添加,删除,更新操作
        public void ExecuteNonQuery(string sql)
        {
            open();
            OleDbCommand cmd;
            cmd = new OleDbCommand(sql, oledb);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            close();
        }
        //执行一个Sql操作:添加,删除,更新操作,返回受影响行数
        public int ExecuteNonQueryCount(string sql)
        {
            open();
            OleDbCommand cmd;
            cmd = new OleDbCommand(sql, oledb);
            int value = cmd.ExecuteNonQuery();
            close();
            return value;
        }
        //执行一条返回第一条记录第一列的SqlCommand命令
        public object ExecuteScalar(string sql)
        {
            open();
            OleDbCommand cmd = new OleDbCommand(sql, oledb);
            object value = cmd.ExecuteScalar();
            close();
            return value;
        }
        //返回记录数
        public int SqlServerRecordCount(string sql)
        {
            open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandText = sql;
            cmd.Connection = oledb;
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            int RecordCount = 0;
            while (dr.Read())
            {
                RecordCount++;
            }
            close();
            return RecordCount;
        }
        //判断是否为数字
        public static bool GetSafeValue(string value)
        {
            if (string.IsNullOrEmpty(value))
            {
                return false;
            }
            foreach (char ch in value)
            {
                if (!char.IsDigit(ch))
                {
                    return false;
                }
            }
            return true;
        }

    }
}

环境vs2010;

本人在学c#中的a.net,想从数据库中直接读取数据,就是使用了SqlDataReader。但这个报错把我搞蒙了,上网查教程看的很迷糊。

谢谢各位大佬。

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • wlj1234
    wlj1234 2020-12-30 16:14
    已采纳

    GetDataReaderValue()函数由问题,里面执行了close();将数据库连接给关闭了,OleDbDataReader对象也随之关闭,自然会报错

    OleDbDataReader用完后最好立刻关闭,否则新的OleDbDataReader对象执行Read便会出错

    点赞 评论

相关推荐