简单的sql数据读取与显示（Dapper）

添加引用

using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;

public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings[“defaultConnection”].ToString();

    public static SqlConnection CreateSqlConnection()
    {
        var connection = new SqlConnection(ConnectionString);
        //connection.Open();
        return connection;
    }

public int ID { get; set; } //系统编号
public int SiteId { get; set; } //站点编号
public int Lane { get; set; } //车道序号
public DateTime PassTime { get; set; } //检测时间
public string PlateNum { get; set; } //车牌号
public string PlateColor { get; set; } //车牌颜色
public float CO2 { get; set; } //CO2值，单位%
public float CO { get; set; } //CO值，单位%
public float HC { get; set; } //HC值，单位ppm
public float NO { get; set; } //NO值，单位ppm
public float R_CO2 { get; set; } //CO2值，单位%
public float R_CO { get; set; } //CO值，单位%
public float R_HC { get; set; } //HC值，单位ppm
public float R_NO { get; set; } //NO值，单位ppm
public float Opacity { get; set; } //不透光度，单位%,
public float K { get; set; }
public float SF { get; set; }
public float VSP { get; set; } //机动车特征功率
public float WindSpeed { get; set; } //风速，单位m/s
public float WindDirection { get; set; } //风向，单位角度°
public float Temperature { get; set; } //温度，单位℃
public float Humidity { get; set; } //相对湿度,单位%
public float Pressure { get; set; } //气压，单位Kpa
public string Result { get; set; } //判定结果(合格、超标、无效)
public float CarLength { get; set; }//车长
public float Speed { get; set; }//车速
public float Acc { get; set; }//加速度
public int EntireBelieve { get; set; }//置信度
public byte[] Photo { get; set; }//图片数据
public string PhotoPath { get; set; }
public bool IsUploaded { get; set; }//数据是否已上传




添加类

using DataStorageSqlServer.Model;
using DataStorageSqlServer.IRepository;
添加继承接口 记得public

引用using Dapper;



public List GetResultsByTime(DateTime start, DateTime end, int pageSize, int currentPage, out int total)
{
using (var cnn = DapperFactory.CreateSqlConnection())
{
cnn.Open();
total = int.Parse(cnn.ExecuteScalar(“SELECT COUNT(*) from ExhaustTelemetry where PassTime between @Start and @End”, new { Start = start, End = end }).ToString());

            List<TelemetryUnionIdentify> result = cnn.Query<TelemetryUnionIdentify>
                (@"SELECT * from (SELECT ROW_NUMBER() OVER(Order by A.Passtime desc ) AS RowId,A.ID,A.SiteId,A.Lane,A.PassTime,A.PlateNum,A.PlateColor,A.CO2,A.CO,A.HC,A.[NO],A.R_CO2,A.R_CO,A.R_HC,A.R_NO,A.Opacity,A.K,A.SF,
                   A.VSP,A.WindSpeed,A.WindDirection,A.Temperature,A.Humidity,A.Pressure,A.Result,A.CarLength,A.Speed,
                   A.Acc,A.IsUploaded,B.EntireBelieve,B.Photo,B.PhotoPath FROM ExhaustTelemetry A left outer join VehicleIdentify B
                   on A.IdentifyID=B.ID WHERE A.PassTime between @Start and @End) x where RowId between @StartRowId and @EndRowId",
                    new { Start = start, End = end, PageSize = pageSize, StartRowId = (currentPage - 1) * pageSize + 1, EndRowId = currentPage * pageSize }) as List<TelemetryUnionIdentify>;
            cnn.Close();
            return result;
        }
    }

开始运行


需要将dll
添加到主运行上面




代码中
using DataStorageSqlServer.IRepository;
using DataStorageSqlServer.Model;
using DataStorageSqlServer.Repository;


可以应付各种程序了