database.cs

email 发送 asp.net 2.0开发

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

namespace HBJ.MailSender
{
	/// <summary>
	/// Database 的摘要说明。
	/// </summary>
	public class Database
	{
		#region 数据库连接

		public static string connStr = "";

		public static SqlConnection conn = null;

		public static SqlConnection Connection(){
			return new SqlConnection(Database.connStr);
		}

		/// <summary>
		/// 打开数据库连接
		/// </summary>
		public static void Open(){
			if(Database.conn.State.ToString().ToUpper().StartsWith("CLOSED")) Database.conn.Open();
		}

		/// <summary>
		/// 关闭数据库连接
		/// </summary>
		public static void Close(){
			if(Database.conn.State.ToString().ToUpper().StartsWith("OPEN")) Database.conn.Close();
		}

		#endregion

		#region 接收邮址数据库操作

		/// <summary>
		/// 获取符合条件的接收邮址
		/// </summary>
		/// <param name="sendType">是否已发送过</param>
		/// <param name="orderType">排序类型,UP 升序,DOWN 降序</param>
		/// <returns></returns>
		public static DataSet HBJ_MailAddress(string sendType,string orderType){
			
			//try{
				string sql = "select * from HBJ_MailAddress where autoID > 0";

				if(sendType != null && sendType == "1"){
					sql += " and isSended = 1";
				}else if(sendType != null && sendType == "0"){
					sql += " and isSended = 0";
				}

				if(orderType != null && orderType == "UP"){
					sql += " order by autoID asc";
				}else{
					sql += " order by autoID desc";
				}

				SqlDataAdapter sda = new SqlDataAdapter(sql,Database.conn);

				DataSet ds = new DataSet();

				sda.Fill(ds,"MailList");

				return ds;

			//}catch(Exception ex){
			//	throw new Exception(ex.Message.ToString());
			//}
		}

		/// <summary>
		/// 保存接收邮址到数据库，只需传递邮件地址即可
		/// </summary>
		/// <param name="mailAddress"></param>
		public static void SaveMailAddressToList(string mailAddress){
			try{
				string sql = "insert into HBJ_MailAddress (mailName) values ('"+ mailAddress +"')";
				Database.Open();
				SqlCommand cmd = new SqlCommand(sql,Database.conn);
				cmd.ExecuteNonQuery();
				Database.Close();
			}catch(Exception ex){
				Database.Close();
				throw new Exception(ex.Message.ToString());
			}
		}

		/// <summary>
		/// 检查是否重复，会降低系统效率
		/// </summary>
		/// <param name="mailAddress"></param>
		/// <returns></returns>
		public static bool MailIsSame(string mailAddress){
			try{
				string sql = "select autoID from HBJ_MailAddress where mailName = '"+ mailAddress +"'";
				SqlDataAdapter sda = new SqlDataAdapter(sql,conn);
				DataSet ds = new DataSet();
				sda.Fill(ds,"IsSame");
				if(ds.Tables[0].Rows.Count > 0){
					ds = null;
					return true;
				}else{
					ds = null;
					return false;
				}
			}catch{
				return false;
			}
		}

		#endregion

		#region 发送邮件地址数据库操作

		/// <summary>
		/// 获取发送邮址
		/// </summary>
		/// <returns></returns>
		public static DataSet HBJ_SendMail(){
			try{
				string sql = "select * from HBJ_SendMail";
				SqlDataAdapter sda = new SqlDataAdapter(sql,Database.conn);

				DataSet ds = new DataSet();

				sda.Fill(ds,"SendMail");

				return ds;
			}catch(Exception ex){
				throw new Exception(ex.Message.ToString());
			}
		}

		/// <summary>
		/// 删除一个邮件地址
		/// </summary>
		/// <param name="mailAddress">待删除的邮件地址</param>
		public static void DelSendMail(string mailAddress){
			//try{
				string sql = "delete from HBJ_SendMail where sendMail = '"+ mailAddress +"'";
				Database.conn.Open();
				SqlCommand cmd = new SqlCommand(sql,Database.conn);
				cmd.ExecuteNonQuery();
				Database.Close();
			//}catch{
			//	Database.Close();
			//}
		}

		/// <summary>
		/// 更新发送邮件
		/// </summary>
		/// <param name="mailAddress">邮址</param>
		/// <param name="mailServer">邮件服务器</param>
		/// <param name="username">用户名</param>
		/// <param name="password">密码</param>
		/// <param name="autoID">在数据库中的已有编号</param>
		public static void UpdateSendMail(string mailAddress,string mailServer,string username,string password){
			try{
				string sql = "update HBJ_SendMail set sendServer='"+ mailServer +"',loginUsername='"+ username +"',loginPassword='"+ password +"' where sendMail = '" + mailAddress + "'";
				Database.Open();
				SqlCommand cmd = new SqlCommand(sql,conn);
				cmd.ExecuteNonQuery();
				Database.Close();
			}catch{
				Database.Close();
			}
		}

		/// <summary>
		/// 增加发送邮件
		/// </summary>
		/// <param name="mailAddress">邮址</param>
		/// <param name="mailServer">邮件服务器</param>
		/// <param name="username">用户名</param>
		/// <param name="password">密码</param>
		public static void InsertSendMail(string mailAddress,string mailServer,string username,string password){
			//try{
				string sql = "insert into HBJ_SendMail (sendMail,sendServer,loginUsername,loginPassword) values ('"+ mailAddress +"','"+ mailServer +"','"+ username +"','"+ password +"')";
				Database.Open();
				SqlCommand cmd = new SqlCommand(sql,conn);
				cmd.ExecuteNonQuery();
				Database.Close();
			//}catch{
			//	Database.Close();
			//}
		}//

		#endregion

		#region 邮件内容数据库操作

		/// <summary>
		/// 获取已设置的邮件内容
		/// </summary>
		/// <returns></returns>
		public static DataSet HBJ_MailContent(){
			try{
				string sql = "select top 1 * from HBJ_MailContent order by autoID desc";
				SqlDataAdapter sda = new SqlDataAdapter(sql,conn);
				DataSet ds  = new DataSet();
				sda.Fill(ds,"MailContent");
				return ds;
			}catch(Exception ex){
				throw new Exception(ex.Message.ToString());
			}
		}

		/// <summary>
		/// 更新邮件内容
		/// </summary>
		/// <param name="title"></param>
		/// <param name="text"></param>
		/// <param name="html"></param>
		public static void UpdateMailContent(string title,string text,string html,string autoID){
			try{
				string sql = "update HBJ_MailContent set mailTitle = '"+ title +"',mailText='"+ text +"',mailHtml='"+ html +"' where autoID = " + autoID;
				Database.Open();
				SqlCommand cmd = new SqlCommand(sql,Database.conn);
				cmd.ExecuteNonQuery();
				Database.Close();
			}catch(Exception ex){
				Database.Close();
				throw new Exception(ex.Message.ToString());
			}
		}

		#endregion
	}
}