C# SQL操作类 DAO类

[c#]代码库

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
///
/// Database 的摘要说明
///
public class Database
{
	protected SqlConnection Connection;
	protected String ConnectionString;
	List paraArray = new List();

	public Database()
	{
		//构造函数
		ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
	}
	~Database()
	{
		//析构函数
		try
		{
			if ( Connection != null )
				Connection.Close();
		}
		catch { }
		try
		{
			Dispose();
			}
			catch { }
		}
public void  OpenMasterStation()
	{
		ConnectionString = ConfigurationManager.ConnectionStrings["HNIUConnectionString"].ConnectionString;
	}
	protected void Open()
	{
		//
		//保护方法，打开数据库连接。
		if ( Connection == null )
		{
			Connection = new SqlConnection ( ConnectionString );
		}
		if ( Connection.State.Equals ( ConnectionState.Closed ) )
		{
			Connection.Open();
		}
	}

	public void Close()
	{
		//公有方法，关闭数据库连接。
		if ( Connection != null )
			Connection.Close();
	}
	public void Dispose()
	{
		//公有方法，释放资源。
		// 确保连接被关闭
		if ( Connection != null )
		{
			Connection.Dispose();
			Connection = null;
		}
	}
	//public void GetDataReader()
	//{
	//公有方法，返回一个DataRead
	//Open();
	//SqlCommand cmd = new SqlCommand(SqlString, Connection);
	//OleDbDataReader dr = cmd.ExecuteReader();
	//return dr;
	//}

	public DataSet GetDataSet ( String SqlString )
	{
		//公有方法，获取数据，返回一个DataSet。
		Open();
		SqlDataAdapter adapter = new SqlDataAdapter ( SqlString, Connection );
		DataSet dataset = new DataSet();
		adapter.Fill ( dataset );
		Close();
		return dataset;
	}
	public int ExecuteSQL ( String SqlString )
	{
		// 公有方法，执行Sql语句。
		// Sql语句
		// 对Update、Insert、Delete为影响到的行数，其他情况为-1
		int count = -1;
		Open();
		try
		{
			SqlCommand cmd = new SqlCommand ( SqlString, Connection );
			count = cmd.ExecuteNonQuery();
		}
		catch
	{
		count = -1;
	}
	finally
	{
		Close();
		}
		return count;
	}

	public int ExecuteSQL ( String SqlProcName, SqlParameter[] SqlParaArray )
	{
		// 公有方法，执行Sql语句。
		// 存储过程名称
		// 参数数组
		// 对Update、Insert、Delete为影响到的行数，其他情况为-1
		int count = -1;
		Open();
		try
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandText = SqlProcName;
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection = Connection;
			cmd.Parameters.AddRange ( SqlParaArray );
			count = cmd.ExecuteNonQuery();
		}
		catch
	{
		count = -1;
	}
	finally
	{
		Close();
		}
		return count;
	}


	public void AddSqlParaArray ( string name,object value )
	{
		paraArray.Add ( new SqlParameter ( name,value ) );
	}
	public object ExecuteScalar ( string SqlProcName )
	{
		// 公有方法，执行Sql语句。
		// 存储过程名称
		// 返回单个值，如果没有查询结果返回为NULL
		Open();
		object result = null;
		try
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandText = SqlProcName;
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection = Connection;
			cmd.Parameters.AddRange ( paraArray.ToArray() );
			result = cmd.ExecuteScalar();
		}
		catch ( Exception ee )
		{
			throw new Exception ( ee.ToString() );
		}
		finally
		{
			Close();
			//if (result == null) MsgInfo.showMessage("DataAccess", 5);
		}
		return result;
	}
	public DataRow GetDataRow ( string SqlProcName )
	{
		Open();
		DataRow dr = null;
		DataSet ds = new DataSet();
		try
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandText = SqlProcName;
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection = Connection;
			cmd.Parameters.AddRange ( paraArray.ToArray() );
			SqlDataAdapter sda = new SqlDataAdapter ( cmd );
			sda.Fill ( ds );
			if ( ds.Tables[0].Rows.Count > 0 ) dr = ds.Tables[0].Rows[0];
		}
		catch { }
		Close();
		return dr;
	}
	public DataRow GetDataRow ( String SqlProcName, SqlParameter[] SqlParaArray )
	{
		//公有方法，获取数据，返回一个DataRow。
		DataSet dataset = GetDataSet ( SqlProcName,SqlParaArray );
		dataset.CaseSensitive = false;
		if ( dataset.Tables[0].Rows.Count > 0 )
		{
			return dataset.Tables[0].Rows[0];
		}
		else
		{
			return null;
		}
	}
	public DataSet GetDataSet ( String SqlProcName, SqlParameter[] SqlParaArray )
	{
		//公有方法，获取数据，返回一个DataSet。
		Open();
		SqlDataAdapter adapter = new SqlDataAdapter();
		SqlCommand cmd = new SqlCommand();
		cmd.CommandText = SqlProcName;
		cmd.Connection = Connection;
		cmd.Parameters.AddRange ( SqlParaArray );
		cmd.CommandType = CommandType.StoredProcedure;
		adapter.SelectCommand = cmd;
		DataSet dataset = new DataSet();
		adapter.Fill ( dataset );
		Close();
		return dataset;
	}
	public int ExecuteNonQuery ( String SqlProcName )
	{
		// 公有方法，执行Sql语句。
		// 存储过程名称
		// 对Update、Insert、Delete为影响到的行数，其他情况为-1
		int count = -1;
		Open();
		try
		{
			SqlCommand cmd = new SqlCommand();
			cmd.CommandText = SqlProcName;
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Connection = Connection;
			cmd.Parameters.AddRange ( paraArray.ToArray() );
			count = cmd.ExecuteNonQuery();
		}
		catch ( Exception ee )
		{
			//MsgInfo.pushMsg(ee.ToString());
			throw new Exception ( ee.ToString() );
		}
		finally
		{
			Close();
		}
		return count;
	}
	public DataTable GetDataTable ( String SqlProcName )
	{
		//公有方法，获取数据，返回一个DataTable。
		DataTable dt = null;
		try
		{
			Open();
			SqlDataAdapter adapter = new SqlDataAdapter();
			SqlCommand cmd = new SqlCommand();
			cmd.CommandText = SqlProcName;
			cmd.Connection = Connection;
			cmd.Parameters.AddRange ( paraArray.ToArray() );
			cmd.CommandType = CommandType.StoredProcedure;
			adapter.SelectCommand = cmd;
			dt = new DataTable();
			adapter.Fill ( dt );
		}
		catch ( Exception ee )
		{
			throw new Exception ( ee.ToString() );
		}
		finally
		{
			Close();
		}
		return dt;
	}
}