增删改查
using System; using System.Data; using System.Text; using System.Data.SqlClient; using Maticsoft.DBUtility;//Please add references namespace Maticsoft.DAL { /// <summary> /// 数据访问类:TXL_list /// </summary> public partial class TXL_list { public TXL_list() {} #region BasicMethod /// <summary> /// 得到最大ID /// </summary> public int GetMaxId() { return DbHelperSQL.GetMaxID("TXLid", "TXL_list"); } /// <summary> /// 是否存在该记录 /// </summary> 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); } /// <summary> /// 增加一条数据 /// </summary> 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); } } /// <summary> /// 更新一条数据 /// </summary> 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; } } /// <summary> /// 删除一条数据 /// </summary> 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; } } /// <summary> /// 批量删除数据 /// </summary> 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; } } /// <summary> /// 得到一个对象实体 /// </summary> 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; } } /// <summary> /// 得到一个对象实体 /// </summary> 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; } /// <summary> /// 获得数据列表 /// </summary> 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()); } /// <summary> /// 获得数据列表 /// </summary> 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()); } /// <summary> /// 获得数据列表 /// </summary> 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; } /// <summary> /// 获得前几行数据 /// </summary> 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()); } /// <summary> /// 获取记录总数 /// </summary> 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); } } /// <summary> /// 分页获取数据列表 /// </summary> 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()); } /* /// <summary> /// 分页获取数据列表 /// </summary> 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(); } } }