dataaccess.cs

企业短信收发系统

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

namespace MessageLibrary
{
	/// <summary>
	/// DataAccess 的摘要说明。
	/// </summary>
	public class DataAccess
	{
		private SqlConnection sqlConnection;
		private SqlDataAdapter sqlDataAdapter;
		private SqlCommand sqlcommand;
		public DataAccess()
		{
			sqlConnection = new SqlConnection("server=.;database=NoteMis;uid=sa;pwd=;");
			sqlDataAdapter = new SqlDataAdapter("",sqlConnection);
			sqlcommand=new SqlCommand();
		}
		//调用存储过程
		public DataSet ExcuteProcedure(string procedureName,SqlParameter[] sqlParameters)
		{
			DataSet ds=new DataSet();
			try
			{
				sqlcommand.Connection=sqlConnection;
				sqlcommand.CommandText=procedureName;
				sqlcommand.CommandType=CommandType.StoredProcedure;
				sqlcommand.Parameters.Clear();
				if(sqlParameters!=null)
				{
					foreach(SqlParameter param in sqlParameters)
					{
						sqlcommand.Parameters.Add(param);
					}
				}
				sqlDataAdapter.SelectCommand=sqlcommand;
				sqlDataAdapter.Fill(ds);
			}
			catch(Exception e)
			{
				string error=e.ToString();
			}
			finally
			{
				sqlConnection.Close();
			}
			return ds;
		}
		//调用存储过程增删改
		public bool ExcuteUpdateProcedure(string procedureName,SqlParameter[] sqlParameters)
		{
			
			try
			{
				sqlConnection.Open();
				sqlcommand.Connection=sqlConnection;
				sqlcommand.CommandText=procedureName;
				sqlcommand.CommandType=CommandType.StoredProcedure;
				sqlcommand.Parameters.Clear();
				if(sqlParameters!=null)
				{
					
					foreach(SqlParameter param in sqlParameters)
					{
						sqlcommand.Parameters.Add(param);
					}
				}
				sqlcommand.ExecuteNonQuery();				
				return true;
				
			}
			catch(Exception e)
			{
				string error=e.ToString();
				return false;
			}
			finally
			{
				sqlConnection.Close();
			}
		}

		//调用带返回参数的存储过程
		public object ExcuteUpdatePro_Return(string procedureName,SqlParameter[] sqlParameters)
		{
			try
			{
				sqlConnection.Open();
				sqlcommand.Connection=sqlConnection;
				sqlcommand.CommandText=procedureName;
				sqlcommand.CommandType=CommandType.StoredProcedure;
				
				if(sqlParameters!=null)
				{
					//sqlcommand.Parameters.Clear();
					foreach(SqlParameter param in sqlParameters)
					{
						sqlcommand.Parameters.Add(param);
					}
				}
				SqlParameter returnParam =new SqlParameter("@Return_Value",SqlDbType.Int);
				returnParam.Direction=ParameterDirection.ReturnValue;
				sqlcommand.Parameters.Add(returnParam);
				sqlcommand.ExecuteNonQuery();
				int a=Convert.ToInt32(returnParam.Value);
				return a;
			}
			catch(Exception e)
			{
				string error=e.ToString();
				return e;
			}
			finally
			{
				sqlConnection.Close();
			}
		}


		public DataSet GetData(string sql,string tableName)
		{
			DataSet ds = new DataSet();
			try
			{
				sqlDataAdapter.SelectCommand = new SqlCommand(sql,sqlConnection);
				sqlDataAdapter.Fill(ds,tableName);
			}
			catch(Exception ex)
			{
				string error = ex.Message;
			}
			return ds;
		}

		public bool sqlExecute(string sql)
		{
			try
			{
				sqlConnection.Open();
				SqlCommand objSqlCommand=sqlConnection.CreateCommand();
				objSqlCommand.CommandText=sql;
				objSqlCommand.ExecuteNonQuery();
				return true;
			}
			catch
			{
				return false;
			}
			finally
			{
				sqlConnection.Close();
			}
		}
	}
}