sqlstore.cs

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

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// Sql相关操作
/// </summary>
public class SqlStore
{
    public static string RootPath = "";//根目录
    private static string connection = System.Configuration.ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;
    /// <summary>
    /// 新增小说主题
    /// </summary>
    /// <param name="title">标题</param>
    /// <param name="author">作者</param>
    /// <param name="isfinish">假连载，真完成</param>
    /// <param name="pic">图片地址，空无图</param>
    /// <param name="path">TXT存放路径</param>
    /// <param name="intro">简介</param>
    /// <param name="hidden">是否隐藏</param>
    /// <param name="boardid">版块ID</param>
    /// <param name="oldid">原ID</param>
    /// <param name="type">站点</param>
    /// <returns>返回主题ID</returns>
    public static int SqlTopicAdd(string title, string author, bool isfinish, string pic,string path, string intro, bool hidden, int boardid, long length, int oldid, int type)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_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("@isfinish", SqlDbType.Bit).Value = isfinish;
        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("@hidden", SqlDbType.Bit).Value = hidden;
        comm.Parameters.Add("@boardid", SqlDbType.Int).Value = boardid;
        comm.Parameters.Add("@oldid", SqlDbType.Int).Value = oldid;
        comm.Parameters.Add("@length", SqlDbType.BigInt).Value = length;
        comm.Parameters.Add("@type", SqlDbType.Int).Value = type;
        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>
    /// 插入章节
    /// </summary>
    /// <param name="topicid">主题ID</param>
    /// <param name="chapter">章节名称</param>
    /// <param name="path">存放内容路径</param>
    /// <param name="length">章节长度</param>
    public static void SqlChapterAdd(int topicid, string chapter, string path, long length, string oldurl)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_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.Parameters.Add("@oldurl", SqlDbType.NVarChar, 80).Value = oldurl;
        try
        {
            comm.ExecuteNonQuery();
        }
        catch (Exception exp)
        {
            PrintFile.PrintError(RootPath, "SqlChapterAdd" + exp.Message);
        }
        comm.Dispose();
        SqlConn.Close();
    }
    /// <summary>
    /// 修复零长度章节
    /// </summary>
    /// <param name="chapterid">章节id</param>
    /// <param name="length">内容长度</param>
    /// <param name="error">修复是否成功，成功为假（0）</param>
    public static void SqlChapterRepair(int chapterid, int length, bool error)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_repair_chapter", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@chapterid", SqlDbType.Int).Value = chapterid;
        comm.Parameters.Add("@length", SqlDbType.BigInt).Value = length;
        comm.Parameters.Add("@error", SqlDbType.Bit).Value = error;
        comm.ExecuteNonQuery();
        comm.Dispose();
        SqlConn.Close();
    }
    /// <summary>
    /// 更新小说
    /// </summary>
    /// <param name="title">小说名称</param>
    /// <param name="oldid">小说原id</param>
    /// <param name="type">站点</param>
    /// <param name="topicid">小说id</param>
    /// <param name="chapter">小说章节数</param>
    /// <param name="tablename">所在表名称</param>
    /// <param name="path">文件路径</param>
    /// <returns></returns>
    public static bool SqlAutoUpdate(string title, int oldid, int type, out int topicid, out int chapter, out string tablename, out string path)
    {
        topicid = 0;
        chapter = 0;
        tablename = "";
        path = "";
        DataTable table = new DataTable();
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_auto_update", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@title", SqlDbType.NVarChar, 100).Value = title;
        comm.Parameters.Add("@oldid", SqlDbType.Int).Value = oldid;
        comm.Parameters.Add("@type", SqlDbType.Int).Value = type;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Direction = ParameterDirection.Output;
        comm.Parameters.Add("@chapter", SqlDbType.Int).Direction = ParameterDirection.Output;
        comm.Parameters.Add("@tablename", SqlDbType.NVarChar, 30).Direction = ParameterDirection.Output;
        comm.Parameters.Add("@path", SqlDbType.NVarChar, 30).Direction = ParameterDirection.Output;
        SqlDataAdapter data = new SqlDataAdapter(comm);
        try
        {
            data.Fill(table);
            data.Dispose();
            topicid = (int)comm.Parameters["@topicid"].Value;
            chapter = (int)comm.Parameters["@chapter"].Value;
            tablename = (string)comm.Parameters["@tablename"].Value;
            path = (string)comm.Parameters["@path"].Value;
        }
        catch (Exception exp)
        {
            PrintFile.PrintError(RootPath, "SqlAutoUpdate" + exp.Message);
            tablename = "lock";
        }
        comm.Dispose();
        SqlConn.Close();
        if (tablename == "lock") return true;
        else return false;
    }
    /// <summary>
    /// 更新插入章节
    /// </summary>
    /// <param name="tablename">表名</param>
    /// <param name="topicid">主题ID</param>
    /// <param name="chapter">章节名称</param>
    /// <param name="path">存放内容路径</param>
    /// <param name="length">章节长度</param>
    public static void SqlAutoChapter(string tablename, int topicid, string chapter, string path, long length, string oldurl)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_auto_chapter", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@tablename", SqlDbType.NVarChar, 30).Value = tablename;
        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.Parameters.Add("@oldurl", SqlDbType.NVarChar, 80).Value = oldurl;
        comm.ExecuteNonQuery();
        comm.Dispose();
        SqlConn.Close();
    }
    /// <summary>
    /// 返回一张表
    /// </summary>
    /// <param name="storeName">存储过程名</param>
    /// <returns></returns>
    public static DataTable SqlNormal(string storename)
    {
        DataTable table = new DataTable();
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand(storename, SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        SqlDataAdapter data = new SqlDataAdapter(comm);
        data.Fill(table);
        data.Dispose();
        comm.Dispose();
        SqlConn.Close();
        return table;
    }
    /// <summary>
    /// 返回多张表
    /// </summary>
    /// <param name="storeName">存储过程名</param>
    /// <returns></returns>
    public static DataSet SqlNormal_(string storename)
    {
        DataSet dataset = new DataSet();
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand(storename, SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        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="isfinish">是否完成</param>
    /// <param name="length">总长度</param>
    /// <param name="type">站点类别</param>
    /// <param name="tablename">章节表</param>
    public static DataTable SqlTopicRepair(int topicid, bool isfinish, long length, int type, string tablename)
    {
        DataTable table = new DataTable();
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_repair_topic", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@topicid", SqlDbType.Int).Value = topicid;
        comm.Parameters.Add("@isfinish", SqlDbType.Bit).Value = isfinish;
        comm.Parameters.Add("@length", SqlDbType.BigInt).Value = length;
        comm.Parameters.Add("@type", SqlDbType.Int).Value = type;
        comm.Parameters.Add("@tablename", SqlDbType.NVarChar, 30).Value = tablename;
        SqlDataAdapter data = new SqlDataAdapter(comm);
        data.Fill(table);
        data.Dispose();
        comm.Dispose();
        SqlConn.Close();
        return table;
    }
    /// <summary>
    /// 检测该用户名是否存在
    /// </summary>
    /// <param name="username">用户名</param>
    /// <returns></returns>
    public static bool SqlUserIsexist(string username)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_user_judge", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@username", SqlDbType.NVarChar, 20).Value = username;
        comm.Parameters.Add("@isexist", SqlDbType.Int).Direction = ParameterDirection.Output;
        comm.ExecuteNonQuery();
        int isexist = (int)comm.Parameters["@isexist"].Value;
        comm.Dispose();
        SqlConn.Close();
        return isexist == 0 ? true : false;
    }
    /// <summary>
    /// 用户注册
    /// </summary>
    /// <param name="username">用户名</param>
    /// <param name="password">密码</param>
    /// <param name="mail">邮箱</param>
    /// <returns></returns>
    public static bool SqlUserRegister(string username, string password, string mail)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_user_register", 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("@mail", SqlDbType.NVarChar, 50).Value = mail;
        comm.Parameters.Add("@isexist", SqlDbType.Int).Direction = ParameterDirection.Output;
        comm.ExecuteNonQuery();
        int isexist = (int)comm.Parameters["@isexist"].Value;
        comm.Dispose();
        SqlConn.Close();
        return isexist == 0 ? true : false;
    }