sqlstore.cs

小说网(c#)版源程序,学习还是不错滴,大家可以下载下来看看.

using System;
using System.Data;
using System.Data.OleDb;
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
{
    private static string connection =System.Configuration.ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;

    public static bool SqlTopicIsexist(int boardid, int oldid)
    {
        SqlConnection SqlConn = new SqlConnection(connection);
        SqlConn.Open();
        SqlCommand comm = new SqlCommand("novel_topic_isexist", SqlConn);
        comm.CommandType = System.Data.CommandType.StoredProcedure;
        comm.Parameters.Add("@boardid", SqlDbType.Int).Value = boardid;
        comm.Parameters.Add("@oldid", SqlDbType.Int).Value = oldid;
        comm.Parameters.Add("@isexist", SqlDbType.Bit).Direction = ParameterDirection.Output;
        comm.ExecuteNonQuery();
        bool isexist = (bool)comm.Parameters["@isexist"].Value;
        comm.Dispose();
        SqlConn.Close();
        return isexist;
    }
    /// <summary>
    /// 新增小说主题
    /// </summary>
    /// <param name="title">标题</param>
    /// <param name="author">作者</param>
    /// <param name="isfinish">假连载，真完成</param>
    /// <param name="pic">图片地址，空无图</param>
    /// <param name="chapter">章节数</param>
    /// <param name="intro">简介</param>
    /// <param name="hidden">是否隐藏</param>
    /// <param name="boardid">版块ID</param>
    /// <param name="oldid">原ID</param>
    /// <returns>返回主题ID</returns>
    public static int SqlTopicAdd(string title, string author, bool isfinish, string pic, int chapter, string intro, bool hidden, int boardid, int oldid)
    {
        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("@chapter", SqlDbType.Int).Value = chapter;
        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("@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;
        comm.ExecuteNonQuery();
        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="boardid">版块id</param>
    /// <param name="oldid">小说原id</param>
    /// <param name="topicid">小说id</param>
    /// <param name="chapter">小说章节数</param>
    /// <param name="tablename">所在表名称</param>
    /// <param name="path">文件所在相对路径</param>
    public static void SqlAutoUpdate(int boardid, int oldid, out int topicid, out int chapter, out string tablename, out string 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("@boardid", SqlDbType.Int).Value = boardid;
        comm.Parameters.Add("@oldid", SqlDbType.Int).Value = oldid;
        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;
        SqlDataAdapter data = new SqlDataAdapter(comm);
        data.Fill(table);
        data.Dispose();
        topicid = (int)comm.Parameters["@topicid"].Value;
        chapter = (int)comm.Parameters["@chapter"].Value;
        tablename = (string)comm.Parameters["@tablename"].Value;
        path = "";
        if (table.Rows.Count == 1)
            path = table.Rows[0]["path"].ToString();
        comm.Dispose();
        SqlConn.Close();
    }
    /// <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="chapter">章节数量</param>
    /// <param name="isfinish">是否完成</param>
    public static void SqlTopicRepair(int topicid, int chapter,bool isfinish)
    {
        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("@chapter", SqlDbType.Int).Value = chapter;
        comm.Parameters.Add("@isfinish", SqlDbType.Bit).Value = isfinish;
        comm.ExecuteNonQuery();
        comm.Dispose();
        SqlConn.Close();
    }
    /// <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();