我的数据连接类

//*******************************************************************************************************

//

// 这是用于连接数据库的类

//

//******************************************************************************************************

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;

}

}

}

• ·将Access数据库移植到SQL Server
• ·如何处理ASP中的图象
• ·精华asp代码
• ·DOS环境变量
• ·计算水仙花数
• ·利用XMLHTTP下载文件
• ·在ASP.NET中实现POST发送数据
• ·NET Framework
• ·带格式的文本串，如何使用BCP导入数据库, 单
• ·系统学习方法趣谈