countyvillage.cs

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

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

namespace DataAccess.TableAccessor
{
    public class CountyVillage
    {
        private Database db;

        public CountyVillage()
        {
            db = DatabaseFactory.CreateDatabase("Connection String");
        }

        /// <summary>
        /// 检测是否有重复记录
        /// </summary>
        /// <param name="areaName"></param>
        /// <returns></returns>
        public bool CheckAreaInfo(string areaName)
        {
            string strSQL = string.Format("SELECT COUNT(AreaName) AS Result FROM Area WHERE (AreaName='{0}') AND (IsValid <> 0)", areaName);
            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;
            }
        }


        /// <returns></returns>
        public bool CheckTypeInfo(string areaName)
        {
            string strSQL = string.Format("SELECT COUNT(TypeName) AS Result FROM Type WHERE (TypeName='{0}') AND (IsValid <> 0)", areaName);
            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 CheckAreaName(string areaName)
        {
            string strSQL = string.Format("SELECT COUNT(AreaID) AS Result FROM Area WHERE (AreaName='{0}') AND (IsValid <> 0)", areaName);
            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 int AddAreaInfo(string areaName, string remark)
        {
            string strSQL = string.Format("INSERT INTO Area  (AreaName, Remark) VALUES  ('{0}','{1}')", areaName, remark);
            if (CheckAreaInfo(areaName))
            {
                return db.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            else
                return 0;
        }






        public int AddTypeInfo(string typeName, string remark)
        {
            string strSQL = string.Format("INSERT INTO Type  (TypeName, Remark) VALUES  ('{0}','{1}')", typeName, remark);
            if (CheckTypeInfo(typeName))
            {
                return db.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            else
                return 0;

            //try
            //{
            //    return db.ExecuteNonQuery(CommandType.Text, strSQL);
            //}
            //catch
            //{
            //    return 0;
            //}
        }
        /// <summary>
        /// 得到所有的地市信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetAreaInfo()
        {
            String strSql = "SELECT AreaID, AreaCode, AreaName, Remark FROM Area WHERE (IsValid <> 0) ORDER BY AreaID";
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }



        public DataTable GetcoustomertypeInfo()
        {
            String strSql = "SELECT TypeID, TypeName, Remark FROM Type WHERE (IsValid <> 0) ORDER BY TypeID";
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }



        /// <summary>
        /// 
        /// 
        /// 得到单个地市信息
        /// </summary>
        /// <param name="areaID"></param>
        /// <returns></returns>
        public DataTable GetAreaInfoByUserID(int areaID)
        {
            string strSQL = string.Format("SELECT AreaID, AreaName, Remark FROM Area WHERE (IsValid <> 0) AND AreaID = {0}", areaID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSQL);
            return ds.Tables[0];
        }


        public DataTable GetTypeInfoByGustomerID(int areaID)
        {
            string strSQL = string.Format("SELECT typeID, TypeName, Remark FROM Type WHERE (IsValid <> 0) AND TypeID = {0}", areaID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSQL);
            return ds.Tables[0];
        }
        /// <summary>
        /// 修改地市信息
        /// </summary>
        /// <param name="areaID"></param>
        /// <param name="areaName"></param>
        /// <param name="bak"></param>
        /// <returns></returns>
        public int UpdateArea(int areaID, string areaName, string bak)
        {
            string strSQL = string.Format("UPDATE Area SET AreaName ='{0}', Remark ='{1}'WHERE AreaID = {2}", areaName, bak, areaID);
            if (CheckAreaName(areaName))
            {
                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
                return db.ExecuteNonQuery(dbCommand);
            }
            else
                return 0;
        }


        public int UpdateType(int areaID, string areaName, string bak)
        {
            string strSQL = string.Format("UPDATE Type SET TypeName ='{0}', Remark ='{1}'WHERE TypeID = {2}", areaName, bak, areaID);
            if (CheckAreaName(areaName))
            {
                DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
                return db.ExecuteNonQuery(dbCommand);
            }
            else
                return 0;
        }
        /// <summary>
        /// 删除地市
        /// </summary>
        /// <param name="areaID"></param>
        /// <returns></returns>
        public int DelArea(int areaID)
        {
            // string strSQL = string.Format("UPDATE Area SET IsValid = 0 WHERE AreaID = {0}", areaID);
            string strSQL = string.Format("exec dbo.prAreaDel {0}", areaID);
            return db.ExecuteNonQuery(CommandType.Text, strSQL);
        }



        public int DelType(int areaID)
        {
            string strSQL = string.Format("UPDATE Type SET IsValid = 0 WHERE TypeID = {0}", areaID);
           // string strSQL = string.Format("exec dbo.prAreaDel {0}", areaID);
            return db.ExecuteNonQuery(CommandType.Text, strSQL);
        }
        /// <summary>
        /// 得到所有区县信息
        /// </summary>
        /// <param name="areaID"></param>
        /// <returns></returns>
        public DataTable GetCountyInfo(int areaID)
        {
            String strSql = string.Format("SELECT CountyID, CountyCode, CountyName, AreaID FROM " +
                "County WHERE (AreaID = {0})", areaID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }
        /// <summary>
        /// 得到所有的乡镇信息
        /// </summary>
        /// <param name="areaID"></param>
        /// <param name="countyID"></param>
        /// <returns></returns>
        public DataTable GetVillageInfo(int areaID, int countyID)
        {
            String strSql = string.Format("SELECT VillageID, AreaID, CountyID, VillageCode, VillageName " +
                "FROM Village WHERE (AreaID = {0}) AND (CountyID = {1})", areaID, countyID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }
        public DataTable GetMostCounty(int AreaID)
        {
            string strSql = string.Format("SELECT CountyID, CountyName FROM County " +
                "WHERE(AreaID = {0})", AreaID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }

        public DataTable GetCountyName()
        {
            string strSql = "SELECT CountyID, CountyName FROM County";
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }

        //public DataTable GetVillageInfo(int typeID,string countyID)
        //{
        //    string strSql = string.Format("SELECT VillageID, VillageName, CountyID FROM Village " +
        //        "WHERE (CountyID = '{0}' AND UserInfoID = {1})", countyID,typeID);
        //    DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
        //    return ds.Tables[0];
        //}

        public DataTable GetVillageName(string countyID)
        {
            string strSql = string.Format("SELECT VillageID, VillageName, CountyID FROM Village " +
                "WHERE (CountyID = '{0}')", countyID);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, strSql);
            return ds.Tables[0];
        }
    }
}