原

ado.net基本使用,sqlhelper简单封装,dbhelper简单使用

3932人阅读 2022/10/1 22:45 总访问：270158 评论：0 收藏：0 手机

分类: 数据库

**sqlhelper简单封装**
```
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;


namespace DAL
{
    public class SqlHelper
    {

        string connstr = "";
        public SqlHelper()
        {
            //读取链接字符串
            connstr = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        }

        public int Execute(string sql, List<SqlParameter> param = null)
        {

            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();

                using (SqlCommand com = new SqlCommand(sql, conn))
                {
                    //参数化
                    if (param != null)
                        com.Parameters.AddRange(param.ToArray());

                    return com.ExecuteNonQuery();
                }
            }
        }

        public object ExecuteScalar(string sql, List<SqlParameter> param = null)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();

                using (SqlCommand com = new SqlCommand(sql, conn))
                {
                    //参数化
                    if (param != null)
                        com.Parameters.AddRange(param.ToArray());

                    return com.ExecuteScalar();
                }
            }
        }

        public DataTable QueryTable(string sql, List<SqlParameter> param = null)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();

                using (SqlCommand com = new SqlCommand(sql, conn))
                {
                    //参数化
                    if (param != null)
                        com.Parameters.AddRange(param.ToArray());

                    DataTable dt = new DataTable();

                    SqlDataAdapter sda = new SqlDataAdapter(com);

                    //把查询结果放入表格
                    sda.Fill(dt);

                    return dt;
                    
                }
            }
        }


    }
}
```

**简单使用1**
```
public class UsersDAL
{
    public List<Users> GetUsers()
    {
        try
        {
            SqlServerHelper sqlHelper = new SqlServerHelper();

            string sql = "select * from Users";

            DataTable dataTable = sqlHelper.QueryTable(sql);


            List<Users> WacUsersList = new List<Users>();

            //把表格解析中对象集合
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                Users user = new Users();

                user.Id = Convert.ToInt32(dataTable.Rows[i]["Id"]);
                user.UserName = dataTable.Rows[i]["UserName"] + "";
                user.Number = dataTable.Rows[i]["Number"] + "";
                user.UClass = dataTable.Rows[i]["UClass"] + "";

                WacUsersList.Add(user);
            }

            return WacUsersList;
        }
        catch (Exception ex)
        {
            return null;
        }
    }
}
```
实体
```
public class Users
{
    public int Id { get; set; }
    public string UserName { get; set; }
    public string Number { get; set; }
    public string UClass { get; set; }
}
```

测试sql
```
 create table Users
  (
     Id int primary key identity(1,1),
     UserName nvarchar(64),
     Number nvarchar(64),
     UClass nvarchar(64)
  )
  
  select * from Users
  
  insert Users values('貂蝉','NS001','NET52')
  insert Users values('吕布','NS002','NET52')
  insert Users values('丁原','NS002','NET52')
  insert Users values('董卓','NS002','NET52')
  insert Users values('王允','NS003','NET52')
```

**简单使用2**
```
using Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.SqlClient;
using System.Data;


namespace DAL
{
    public class UsersDAL
    {
        /// <summary>
        /// 总页数
        /// </summary>
        /// <returns></returns>
        public int GetAllPage(int _rows, string usrename, string number)
        {
            SqlHelper sh = new SqlHelper();


            //sql条件
            string sqlparam = "";

            List<SqlParameter> splist = new List<SqlParameter>();

            if (!string.IsNullOrWhiteSpace(number))
            {
                sqlparam = " and number=@number ";
                splist.Add(new SqlParameter("number", number));
            }
            if (!string.IsNullOrWhiteSpace(usrename))
            {
                sqlparam += " and username=@username ";
                splist.Add(new SqlParameter("username", usrename));
            }

            string sql = string.Format("select count(1) from users where 1=1  {0}", sqlparam);

            int allcount = Convert.ToInt32(sh.ExecuteScalar(sql, splist));

            //总条数变成总页数

            int allpage = allcount / _rows;
            if (allcount % _rows != 0)
            {
                allpage++;
            }


            return allpage;
        }

        public List<Users> GetUser(int _page, int _rows, string usrename, string number)
        {

            SqlHelper sh = new SqlHelper();

            //1  1-5     (page-1)*rows+1 = (1-1)*5+1=1
            //2  6-10    (page-1)*rows+1 = (2-1)*5+1=6
            //3  11-15   (page-1)*rows+1 = (3-1)*5+1=11

            //sql条件
            string sqlparam = "";
            List<SqlParameter> splist = new List<SqlParameter>();
            if (!string.IsNullOrWhiteSpace(number))
            {
                sqlparam += " and number=@number ";
                splist.Add(new SqlParameter("number", number));
            }
            if (!string.IsNullOrWhiteSpace(usrename))
            {
                sqlparam += " and username=@username ";
                splist.Add(new SqlParameter("username", usrename));
            }


            string sql = string.Format(@" select * from ( select *,ROW_NUMBER() over(order by id) as 'rownum' from Users where 1 = 1 {0} )
                             temp where rownum  between {1} and {2} ", sqlparam, (_page - 1) * _rows + 1, _page * _rows);


            DataTable dt = sh.QueryTable(sql, splist);

            List<Users> ulist = new List<Users>();


            //把表格解析中对象集合
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Users user = new Users();

                user.Id = Convert.ToInt32(dt.Rows[i]["Id"]);
                user.UserName = dt.Rows[i]["UserName"].ToString();
                user.Number = dt.Rows[i]["Number"].ToString();
                user.UClass = dt.Rows[i]["UClass"].ToString();

                ulist.Add(user);

            }

            return ulist;

        }


    }
}
```

👈{{preArticle.title}}

👉{{nextArticle.title}}

评价

素衣清颜淡若尘

旧年素颜,君记否

博主信息

排名

文章

粉丝

文章类别

随笔 15篇

前端 15篇

.NET 4篇

小程序 2篇

Python 2篇

C++ 2篇

数据库 9篇

PHP 1篇

后端 1篇

移动开发 1篇

ado.net基本使用,sqlhelper简单封装,dbhelper简单使用

{{titleitem}} {{titleitem}}

{{titleitem}} {{titleitem}}