condatabase.cs

来自「一个操作数据库的类」· CS 代码 · 共 197 行

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

namespace  xwWebSite
{
	/// <summary>
	/// conDataBase 的摘要说明。
	/// </summary>
	public class conDataBase
	{
		SqlConnection conn=new SqlConnection();
		public conDataBase()
		{
			string strconn=ConfigurationSettings.AppSettings["ConnectionString"];
			conn.ConnectionString=strconn;
		}
		public DataSet GetDataSet(String Sql,String TN,SqlParameter[] arr)//返回DataSet的sql
		{
			int i=0;
			DataSet dts=new DataSet();
			//Sql="begin transaction "+Sql+" commit ";
			try
			{
				SqlDataAdapter dad=new SqlDataAdapter(Sql,conn);
				if(arr.Length>0)
				{
					for(i=0;i<arr.Length;i++)
					{
						dad.SelectCommand.Parameters.Add(arr[i]);
					}
				}
				
				conn.Open();
				if(TN!="")
				{
					dad.Fill(dts,TN);
				}
				else
				{
					dad.Fill(dts);
				}
			}
			finally
			{
				conn.Close();
				conn.Dispose();
			}
			
			return dts;
		}
		public int ExecuteNQ(String Sql,SqlParameter[] arr)//执行没有返回值的sql语句
		{
			int i=0;
			try
			{
				SqlCommand cmd=new SqlCommand(Sql,conn);
				if(arr.Length>0)
				{
					for(i=0;i<arr.Length;i++)
					{
						cmd.Parameters.Add(arr[i]);
					}
				}
				conn.Open();
				return cmd.ExecuteNonQuery();
			}
			finally
			{
				conn.Close();
				conn.Dispose();
			}
	
		}
		public SqlDataReader ExecuteR(String Sql,SqlParameter[] arr)//返回DataReader的sql
		{
			int i=0;
			SqlDataReader dtr;
			try
			{
				SqlCommand cmd=new  SqlCommand(Sql,conn);
				if(arr.Length>0)
				{
					for(i=0;i<arr.Length;i++)
					{
						cmd.Parameters.Add(arr[i]);
					}
				}
				conn.Open();
				dtr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
			}
			finally
			{}
			return dtr;
		}
		public Object ExecuteS(String Sql,SqlParameter[] arr)//返回结果集第一行第一列的sql
		{
			int i=0;
			Object Results;
			try
			{
				SqlCommand cmd=new SqlCommand(Sql,conn);
				if(arr.Length>0)
				{
					for(i=0;i<arr.Length;i++)
					{
						cmd.Parameters.Add(arr[i]);
					}
				}
				conn.Open();
				Results=cmd.ExecuteScalar();
			}
			finally
			{
				conn.Close();
			}
			
			return Results;
		}
		public DataSet prcGetDS(SqlParameter[] par,string prcN)//返回DataSet的存储过程
		{
			DataSet ds;
			try
			{
				
				ds=new DataSet();
				SqlDataAdapter dad=new SqlDataAdapter(prcN,conn);
				dad.SelectCommand.CommandType=CommandType.StoredProcedure;
				for(int i=0;i<par.Length;i++)
				{
					dad.SelectCommand.Parameters.Add(par[i]);
				}
				conn.Open();
				dad.Fill(ds);
			}
			finally
			{
				conn.Close();
				conn.Dispose();
				conn=null;
			}
			return ds;
		}
		public void prcExeNQ(SqlParameter[] par,string prcN)//没有返回值的存储过程
		{
			try
			{
				SqlCommand cmd=new SqlCommand(prcN,conn);
				cmd.CommandType=CommandType.StoredProcedure;
				for(int i=0;i<par.Length;i++)
				{
					cmd.Parameters.Add(par[i]);
				}
				conn.Open();
				cmd.ExecuteNonQuery();
			}
			finally
			{
				conn.Close();
				conn.Dispose();
				conn=null;
			}
		}
		public SqlDataReader prcExecute(SqlParameter[] par,string prcN)//返回DataReader的存储过程
		{
			SqlDataReader dtr;
			try
			{
				SqlCommand cmd=new SqlCommand(prcN,conn);
				cmd.CommandType=CommandType.StoredProcedure;
				for(int i=0;i<par.Length;i++)
				{
					cmd.Parameters.Add(par[i]);
				}
				conn.Open();
				dtr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
			}
			finally
			{
			}
			return dtr;
		}

		public void Dispose() //释放Connection
		{
			// 确认连接是否已经关闭
			if (conn != null) 
			{
				conn.Dispose();
				conn = null;
			}				
		}

	}
}