sqlstore.cs

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

        comm.Dispose();
        return table;
    }
    /// <summary>
    /// 推荐小说
    /// </summary>
    /// <param name="topicid">小说id</param>
    public static void SqlCommendNovel(int topicid)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_commend_novel", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        comm.ExecuteNonQuery();
        comm.Dispose();
        SqlConn.Close();
    }
    /// <summary>
    /// 隐藏小说
    /// </summary>
    /// <param name="topicid">小说id</param>
    public static void SqlHidden(int topicid)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_hidden", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        comm.ExecuteNonQuery();
        comm.Dispose();
        SqlConn.Close();
    }
    /// <summary>
    /// 推荐小说
    /// </summary>
    /// <param name="topicid">小说id</param>
    public static void SqlCommend(int topicid)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_commend_commend", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        comm.ExecuteNonQuery();
        comm.Dispose();
        SqlConn.Close();
    }
    /// <summary>
    /// 删除推荐
    /// </summary>
    /// <param name="topicid">小说id</param>
    public static void SqlCommendDel(int topicid)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_commend_del", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        comm.ExecuteNonQuery();
        comm.Dispose();
        SqlConn.Close();
    }
    /// <summary>
    /// 删除评论
    /// </summary>
    /// <param name="topicid">评论id</param>
    public static void SqlCommentDel(int commentid)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_comment_del", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@commentid", SqlDbType.Int).Value = commentid;
        comm.ExecuteNonQuery();
        comm.Dispose();
        SqlConn.Close();
    }
    /// <summary>
    /// 添加书窝
    /// </summary>
    /// <param name="username">用户名</param>
    /// <param name="topicid">小说ID</param>
    /// <returns> 值=101，超出100本；  值=111，该书已收藏；  值=1，添加成功</returns>
    public static int SqlLikeAdd(string username, int topicid)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_like_add", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@username", SqlDbType.NVarChar, 20).Value = username;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        comm.Parameters.Add("@mark", SqlDbType.Int).Direction = ParameterDirection.Output;
        comm.ExecuteNonQuery();
        int mark = (int)comm.Parameters["@mark"].Value;
        comm.Dispose();
        SqlConn.Close();
        return mark;
    }
    /// <summary>
    /// 判断书窝是否有更新
    /// </summary>
    /// <param name="username">用户名</param>
    /// <returns></returns>
    public static bool SqlLikeUpdate(string username)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_like_update", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@username", SqlDbType.NVarChar, 20).Value = username;
        comm.Parameters.Add("@isupdate", SqlDbType.Bit).Direction = ParameterDirection.Output;
        comm.ExecuteNonQuery();
        bool isupdate = (bool)comm.Parameters["@isupdate"].Value;
        comm.Dispose();
        SqlConn.Close();
        return isupdate;
    }
    /// <summary>
    /// 发送确认函
    /// </summary>
    /// <param name="username">用户名</param>
    /// <param name="validate">验证码</param>
    /// <param name="mail">用户注册邮箱</param>
    /// <returns></returns>
    public static string SqlGetPass(string username, string validate)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_user_getpass", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@username", SqlDbType.NVarChar, 20).Value = username;
        comm.Parameters.Add("@validate", SqlDbType.NVarChar, 50).Value = validate;
        comm.Parameters.Add("@mail", SqlDbType.NVarChar, 50).Direction = ParameterDirection.Output;
        comm.ExecuteNonQuery();
        string mail = (string)comm.Parameters["@mail"].Value;
        comm.Dispose();
        SqlConn.Close();
        return mail;
    }
    /// <summary>
    /// 忘记密码修改页初始时验证链接是否合法
    /// </summary>
    /// <param name="username">用户名</param>
    /// <param name="validate">验证码</param>
    /// <returns></returns>
    public static bool SqlModifyConfirm(string username, string validate)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("nover_user_modify", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@username", SqlDbType.NVarChar, 20).Value = username;
        comm.Parameters.Add("@validate", SqlDbType.NVarChar, 50).Value = validate;
        comm.Parameters.Add("@exist", SqlDbType.Bit).Direction = ParameterDirection.Output;
        comm.ExecuteNonQuery();
        bool exist = (bool)comm.Parameters["@exist"].Value;
        comm.Dispose();
        SqlConn.Close();
        return exist;
    }
    /// <summary>
    /// 忘记密码时修改密码
    /// </summary>
    /// <param name="username">用户名</param>
    /// <param name="password">密码</param>
    /// <returns></returns>
    public static bool SqlModify(string username, string password)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_user_modifypassword", 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;
        int update = comm.ExecuteNonQuery();
        comm.Dispose();
        SqlConn.Close();
        return update == 1 ? true : false;
    }
    /// <summary>
    /// 修改密码
    /// </summary>
    /// <param name="username">用户名</param>
    /// <param name="password_old">旧密码</param>
    /// <param name="password_new">新密码</param>
    /// <returns></returns>
    public static bool SqlPassWord(string username, string password_old, string password_new)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_user_password", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@username", SqlDbType.NVarChar, 20).Value = username;
        comm.Parameters.Add("@password_old", SqlDbType.NVarChar, 20).Value = password_old;
        comm.Parameters.Add("@password_new", SqlDbType.NVarChar, 20).Value = password_new;
        int update = comm.ExecuteNonQuery();
        comm.Dispose();
        SqlConn.Close();
        return update == 1 ? true : false;
    }
    /// <summary>
    /// DIY新增小说主题
    /// </summary>
    /// <param name="title">标题</param>
    /// <param name="author">作者</param>
    /// <param name="pic">图片地址，空无图</param>
    /// <param name="path">TXT存放路径</param>
    /// <param name="intro">简介</param>
    /// <param name="boardid">版块ID</param>
    /// <returns>返回主题ID</returns>
    public static int SqlDiyAddTopic(string title, string author, string pic, string path,string intro, int boardid)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_diy_add_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("@pic", SqlDbType.NVarChar, 80).Value = pic;
        comm.Parameters.Add("@path", SqlDbType.NVarChar, 50).Value = path;
        comm.Parameters.Add("@intro", SqlDbType.NVarChar, 2000).Value = intro;
        comm.Parameters.Add("@boardid", SqlDbType.Int).Value = boardid;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Direction = ParameterDirection.Output;
        comm.ExecuteNonQuery();
        int topicid = (int)comm.Parameters["@topicid"].Value;
        comm.Dispose();
        SqlConn.Close();
        return topicid;
    }
    /// <summary>
    /// DIY插入章节
    /// </summary>
    /// <param name="topicid">主题ID</param>
    /// <param name="chapter">章节名称</param>
    /// <param name="path">存放内容路径</param>
    /// <param name="length">章节长度</param>
    public static void SqlDiyAddChapter(int topicid, string chapter, string path, long length)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_diy_add_chapter", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        comm.Parameters.Add("@chapter", SqlDbType.NVarChar, 60).Value = chapter;
        comm.Parameters.Add("@path", SqlDbType.NVarChar, 80).Value = path;
        comm.Parameters.Add("@length", SqlDbType.BigInt).Value = length;
        comm.ExecuteNonQuery();
        comm.Dispose();
        SqlConn.Close();
    }
    /// <summary>
    /// 返回小说章节信息
    /// </summary>
    /// <param name="topicid">小说ID</param>
    /// <param name="author">作者</param>
    /// <param name="chapterid">章节ID</param>
    /// <param name="operate">操作代码</param>
    /// <param name="title">标题</param>
    /// <param name="path">路径</param>
    /// <param name="chapterpath">标题</param>
    /// <returns></returns>
    public static DataSet SqlDiyReadChapter(int topicid, string author, int chapterid, int operate, ref string title, ref string path, ref string chapterpath)
    {
        DataSet dataset = new DataSet();
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_diy_read_chapter", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        comm.Parameters.Add("@author", SqlDbType.NVarChar, 20).Value = author;
        comm.Parameters.Add("@chapterid", SqlDbType.Int).Value = chapterid;
        comm.Parameters.Add("@operate", SqlDbType.Int).Value = operate;
        comm.Parameters.Add("@title", SqlDbType.NVarChar, 100).Direction = ParameterDirection.Output;
        comm.Parameters.Add("@path", SqlDbType.NVarChar, 50).Direction = ParameterDirection.Output;
        comm.Parameters.Add("@chapterpath", SqlDbType.NVarChar, 100).Direction = ParameterDirection.Output;
        SqlDataAdapter data = new SqlDataAdapter(comm);
        data.Fill(dataset);
        data.Dispose();
        SqlConn.Close();
        title = (string)comm.Parameters["@title"].Value;
        path = (string)comm.Parameters["@path"].Value;
        chapterpath = (string)comm.Parameters["@chapterpath"].Value;
        comm.Dispose();