我的数据连接类
//*******************************************************************************************************
//
// 这是用于连接数据库的类
//
//******************************************************************************************************
using System;
using System.Data;
using System.Data.OleDb;
namespace DataAccess
{
/// <summary>
/// ClassDbBinding 的摘要说明。
/// </summary>
public class ClassDbBinding
{
static OleDbConnection cnn;
static OleDbDataAdapter da;
static DataTable tbl;
static string ConString; //连接字符串
public ClassDbBinding()
{
//
// TODO: 在此处添加构造函数逻辑
//
//构建连接
// cnn=new OleDbConnection();
// cnn.ConnectionString=oleDbConnectionString;
}
public static string getConString()
{
return ConString;
}
public static void setConString(string strCon)
{
ConString=strCon;
}
// public static string getConString //静态属性为何不能用?
// {
// get
// {
// return getConString;
//
// }
// set
// {
// if (value!=getConString)
// {
// getConString=value;
//
// }
// }
// }
//绑定到浏览需要的查询结果集
public DataTable BindingTable(string sqlStatement)
{
cnn=new OleDbConnection();
cnn.ConnectionString=ConString;
cnn.Open();
tbl=new DataTable();
da=new OleDbDataAdapter(sqlStatement,cnn);
da.Fill(tbl);
return tbl;
}
//以OleDbParameter[]为参数的查询
public OleDbCommand selectcmd(OleDbParameter[] cmdpArray)
{
cnn=new OleDbConnection();
cnn.ConnectionString=ConString;
cnn.Open();
OleDbCommand cmd= new OleDbCommand("SELECT * FROM 辅料表 WHERE 辅料ID Like ? and 辅料名 Like ? ", cnn);
for (int j=0; j<cmdpArray.Length; j++)
{
cmd.Parameters.Add(cmdpArray[j]) ;
}
return cmd;
}
//以string为参数的查询
public OleDbCommand pcmd(string strP)
{
cnn=new OleDbConnection();
cnn.ConnectionString=ConString;
cnn.Open();
OleDbCommand cmd= new OleDbCommand("SELECT * FROM 辅料表 WHERE 辅料ID ='"+strP+"'", cnn);
return cmd;
}
//构建DataSet
public DataSet dbFL(string cmdTxt)
{
cnn=new OleDbConnection();
cnn.ConnectionString=ConString;
cnn.Open();
DataSet dsFL=new DataSet();
da=new OleDbDataAdapter(cmdTxt,cnn);
da.Fill(dsFL,"辅料表");
return dsFL;
}
//插入数据到辅料表中
public OleDbCommand insertFL(string strIn)
{
cnn=new OleDbConnection();
cnn.ConnectionString=ConString;
try
{
cnn.Open();
}
catch(Exception e)
{
Console.WriteLine("产生错误:\n{0}",e.Message);
}
OleDbCommand cmdIn=new OleDbCommand("INSERT INTO 辅料表(辅料ID,辅料名,辅料规格, 辅料数量,单重,产地,批次) VALUES (?, ?, ?, ?, ?, ?, ?)",cnn);
OleDbParameterCollection pc=cmdIn.Parameters;
pc.Add(new System.Data.OleDb.OleDbParameter("辅料ID", System.Data.OleDb.OleDbType.VarWChar, 14, "辅料ID"));
pc.Add(new System.Data.OleDb.OleDbParameter("辅料名", System.Data.OleDb.OleDbType.VarWChar, 50, "辅料名"));
pc.Add(new System.Data.OleDb.OleDbParameter("辅料规格", System.Data.OleDb.OleDbType.VarWChar, 50, "辅料规格"));
pc.Add(new System.Data.OleDb.OleDbParameter("辅料数量", System.Data.OleDb.OleDbType.VarWChar,2, "辅料数量"));
pc.Add(new System.Data.OleDb.OleDbParameter("单重", System.Data.OleDb.OleDbType.VarWChar, 2, "单重"));
pc.Add(new System.Data.OleDb.OleDbParameter("产地", System.Data.OleDb.OleDbType.VarWChar, 50, "产地"));
pc.Add(new System.Data.OleDb.OleDbParameter("批次", System.Data.OleDb.OleDbType.VarWChar, 50, "批次"));
cmdIn.CommandText="INSERT INTO 辅料表(辅料ID,辅料名,辅料规格, 辅料数量,单重,产地,批次) VALUES ("+strIn+")";
try
{
cmdIn.ExecuteNonQuery();
}
catch(OleDbException e)
{
Console.WriteLine("连接出现错误："+e.Message);
}
return cmdIn;
}
//插入数据到出入库表中
public OleDbCommand insertCHR(string strIn)
{
cnn=new OleDbConnection();
cnn.ConnectionString=ConString;
try
{
cnn.Open();
}
catch(Exception e)
{
Console.WriteLine("产生错误:\n{0}",e.Message);
}
OleDbCommand cmdIn=new OleDbCommand("INSERT INTO 出入库表(操作ID,辅料ID,日期, 操作人,操作类型，提取数量) VALUES (?, ?, ?, ?, ?,?)",cnn);
OleDbParameterCollection pc=cmdIn.Parameters;
pc.Add(new System.Data.OleDb.OleDbParameter("操作ID", System.Data.OleDb.OleDbType.VarWChar, 14, "操作ID"));
pc.Add(new System.Data.OleDb.OleDbParameter("辅料ID", System.Data.OleDb.OleDbType.VarWChar, 50, "辅料ID"));
pc.Add(new System.Data.OleDb.OleDbParameter("日期", System.Data.OleDb.OleDbType.Date, 10, "日期"));
pc.Add(new System.Data.OleDb.OleDbParameter("操作人", System.Data.OleDb.OleDbType.VarWChar,2, "操作人,"));
pc.Add(new System.Data.OleDb.OleDbParameter("操作类型", System.Data.OleDb.OleDbType.VarWChar, 2, "操作类型"));
pc.Add(new System.Data.OleDb.OleDbParameter("提取数量", System.Data.OleDb.OleDbType.VarWChar, 2, "提取数量"));
cmdIn.CommandText="INSERT INTO 出入库表(操作ID,辅料ID,日期, 操作人,操作类型,提取数量) VALUES ("+strIn+")";
try
{
cmdIn.ExecuteNonQuery();
}
catch(OleDbException e)
{
Console.WriteLine("连接出现错误："+e.Message);
}
return cmdIn;
}
//更新辅料表记录
public OleDbCommand updateFL(string strUp)
{
cnn=new OleDbConnection();
cnn.ConnectionString=ConString;
try
{
cnn.Open();
}
catch(Exception e)
{
Console.WriteLine("产生错误:\n{0}",e.Message);
}
OleDbCommand cmdUp=new OleDbCommand("",cnn);
cmdUp.CommandText = @"UPDATE 辅料表 SET 产地 = ?, 单重 = ?, 辅料ID = ?, 辅料规格 = ?, 辅料名 = ?, 辅料数量 = ?, 批次 = ? WHERE (辅料ID = ?) AND (产地 = ? OR ? IS NULL AND 产地 IS NULL) AND (单重 = ? OR ? IS NULL AND 单重 IS NULL) AND (批次 = ? OR ? IS NULL AND 批次 IS NULL) AND (辅料名 = ? OR ? IS NULL AND 辅料名 IS NULL) AND (辅料数量 = ? OR ? IS NULL AND 辅料数量 IS NULL) AND (辅料规格 = ? OR ? IS NULL AND 辅料规格 IS NULL)";
cmdUp.Connection = cnn;
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("产地", System.Data.OleDb.OleDbType.VarWChar, 50, "产地"));
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("单重", System.Data.OleDb.OleDbType.Integer, 0, "单重"));
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("辅料ID", System.Data.OleDb.OleDbType.VarWChar, 14, "辅料ID"));
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("辅料规格", System.Data.OleDb.OleDbType.VarWChar, 50, "辅料规格"));
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("辅料名", System.Data.OleDb.OleDbType.VarWChar, 50, "辅料名"));
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("辅料数量", System.Data.OleDb.OleDbType.Integer, 0, "辅料数量"));
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("批次", System.Data.OleDb.OleDbType.VarWChar, 50, "批次"));
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_辅料ID", System.Data.OleDb.OleDbType.VarWChar, 14, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "辅料ID", System.Data.DataRowVersion.Original, null));
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_产地", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "产地", System.Data.DataRowVersion.Original, null));
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_产地1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "产地", System.Data.DataRowVersion.Original, null));
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_单重", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "单重", System.Data.DataRowVersion.Original, null));
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_单重1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "单重", System.Data.DataRowVersion.Original, null));
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_批次", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "批次", System.Data.DataRowVersion.Original, null));
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_批次1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "批次", System.Data.DataRowVersion.Original, null));
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_辅料名", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "辅料名", System.Data.DataRowVersion.Original, null));
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_辅料名1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "辅料名", System.Data.DataRowVersion.Original, null));
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_辅料数量", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "辅料数量", System.Data.DataRowVersion.Original, null));
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_辅料数量1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "辅料数量", System.Data.DataRowVersion.Original, null));
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_辅料规格", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "辅料规格", System.Data.DataRowVersion.Original, null));
cmdUp.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_辅料规格1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "辅料规格", System.Data.DataRowVersion.Original, null));
cmdUp.CommandText=strUp;
try
{
cmdUp.ExecuteNonQuery();
}
catch(OleDbException e)
{
Console.WriteLine("连接出现错误："+e.Message);
}
return cmdUp;
}
}
}