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。但这个报错把我搞蒙了，上网查教程看的很迷糊。
谢谢各位大佬。
