dataaccess.cs

人力资源管理系统 读者须安装好IIS和Microsoft Visual Stutio.NET相关开发环境

			_command.Parameters.Clear();

			_connection.Close() ;

			return ds ;
		}

		/// <summary>
		/// 执行返回DataSet的存储过程
		/// </summary>		
		/// <param name="spName">存储过程名</param>
		/// <param name="parameterValues">参数值序列</param>
		/// <returns>查询结果DataSet</returns>
		public static DataSet ExecuteDataset(string spName, params object[] parameterValues)
		{
			//如果有参数值, 先赋参数值再执行
			if ((parameterValues != null) && (parameterValues.Length > 0)) 
			{
				//获取存储过程参数
				SqlParameter[] commandParameters = GetSpParameters(spName,false);

				//为存储过程参数设定参数值
				AssignParameterValues(commandParameters, parameterValues);

				//调用执行带数值的存储过程方法
				return ExecuteDataset_in(spName, commandParameters);
			}
				//调用不带参数的方法
			else 
			{
				return ExecuteDataset_in(spName);
			}
		}		
		#endregion

		#region 执行返回DataTable的存储过程
		/// <summary>
		/// 执行返回DataTable的存储过程
		/// </summary>
		/// <param name="commandType">命令类型，存储过程、Text等</param>
		/// <param name="commandText">存储过程名或SQL语句等</param>
		/// <param name="commandParameters">参数序列</param>
		/// <returns>查询结果DataTable</returns>
		private static DataTable ExecuteDataTable_in(string commandText, params SqlParameter[] commandParameters)
		{	
			//如果数据连接未开启，打开它
			if (_connection.State != ConnectionState.Open)
			{
				_connection.Open();
			}

			//准备命令
			PrepareCommand(commandText, commandParameters);
			
			//创建DataAdapter和DataSet
			SqlDataAdapter da = new SqlDataAdapter(_command);
			DataTable dt = new DataTable();

			//填充DataTable
			try
			{
				da.Fill(dt);
			}
			catch(Exception e)
			{
				//执行失败
				//从异常中获取失败信息
				string msg = e.Message ;
				//调用写日志方法
				Trace.Write("FillDataTable","Error",commandText+"|"+msg) ;
			}
			
			// 清除参数		
			_command.Parameters.Clear();

			//执行完毕，关闭连接
			_connection.Close() ;

			return dt ;
		}

		/// <summary>
		/// 执行返回DataTable的存储过程
		/// </summary>		
		/// <param name="spName">存储过程名</param>
		/// <param name="parameterValues">参数值序列</param>
		/// <returns>查询结果DataTable</returns>
		public static DataTable ExecuteDataTable(string spName, params object[] parameterValues)
		{
			//如果有参数值, 先赋参数值再执行
			if ((parameterValues != null) && (parameterValues.Length > 0)) 
			{
				//获取存储过程参数
				SqlParameter[] commandParameters = GetSpParameters(spName,false);

				//为存储过程参数设定参数值
				AssignParameterValues(commandParameters, parameterValues);

				//调用执行带数值的存储过程方法
				return ExecuteDataTable_in(spName, commandParameters);
			}
				//调用不带参数的方法
			else 
			{
				return ExecuteDataTable_in(spName);
			}
		}		
		#endregion

		#region 检索标量
		
		/// <summary>
		/// 执行检索单个标量的存储过程
		/// </summary>
		/// <param name="commandText">存储过程名</param>
		/// <param name="commandParameters">存储过程参数值序列</param>
		/// <returns>检索结果</returns>
		private static object ExecuteScalar_in(string commandText, params SqlParameter[] commandParameters)
		{
			//如果数据连接未开启，打开它
			if (_connection.State != ConnectionState.Open)
			{
				_connection.Open();
			}

			//准备命令
			PrepareCommand(commandText, commandParameters);

			//execute the command & return the results
			object retval = null ;
			try
			{
				retval = _command.ExecuteScalar();
			}
			catch(Exception e)
			{
				//执行失败
				//从异常中获取失败信息
				string msg = e.Message ;
				//调用写日志方法
				Trace.Write("ExecuteScalar","Error",commandText+"|"+msg) ;
			}

			//清除参数.
			_command.Parameters.Clear();

			//关闭连接
			_connection.Close() ;

			return retval;
		}

		/// <summary>
		/// 执行检索标量的存储过程
		/// </summary>
		/// <param name="spName">存储过程名</param>
		/// <param name="parameterValues">存储过程参数值序列</param>
		/// <returns>检索结果</returns>
		public static object ExecuteScalar(string spName, params object[] parameterValues)
		{			
			//如果有参数值, 先赋参数值再执行
			if ((parameterValues != null) && (parameterValues.Length > 0)) 
			{
				//获取存储过程参数
				SqlParameter[] commandParameters = GetSpParameters(spName,false);
				//参数值
				AssignParameterValues(commandParameters, parameterValues);

				//调用带参数的执行方法
				return ExecuteScalar_in(spName, commandParameters);
			}
				//调用不带参数的执行方法
			else 
			{
				return ExecuteScalar_in(spName);
			}
		}	
		#endregion

		#region DataAdapter

		public static int AdapterUpdate(DataTable dt,string tableName,string key)
		{
			int ret = -1 ;
			AdapterCommand(dt,tableName,key);	

			//如果数据连接未开启，打开它
			if (_connection.State != ConnectionState.Open)
			{
				_connection.Open();
			}		

			//执行
			try
			{
				ret = _adapter.Update(dt);
			}
			catch(Exception e)
			{
				//执行失败
				//从异常中获取失败信息
				string msg = e.Message ;
				//调用写日志方法
				Trace.Write("AdapterUpdate","Error",tableName+"|"+msg) ;
			}

			return ret;
		}

		// 为Table在Adapter上生成insert和update命令
		private static void AdapterCommand(DataTable dt,string table,string key)
		{
			string in_sql = "" ;
			string up_sql = "" ;
			string val = "" ;

			//初始化插入命令
			SqlCommand in_cmd=new SqlCommand() ;
			in_cmd.CommandType=CommandType.Text;
			in_cmd.Connection=_connection;

			//初始化更新命令
			SqlCommand up_cmd=new SqlCommand() ;
			up_cmd.CommandType=CommandType.Text;
			up_cmd.Connection=_connection;

			//初始化删除命令
			SqlCommand de_cmd=new SqlCommand() ;
			de_cmd.CommandType = CommandType.Text;
			de_cmd.Connection = _connection;

			DataColumn dc = null ;

			for(int i=0;i<dt.Columns.Count;i++)
			{
				//拼写插入语句
				if(dt.Columns[i].ColumnName == key)
				{
					continue ;
				}

				if(in_sql != "" )
				{
					in_sql += "," ;
				}
				in_sql += dt.Columns[i].ColumnName;

				//值
				if( val != "" ) 
				{
						val += "," ;
				}
				val += "@?" ;				
				
				dc = dt.Columns[i];

				//添加插入命令参数
				in_cmd.Parameters.Add("@p"+i.ToString(),
					ColumnType(dc),
					(dc.MaxLength<0?200:dc.MaxLength),
					dc.ColumnName);

				//拼写更新语句
				if(up_sql != "")  
				{
						up_sql += "," ;
				}
				up_sql += dt.Columns[i].ColumnName + "=?" ;

				//添加更新命令参数
				up_cmd.Parameters.Add("@p"+i.ToString(),
					ColumnType(dc),
					(dc.MaxLength<0?2000:dc.MaxLength),
					dc.ColumnName);
			}

			StringWriter sw=new StringWriter();

			//如果有主键
			if ( key != "")
			{
				dc=dt.Columns[key];
				up_cmd.Parameters.Add("@p"+dt.Columns.Count.ToString(),
					ColumnType(dc),
					(dc.MaxLength<0?2000:dc.MaxLength),
					dc.ColumnName);

				//添加删除命令参数
				de_cmd.Parameters.Add("@p0",
					ColumnType(dc),
					(dc.MaxLength<0?2000:dc.MaxLength),
					dc.ColumnName);
				//删除命令
				de_cmd.CommandText="delete from " + table + " where " + key +"=@?";
				
				sw=null;
				sw=new StringWriter();
				sw.Write("update {0} set {1} where " + key + "=@?",table,up_sql);
				up_cmd.CommandText=sw.ToString();
			}

			//插入命令	
			sw = null ;
			sw = new StringWriter() ;
			sw.Write("insert into {0} ({1}) values({2})",
				table,in_sql,val);
			in_cmd.CommandText=sw.ToString();

			//为Adapter设置添加、更新、删除命令
			_adapter.InsertCommand = in_cmd ;
			_adapter.UpdateCommand = up_cmd ;
			_adapter.DeleteCommand = de_cmd ;
		}


		private static System.Data.SqlDbType ColumnType(DataColumn dc)
		{
			//默认为字符类型
			SqlDbType type = SqlDbType.VarChar;
			
			//新建一个C#数据类型和Sql Server数据类型的转换
			//暂时只处理日期、字符和整型三种数据类型
			object[] oType = new object[]
			{					
				new object[]{Type.GetType("System.DateTime"),SqlDbType.DateTime },
				new object[]{Type.GetType("System.String"),SqlDbType.VarChar },
				new object[]{Type.GetType("System.Int32"),SqlDbType.Int },
			};

			//将数据类型对应到SQL Server数据类型
			for(int i=0;i<oType.Length;i++)
			{
				object[] o = (object[])oType[i];
				if(dc.DataType == (Type)o[0])
				{
					type = (SqlDbType)o[1];
					break ;
				}
			}

			//返回SQL Server数据类型
			return type ;
		}

		#endregion
	}
}