smstypeinfo.cs

该服务平台解决了计算机网络与移动网络之间信息交换问题

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace DataAccess.TableAccessor
{
    public class SMSTypeInfo
    {
        private const string SQL_GET_MOSTSMSTYPE = "SELECT SMSTypeInfo.SMSTypeID, SMSTypeInfo.SMSTypeName, MainSMSTypeInfo.MainSMSTypeName " +
            "FROM SMSTypeInfo INNER JOIN MainSMSTypeInfo ON SMSTypeInfo.MainSMSTypeInfoID = MainSMSTypeInfo.MainSMSTypeInfoID " +
            "WHERE SMSTypeInfo.IsValid <> 0 ";
        private const string SQL_GET_MOSTSMSTYPE_BY_MAINSMSID = "SELECT SMSTypeInfo.SMSTypeID, SMSTypeInfo.SMSTypeName, MainSMSTypeInfo.MainSMSTypeName " +
            "FROM SMSTypeInfo INNER JOIN MainSMSTypeInfo ON SMSTypeInfo.MainSMSTypeInfoID = MainSMSTypeInfo.MainSMSTypeInfoID " +
            "WHERE SMSTypeInfo.IsValid <> 0 AND MainSMSTypeInfo.IsValid <> 0 AND SMSTypeInfo.MainSMSTypeInfoID = {0}";
        private const string SQL_GET_MOSTSMSTYPE_BY_0 = "SELECT SMSTypeInfo.SMSTypeID, SMSTypeInfo.SMSTypeName, MainSMSTypeInfo.MainSMSTypeName " +
            "FROM SMSTypeInfo INNER JOIN MainSMSTypeInfo ON SMSTypeInfo.MainSMSTypeInfoID = MainSMSTypeInfo.MainSMSTypeInfoID "  +
            "WHERE SMSTypeInfo.IsValid <> 0 AND MainSMSTypeInfo.IsValid <> 0";
        private const string SQL_GET_MOSTSMSTYPE_BY_MAINSMSTYPEID = "SELECT SMSTypeID, SMSTypeName FROM SMSTypeInfo WHERE IsValid <> 0" +
            "AND MainSMSTypeInfoID = {0}";
        private const string SQL_GET_MOSTMAINSMSTYPE = "SELECT MainSMSTypeInfoID, MainSMSTypeName FROM MainSMSTypeInfo WHERE IsValid <> 0";
        private Database db = null;
        public SMSTypeInfo()
        {
            db = DatabaseFactory.CreateDatabase("Connection String");
        }

        public bool CheckSMSTypeName(string typeName)
        {
            string strSQL = string.Format("SELECT COUNT(SMSTypeID) AS Result FROM SMSTypeInfo WHERE (SMSTypeName = '{0}') AND (IsValid <> 0)",typeName);
            int result = 0;
            DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
            //db.a.AddInParameter(dbCommand, "@SMSTypeName", DbType.String, typeName);
            object obj = db.ExecuteScalar(dbCommand);
            try
            {
                result = int.Parse(obj.ToString());
                if (result > 0)
                    return false;
                else
                    return true;
            }
            catch
            {
                return false;
            }

        }

        public bool CheckMainSMSTypeName(string smsTypeName)
        {
            string strSQL = string.Format("SELECT COUNT(MainSMSTypeInfoID) AS Result FROM MainSMSTypeInfo WHERE (MainSMSTypeName='{0}') AND (IsValid <> 0)", smsTypeName);
            int  result = 0;
            DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
            object obj = db.ExecuteScalar(dbCommand);
            try
            {
                result = int.Parse(obj.ToString());
                if (result > 0)
                    return false;
                else
                    return true;
            }
            catch
            {
                return false;
            }
        }

        public bool CheckMainSMSTypeName1(string smsTypeName)
        {
            string strSQL = string.Format("SELECT COUNT(MainSMSTypeName) AS Result FROM MainSMSTypeInfo WHERE (MainSMSTypeName='{0}') AND (IsValid <> 0)", smsTypeName);
            int result = 0;
            DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
            object obj = db.ExecuteScalar(dbCommand);
            try
            {
                result = int.Parse(obj.ToString());
                if (result > 0)
                    return false;
                else
                    return true;
            }
            catch
            {
                return false;
            }
        }

        public DataTable GetMostSMSType()
        {
            DataSet ds = db.ExecuteDataSet(CommandType.Text, SQL_GET_MOSTSMSTYPE);
            return ds.Tables[0];
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="mainID"></param>
        /// <returns></returns>
        public DataTable GetMostSMSTypeByMainSMSTypeID(int mainID)
        {
            DataSet ds = new DataSet();
            if (mainID != 0)
            {
                string strSQL = string.Format(SQL_GET_MOSTSMSTYPE_BY_MAINSMSID, mainID);
                 ds = db.ExecuteDataSet(CommandType.Text, strSQL);
                return ds.Tables[0];
            }
            else
            {
                ds = db.ExecuteDataSet(CommandType.Text, SQL_GET_MOSTSMSTYPE_BY_0);
                return ds.Tables[0];
            }
        }
        public DataTable GetMostMainSMSType()
        {
            DataSet ds = db.ExecuteDataSet(CommandType.Text, SQL_GET_MOSTMAINSMSTYPE);
            return ds.Tables[0];
        }

        public DataTable GetMostSMStypeByMainSMSTypeID(int mainSMSTypeID)
        {
            string strSQL = string.Format(SQL_GET_MOSTSMSTYPE_BY_MAINSMSTYPEID, mainSMSTypeID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSQL);
            return ds.Tables[0];
        }

        public DataTable GetSMSTypeInfo()
        {
            string strSql = string.Format("select SMSTypeID, SMSTypeName from SMSTypeInfo where IsValid=1");
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }
        public int AddSMSType(string typeName)
        {
            string strSql = string.Format("INSERT INTO SMSTypeInfo(SMSTypeName) VALUES('{0}')", typeName);
            
            if (CheckSMSTypeName(typeName))
            {
                return db.ExecuteNonQuery(CommandType.Text, strSql);
            }
            else
                return 0;
        }

        public int AddMainSMSType(string smsTypeName)
        {
            string strSQL = string.Format("INSERT INTO MainSMSTypeInfo (MainSMSTypeName) VALUES ('{0}')",  smsTypeName);
            if (CheckMainSMSTypeName1(smsTypeName))
            {
                return db.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            else
                return 0;
        }

        public int UpdateSMSType(int typeNameID, string typeName)
        {
            string strSQL = string.Format("UPDATE SMSTypeInfo SET SMSTypeName = '{0}' WHERE SMSTypeID = {1}", typeName, typeNameID);
            if (CheckSMSTypeName(typeName))
            {
                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
                return db.ExecuteNonQuery(dbCommand);
            }
            else
                return 0;
        }
        public int UpdateMainSMSType(int mainTypeID, string mainSMSTypeName)
        {
            string strSQL = string.Format("UPDATE MainSMSTypeInfo SET MainSMSTypeName = '{0}' WHERE MainSMSTypeInfoID = {1}", mainSMSTypeName, mainTypeID);
            if (CheckMainSMSTypeName(mainSMSTypeName))
            {
                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
                return db.ExecuteNonQuery(dbCommand);
            }
            else
                return 0;
        }

        public int DeleteSMSType(int typeNameID)
        {
            string strSQL = string.Format("UPDATE SMSTypeInfo SET IsValid = 0 WHERE SMSTypeID = {0}", typeNameID);
            DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
            return db.ExecuteNonQuery(dbCommand);
        }

        public int DeleteMainSMSType(int TypeID)
        {
            //string strSQL = string.Format("UPDATE MainSMSTypeInfo  SET IsValid = 0 WHERE MainSMSTypeInfoID = {0}", TypeID);
            string strSQL = string.Format("exec dbo.DelMainOperationType {0}", TypeID);
            return db.ExecuteNonQuery(CommandType.Text,strSQL);
        }

        public DataSet GetSmsTypeName(int SmsType)
        {
            string SQLstring = string.Format("select SMSTypeName from SMSTypeInfo where SMSTypeID ='{0}'", SmsType);
            DataSet ds = new DataSet();
            //db.LoadDataSet(CommandType.Text, SQLstring,"Table");
            db.LoadDataSet(CommandType.Text, SQLstring, ds, new string[] { "Table" });
            return ds;
        }
    }
}