asp.net代码练习 work064 三层结构，SqlDbHelper通用类

最新推荐文章于 2022-03-26 20:57:39 发布

虾米大王

最新推荐文章于 2022-03-26 20:57:39 发布

阅读量284

点赞数

分类专栏： asp.net c# 文章标签： c#

本文链接：https://blog.csdn.net/modern358/article/details/117606391

版权

c# 同时被 2 个专栏收录

284 篇文章 0 订阅

订阅专栏

asp.net

258 篇文章 2 订阅

订阅专栏

sqldbhelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;


namespace work064
{
    /// <summary>
    /// asp.net 三层架构
    /// 针对sql server数据库操作的通用类
    /// 作者：虾米大王
    /// 日期：2021年6月1日
    /// 版本： 1.0
    /// </summary>
    public class SqlDbHelper
    {

        private string _connectionString;

        /// <summary>
        /// 构造函数
        /// </summary>
        public SqlDbHelper()
        { 
            
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">数据库的连接字符串</param>
        public SqlDbHelper(string connectionString)
        {
            this._connectionString = connectionString;
        }

        /// <summary>
        /// 设置数据库连接字符串
        /// </summary>
        public string ConnectionString
        {
            set
            {
                this._connectionString = value;
            }
        }

        /// <summary>
        /// 执行查询，返回DataTable结果集
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="cmdType">执行语句的类型，可以是存储过程或者SQL文本</param>
        /// <param name="parameters">执行语句的参数数组</param>
        /// <returns>DataTable结果集</returns>
        public System.Data.DataTable ExecuteDataTable(string sql, System.Data.CommandType cmdType, System.Data.SqlClient.SqlParameter[] parameters)
        {
            System.Data.DataTable table1 = new System.Data.DataTable();

            //使用using，是为了包含其中的代码执行结束后自动关闭
            using(System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(this._connectionString))
            {
                using(System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql,con))
                {
                    cmd.CommandType = cmdType;
                    if (parameters != null)
                    {
                        foreach (System.Data.SqlClient.SqlParameter para in parameters)
                        {
                            cmd.Parameters.Add(para);
                        }
                    }
                    System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(cmd);
                    adapter.Fill(table1);
                }
            }

            return table1;
        }

        /// <summary>
        /// 执行查询，返回DataTable结果集（函数同名，重构）
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns>DataTable结果集</returns>
        public System.Data.DataTable ExecuteDataTable(string sql)
        {
            return ExecuteDataTable(sql, System.Data.CommandType.Text, null);
        }

        /// <summary>
        /// 执行查询，返回DataTable结果集（函数同名，重构）
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="cmdType">执行语句的类型，可以是存储过程或者SQL文本</param>
        /// <returns>DataTable结果集</returns>
        public System.Data.DataTable ExecuteDataTable(string sql, System.Data.CommandType cmdType)
        {
            return ExecuteDataTable(sql, cmdType, null);
        }

        /// <summary>
        /// 执行查询，返回一个SqlDataReader的对象实例
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="cmdType">执行语句的类型，可以是存储过程或者SQL文本</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>SqlDataReader对象实例</returns>
        public System.Data.SqlClient.SqlDataReader ExecuteReader(string sql, System.Data.CommandType cmdType, System.Data.SqlClient.SqlParameter[] parameters)
        {
            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(this._connectionString);
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, con);
            if (parameters != null)
            {
                foreach (System.Data.SqlClient.SqlParameter para in parameters)
                {
                    cmd.Parameters.Add(para);
                }
            }
            con.Open();
            //执行行为，关闭reader，同时自动关闭con
            return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 执行查询，返回一个SqlDataReader的对象实例（函数同名，重构）
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns>SqlDataReader对象实例</returns>
        public System.Data.SqlClient.SqlDataReader ExecuteReader(string sql)
        {
            return ExecuteReader(sql, System.Data.CommandType.Text, null);
        }

        /// <summary>
        /// 执行查询，返回一个SqlDataReader的对象实例（函数同名，重构）
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="cmdType">SqlDataReader对象实例</param>
        /// <returns>SqlDataReader对象实例</returns>
        public System.Data.SqlClient.SqlDataReader ExecuteReader(string sql, System.Data.CommandType cmdType)
        {
            return ExecuteReader(sql, cmdType, null);
        }

        /// <summary>
        /// 执行查询，返回结果集为首行首列的值
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="cmdType">执行语句的类型，可以是存储过程或者SQL文本</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>首行首列的值</returns>
        public Object ExecuteScalar(string sql,System.Data.CommandType cmdType,System.Data.SqlClient.SqlParameter[] parameters)
        {
            object result = null;

            using(System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(this._connectionString))
            {
                using(System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql,con))
                {
                    cmd.CommandType = cmdType;
                    
                    if (parameters != null)
                    {
                        foreach (System.Data.SqlClient.SqlParameter para in parameters)
                        {
                            cmd.Parameters.Add(para);
                        }
                    }
                    con.Open();
                    result = cmd.ExecuteScalar();
                }
            }

            return result;
        }

        /// <summary>
        /// 执行查询，返回结果集为首行首列的值
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns>首行首列的值</returns>
        public Object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, System.Data.CommandType.Text, null);
        }

        /// <summary>
        /// 执行查询，返回结果集为首行首列的值
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="cmdType">执行语句的类型，可以是存储过程或者SQL文本</param>
        /// <returns>首行首列的值</returns>
        public Object ExecuteScalar(string sql, System.Data.CommandType cmdType)
        {
            return ExecuteScalar(sql, cmdType, null);
        }

        /// <summary>
        /// 对数据库执行增删改操作
        /// </summary>
        /// <param name="sql">执行的SQL语句</param>
        /// <param name="cmdType">执行语句的类型，可以是存储过程或者SQL文本</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string sql, System.Data.CommandType cmdType, System.Data.SqlClient.SqlParameter[] parameters)
        {
            int count = 0;

            using(System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(this._connectionString))
            {
                using(System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql,con))
                {
                    cmd.CommandType = cmdType;

                    if (parameters != null)
                    {
                        foreach (System.Data.SqlClient.SqlParameter para in parameters)
                        {
                            cmd.Parameters.Add(para);
                        }
                    }

                    con.Open();
                    count = cmd.ExecuteNonQuery();
                }
            }
            //执行insert,update,delete之后，返回的受影响行数
            return count; 
        }

        /// <summary>
        /// 对数据库执行增删改操作（函数同名，重构）
        /// </summary>
        /// <param name="sql">执行的SQL语句</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, System.Data.CommandType.Text, null);
        }

        /// <summary>
        /// 对数据库执行增删改操作（函数同名，重构）
        /// </summary>
        /// <param name="sql">执行的SQL语句</param>
        /// <param name="cmdType">执行语句的类型，可以是存储过程或者SQL文本</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string sql, System.Data.CommandType cmdType)
        {
            return ExecuteNonQuery(sql, cmdType, null);
        }

        /// <summary>
        /// 返回当前数据库中，由用户创建的所有表
        /// </summary>
        /// <returns>DataTable结果集</returns>
        public System.Data.DataTable GetTables()
        {
            System.Data.DataTable table1 = null;

            using(System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(this._connectionString))
            {
                con.Open();
                table1 = con.GetSchema("Tables");
            }

            return table1;
        }
    } 
}

webform1.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="work064.WebForm1" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title>三层结构，通用类，测试代码</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="Button1" runat="server" Text="GetTables" OnClick="Button1_Click" />
        <asp:Button ID="Button2" runat="server" Text="ExecuteDataTable1" OnClick="Button2_Click" />
        <asp:Button ID="Button3" runat="server" Text="ExecuteDataTable2" OnClick="Button3_Click"/>
        <asp:Button ID="Button4" runat="server" Text="ExecuteDataTable3" OnClick="Button4_Click" />
    </div>
    </form>
</body>
</html>

webform1.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace work064
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlConnectionStringBuilder bu = new System.Data.SqlClient.SqlConnectionStringBuilder();
            bu.DataSource = "(local)";
            bu.InitialCatalog = "test";
            bu.UserID = "sa";
            bu.Password = "123456";

            SqlDbHelper helper = new SqlDbHelper();
            helper.ConnectionString = bu.ConnectionString;

            

            GridView gv1 = new GridView();
            Page.Form.Controls.Add(gv1);
            gv1.DataSource = helper.GetTables();
            gv1.DataBind();

            

        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlConnectionStringBuilder bu = new System.Data.SqlClient.SqlConnectionStringBuilder();
            bu.DataSource = "(local)";
            bu.InitialCatalog = "test";
            bu.UserID = "sa";
            bu.Password = "123456";

            SqlDbHelper helper = new SqlDbHelper(bu.ConnectionString);

            GridView gv1 = new GridView();
            Page.Form.Controls.Add(gv1);
            gv1.DataSource = helper.ExecuteDataTable("select * from userinfo");
            gv1.DataBind();
        }

        protected void Button3_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlConnectionStringBuilder bu = new System.Data.SqlClient.SqlConnectionStringBuilder();
            bu.DataSource = "(local)";
            bu.InitialCatalog = "test";
            bu.UserID = "sa";
            bu.Password = "123456";

            SqlDbHelper helper = new SqlDbHelper(bu.ConnectionString);

            GridView gv1 = new GridView();
            Page.Form.Controls.Add(gv1);
            gv1.DataSource = helper.ExecuteDataTable("select * from userinfo", System.Data.CommandType.Text);
            gv1.DataBind();
        }

        protected void Button4_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlConnectionStringBuilder bu = new System.Data.SqlClient.SqlConnectionStringBuilder();
            bu.DataSource = "(local)";
            bu.InitialCatalog = "test";
            bu.UserID = "sa";
            bu.Password = "123456";

            SqlDbHelper helper = new SqlDbHelper();
            helper.ConnectionString = bu.ConnectionString;

            GridView gv1 = new GridView();
            Page.Form.Controls.Add(gv1);
            System.Data.SqlClient.SqlParameter[] p1 = {
                                                      new System.Data.SqlClient.SqlParameter()
                                                      };
            p1[0].ParameterName = "@uName";
            p1[0].Value = "zhangfei";

            gv1.DataSource = helper.ExecuteDataTable("select * from userinfo where user_name=@uName", System.Data.CommandType.Text,p1);
            gv1.DataBind();
        }
    }
}

sql

create database test;

create table UserInfo(
	user_id int identity(1,1) not null,
	user_name varchar(20) not null unique,
	real_name nvarchar(8) not null,
	age tinyint not null,
	sex bit not null,
	mobile varchar(14),
	phone varchar(14),
	email varchar(50) not null,
	primary key(user_id) 
);

insert into UserInfo values('zhangfei','张飞',36,1,'13011110001','8845996','zf@qq.com');
insert into UserInfo values('guanyu','关羽',38,1,'13011110002','8845995','gy@qq.com');
insert into UserInfo values('liubei','刘备',42,1,'13011110003','8845994','lb@qq.com');
insert into UserInfo values('zhaoyun','赵云',32,1,'13011110004','8845993','zy@qq.com');
insert into UserInfo values('huangzhong','黄忠',50,1,'13011110005','8845992','hz@qq.com');
insert into UserInfo values('caocao','曹操',48,1,'13011110006','8845991','cc@qq.com');
insert into UserInfo values('sunquan','孙权',33,1,'13011110007','8845990','sq@qq.com');
insert into UserInfo values('diaochan','貂蝉',20,0,'13011110008','8845880','dc@qq.com');
insert into UserInfo values('daqiao','大乔',21,0,'13011110009','8845881','dq@qq.com');
insert into UserInfo values('xiaoqiao','小乔',20,0,'13011110010','8845882','xq@qq.com');

AssemblyInfo.cs

using System.Reflection;
using System.Runtime.CompilerServices;
using System.Runtime.InteropServices;

// 有关程序集的常规信息通过下列特性集
// 控制。更改这些特性值可修改
// 与程序集关联的信息。
[assembly: AssemblyTitle("work064")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("Microsoft")]
[assembly: AssemblyProduct("work064")]
[assembly: AssemblyCopyright("版权所有(C) Microsoft 2021")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]

// 将 ComVisible 设置为 false 会使此程序集中的类型
// 对 COM 组件不可见。如果需要从 COM 访问此程序集中的某个类型，
// 请针对该类型将 ComVisible 特性设置为 true。
[assembly: ComVisible(false)]

// 如果此项目向 COM 公开，则下列 GUID 用于类型库的 ID
[assembly: Guid("ad538896-597d-451f-9c80-034ced45fa06")]

// 程序集的版本信息由下列四个值组成:
//
//      主版本
//      次版本
//      内部版本号
//      修订号
//
// 可以指定所有这些值，也可以使用“修订号”和“内部版本号”的默认值，
// 方法是按如下所示使用“*”:
[assembly: AssemblyVersion("1.0.0.0")]
[assembly: AssemblyFileVersion("1.0.0.0")]

虾米大王

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
asp.net代码练习 work064 三层结构，SqlDbHelper通用类

sqldbhelper.csusing System;using System.Collections.Generic;using System.Linq;using System.Web;namespace work064{ /// <summary> /// asp.net 三层架构 /// 针对sql server数据库操作的通用类 /// 作者：虾米大王 /// 日期：2021年6月1日 /// 版本： 1.0
复制链接

扫一扫