数据访问层DataAccess

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
namespace DataAccess
{
 /// <summary>
 /// DataBase 的摘要说明。
 /// </summary>
 public class DataBase
 {
  private SqlConnection vConn=null;
  public DataBase()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
  }
  /// <summary>
  /// 该函数用于打开conn
  /// </summary>
  /// <param name="vConn"></param>
  /// <returns>返回""表示没有出错。</returns>
  private String OpenConn(SqlConnection vConn)
  {
   try
   {
    if(vConn==null)
     vConn=new SqlConnection();
    vConn.ConnectionString=JRD.SystemCommon.SysOpVar.ConnString;
    vConn.Open();
    return "";
   }
   catch
   {
    return "连接错误";
   }
  } 
  /// <summary>
  /// 功能：执行增加，修改，删除操作
  /// </summary>
  /// <param name="vSql">输入：执行的SQL语句</param>
  /// <returns>输出：成功true, 失败false</returns>
  public bool ExecSQL(string vSql)
  {
   SqlCommand vCmd = null;
   bool vRet =false;   
   vConn = new SqlConnection();
   if(OpenConn(vConn) != "") //返回不为空值，则连接错误
    return false;
   else
   {
    try
    {
     vCmd = new SqlCommand(vSql, vConn);
     if (vCmd.ExecuteNonQuery() == 0)
     {
      vRet = false;
     }
     else
      vRet = true;
    }  
    catch
    {
     vRet=false;
    }
    finally
    {
     vConn.Close();
     vConn = null;
    }
    return vRet;
   }
           
         }
  /// <summary>
  /// 该函数直接用commandtext属性直接更新数据库数据,同时往系统日志库中插入数据
  /// </summary>
  /// <param name="SqlList">sqllist为多条sql语句集.</param>
  /// <returns>boolAppXtrz 判断是否要添加系统日志表记录,执行成功返回""</returns>
  public bool ExecuteSqlList(ArrayList SqlList)
  {
   SqlCommand vCmd= null;
   vConn = new SqlConnection();
   if(OpenConn(vConn) != "") //返回不为空值，则连接错误
    return false;
   else
   {
    SqlTransaction MyTran;
    int i;
    MyTran = vConn.BeginTransaction();
    vCmd = new SqlCommand();
    vCmd.Connection = vConn;    
    vCmd.Transaction = MyTran;
    try
    {
     for(i = 0;i<SqlList.Count;i++)
     {      
      vCmd.CommandText = SqlList[i].ToString();
      vCmd.ExecuteNonQuery();
     }
     MyTran.Commit();
     return true;
    }
    catch
    {
     return false;    
    }
    finally
    {
     vCmd =null;
     MyTran = null;
     vConn.Close();
     vConn = null;
    }
   }   
  }
  public SqlDataReader GetDataReader(string vSql)
  {
   SqlCommand vCmd=null;
   vConn = new SqlConnection();
   if(OpenConn(vConn) != "") //返回不为空值，则连接错误
    return null;
   else
   {
    try
    {
     vCmd = new SqlCommand(vSql, vConn);
     return vCmd.ExecuteReader(CommandBehavior.CloseConnection);
    }
    catch
    {
     return null;
    }
   }
  }
  public DataSet GetDataSet(string vSql)
  {
   SqlCommand vCmd=null;
   vConn = new SqlConnection();
   if(OpenConn(vConn) != "") //返回不为空值，则连接错误
    return null;
   else
   {
    try
    {
     vCmd = new SqlCommand(vSql, vConn);
     SqlDataAdapter vDtAdpt = new SqlDataAdapter();
     vDtAdpt.SelectCommand = vCmd;
     DataSet vDtset = new DataSet();
     vDtAdpt.Fill(vDtset, "pubtable");
     return vDtset;
    }
    catch
    {
     return null;
    }
    finally
    {
     vCmd=null;
     vConn.Close();
     vConn=null;
    }
   }
  }
  public DataTable GetDataTable(string vSql)
  {
   if(GetDataSet(vSql)==null)
    return null;
   else
       return GetDataSet(vSql).Tables[0];
  }
  /// <summary>
  /// 该函数用于绑定dataview
  /// </summary>
  /// <param name="strSql"></param>
  /// <param name="DV"></param>
  /// <returns></returns>
  public bool GetDataView(string strSql,DataView DV)
  {
   SqlDataAdapter Adapter = new SqlDataAdapter();
   DataSet DS = new DataSet();
   DataTable DTable =new DataTable();
   if(OpenConn(vConn) != "") //返回不为空值，则连接错误
    return false;
   else
   {
    try
    {
     Adapter = new SqlDataAdapter(strSql, vConn);
     Adapter.Fill(DS, "_tem");
     DTable = DS.Tables["_tem"];
     DV = new DataView(DTable);
     if(DV.Count == 0)  return false;
     else
      return true;
    }
    catch
    {
     return false;
    }
    finally
    {
     Adapter.Dispose();
     Adapter = null;
     DS.Dispose();
     DS = null;
     vConn.Close();
     vConn=null;
    }
   }
  }
  /// <summary>
  /// 取一个值的Sql语句，可以根据id去名称
  /// </summary>
  /// <param name="vSql"></param>
  /// <returns></returns>
  public object GetOneValue(string vSql)
  {
   SqlDataReader vDr=null;
   object vResult =null;
   try
   {
    vDr= GetDataReader(vSql);
    if(vDr.Read())
     vResult = vDr.GetValue(0);       
    vDr.Close();
   }
   catch
   {
    vResult=null;
   }
   finally
   {
    if(vDr != null)
    {
     vDr.Close();
     vDr=null;
    }
   }
            return vResult;
  }
  /// <summary>
  /// 取数据库中的最大值加1,通常用于增加数据
  /// </summary>
  /// <param name="vTableName"></param>
  /// <param name="vFieldName"></param>
  /// <returns></returns>
  public string MaxId(string vTableName,string vFieldName, string WhereSql)
  {
   string vSql =" select Max("+vFieldName+") as fMaxid from "+vTableName+" ";
   if(WhereSql!="")
    vSql+= WhereSql;

   string vId;
   object vMaxId = GetOneValue(vSql);
   if(vMaxId ==DBNull.Value)
    vId="1";
   else
    vId=(Int32.Parse(vMaxId.ToString()) + 1).ToString();

      return vId;
  }
       /// <summary>
       /// 返回最大值加1的编码
       /// </summary>
       /// <param name="TableName"></param>
       /// <param name="KeyFieldName"></param>
       /// <param name="KeyFieldLength"></param>
       /// <param name="WhereSql"></param>
       /// <returns></returns>
  public string GetMaxID(string TableName,string KeyFieldName,byte KeyFieldLength,string WhereSql)
  {
   string vMax = MaxId(TableName,KeyFieldName,WhereSql);
   string vMaxId="";
   if(KeyFieldLength>vMax.Length )
   {
    for(int i=0;i<KeyFieldLength - (vMax).Length;i++)
    {
     vMaxId+="0";
    }
   }
   return vMaxId+vMax;
  }
  /// <summary>
  /// 记录的条数
  /// </summary>
  /// <param name="vSql"></param>
  /// <returns></returns>
  public int GetRowCount(string vSql)
  {
            DataTable vDt =GetDataTable(vSql);
   return vDt.Rows.Count;
  }
  /// <summary>
  /// 是否存在记录
  /// </summary>
  /// <param name="vSql"></param>
  /// <returns></returns>
  public bool IsRecordExist(string vSql)
  {
   if(GetRowCount(vSql)>0)
    return true;
   else
    return false;
  }
  /// <summary>
  /// 该函数用于获得sql语句的第一条记录
  /// sql语句返回为空的时候，返回"null"或者Rowlist的Count=0
  /// </summary>
  /// <param name="Strsql"></param>
  /// <param name="RowList"></param>
  /// <returns>出错返回""</returns>
  public string GetFirstRow(string Strsql, ArrayList RowList)
  {
   SqlDataReader Reader;
   string strReturnValue="";
   Reader=GetDataReader(Strsql);
   return strReturnValue;
  }
 //
 //－－－－－－－－－－－－－－－－－－－－－－－－执行存储过程--------------------
  /// <summary>
  /// Private routine allowed only by this base class, it automates the task
  /// of building a OdbcCommand object designed to obtain a return value from
  /// the stored procedure.
  /// </summary>
  /// <param name="vStoredProc">Name of the stored procedure in the DB, eg. sp_DoTask</param>
  /// <param name="vParameters">Array of IDataParameter objects containing vParameters to the stored proc</param>
  /// <returns>Newly instantiated OdbcCommand instance</returns>
  private SqlCommand BuildIntCommand(string vStoredProc, IDataParameter[] vParameters)
  {
   SqlCommand vCmd = BuildQueryCommand( vStoredProc, vParameters );
   vCmd.Parameters.Add( new SqlParameter ( "@ReturnValue",
    System.Data.SqlDbType.Int,
    4, /* Size */
    ParameterDirection.ReturnValue,
    false, /* is nullable */
    0, /* byte precision */
    0, /* byte scale */
    string.Empty,
    DataRowVersion.Default,
    null ));

   return vCmd;
  }

  /// <summary>
  /// Builds a OdbcCommand designed to return a OdbcDataReader, and not
  /// an actual integer value.
  /// </summary>
  /// <param name="vStoredProc">Name of the stored procedure</param>
  /// <param name="vParameters">Array of IDataParameter objects</param>
  /// <returns></returns>
  public SqlCommand BuildQueryCommand(string vStoredProc, IDataParameter[] vParameters)
  {
   SqlCommand vCmd = new SqlCommand( vStoredProc, vConn );
   vCmd.CommandType = CommandType.StoredProcedure;
   foreach (SqlParameter parameter in vParameters)
   {
    vCmd.Parameters.Add( parameter );
   }

   return vCmd;
  }

  public object ExecProcedure(string vStoredProc, IDataParameter[] vParameters)
  {
   object vRet = 0;
   SqlDataReader vDr = RunProcedure (vStoredProc, vParameters);
   if (vDr.Read ())
   {
    vRet = vDr.GetValue (0);
    while (vDr.Read ());
   } 
   vDr.Close ();

   return vRet;
  }

  public int ExecProcedureNonQuery(string vStoredProc, IDataParameter[] vParameters)
  {
   vConn.Open ();
   SqlCommand vCmd = BuildQueryCommand( vStoredProc, vParameters );
   int vRowsAffected = vCmd.ExecuteNonQuery();   
   vConn.Close();
   return vRowsAffected;
  }

  /// <summary>
  /// Will run a stored procedure, can only be called by those classes deriving
  /// from this base. It returns a OdbcDataReader containing the vRet of the stored
  /// procedure.
  /// </summary>
  /// <param name="vStoredProc">Name of the stored procedure</param>
  /// <param name="vParameters">Array of vParameters to be passed to the procedure</param>
  /// <returns>A newly instantiated OdbcDataReader object</returns>
  public SqlDataReader RunProcedure(string vStoredProc, IDataParameter[] vParameters )
  {
   vConn.Open();
   SqlCommand vCmd = BuildQueryCommand( vStoredProc, vParameters );
   vCmd.CommandType = CommandType.StoredProcedure;

   return vCmd.ExecuteReader(CommandBehavior.CloseConnection);
  }

  /// <summary>
  /// Creates a DataSet by running the stored procedure and placing the vRets
  /// of the query/proc into the given tablename.
  /// </summary>
  /// <param name="vStoredProc"></param>
  /// <param name="vParameters"></param>
  /// <param name="vTableName"></param>
  /// <returns></returns>
  public DataSet RunProcedure(string vStoredProc, IDataParameter[] vParameters, string vTableName)
  {
   vConn.Open();

   DataSet vDSet = new DataSet ();
   SqlDataAdapter vAdtr = new SqlDataAdapter();
   vAdtr.SelectCommand = BuildQueryCommand(vStoredProc, vParameters);
   vAdtr.Fill(vDSet, vTableName);

   vConn.Close();

   return vDSet;
  }

  /// <summary>
  /// Takes an -existing- dataset and fills the given table name with the vRets
  /// of the stored procedure.
  /// </summary>
  /// <param name="vStoredProc"></param>
  /// <param name="vParameters"></param>
  /// <param name="vDSet"></param>
  /// <param name="vTableName"></param>
  /// <returns></returns>
  public void RunProcedure(string vStoredProc, IDataParameter[] vParameters, DataSet vDSet, string vTableName )
  {
   vConn.Open();
   SqlDataAdapter sqlDA = new SqlDataAdapter();
   sqlDA.SelectCommand = BuildQueryCommand( vStoredProc, vParameters );
   sqlDA.Fill( vDSet, vTableName );

   vConn.Close();   
  }
  //
 }
}