我的数据连接类

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

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;
   
   
  }

  }

 }

 

本文地址：http://com.8s8s.com/it/it43309.htm