databaseadmin.cs

很不错的学生管理系统

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

namespace com.egt.data
{
	/// <summary>
	/// DataBase 的摘要说明。
	/// </summary>
	public class DataBaseAdmin
	{
		private static DataSet dicDs = new DataSet();

		private DataBaseAdmin()
		{

		}
		public static DataBaseAdmin getInstance()
		{
			DataBaseAdmin db=new DataBaseAdmin();
			return db;
		}
		//取出数据库连接字符串
        private static readonly string conStr = ConfigurationSettings.AppSettings["ConnString"];		
		
		/// <summary>
		/// 得到数据库连接对象
		/// </summary>
		/// <returns>数据库连接对象</returns>
		public static SqlConnection GetConObject()
		{
            return new SqlConnection(conStr);
		}


		/// <summary>
		/// 执行sql语句查询数据库
		/// by SunJing
		/// </summary>
		/// <param name="command">sql语句</param>
		/// <returns>查询结果数据集</returns>
		public static DataSet ExecuteQueryCommand(SqlCommand command)
		{
			DataSet ds = new DataSet();
			SqlConnection con = GetConObject();
			command.Connection = con;
            SqlDataAdapter oda = new SqlDataAdapter(command);
			oda.Fill(ds);//填充到数据集中
			if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
			{
				ds = null;
			}
			return ds;
		}

		#region 执行存储过程
		/// <summary>
		/// 执行操作数据库的存储过程
		/// </summary>
		/// <param name="procName">存储过程名称</param>
		/// <param name="cmdParms">存储过程所使用的参数</param>
		/// <returns>存储过程执行后所影响的行数</returns>
		public static int ExecuteNonQuery(string procName, SqlParameter[] cmdParms) 
		{
            SqlCommand cmd = new SqlCommand();

			using (SqlConnection con = GetConObject()) 
			{
				CreateCommand(cmd, con, null, procName, cmdParms);
				int val = cmd.ExecuteNonQuery();
				cmd.Parameters.Clear();
				return val;
			}
		}	

		
		/// <summary>
		/// 执行操作数据库的存储过程(事务控制)
		/// </summary>
		/// <param name="trans">数据库连接所关联的事务对象</param>
		/// <param name="procName">存储过程名称</param>
		/// <param name="cmdParms">存储过程所使用的参数</param>
		/// <returns>存储过程执行后所影响的行数</returns>
        public static int ExecuteNonQuery(SqlTransaction trans, string procName, SqlParameter[] cmdParms) 
		{
            SqlCommand cmd = new SqlCommand();
			CreateCommand(cmd, trans.Connection, trans, procName, cmdParms);
			int val = cmd.ExecuteNonQuery();
			cmd.Parameters.Clear();
			return val;
		}


		public static int ExecuteNonQuery( string[] procName, SqlParameter[][] cmdParms) 
		{
			SqlConnection con = GetConObject();
			using (SqlTransaction trans =con.BeginTransaction(IsolationLevel.Serializable))
			{
				try
				{
                    SqlCommand cmd = new SqlCommand();
					for(int i=0;i < procName.Length;i++)
					{
						CreateCommand(cmd, trans.Connection, trans, procName[i], cmdParms[i]);
						cmd.ExecuteNonQuery();
						cmd.Parameters.Clear();
					}
					trans.Commit();
					return 1;

				}
				catch(Exception)
				{
					trans.Rollback();
				}
				finally
				{
					con.Close();
				}
				return 0;
			}
		}
		#endregion

		#region 返回只读结果集
		/// <summary>
		/// 执行读数据集操作，以DataReader的形式返回
		/// </summary>
		/// <param name="procName">存储过程名称</param>
		/// <param name="cmdParms">存储过程所使用的参数</param>
		/// <returns>DataReader对象</returns>
		public static SqlDataReader ExecuteReader(string procName, SqlParameter[] cmdParms) 
		{
            SqlCommand cmd = new SqlCommand();
			SqlConnection con = GetConObject();

			try 
			{
				CreateCommand(cmd, con, null, procName, cmdParms);
				SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				cmd.Parameters.Clear();
				return dr;
			}
			catch(Exception e)
			{
				con.Close();
				throw(e);
			}			
		}
        public static SqlDataReader ExecuteReader(SqlTransaction trans, string procName, SqlParameter[] cmdParms) 
		{
            SqlCommand cmd = new SqlCommand();
			//OracleConnection con = GetConObject();

			try 
			{
				CreateCommand(cmd, trans.Connection, trans, procName, cmdParms);
				SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				cmd.Parameters.Clear();
				return dr;
			}
			catch(Exception e)
			{
				//con.Close();
				throw(e);
			}			
		}		
		
		/// <summary>
		/// 执行读数据集操作，以DataReader的形式返回
		/// </summary>
		/// <param name="procName">存储过程名称</param>		
		/// <returns>DataReader对象</returns>
        public static SqlDataReader ExecuteReader(string procName) 
		{
            SqlCommand cmd = new SqlCommand();
			SqlConnection con = GetConObject();

			try 
			{
				CreateCommand(cmd, con, null, procName, null);
				SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				cmd.Parameters.Clear();
				return dr;
			}
			catch 
			{
				con.Close();
				throw;
			}			
		}
		#endregion
		
		/// <summary>
		/// 执行读数据集操作，以DataSet的形式返回
		/// </summary>
		/// <param name="procName">存储过程名称</param>
		/// <param name="cmdParms">存储过程所使用的参数</param>
		/// <returns>DataSet对象</returns>
        public static DataSet ExecuteDataSet(string procName, SqlParameter[] cmdParms) 
		{
            SqlCommand cmd = new SqlCommand();
			SqlConnection con = GetConObject();
			DataSet ds = new DataSet();

			try 
			{
				CreateCommand(cmd, con, null, procName, cmdParms);
                SqlDataAdapter da = new SqlDataAdapter();
				da.SelectCommand = cmd;
				da.Fill(ds);
				cmd.Parameters.Clear();
				return ds;
			}
			catch(Exception)
			{
				throw;
			}	
			finally
			{
				con.Close();
			}
		}

        public static DataSet ExecuteDataSet(SqlTransaction trans, string procName, SqlParameter[] cmdParms) 
		{
            SqlCommand cmd = new SqlCommand();
			SqlConnection con = GetConObject();
			DataSet ds = new DataSet();

			try 
			{
				CreateCommand(cmd, trans.Connection, trans, procName, cmdParms);
                SqlDataAdapter da = new SqlDataAdapter();
				da.SelectCommand = cmd;
				da.Fill(ds);
				cmd.Parameters.Clear();
				return ds;
			}
			catch 
			{
				throw;
			}	
			finally
			{
				//con.Close();
			}
		}

		/// <summary>
		/// 执行读数据集操作，以DataSet的形式返回
		/// </summary>
		/// <param name="procName">存储过程名称</param>		
		/// <returns>DataReader对象</returns>
		public static DataSet ExecuteDataSet(string procName) 
		{
            SqlCommand cmd = new SqlCommand();
			SqlConnection con = GetConObject();
			DataSet ds = new DataSet();

			try 
			{
				CreateCommand(cmd, con, null, procName, null);
                SqlDataAdapter da = new SqlDataAdapter();
				da.SelectCommand = cmd;
				da.Fill(ds);
				cmd.Parameters.Clear();
				return ds;
			}
			catch 
			{
				throw;
			}
			finally
			{
				con.Close();
			}
		}	

		/// <summary>
		/// 创建数据库执行命令
		/// </summary>
		/// <param name="cmd">数据库执行命令对象</param>
		/// <param name="con">数据库连接对象</param>
		/// <param name="trans">数据库事务对象</param>
		/// <param name="procName">存储过程名称</param>
		/// <param name="cmdParms">存储过程所使用的参数数组</param>
		private static void CreateCommand(SqlCommand cmd, SqlConnection con, SqlTransaction trans, string procName, SqlParameter[] cmdParms) 
		{
			if (con.State != ConnectionState.Open)
				con.Open();

			cmd.Connection = con;
			cmd.CommandText = procName;

			if (trans != null)
				cmd.Transaction = trans;

			cmd.CommandType = CommandType.StoredProcedure;

			if (cmdParms != null) 
			{
				foreach(SqlParameter parm in cmdParms)
					cmd.Parameters.Add(parm);
			}
		}
		
		/// <summary>
		/// 准备输入参数.
		/// </summary>
		/// <param name="paramName">参数名称</param>
		/// <param name="dbType">参数数据类型</param>
		/// <param name="size">参数大小</param>
		/// <param name="value">参数值</param>
		/// <returns>输入参数</returns>
		public static SqlParameter MakeInParam(string paramName, SqlDbType dbType, int size, object inValue) 
		{
			return MakeParam(paramName, dbType, size, ParameterDirection.Input, inValue);				
		}		


		/// <summary>
		/// 准备输出参数.
		/// </summary>
		/// <param name="paramName">参数名称</param>
		/// <param name="dbType">参数数据类型</param>
		/// <param name="size">参数大小</param>		
		/// <returns>输出参数</returns>
        public static SqlParameter MakeOutParam(string paramName, SqlDbType dbType, int size) 
		{
			return MakeParam(paramName, dbType, size, ParameterDirection.Output, null);
		}

        public static SqlParameter MakeOutParam(string paramName, SqlDbType dbType, int size, object inValue) 
		{
			return MakeParam(paramName, dbType, size, ParameterDirection.Output, inValue);
		}	

		/// <summary>
		/// 准备存储过程的参数.
		/// </summary>
		/// <param name="paramName">参数名称</param>
		/// <param name="dbType">参数数据类型</param>
		/// <param name="size">参数大小</param>		
		/// <param name="direction">参数方向</param>
		/// <param name="inValue">参数值</param>
		/// <returns>输入或输出参数</returns>
        private static SqlParameter MakeParam(string paramName, SqlDbType dbType, int size, ParameterDirection direction, object inValue) 
		{
			SqlParameter param;

			if(size > 0)
                param = new SqlParameter(paramName, dbType, size);
			else
                param = new SqlParameter(paramName, dbType);

			param.Direction = direction;

			if (!(direction == ParameterDirection.Output && inValue == null))
				param.Value = inValue;

			return param;
		}
	}
}