sqlstore.cs

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

    /// <summary>
    /// 小说介绍
    /// </summary>
    /// <param name="topicid">主题ID</param>
    /// <returns></returns>
    public static DataSet SqlIntro(int topicid)
    {
        DataSet dataset = new DataSet();
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_intro", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        SqlDataAdapter data = new SqlDataAdapter(comm);
        data.Fill(dataset);
        data.Dispose();
        comm.Dispose();
        SqlConn.Close();
        return dataset;
    }
    /// <summary>
    /// 章节列表
    /// </summary>
    /// <param name="topicid">主题ID</param>
    /// <param name="title">标题</param>
    /// <param name="author">作者</param>
    /// <param name="intro">作者</param>
    /// <param name="path">作者</param>
    /// <returns></returns>
    public static DataTable SqlChapterList(int topicid, out string title, out string author, out string intro,out string path)
    {
        DataTable table = new DataTable();
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_chapterlist", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        comm.Parameters.Add("@title", SqlDbType.NVarChar, 100).Direction = ParameterDirection.Output;
        comm.Parameters.Add("@author", SqlDbType.NVarChar, 100).Direction = ParameterDirection.Output;
        comm.Parameters.Add("@intro", SqlDbType.NVarChar, 3000).Direction = ParameterDirection.Output;
        comm.Parameters.Add("@path", SqlDbType.NVarChar, 3000).Direction = ParameterDirection.Output;
        SqlDataAdapter data = new SqlDataAdapter(comm);
        data.Fill(table);
        data.Dispose();
        title = (string)comm.Parameters["@title"].Value;
        author = (string)comm.Parameters["@author"].Value;
        intro = (string)comm.Parameters["@intro"].Value;
        path = (string)comm.Parameters["@path"].Value;
        comm.Dispose();
        SqlConn.Close();
        return table;
    }
    /// <summary>
    /// 分页存储过程
    /// </summary>
    /// <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 SqlPage(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_page", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        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="boardid">版块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></returns>
    public static DataSet SqlNovelList(int boardid, string tableName, string tableId, string field, string order, string where, int pageCurrent, int pageSize, ref int recordAmount, ref int pageAmount)
    {
        DataSet dataset = new DataSet();
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_list", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@boardid", SqlDbType.Int).Value = boardid;
        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(dataset);
        data.Dispose();
        SqlConn.Close();
        recordAmount = (int)comm.Parameters["@RecordCount"].Value;
        pageAmount = (int)comm.Parameters["@PageCount"].Value;
        comm.Dispose();
        return dataset;
    }
    /// <summary>
    /// 执行无返回命令
    /// </summary>
    /// <param name="topicid">主题ID</param>
    /// <param name="mark">操作类型</param>
    /// <param name="username">用户名</param>
    public static void SqlAjax(int topicid, int mark, string username)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_ajax", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        comm.Parameters.Add("@mark", SqlDbType.Int).Value = mark;
        comm.Parameters.Add("@username", SqlDbType.NVarChar, 20).Value = username;
        comm.ExecuteNonQuery();
        comm.Dispose();
        SqlConn.Close();
    }
    /// <summary>
    /// 用户登陆
    /// </summary>
    /// <param name="username">用户名</param>
    /// <param name="password">密码</param>
    /// <returns></returns>
    public static bool SqlUserLogin(string username, string password)
    {
        DataTable table = new DataTable();
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_user_login", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@username", SqlDbType.NVarChar, 20).Value = username;
        comm.Parameters.Add("@password", SqlDbType.NVarChar, 20).Value = password;
        comm.Parameters.Add("@issuccess", SqlDbType.Bit).Direction = ParameterDirection.Output;
        SqlDataAdapter data = new SqlDataAdapter(comm);
        data.Fill(table);
        data.Dispose();
        bool issuccess = (bool)comm.Parameters["@issuccess"].Value;
        comm.Dispose();
        SqlConn.Close();
        return issuccess;
    }
    /// <summary>
    /// 管理登陆
    /// </summary>
    /// <param name="username">用户名</param>
    /// <param name="password">密码</param>
    /// <param name="purview">权限</param>
    /// <returns></returns>
    public static bool SqlAdminLogin(string username, string password, out int purview)
    {
        DataTable table = new DataTable();
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_admin_login", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@username", SqlDbType.NVarChar, 20).Value = username;
        comm.Parameters.Add("@password", SqlDbType.NVarChar, 20).Value = password;
        comm.Parameters.Add("@purview", SqlDbType.Int).Direction = ParameterDirection.Output;
        SqlDataAdapter data = new SqlDataAdapter(comm);
        data.Fill(table);
        data.Dispose();
        purview = (int)comm.Parameters["@purview"].Value;
        comm.Dispose();
        SqlConn.Close();
        return purview != 0 ? true : false;
    }
    /// <summary>
    /// 小说搜索
    /// </summary>
    /// <param name="search">搜索内容</param>
    /// <param name="type">真搜索标题，加搜索作者</param>
    /// <param name="judge">真精确搜索，假模糊搜索</param>
    /// <returns></returns>
    public static DataTable SqlSearch(string search, bool type,bool judge)
    {
        DataTable table = new DataTable();
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_search", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@search", SqlDbType.NVarChar, 20).Value = search;
        comm.Parameters.Add("@type", SqlDbType.Bit).Value = type;
        comm.Parameters.Add("@judge", SqlDbType.Bit).Value = judge;
        SqlDataAdapter data = new SqlDataAdapter(comm);
        data.Fill(table);
        data.Dispose();
        SqlConn.Close();
        comm.Dispose();
        return table;
    }
    /// <summary>
    /// 新增评论
    /// </summary>
    /// <param name="topicid">小说ID</param>
    /// <param name="sender">评论者</param>
    /// <param name="content">内容</param>
    /// <param name="ip">ip</param>
    public static void SqlComment(int topicid, string sender, string content, string ip)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_addcomment", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        comm.Parameters.Add("@sender", SqlDbType.NVarChar, 20).Value = sender;
        comm.Parameters.Add("@content", SqlDbType.NVarChar, 2000).Value = content;
        comm.Parameters.Add("@ip", SqlDbType.NVarChar, 15).Value = ip;
        comm.ExecuteNonQuery();
        comm.Dispose();
        SqlConn.Close();
    }
    /// <summary>
    /// 我的书窝
    /// </summary>
    /// <param name="username">用户名</param>
    /// <param name="deltopicid">删除小说ID</param>
    /// <param name="isupdate">是否只查看有更新小说</param>
    /// <returns></returns>
    public static DataTable SqlLike(string username, int deltopicid, bool isupdate)
    {
        DataTable table = new DataTable();
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_like_read", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@username", SqlDbType.NVarChar, 20).Value = username;
        comm.Parameters.Add("@deltopicid", SqlDbType.Int).Value = deltopicid;
        comm.Parameters.Add("@isupdate", SqlDbType.Bit).Value = isupdate;
        SqlDataAdapter data = new SqlDataAdapter(comm);
        data.Fill(table);
        data.Dispose();
        SqlConn.Close();
        comm.Dispose();
        return table;
    }
    /// <summary>
    /// 返回小说章节信息
    /// </summary>
    /// <param name="tablename">表名</param>
    /// <param name="topicid">小说ID</param>
    /// <returns></returns>
    public static DataTable SqlChapter(string tablename, int topicid)
    {
        DataTable table = new DataTable();
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_readchapter", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@tablename", SqlDbType.NVarChar, 30).Value = tablename;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        SqlDataAdapter data = new SqlDataAdapter(comm);
        data.Fill(table);
        data.Dispose();
        SqlConn.Close();