数据访问层DataAccess
数据访问层DataAccessusing 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();
}
//
}
}
