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

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;
        }
    }
}