增删改查-白红宇

增删改查
阅读量：7136 次
发布时间：2019-06-28
本文共 14438 字，大约阅读时间需要 48 分钟。
using System;using System.Data;using System.Text;using System.Data.SqlClient;using Maticsoft.DBUtility;//Please add referencesnamespace Maticsoft.DAL{    ///     /// 数据访问类:TXL_list    /// 
    public partial class TXL_list    {        public TXL_list()        {}        #region  BasicMethod        ///         /// 得到最大ID        /// 
        public int GetMaxId()        {        return DbHelperSQL.GetMaxID("TXLid", "TXL_list");         }        ///         /// 是否存在该记录        /// 
        public bool Exists(int TXLid)        {            StringBuilder strSql=new StringBuilder();            strSql.Append("select count(1) from TXL_list");            strSql.Append(" where TXLid=@TXLid");            SqlParameter[] parameters = {                    new SqlParameter("@TXLid", SqlDbType.Int,4)            };            parameters[0].Value = TXLid;            return DbHelperSQL.Exists(strSql.ToString(),parameters);        }        ///         /// 增加一条数据        /// 
        public int Add(Maticsoft.Model.TXL_list model)        {            StringBuilder strSql=new StringBuilder();            strSql.Append("insert into TXL_list(");            strSql.Append("TXLname,TXLtel,TXLimage,TXLsex,TXLarea,TXLnum,TXLyear,groupid)");            strSql.Append(" values (");            strSql.Append("@TXLname,@TXLtel,@TXLimage,@TXLsex,@TXLarea,@TXLnum,@TXLyear,@groupid)");            strSql.Append(";select @@IDENTITY");            SqlParameter[] parameters = {                    new SqlParameter("@TXLname", SqlDbType.NVarChar,50),                    new SqlParameter("@TXLtel", SqlDbType.NVarChar,50),                    new SqlParameter("@TXLimage", SqlDbType.NVarChar,50),                    new SqlParameter("@TXLsex", SqlDbType.NChar,10),                    new SqlParameter("@TXLarea", SqlDbType.NVarChar,50),                    new SqlParameter("@TXLnum", SqlDbType.NVarChar,50),                    new SqlParameter("@TXLyear", SqlDbType.NVarChar,50),                    new SqlParameter("@groupid", SqlDbType.NVarChar,50)};            parameters[0].Value = model.TXLname;            parameters[1].Value = model.TXLtel;            parameters[2].Value = model.TXLimage;            parameters[3].Value = model.TXLsex;            parameters[4].Value = model.TXLarea;            parameters[5].Value = model.TXLnum;            parameters[6].Value = model.TXLyear;            parameters[7].Value = model.groupid;            object obj = DbHelperSQL.GetSingle(strSql.ToString(),parameters);            if (obj == null)            {                return 0;            }            else            {                return Convert.ToInt32(obj);            }        }        ///         /// 更新一条数据        /// 
        public bool Update(Maticsoft.Model.TXL_list model)        {            StringBuilder strSql=new StringBuilder();            strSql.Append("update TXL_list set ");            strSql.Append("TXLname=@TXLname,");            strSql.Append("TXLtel=@TXLtel,");            strSql.Append("TXLimage=@TXLimage,");            strSql.Append("TXLsex=@TXLsex,");            strSql.Append("TXLarea=@TXLarea,");            strSql.Append("TXLnum=@TXLnum,");            strSql.Append("TXLyear=@TXLyear,");            strSql.Append("groupid=@groupid");            strSql.Append(" where TXLid=@TXLid");            SqlParameter[] parameters = {                    new SqlParameter("@TXLname", SqlDbType.NVarChar,50),                    new SqlParameter("@TXLtel", SqlDbType.NVarChar,50),                    new SqlParameter("@TXLimage", SqlDbType.NVarChar,50),                    new SqlParameter("@TXLsex", SqlDbType.NChar,10),                    new SqlParameter("@TXLarea", SqlDbType.NVarChar,50),                    new SqlParameter("@TXLnum", SqlDbType.NVarChar,50),                    new SqlParameter("@TXLyear", SqlDbType.NVarChar,50),                    new SqlParameter("@groupid", SqlDbType.NVarChar,50),                    new SqlParameter("@TXLid", SqlDbType.Int,4)};            parameters[0].Value = model.TXLname;            parameters[1].Value = model.TXLtel;            parameters[2].Value = model.TXLimage;            parameters[3].Value = model.TXLsex;            parameters[4].Value = model.TXLarea;            parameters[5].Value = model.TXLnum;            parameters[6].Value = model.TXLyear;            parameters[7].Value = model.groupid;            parameters[8].Value = model.TXLid;            int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);            if (rows > 0)            {                return true;            }            else            {                return false;            }        }        ///         /// 删除一条数据        /// 
        public bool Delete(int TXLid)        {                        StringBuilder strSql=new StringBuilder();            strSql.Append("delete from TXL_list ");            strSql.Append(" where TXLid=@TXLid");            SqlParameter[] parameters = {                    new SqlParameter("@TXLid", SqlDbType.Int,4)            };            parameters[0].Value = TXLid;            int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);            if (rows > 0)            {                return true;            }            else            {                return false;            }        }        ///         /// 批量删除数据        /// 
        public bool DeleteList(string TXLidlist )        {            StringBuilder strSql=new StringBuilder();            strSql.Append("delete from TXL_list ");            strSql.Append(" where TXLid in ("+TXLidlist + ")  ");            int rows=DbHelperSQL.ExecuteSql(strSql.ToString());            if (rows > 0)            {                return true;            }            else            {                return false;            }        }        ///         /// 得到一个对象实体        /// 
        public Maticsoft.Model.TXL_list GetModel(int TXLid)        {                        StringBuilder strSql=new StringBuilder();            strSql.Append("select  top 1 TXLid,TXLname,TXLtel,TXLimage,TXLsex,TXLarea,TXLnum,TXLyear,groupid from TXL_list ");            strSql.Append(" where TXLid=@TXLid");            SqlParameter[] parameters = {                    new SqlParameter("@TXLid", SqlDbType.Int,4)            };            parameters[0].Value = TXLid;            Maticsoft.Model.TXL_list model=new Maticsoft.Model.TXL_list();            DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);            if(ds.Tables[0].Rows.Count>0)            {                return DataRowToModel(ds.Tables[0].Rows[0]);            }            else            {                return null;            }        }        ///         /// 得到一个对象实体        /// 
        public Maticsoft.Model.TXL_list DataRowToModel(DataRow row)        {            Maticsoft.Model.TXL_list model=new Maticsoft.Model.TXL_list();            if (row != null)            {                if(row["TXLid"]!=null && row["TXLid"].ToString()!="")                {                    model.TXLid=int.Parse(row["TXLid"].ToString());                }                if(row["TXLname"]!=null)                {                    model.TXLname=row["TXLname"].ToString();                }                if(row["TXLtel"]!=null)                {                    model.TXLtel=row["TXLtel"].ToString();                }                if(row["TXLimage"]!=null)                {                    model.TXLimage=row["TXLimage"].ToString();                }                if(row["TXLsex"]!=null)                {                    model.TXLsex=row["TXLsex"].ToString();                }                if(row["TXLarea"]!=null)                {                    model.TXLarea=row["TXLarea"].ToString();                }                if(row["TXLnum"]!=null)                {                    model.TXLnum=row["TXLnum"].ToString();                }                if(row["TXLyear"]!=null)                {                    model.TXLyear=row["TXLyear"].ToString();                }                if(row["groupid"]!=null)                {                    model.groupid=row["groupid"].ToString();                }            }            return model;        }        ///         /// 获得数据列表        /// 
        public DataSet GetList(string strWhere)        {            StringBuilder strSql=new StringBuilder();            strSql.Append("select TXLid,TXLname,TXLtel,TXLimage,TXLsex,TXLarea,TXLnum,TXLyear,groupid ");            strSql.Append(" FROM TXL_list ");            if(strWhere.Trim()!="")            {                strSql.Append(" where "+strWhere);            }            return DbHelperSQL.Query(strSql.ToString());        }        ///         /// 获得数据列表        /// 
        public DataSet GetList1(string strWhere)        {            StringBuilder strSql = new StringBuilder();            strSql.Append("select * from Group_list ");            if (strWhere.Trim() != "")            {                strSql.Append(" where " + strWhere);            }            return DbHelperSQL.Query(strSql.ToString());        }        ///         /// 获得数据列表        /// 
        public DataSet GetList2(string strWhere)        {            StringBuilder strSql = new StringBuilder();            strSql.Append("select a.*,b.groupname from TXL_list a inner join Group_list b on a.groupid=b.groupid ");            if (strWhere.Trim() != "")            {                strSql.Append(" where " + strWhere);            }            return DbHelperSQL.Query(strSql.ToString());        }        public DataSet GetModel(string name)        {            StringBuilder strSql = new StringBuilder();            strSql.Append("select  TXLid,TXLname,TXLtel,TXLimage,TXLsex,TXLarea,TXLnum,TXLyear,groupid from TXL_list ");            strSql.Append(" where TXLname like '%'+@TXLname+ '%'");            SqlParameter[] parameters = {                    new SqlParameter("@TXLname", SqlDbType.NVarChar,50)            };            parameters[0].Value = name;            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);                          return ds;                  }        public DataSet Getphone(string phone)        {            StringBuilder strSql = new StringBuilder();            strSql.Append("select   TXLid,TXLname,TXLtel,TXLimage,TXLsex,TXLarea,TXLnum,TXLyear,groupid from TXL_list ");            strSql.Append(" where TXLtel like '%'+@TXLtel+'%'");            SqlParameter[] parameters = {                    new SqlParameter("@TXLtel", SqlDbType.NVarChar,50)            };            parameters[0].Value = phone;            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);                           return ds;                 }        ///         /// 获得前几行数据        /// 
        public DataSet GetList(int Top,string strWhere,string filedOrder)        {            StringBuilder strSql=new StringBuilder();            strSql.Append("select ");            if(Top>0)            {                strSql.Append(" top "+Top.ToString());            }            strSql.Append(" TXLid,TXLname,TXLtel,TXLimage,TXLsex,TXLarea,TXLnum,TXLyear,groupid ");            strSql.Append(" FROM TXL_list ");            if(strWhere.Trim()!="")            {                strSql.Append(" where "+strWhere);            }            strSql.Append(" order by " + filedOrder);            return DbHelperSQL.Query(strSql.ToString());        }        ///         /// 获取记录总数        /// 
        public int GetRecordCount(string strWhere)        {            StringBuilder strSql=new StringBuilder();            strSql.Append("select count(1) FROM TXL_list ");            if(strWhere.Trim()!="")            {                strSql.Append(" where "+strWhere);            }            object obj = DbHelperSQL.GetSingle(strSql.ToString());            if (obj == null)            {                return 0;            }            else            {                return Convert.ToInt32(obj);            }        }        ///         /// 分页获取数据列表        /// 
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)        {            StringBuilder strSql=new StringBuilder();            strSql.Append("SELECT * FROM ( ");            strSql.Append(" SELECT ROW_NUMBER() OVER (");            if (!string.IsNullOrEmpty(orderby.Trim()))            {                strSql.Append("order by T." + orderby );            }            else            {                strSql.Append("order by T.TXLid desc");            }            strSql.Append(")AS Row, T.*  from TXL_list T ");            if (!string.IsNullOrEmpty(strWhere.Trim()))            {                strSql.Append(" WHERE " + strWhere);            }            strSql.Append(" ) TT");            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);            return DbHelperSQL.Query(strSql.ToString());        }        /*        ///         /// 分页获取数据列表        /// 
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)        {            SqlParameter[] parameters = {                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),                    new SqlParameter("@PageSize", SqlDbType.Int),                    new SqlParameter("@PageIndex", SqlDbType.Int),                    new SqlParameter("@IsReCount", SqlDbType.Bit),                    new SqlParameter("@OrderType", SqlDbType.Bit),                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),                    };            parameters[0].Value = "TXL_list";            parameters[1].Value = "TXLid";            parameters[2].Value = PageSize;            parameters[3].Value = PageIndex;            parameters[4].Value = 0;            parameters[5].Value = 0;            parameters[6].Value = strWhere;                return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");        }*/        #endregion  BasicMethod        #region  ExtensionMethod        #endregion  ExtensionMethod        public string GetGroupName(int groupid)        {            StringBuilder strSql = new StringBuilder();            strSql.Append("select groupname from Group_list where groupid=@groupid ");            SqlParameter[] parameters = {                    new SqlParameter("@groupid", SqlDbType.Int,32)            };            parameters[0].Value = groupid;            return DbHelperSQL.GetSingle(strSql.ToString(), parameters).ToString();        }    }}