sqlstore.cs

起点9 V2.0小说整站系统 介绍： 1、 采用企业级标准应用三层架构

        return dataset;
    }
    /// <summary>
    /// 分页显示列表
    /// </summary>
    /// <param name="topicid">小说id</param>
    /// <param name="author">作者</param>
    /// <param name="storeName">存储过程名</param>
    /// <param name="tableName">表名</param>
    /// <param name="tableId">关键字</param>
    /// <param name="field">要返回的字段</param>
    /// <param name="order">排序字段</param>
    /// <param name="where">条件</param>
    /// <param name="pageCurrent">要返回的页</param>
    /// <param name="pageSize">每页显示记录数</param>
    /// <returns></returns>
    public static DataTable SqlDiylList(int topicid, string author, string tableName, string tableId, string field, string order, string where, int pageCurrent, int pageSize, ref int recordAmount, ref int pageAmount)
    {
        DataTable table = new DataTable();
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_diy_list", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        comm.Parameters.Add("@author", SqlDbType.NVarChar, 100).Value = author;
        comm.Parameters.Add("@tbname", SqlDbType.NVarChar, 4000).Value = tableName;
        comm.Parameters.Add("@FieldKey", SqlDbType.NVarChar, 4000).Value = tableId;
        comm.Parameters.Add("@PageCurrent", SqlDbType.Int).Value = pageCurrent;
        comm.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
        comm.Parameters.Add("@FieldShow", SqlDbType.NVarChar, 1000).Value = field;
        comm.Parameters.Add("@FieldOrder", SqlDbType.NVarChar, 1000).Value = order;
        comm.Parameters.Add("@Where", SqlDbType.NVarChar, 1000).Value = where;
        comm.Parameters.Add("@PageCount", SqlDbType.Int).Direction = ParameterDirection.Output;
        comm.Parameters.Add("@RecordCount", SqlDbType.Int).Direction = ParameterDirection.Output;
        SqlDataAdapter data = new SqlDataAdapter(comm);
        data.Fill(table);
        data.Dispose();
        SqlConn.Close();
        recordAmount = (int)comm.Parameters["@RecordCount"].Value;
        pageAmount = (int)comm.Parameters["@PageCount"].Value;
        comm.Dispose();
        return table;
    }
    /// <summary>
    /// 修改章节
    /// </summary>
    /// <param name="author">作者</param>
    /// <param name="topicid">小说ID</param>
    /// <param name="chapterid">章节ID</param>
    /// <param name="chapter">章节名称</param>
    /// <param name="length">章节长度</param>
    /// <returns></returns>
    public static string SqlDiyModChapter(string author, int topicid, int chapterid, string chapter, long length)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_diy_mod_chapter", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@author", SqlDbType.NVarChar, 20).Value = author;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        comm.Parameters.Add("@chapterid", SqlDbType.Int).Value = chapterid;
        comm.Parameters.Add("@chapter", SqlDbType.NVarChar, 60).Value = chapter;
        comm.Parameters.Add("@length", SqlDbType.BigInt).Value = length;
        comm.Parameters.Add("@path", SqlDbType.NVarChar, 80).Direction = ParameterDirection.Output;
        comm.ExecuteNonQuery();
        string path = (string)comm.Parameters["@path"].Value;
        comm.Dispose();
        SqlConn.Close();
        return path;
    }
    /// <summary>
    /// 修改小说简介
    /// </summary>
    /// <param name="author">作者</param>
    /// <param name="topicid">小说ID</param>
    /// <returns></returns>
    public static DataTable SqlDiyIntro(string author, int topicid)
    {
        DataTable table = new DataTable();
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_diy_intro", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@author", SqlDbType.NVarChar, 20).Value = author;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        SqlDataAdapter data = new SqlDataAdapter(comm);
        data.Fill(table);
        data.Dispose();
        SqlConn.Close();
        comm.Dispose();
        return table;
    }
    /// <summary>
    /// 修改小说简介
    /// </summary>
    /// <param name="title">标题</param>
    /// <param name="author">作者</param>
    /// <param name="intro">介绍</param>
    /// <param name="boardid">版块ID</param>
    /// <param name="topicid">小说ID</param>
    /// <param name="cover">原封面地址</param>
    /// <returns></returns>
    public static bool SqlDiyModTopic(string title, string author, string intro, int boardid, int topicid, ref string cover)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_diy_mod_topic", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@title", SqlDbType.NVarChar, 100).Value = title;
        comm.Parameters.Add("@author", SqlDbType.NVarChar, 100).Value = author;
        comm.Parameters.Add("@intro", SqlDbType.NVarChar, 2000).Value = intro;
        comm.Parameters.Add("@boardid", SqlDbType.Int).Value = boardid;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        comm.Parameters.Add("@cover", SqlDbType.NVarChar, 80).Direction = ParameterDirection.Output;
        int update = comm.ExecuteNonQuery();
        cover = (string)comm.Parameters["@cover"].Value;
        comm.Dispose();
        SqlConn.Close();
        return update == 1 ? true : false;
    }
    /// <summary>
    /// cookie登陆
    /// </summary>
    /// <param name="username">用户名</param>
    public static void SqlUserCookie(string username)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_user_cookie", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@username", SqlDbType.NVarChar, 20).Value = username;
        comm.ExecuteNonQuery();
        comm.Dispose();
        SqlConn.Close();
    }
    /// <summary>
    /// 删除、还原回收站
    /// </summary>
    /// <param name="topicid">小说ID</param>
    /// <param name="operate">操作类型：true删除；false还原</param>
    public static void SqlRecycler(int topicid, bool operate)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_recycler", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        comm.Parameters.Add("@operate", SqlDbType.Bit).Value = operate;
        comm.ExecuteNonQuery();
        comm.Dispose();
        SqlConn.Close();
    }
    /// <summary>
    /// 分页重复列表
    /// </summary>
    /// <param name="topicid">小说ID</param>
    /// <param name="storeName">存储过程名</param>
    /// <param name="tableName">表名</param>
    /// <param name="tableId">关键字</param>
    /// <param name="field">要返回的字段</param>
    /// <param name="order">排序字段</param>
    /// <param name="where">条件</param>
    /// <param name="pageCurrent">要返回的页</param>
    /// <param name="pageSize">每页显示记录数</param>
    /// <returns></DataTable>
    public static DataTable SqlRepeate(int topicid, string tableName, string tableId, string field, string order, string where, int pageCurrent, int pageSize, ref int recordAmount, ref int pageAmount)
    {
        DataTable table = new DataTable();
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_repeat", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        comm.Parameters.Add("@tbname", SqlDbType.NVarChar, 4000).Value = tableName;
        comm.Parameters.Add("@FieldKey", SqlDbType.NVarChar, 4000).Value = tableId;
        comm.Parameters.Add("@PageCurrent", SqlDbType.Int).Value = pageCurrent;
        comm.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
        comm.Parameters.Add("@FieldShow", SqlDbType.NVarChar, 1000).Value = field;
        comm.Parameters.Add("@FieldOrder", SqlDbType.NVarChar, 1000).Value = order;
        comm.Parameters.Add("@Where", SqlDbType.NVarChar, 1000).Value = where;
        comm.Parameters.Add("@PageCount", SqlDbType.Int).Direction = ParameterDirection.Output;
        comm.Parameters.Add("@RecordCount", SqlDbType.Int).Direction = ParameterDirection.Output;
        SqlDataAdapter data = new SqlDataAdapter(comm);
        data.Fill(table);
        data.Dispose();
        SqlConn.Close();
        recordAmount = (int)comm.Parameters["@RecordCount"].Value;
        pageAmount = (int)comm.Parameters["@PageCount"].Value;
        comm.Dispose();
        return table;
    }
    /// <summary>
    /// 取小说封面及文本文件存放目录地址
    /// </summary>
    /// <param name="topicid">小说ID</param>
    /// <param name="pic">封面地址</param>
    /// <param name="path">内容文本地址</param>
    public static void SqlNovelFilePath(int topicid, ref string pic, ref string path)
    {
        DataTable table = new DataTable();
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_filepath", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        comm.Parameters.Add("@pic", SqlDbType.NVarChar, 80).Direction = ParameterDirection.Output;
        comm.Parameters.Add("@path", SqlDbType.NVarChar, 50).Direction = ParameterDirection.Output;
        SqlDataAdapter data = new SqlDataAdapter(comm);
        data.Fill(table);
        data.Dispose();
        pic = (string)comm.Parameters["@pic"].Value;
        path = (string)comm.Parameters["@path"].Value;
        comm.Dispose();
        SqlConn.Close();
    }
    /// <summary>
    /// 删除该小说所有数据库记录
    /// </summary>
    /// <param name="topicid">小说ID</param>
    /// <param name="mark">假：保留主题</param>
    public static void SqlNovelDelete(int topicid,bool mark)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_delete", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        comm.Parameters.Add("@mark", SqlDbType.Bit).Value = mark;
        comm.ExecuteNonQuery();
        comm.Dispose();
        SqlConn.Close();
    }
    /// <summary>
    /// 调整用户权限
    /// </summary>
    /// <param name="serial">用户ID</param>
    /// <param name="operater">操作人</param>
    public static void SqlUserPurview(int serial, string operater)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_user_purview", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@serial", SqlDbType.Int).Value = serial;
        comm.Parameters.Add("@operater", SqlDbType.NVarChar, 20).Value = operater;
        comm.ExecuteNonQuery();
        comm.Dispose();
        SqlConn.Close();
    }
    /// <summary>
    /// 修复单篇小说
    /// </summary>
    /// <param name="topicid">小说ID</param>
    /// <param name="chapter">章节数量</param>
    /// <param name="chapterid">章节ID</param>
    /// <param name="tablename">章节所在表</param>
    public static void SqlSingle(int topicid, int chapter, int chapterid, string tablename)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_single", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        comm.Parameters.Add("@chapter", SqlDbType.Int).Value = chapter;
        comm.Parameters.Add("@chapterid", SqlDbType.Int).Value = chapterid;
        comm.Parameters.Add("@tablename", SqlDbType.NVarChar, 30).Value = tablename;
        comm.ExecuteNonQuery();
        comm.Dispose();
        SqlConn.Close();
    }
    /// <summary>
    /// SQL接口
    /// </summary>
    /// <param name="command">SQL命令</param>
    /// <returns></returns>
    public static int SqlExec(string command)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_exec", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@command", SqlDbType.NVarChar, 3000).Value = command;
        int update=comm.ExecuteNonQuery();
        comm.Dispose();