accessdb.cs

专业的办公oa代码下载 c#语言编写 三层结构

using System;
using System.Data;
using System.Data.SqlClient;


namespace qminoa.DA
{
	/// <summary>
	/// AccessDB.cs 定义数据库访问的各种方法
	/// 
	/// Author: Sun Qiang
	/// </summary>
	public class AccessDb
	{
		//各种数据类型、对象声明
		protected SqlDataAdapter sqlDataAdapter;
		protected SqlCommand sqlCommand;
		protected SqlConnection sqlConnection;
		protected SqlDataReader sqlDataReader;
		protected DataSet dataSet;
		protected DataView dataView;

		//数据库连接字符串
		string connString = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString");

	
        
		public AccessDb()
		{			
			//构造函数　功能：打开数据库连接
			sqlDataAdapter = new SqlDataAdapter();
			sqlDataAdapter.SelectCommand = new SqlCommand();
			sqlDataAdapter.SelectCommand.Connection = new SqlConnection(connString);
			sqlDataAdapter.SelectCommand.Connection.Open();

		}//End 构造函数
		
		//简单的登录数据库处理过程　只需要输入用户名即可
		public DataSet Login(string loginUsername, string loginPassword)
		{
			//执行存储过程 cpSempleLogin
			sqlDataAdapter.SelectCommand.CommandText = "cpSempleLogin";
			sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

			sqlDataAdapter.SelectCommand.Parameters.Add("@LoginID",SqlDbType.VarChar,16);
			sqlDataAdapter.SelectCommand.Parameters["@LoginID"].Value = loginUsername;

			dataSet = new DataSet();
			
			sqlDataAdapter.Fill(dataSet);

			sqlDataAdapter.SelectCommand.Connection.Close();

			return dataSet;
		}//End Login函数

		//接收存储过程名
		public void SendStoredProcedure(string storedProcedureName)
		{
			sqlDataAdapter.SelectCommand.Parameters.Clear();
			sqlDataAdapter.SelectCommand.CommandText = storedProcedureName;
			sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
		}

		//接收存储过程参数名、参数名类型、参数值 参数值类型重载(int paraValue)
		public void SendStoredProcedureParameters(string paraName, SqlDbType paraType, int paraValue)
		{
			sqlDataAdapter.SelectCommand.Parameters.Add(paraName, paraType);
			sqlDataAdapter.SelectCommand.Parameters[paraName].Value = paraValue;
		}

		//接收存储过程参数名、参数名类型、参数值 参数值类型重载(string paraValue)
		public void SendStoredProcedureParameters(string paraName, SqlDbType paraType, string paraValue)
		{
			sqlDataAdapter.SelectCommand.Parameters.Add(paraName, paraType);
			sqlDataAdapter.SelectCommand.Parameters[paraName].Value = paraValue;
		}

		//接收存储过程参数名、参数名类型、参数值 参数值类型重载(bool paraValue)
		public void SendStoredProcedureParameters(string paraName, SqlDbType paraType, bool paraValue)
		{
			sqlDataAdapter.SelectCommand.Parameters.Add(paraName, paraType);
			sqlDataAdapter.SelectCommand.Parameters[paraName].Value = paraValue;
		}

		//接收存储过程参数名、参数名类型、参数值 参数值类型重载(byte paraValue)
		public void SendStoredProcedureParameters(string paraName, SqlDbType paraType, byte paraValue)
		{
			sqlDataAdapter.SelectCommand.Parameters.Add(paraName, paraType);
			sqlDataAdapter.SelectCommand.Parameters[paraName].Value = paraValue;
		}

		//接收存储过程参数名、参数名类型、参数值 参数值类型重载(char paraValue)
		public void SendStoredProcedureParameters(string paraName, SqlDbType paraType, char paraValue)
		{
			sqlDataAdapter.SelectCommand.Parameters.Add(paraName, paraType);
			sqlDataAdapter.SelectCommand.Parameters[paraName].Value = paraValue;
		}

		//接收存储过程参数名、参数名类型、参数值 参数值类型重载(object paraValue)
		public void SendStoredProcedureParameters(string paraName, SqlDbType paraType, object paraValue)
		{
			sqlDataAdapter.SelectCommand.Parameters.Add(paraName, paraType);
			sqlDataAdapter.SelectCommand.Parameters[paraName].Value = paraValue;
		}

		//执行Sql语句或者存储过程，但不需返回值
		public void ExecuteNoReturn()
		{
			sqlDataAdapter.SelectCommand.ExecuteNonQuery();
		}

		public void ExecuteReader()
		{
			sqlDataAdapter.SelectCommand.ExecuteReader();
			//sqlDataAdapter.UpdateCommand = "";
			//sqlDataAdapter.UpdateCommand.
		}


		//返回DataSet类型对象
		public DataSet ReturnDataSetResult()
		{
			dataSet = new DataSet();

			sqlDataAdapter.Fill(dataSet);
			return dataSet;
		}

		//重载ReturnDataSetResult()
		//返回DataSet类型对象到指定表中
		public DataSet ReturnDataSetResult(string tableName)
		{
			sqlDataAdapter.Fill(dataSet, tableName);
			return dataSet;
		}

		//返回DataView类型引用
		public DataView ReturnDataViewResult()
		{
			ReturnDataSetResult();

			dataView = dataSet.Tables[0].DefaultView;

			return dataView;
		}

		//返回SqlDataReader类型引用
		public SqlDataReader ReturnSqlDataReaderResult()
		{
			//sqlDataReader = new SqlDataReader();
            sqlDataReader = sqlDataAdapter.SelectCommand.ExecuteReader();

			return sqlDataReader;
		}

		//执行SQL语句并返回SqlDataReader类型对象
		public SqlDataReader ExecuteSql(string sql)
		{
			sqlCommand = new SqlCommand();
			sqlCommand.Connection = new SqlConnection(connString);
			sqlCommand.CommandText = sql;
			sqlDataReader = sqlCommand.ExecuteReader();
			return sqlDataReader;
		}

		//关闭数据库连接
		public void CloseConnection()
		{
			sqlConnection.Close();
		}


		public void ClearParameters()
		{
			sqlDataAdapter.SelectCommand.Parameters.Clear();
		}

		//		public void SendStoredProcedureParameters(string paraName, SqlDbType paraType, int paraSize)
		//		{
		//			sqlDataAdapter.SelectCommand.Parameters.Add(paraName, paraType, paraSize);
		//		}
		//
		//		public void SendStoredProcedureParameters(string paraName, SqlDbType paraType, int paraSize, string paraSourceColumn)
		//		{
		//			sqlDataAdapter.SelectCommand.Parameters.Add(paraName, paraType, paraSize, paraSourceColumn);
		//		}
	
	}
}