database.cs

介绍学习的管理系统说明什么的

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


namespace DataAccess
{
	/// <summary>
	///
	/// </summary>
	public class DataBase
	{
		private String Server, dbName;
		private String SqlConnectionString;
		private SqlConnection con;		

		public DataBase()
        {
			// 初始化数据库连接字符串
			Server = ConfigurationSettings.AppSettings["Server"].Trim();
			dbName = ConfigurationSettings.AppSettings["Database"].Trim();
			SqlConnectionString = "user id=sa; initial catalog=" + dbName + ";data source=" + Server + ";Connect Timeout=30";
		}
		/// <summary>
		/// 打开数据库连接。
		/// </summary>
		private void Open() 
		{
			if (con == null) 
			{
				con = new SqlConnection(SqlConnectionString);
				con.Open();
			}
		}

		/// <summary>
		/// 关闭数据库连接。
		/// </summary>
		public void Close() 
		{
			if (con != null)
			{
				con.Close();
				this.Dispose();
			}	
		}

		/// <summary>
		/// Release resources.
		/// 释放资源。
		/// </summary>
		public void Dispose() 
		{
			// make sure connection is closed
			if (con != null) 
			{
				con.Dispose();
				con = null;
			}				
		}

		/// <summary>
		/// 测试数据库连接是否成功
		/// </summary>
		/// <param name="server">server</param>
		/// <param name="database">database</param>
		/// <param name="uid">用户名</param>
		/// <param name="password">密码</param>
		/// <returns>bool</returns>
		public bool TestConnection(String server, String database, String uid, String password)
		{
			try
			{	
				con = null;
				SqlConnectionString = "user id=" + uid + ";password=" + password + ";initial catalog=" + database + ";data source=" + server + ";Connect Timeout=30";
				this.Open();
			}
			catch
			{				
				return false;
			}
			return true;
		}

		/// <summary>
		/// 创建command对象以便执行sql语句。
		/// </summary>
		/// <param name="sql">Sql Text.</param>		
		/// <returns>Command object.</returns>
		private SqlCommand CreateCommand(string sql) 
		{
			// make sure connection is open
			Open();
			SqlCommand cmd = new SqlCommand(sql, con);
			cmd.CommandType = CommandType.Text;
			return cmd;
		}

		/// <summary>
		/// 创建带Prameters的Command对象
		/// </summary>
		/// <param name="sql">sql语句</param>
		/// <param name="prams">SqlParameters参数</param>
		/// <returns>Command对象</returns>
		public SqlCommand CreateCommand(String sql, SqlParameter[] prams)
		{
			Open();
			SqlCommand cmd = new SqlCommand(sql,con);
			cmd.CommandType = CommandType.Text;
			cmd.Parameters.Clear();			
			if (prams != null)
			{
				foreach (SqlParameter parameter in prams)
					cmd.Parameters.Add(parameter);
			}
			return cmd;
		}

		/// <summary>
		/// 创建执行存储过程的SqlCommand对象。
		/// </summary>
		/// <param name="p_upName">存储过程名</param>
		/// <param name="p_prams">参数</param>
		/// <returns>SqlCommand对象</returns>
		public SqlCommand CreateProcedureCommand(String p_upName, SqlParameter[] p_prams)
		{
			Open();
			SqlCommand cmd = new SqlCommand(p_upName, con);
			cmd.CommandType = CommandType.StoredProcedure;			
			if (p_prams != null)
			{
				foreach (SqlParameter parameter in p_prams)
					cmd.Parameters.Add(parameter);
			}
			// return param
			cmd.Parameters.Add(
				new SqlParameter("ReturnValue", SqlDbType.Int, 4,
				ParameterDirection.ReturnValue, false, 0, 0,
				string.Empty, DataRowVersion.Default, null));
			return cmd;
		}

        /// <summary>
        /// 创建执行存储过程的SqlCommand对象。无参数
        /// </summary>
        /// <param name="p_upName">存储过程名</param>
        /// <returns>SqlCommand对象</returns>
        public SqlCommand CreateProcedureCommand(String p_upName)
        {
            Open();
            SqlCommand cmd = new SqlCommand(p_upName, con);
            cmd.CommandType = CommandType.StoredProcedure;
            // return param
            cmd.Parameters.Add(
                new SqlParameter("ReturnValue", SqlDbType.Int, 4,
                ParameterDirection.ReturnValue, false, 0, 0,
                string.Empty, DataRowVersion.Default, null));
            return cmd;
        }

        /// <summary>
        /// 执行存储过程 eg:无参数
        /// RunProc("upProcedureName", prams);			// run the stored procedure
        /// strVlaue = (string) prams[index].Value;     // get the output param value
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <returns>成功是否</returns>
        public string RunProc(string procName)
        {
            string a = "ok";
            SqlCommand cmd = CreateProcedureCommand(procName);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

                a = ex.Message.ToString(); ;
            }
            finally
            {
                this.Close();
            }
            return a;
        }


		/// <summary>
		/// 执行存储过程 eg:
		/// RunProc("upProcedureName", prams);			// run the stored procedure
		/// strVlaue = (string) prams[index].Value;     // get the output param value
		/// </summary>
		/// <param name="procName">存储过程名</param>
		/// <param name="prams">参数</param>
		/// <returns>成功是否</returns>
		public string RunProc(string procName, SqlParameter[] prams)
        {
            string a = "ok";
			SqlCommand cmd = CreateProcedureCommand(procName, prams);
			try
			{
				cmd.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				
				a = ex.Message.ToString();;
			}
			finally
			{
				this.Close();
			}
            return a;
		}

		/// <summary>
		/// 执行一个无返回的sql语句
		/// </summary>				
		/// <param name="sql">sql语句</param>
		/// <returns>执行结果</returns>
		public bool QueryExec(string sql)
		{				
			SqlCommand cmd = CreateCommand(sql);
            //try
            //{				
				cmd.ExecuteNonQuery();
            //}
            //catch(Exception ex)
            //{	
            //    ex.Message.ToString();
            //    return false;
            //}
            //finally
            //{
            //    this.Close();				
            //}
			return true;
		}

		/// <summary>
		/// 执行一个无返回的sql语句，带parameters
		/// </summary>				
		/// <param name="sql">sql语句</param>
		/// <returns>执行结果</returns>
		public bool QueryExec(string sql, SqlParameter[] parms)
		{				
			SqlCommand cmd = CreateCommand(sql, parms);
			try
			{				
				cmd.ExecuteNonQuery();
			}
			catch(Exception ex)
			{	
				ex.Message.ToString();
				return false;
			}
			finally
			{
				this.Close();				
			}
			return true;
		}

		/// <summary>
		/// 执行一个插入记录操作，返回primary key
		/// </summary>				
		/// <param name="sql">insert sql语句</param>
		/// <returns>返回的primary key</returns>
		public String InsertExec(string sql)
		{
			sql += ";SELECT @@identity AS [@@IDENTITY];";
			SqlCommand cmd = CreateCommand(sql);
			try
			{			
				return cmd.ExecuteScalar().ToString();
			}
			catch
			{
				return null;
			}
			finally
			{
				this.Close();				
			}
		}

		/// <summary>
		/// 执行一个插入记录操作，带parameters，返回primary key
		/// </summary>				
		/// <param name="sql">insert sql语句</param>
		/// <returns>返回的primary key</returns>
		public String InsertExec(string sql, SqlParameter[] prams)
		{
			sql += ";SELECT @@identity AS [@@IDENTITY];";
			SqlCommand cmd = CreateCommand(sql, prams);
			try
			{			
				return cmd.ExecuteScalar().ToString();
			}
			catch(Exception ex)
			{
				ex.Message.ToString();
				return null;
			}
			finally
			{
				this.Close();				
			}
		}

		/// <summary>
		/// 通过查询指定的SQL语句来获得一个返回值
		/// </summary>
		/// <param name="sql">SQL语句</param>
		/// <returns>返回值</returns>
		public String QueryValue(string sql)
		{
			SqlCommand cmd = CreateCommand(sql);
			try
			{				
				return cmd.ExecuteScalar().ToString();
			}
			catch(Exception ex)
			{	
				ex.Message.ToString();
				return null;
			}
			finally
			{
				this.Close();
			}
		}	
		/// <summary>
		/// 通过查询指定的SQL语句来获得一个返回表，带parms
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="prams"></param>
		/// <returns></returns>
		public DataTable QueryDataTable(string sql,SqlParameter[] parms)
		{
			DataTable dataTable = new DataTable();
			SqlCommand cmd = CreateCommand(sql,parms);
			try
			{			
				SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
				dataAdapter.Fill(dataTable);
			}
			catch(Exception ex)
			{
				ex.Message.ToString();
				dataTable = null;
			}
			finally
			{
				this.Close();
			}
			return dataTable;
		}

		/// <summary>
		/// 通过查询指定的SQL语句来获得一个返回值，带parms
		/// </summary>
		/// <param name="sql">SQL语句</param>
		/// <returns>返回值</returns>
		public String QueryValue(string sql, SqlParameter[] prams)
		{
			SqlCommand cmd = CreateCommand(sql, prams);
			try
			{				
				return cmd.ExecuteScalar().ToString();
			}
			catch(Exception ex)
			{	
				ex.Message.ToString();
				return null;
			}
			finally
			{
				this.Close();
			}
		}
	
		/// <summary>
		/// 查询返回DATAREADER
		/// </summary>
		/// <param name="dr"></param>
		/// <param name="error"></param>
		/// <param name="SqlConnectionString"></param>
		/// <param name="sql">sql语句</param>
		public SqlDataReader QueryDataReader(string sql)
		{			
			try
			{
				SqlCommand cmd = CreateCommand(sql);
				return cmd.ExecuteReader();		
			}
			catch
			{	
				return null;
			}
			finally
			{				
				//
			}	
		}

		/// <summary>
		/// 查询返回DATASET
		/// </summary>
		/// <param name="sql">sql语句</param>
		/// <returns>DataSet对象</returns>
		public DataSet QueryDataSet (string sql)
		{			
			SqlCommand cmd = CreateCommand(sql);
			DataSet ds = new DataSet();			
            try
            {
				SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);							
				dataAdapter.Fill(ds);
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
                ds = null;
            }
            finally
            {
                this.Close();
            }
			return ds;	
		}

		/// <summary>
		/// Query Open Result With DataTable.
		/// 通过sql语句返回DataTable。
		/// Create by wujq.
		/// </summary>
		/// <returns>DataTable</returns>
		public DataTable QueryDataTable(string sql)
		{
			DataTable dataTable = new DataTable();
			SqlCommand cmd = CreateCommand(sql);
			try
			{			
				SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
				dataAdapter.Fill(dataTable);
			}
			catch(Exception ex)
			{	
				ex.Message.ToString();
				dataTable = null;
			}
			finally
			{
				this.Close();
			}
			return dataTable;
		}

		/// <summary>
		/// Make input param.
		/// 包装输入参数。
		/// </summary>
		/// <param name="ParamName">Name of param.</param>
		/// <param name="DbType">Param type.</param>
		/// <param name="Size">Param size.</param>
		/// <param name="Value">Param value.</param>
		/// <returns>New parameter.</returns>
		public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value) 
		{
			return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
		}		

		/// <summary>
		/// Make output param.
		/// 包装输出参数。
		/// </summary>
		/// <param name="ParamName">Name of param.</param>
		/// <param name="DbType">Param type.</param>
		/// <param name="Size">Param size.</param>
		/// <returns>New parameter.</returns>
		public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size) 
		{
			return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
		}	

		public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size, object Value) 
		{
			return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, Value);
		}	

		/// <summary>
		/// Make stored procedure param.
		/// 包装Command参数。
		/// </summary>
		/// <param name="ParamName">Name of param.</param>
		/// <param name="DbType">Param type.</param>
		/// <param name="Size">Param size.</param>
		/// <param name="Direction">Parm direction.</param>
		/// <param name="Value">Param value.</param>
		/// <returns>New parameter.</returns>
		public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value) 
		{
			SqlParameter param;

			if(Size > 0)
				param = new SqlParameter(ParamName, DbType, Size);
			else
				param = new SqlParameter(ParamName, DbType);

			param.Direction = Direction;
			if (!(Direction == ParameterDirection.Output && Value == null))
				param.Value = Value;
			else Value = param.Value;
			return param;
		}
        /// <summary>
        /// 返回一个表或视图
        /// </summary>
        /// <param name="sql">生成表或视图的SQL的语句</param>
        /// <returns>New View</returns>
        public DataView View(string sql)
        {
            DataTable ds = new DataTable();
            try
            {
                SqlCommand com = CreateCommand(sql);
                SqlDataAdapter sqladap = new SqlDataAdapter(com);
               
                sqladap.Fill(ds);
            }
            catch
            { 
                //ds = null;
            }
            finally
            {
                this.Close();
            }
            return ds.DefaultView;

        }
	}
}