asp.net(C#)处理数据一个通用类，包括存储过程，适用于初学者。-优快云博客

CODE:

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>
//该源码下载自www.51aspx.com(５１ａｓｐｘ．ｃｏｍ)
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="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, string intro, bool hidden, int boardid, long length, int oldid, bool isvip)
{
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("@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("@isvip", SqlDbType.Bit).Value = isvip;
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.ToString());
}
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="isvip">VIP</param>
/// <param name="topicid">小说id</param>
/// <param name="chapter">小说章节数</param>
/// <param name="tablename">所在表名称</param>
/// <param name="path">文件路径</param>
public static void SqlAutoUpdate(string title, int oldid, bool isvip, 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("@isvip", SqlDbType.Bit).Value = isvip;
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);
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 = "";
if (table.Rows.Count == 1)
path = table.Rows[0]["path"].ToString();
}
catch (Exception exp)
{
PrintFile.PrintError(RootPath, "SqlAutoUpdate" + exp.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="isfinish">是否完成</param>
/// <param name="length">总长度</param>
/// <param name="isvip">VIP</param>
public static void SqlTopicRepair(int topicid, bool isfinish, long length, bool isvip)
{
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("@isvip", SqlDbType.Bit).Value = isvip;
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();
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;
}
/// <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>
/// <returns></returns>
public static DataTable SqlChapterList(int topicid, out string title, out string author, out string intro)
{
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;
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;
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>
/// <returns></returns>
public static DataTable SqlSearch(string search, bool type)
{
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;
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();
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="intro">简介</param>
/// <param name="boardid">版块ID</param>
/// <returns>返回主题ID</returns>
public static int SqlDiyAddTopic(string title, string author, string pic, 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("@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>
/// <returns></returns>
public static DataSet SqlDiyReadChapter(int topicid, string author, int chapterid, int operate, ref string title, ref string path)
{
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, 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;
comm.Dispose();
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;
SqlDataAdapter data = new SqlDataAdapter(comm);
data.Fill(table);
data.Dispose();
pic = (string)comm.Parameters["@pic"].Value;
if (table.Rows.Count == 1)
path = table.Rows[0]["path"].ToString();
comm.Dispose();
SqlConn.Close();
}
/// <summary>
/// 删除该小说所有数据库记录
/// </summary>
/// <param name="topicid">小说ID</param>
public static void SqlNovelDelete(int topicid)
{
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.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();
}
}