/// <summary>更新实体数据
/// 更新实体数据
/// </summary>
/// <param name="info">表实体模型</param>
public void Update(TableBaseInfo info)
{
string sql = string.Format("UPDATE TableBase SET TableName=@TableName,TableDescription=@TableDescription,Fields=@Fields,UserID=@UserID,UserName=@UserName,Status=@Status,AddTime=@AddTime,UpdateTime=@UpdateTime,MySysTableID=@MySysTableID where ID={0}", info.ID);
SqlParameter[] paras =
{
new SqlParameter("@TableName", SqlDbType.NVarChar),
new SqlParameter("@TableDescription", SqlDbType.NVarChar),
new SqlParameter("@Fields", SqlDbType.NText),
new SqlParameter("@UserID", SqlDbType.Int),
new SqlParameter("@UserName", SqlDbType.NVarChar),
new SqlParameter("@Status", SqlDbType.Int),
new SqlParameter("@AddTime", SqlDbType.DateTime),
new SqlParameter("@UpdateTime", SqlDbType.DateTime),
new SqlParameter("@MySysTableID", SqlDbType.Int)
};
paras[0].Value = info.TableName;
paras[1].Value = info.TableDescription;
paras[2].Value = info.Fields;
paras[3].Value = info.UserID;
paras[4].Value = info.UserName;
paras[5].Value = info.Status;
paras[6].Value = info.AddTime;
paras[7].Value = info.UpdateTime;
paras[8].Value = info.MySysTableID;
MssqlHandler.ExecuteNonQuery(sql, paras);
}
/// <summary>插入实体数据
/// 插入实体数据
/// </summary>
/// <param name="info">表实体模型</param>
public void Insert(TableBaseInfo info)
{
string sql = "INSERT INTO TableBase(TableName,TableDescription,Fields,UserID,UserName,Status,AddTime,UpdateTime,MySysTableID) values(@TableName,@TableDescription,@Fields,@UserID,@UserName,@Status,@AddTime,@UpdateTime,@MySysTableID)";
SqlParameter[] paras =
{
new SqlParameter("@TableName", SqlDbType.NVarChar),
new SqlParameter("@TableDescription", SqlDbType.NVarChar),
new SqlParameter("@Fields", SqlDbType.NText),
new SqlParameter("@UserID", SqlDbType.Int),
new SqlParameter("@UserName", SqlDbType.NVarChar),
new SqlParameter("@Status", SqlDbType.Int),
new SqlParameter("@AddTime", SqlDbType.DateTime),
new SqlParameter("@UpdateTime", SqlDbType.DateTime),
new SqlParameter("@MySysTableID", SqlDbType.Int)
};
paras[0].Value = info.TableName;
paras[1].Value = info.TableDescription;
paras[2].Value = info.Fields;
paras[3].Value = info.UserID;
paras[4].Value = info.UserName;
paras[5].Value = info.Status;
paras[6].Value = info.AddTime;
paras[7].Value = info.UpdateTime;
paras[8].Value = info.MySysTableID;
MssqlHandler.ExecuteNonQuery(sql, paras);
}
/// <summary>根据主键ID读取记录
/// 根据主键ID读取记录
/// </summary>
/// <param name="id">表主键</param>
/// <returns>话题类别实体对象</returns>
public TopicCategoryInfo Read(int id)
{
string sql = string.Format("SELECT ID,FatherID,CateName,CateCName,TopicCount,Keywords,Description,OrderID FROM TopicCategory WHERE ID ={0}", id);
TopicCategoryInfo info = new TopicCategoryInfo();
using (SqlDataReader dr = MssqlHandler.ExecuteReader(sql))
{
PrepareSingleModel(dr, info);
}
return(info);
}
/// <summary>根据主键ID读取记录
/// 根据主键ID读取记录
/// </summary>
/// <param name="id">表主键</param>
/// <returns>自助建表基本信息实体对象</returns>
public TableBaseInfo Read(int id)
{
string sql = string.Format("SELECT ID,TableName,TableDescription,Fields,UserID,UserName,Status,AddTime,UpdateTime,MySysTableID FROM TableBase WHERE ID ={0}", id);
TableBaseInfo info = new TableBaseInfo();
using (SqlDataReader dr = MssqlHandler.ExecuteReader(sql))
{
PrepareSingleModel(dr, info);
}
return(info);
}
/******************* 以上自动生成代码 *****************/
public TableBaseInfo Read(int userid, string tableName)
{
string sql = string.Format("SELECT ID,TableName,TableDescription,Fields,UserID,UserName,Status,AddTime,UpdateTime,MySysTableID FROM TableBase WHERE UserID={0} and TableName='{1}'", userid, tableName.Replace(" ", ""));
TableBaseInfo info = new TableBaseInfo();
using (SqlDataReader dr = MssqlHandler.ExecuteReader(sql))
{
PrepareSingleModel(dr, info);
}
return(info);
}
/// <summary>
/// 根据搜索条件、排序、页码、每页数和返回总记录参数 读取模型列表
/// </summary>
/// <param name="where">带where的查询条件</param>
/// <param name="orderBy">排序字段+排序方式</param>
/// <param name="pageNum">当前页码</param>
/// <param name="pageSize">每页页数</param>
/// <param name="totalCount">返回的总记录数</param>
/// <returns></returns>
public List <TableBaseInfo> ReadList(string where, string orderBy, int pageNum, int pageSize, ref int totalCount)
{
string sql = string.Format("SELECT ID,TableName,TableDescription,Fields,UserID,UserName,Status,AddTime,UpdateTime,MySysTableID FROM TableBase {0} {1}", where, orderBy, (pageNum - 1) * pageSize, pageSize);
List <TableBaseInfo> infolist = new List <TableBaseInfo>();
using (SqlDataReader dr = MssqlHandler.ExecuteReader(sql))
{
PrepareListModel(dr, infolist);
}
if (totalCount > -1)
{
sql = string.Format("SELECT count(0) FROM TableBase {0}", where);
totalCount = Convert.ToInt32(MssqlHandler.ExecuteScalar(sql));
}
return(infolist);
}
/// <summary>更新实体数据
/// 更新实体数据
/// </summary>
/// <param name="info">表实体模型</param>
public void Update(TopicCategoryInfo info)
{
string sql = string.Format("UPDATE TopicCategory SET FatherID=@FatherID,CateName=@CateName,CateCName=@CateCName,TopicCount=@TopicCount,Keywords=@Keywords,Description=@Description,OrderID=@OrderID where ID={0}", info.ID);
SqlParameter[] paras =
{
new SqlParameter("@FatherID", SqlDbType.Int),
new SqlParameter("@CateName", SqlDbType.NVarChar),
new SqlParameter("@CateCName", SqlDbType.NVarChar),
new SqlParameter("@TopicCount", SqlDbType.Int),
new SqlParameter("@Keywords", SqlDbType.NVarChar),
new SqlParameter("@Description", SqlDbType.NVarChar),
new SqlParameter("@OrderID", SqlDbType.Int)
};
paras[0].Value = info.FatherID;
paras[1].Value = info.CateName;
paras[2].Value = info.CateCName;
paras[3].Value = info.TopicCount;
paras[4].Value = info.Keywords;
paras[5].Value = info.Description;
paras[6].Value = info.OrderID;
MssqlHandler.ExecuteNonQuery(sql, paras);
}
/// <summary>插入实体数据
/// 插入实体数据
/// </summary>
/// <param name="info">表实体模型</param>
public void Insert(TopicCategoryInfo info)
{
string sql = "INSERT INTO TopicCategory(FatherID,CateName,CateCName,TopicCount,Keywords,Description,OrderID) values(@FatherID,@CateName,@CateCName,@TopicCount,@Keywords,@Description,@OrderID)";
SqlParameter[] paras =
{
new SqlParameter("@FatherID", SqlDbType.Int),
new SqlParameter("@CateName", SqlDbType.NVarChar),
new SqlParameter("@CateCName", SqlDbType.NVarChar),
new SqlParameter("@TopicCount", SqlDbType.Int),
new SqlParameter("@Keywords", SqlDbType.NVarChar),
new SqlParameter("@Description", SqlDbType.NVarChar),
new SqlParameter("@OrderID", SqlDbType.Int)
};
paras[0].Value = info.FatherID;
paras[1].Value = info.CateName;
paras[2].Value = info.CateCName;
paras[3].Value = info.TopicCount;
paras[4].Value = info.Keywords;
paras[5].Value = info.Description;
paras[6].Value = info.OrderID;
MssqlHandler.ExecuteNonQuery(sql, paras);
}
/// <summary>删除多条指定ID的数据
/// 删除多条指定ID的数据
/// </summary>
/// <param name="ids">用户表的主键值,以,号分隔</param>
public void Delete(string ids)
{
string sql = "Update TopicCategory SET IsDelete=1,UpdateTime=getdate() WHERE [ID] IN(" + ids + ")";
MssqlHandler.ExecuteNonQuery(sql);
}
