dataaccess.cs

在。NET中利用VB6.0的控件来现实对串口的操作来完成短信发送的功能

using System;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;    
using System.Collections;

namespace Sms
{
	/// <summary>
	/// DataAccess 的摘要说明。
	/// </summary>
	public class DataAccess
	{
		protected SqlDataAdapter m_dataAdapter=new SqlDataAdapter() ;		
		protected SqlConnection m_connect;

		public DataAccess()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}


		/// <summary>
		///     释放对象的资源
		/// </summary>
		public void Dispose()
		{
			Dispose(true);
			///为可能继承本类的其它的提供服务
			GC.SuppressFinalize(true);
		}

		/// <summary>
		///		释放对象中的变量实例
		/// </summary>
		protected virtual void Dispose(bool disposing)
		{
			if (! disposing)
				return;  
			if(this.m_connect!=null){m_connect.Dispose();m_connect=null;}
			if(this.m_dataAdapter!=null)
			{
				m_dataAdapter.Dispose();
				m_dataAdapter=null;
			}
			
		}
		/// <summary>
		///		连接SQL数据库
		/// </summary>
		public void OpenDatabase()
		{
			m_connect=new SqlConnection("server=localhost; User ID=Sms;Password = sms;database=Sms");
			try{m_connect.Open();}
			catch{throw new Exception("数据库连接失败！请检查网络及服务器！");}
			
		}
		public void CloseDatabase()
		{
			m_connect.Close();
		}

		/// <summary>
		///     Retrieves the commodity with the provided email address.
		///     <param name="emailAddress">Categories address for commodity.</param>
		///     <retvalue>CommodityData, a dataset containing detailed commodity information.</retvalue>
		/// </summary>
		public DataTable GetParameter()
		{
			if ( this.m_dataAdapter == null )
			{
				throw new System.ObjectDisposedException( GetType().FullName );
			} 
			this.OpenDatabase();          
			DataTable parameter =new DataTable();
			//bus.Columns.Add("BUSNUMBER",typeof(System.String));
			//
			// Get the load command
			//
			try
			{
				this.m_dataAdapter.SelectCommand =  new SqlCommand("GetParameter",this.m_connect);
				this.m_dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
        				        
				this.m_dataAdapter.Fill(parameter);				
			}	
			catch
			{
				throw;				
			}
			finally
			{
				this.CloseDatabase();
				
				this.m_dataAdapter.Dispose();
			}
		
			return parameter;
		}


		/// <summary>
		///     Retrieves the commodity with the provided email address.
		///     <param name="emailAddress">Categories address for commodity.</param>
		///     <retvalue>CommodityData, a dataset containing detailed commodity information.</retvalue>
		/// </summary>
		public DataTable GetSmsInform()
		{
			if ( this.m_dataAdapter == null )
			{
				throw new System.ObjectDisposedException( GetType().FullName );
			} 
			this.OpenDatabase();          
			DataTable smsInform =new DataTable();
			//bus.Columns.Add("BUSNUMBER",typeof(System.String));
			//
			// Get the load command
			//
			try
			{
				this.m_dataAdapter.SelectCommand =  new SqlCommand("GetSmsInform",this.m_connect);
				this.m_dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
        				        
				this.m_dataAdapter.Fill(smsInform);				
			}	
			catch
			{
				throw;
			}
			finally
			{
				this.CloseDatabase();
				
				this.m_dataAdapter.Dispose();
			}
		
			return smsInform;
		}


		/// <summary>
		///     Retrieves the commodity with the provided email address.
		///     <param name="emailAddress">Categories address for commodity.</param>
		///     <retvalue>CommodityData, a dataset containing detailed commodity information.</retvalue>
		/// </summary>
		public bool UpdateSmsInform( DataTable smsInform)
		{
			if ( this.m_dataAdapter == null )
			{
				throw new System.ObjectDisposedException( GetType().FullName );
			} 
			this.OpenDatabase();          
			//bus.Columns.Add("BUSNUMBER",typeof(System.String));
			//
			// Get the load command
			//
			try
			{
				this.m_dataAdapter.SelectCommand =  new SqlCommand("UpdateSmsInform",this.m_connect);
				this.m_dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
				this.m_dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@SmsInform_Id", SqlDbType.Int));
				this.m_dataAdapter.SelectCommand.Parameters["@SmsInform_Id"].Value = (int)smsInform.Rows[0][0];
				this.m_dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@SendTime", SqlDbType.DateTime));
				this.m_dataAdapter.SelectCommand.Parameters["@SendTime"].Value = smsInform.Rows[0][3];
				this.m_dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@State", SqlDbType.Int));
				this.m_dataAdapter.SelectCommand.Parameters["@State"].Value = (int)smsInform.Rows[0][4];
        				        
				this.m_dataAdapter.Fill(smsInform);				
			}	
			catch
			{
				throw;
			}
			finally
			{
				this.CloseDatabase();
				
				this.m_dataAdapter.Dispose();
			}
		
			return true;
		}


		/// <summary>
		///     Retrieves the commodity with the provided email address.
		///     <param name="emailAddress">Categories address for commodity.</param>
		///     <retvalue>CommodityData, a dataset containing detailed commodity information.</retvalue>
		/// </summary>
		public bool UpdateParameter(string commPort,string bitRatio,string parityCheck,string dataBit,string digit,string interval)
		{
			if ( this.m_dataAdapter == null )
			{
				throw new System.ObjectDisposedException( GetType().FullName );
			} 
			this.OpenDatabase();          
			//bus.Columns.Add("BUSNUMBER",typeof(System.String));
			//
			DataTable temp = new DataTable();
			// Get the load command
			//
			try
			{
				this.m_dataAdapter.SelectCommand =  new SqlCommand("UpdateParameter",this.m_connect);
				this.m_dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
				this.m_dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@Parameter_Id", SqlDbType.Int));
				this.m_dataAdapter.SelectCommand.Parameters["@Parameter_Id"].Value = 0;
				this.m_dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@CommPort", SqlDbType.VarChar,5));
				this.m_dataAdapter.SelectCommand.Parameters["@CommPort"].Value = commPort;
				this.m_dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@BitRatio", SqlDbType.VarChar,6));
				this.m_dataAdapter.SelectCommand.Parameters["@BitRatio"].Value = bitRatio;
				this.m_dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@ParityCheck", SqlDbType.VarChar,10));
				this.m_dataAdapter.SelectCommand.Parameters["@ParityCheck"].Value = parityCheck;
				this.m_dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@DataBit", SqlDbType.VarChar,5));
				this.m_dataAdapter.SelectCommand.Parameters["@DataBit"].Value = dataBit;
				this.m_dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@Digit", SqlDbType.VarChar,3));
				this.m_dataAdapter.SelectCommand.Parameters["@Digit"].Value = digit;
				this.m_dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@Interval", SqlDbType.VarChar,5));
				this.m_dataAdapter.SelectCommand.Parameters["@Interval"].Value = interval;
				this.m_dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@State", SqlDbType.Int));
				this.m_dataAdapter.SelectCommand.Parameters["@State"].Value = 0;
        				        
				this.m_dataAdapter.Fill(temp);				
				//temp.Dispose();
			}	
			catch
			{
				throw;
			}
			finally
			{
				this.CloseDatabase();
				
				this.m_dataAdapter.Dispose();
			}
		
			return true;
		}


	}
}