Ado.Net 数据库访问技术(.Net 6版本)

SqlConnection connection =
new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21;timeout=30;");
connection.Open();
Console.WriteLine($"ConnectionTimeout={connection.ConnectionTimeout}");
connection.Close();

System.Windows.Controls.TextBox txtUserId = new System.Windows.Controls.TextBox();  
System.Windows.Controls.PasswordBox txtPwd = new System.Windows.Controls.PasswordBox();  
​
​
SqlConnection conn = new SqlConnection("server=.;database=unit21;");
SecureString pwd = txtPwd.SecurePassword;  
pwd.MakeReadOnly();  
SqlCredential cred = new SqlCredential(txtUserId.Text, pwd);  
conn.Credential = cred;  
conn.Open();  
// ...
conn.close();

/* *
 * using : 引用命名空间
 * using: 释放资源
 *
 * 1. 为什么以前我们定义的变量不需要释放资源呢？
 * GC: 垃圾回收器，这里面封装了一些算法规定了什么时候去回收资源。我们自己定义一些变量，不需要关心它有没有释放掉，因为GC会帮我们自动回收资源
 *
 * 2. 既然已经有了GC,为什么还需要手动的释放资源呢？
 * 答：GC 只能回收托管资源（由.Net CLR管理的资源），而SqlConnection是属于非托管资源
 *
 * 3. 我怎么知道它是非托管？
 * 答：如果它实现了IDisposable 接口，那么我们就可以使用using 来手动的释放资源了
 */
​
using (SqlConnection connection = new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21;timeout=30"))
{
    connection.Open();// 打开连接
    Console.WriteLine($"State={connection.State}");
    Console.WriteLine($"DataBase={connection.Database}");
    Console.WriteLine($"ServerVersion={connection.ServerVersion}");
    Console.WriteLine($"DataSource={connection.DataSource}");
    Console.WriteLine($"ConnectionTimeout={connection.ConnectionTimeout}");
} // 并未调用close()方法，但会自动关闭

using SqlConnection connection = new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21;timeout=30");
connection.Open();// 打开连接
Console.WriteLine($"State={connection.State}");
Console.WriteLine($"DataBase={connection.Database}");
Console.WriteLine($"ServerVersion={connection.ServerVersion}");
Console.WriteLine($"DataSource={connection.DataSource}");
Console.WriteLine($"ConnectionTimeout={connection.ConnectionTimeout}");

using SqlConnection connection = new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21;timeout=30");
connection.Open();
using SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandText = $"insert into product values('枸杞',18)";
cmd.ExecuteNonQuery();// 执行

using SqlConnection connection = new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21;timeout=30");
connection.Open();
using SqlCommand cmd = new SqlCommand("select count(id) from product");
cmd.Connection = connection;
object count = cmd.ExecuteScalar();// 获取首行首列

using SqlConnection connection = new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21;timeout=30");
connection.Open();
using SqlCommand cmd = new SqlCommand("select * from product",connection);
using SqlDataReader dataReader = cmd.ExecuteReader(); // 返回一个读取对象

using SqlConnection connection = new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21;timeout=30");
connection.Open();
using SqlCommand cmd = new SqlCommand("select * from product",connection);
using SqlDataReader dataReader = cmd.ExecuteReader();
​
while (dataReader.Read())
{
    object id = dataReader.GetValue("id");
    object pname = dataReader.GetValue("pname");
    object price = dataReader.GetValue("price");
​
    Console.WriteLine($"id: {id}\t pname: {pname}\t price: {price}\t \n");
}

using SqlConnection connection = new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21;timeout=30");
connection.Open();
using SqlCommand cmd = new SqlCommand("select * from product",connection);
using SqlDataReader dataReader = cmd.ExecuteReader();
​
while (dataReader.Read())
{
    // 索引下标顺序必须与数据库列顺序一致
    object id = dataReader[0];
    object pname = dataReader[1];
    object price = dataReader[2];
​
    Console.WriteLine($"id: {id}\t pname: {pname}\t price: {price}\t \n");
}

using SqlConnection connection = new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21;timeout=30");
connection.Open();
using SqlCommand cmd = new SqlCommand("select * from product",connection);
using SqlDataReader dataReader = cmd.ExecuteReader();
​
while (dataReader.Read())
{
    // 索引下标顺序必须与数据库列顺序一致
    Product product = new Product(
        dataReader.GetInt32(0),
        dataReader.GetString(1),
        dataReader.GetInt32(2)
    );
​
    Console.WriteLine($"id: {product.Id}\t pname: {product.Name}\t price: {product.Price}\t \n");
}
​
record Product(int Id,string Name,int Price);

using SqlConnection connection =
    new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21");
​
using SqlDataAdapter adapter = new SqlDataAdapter();
using SqlCommand cmd = new SqlCommand("select * from product");
cmd.Connection = connection;
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();
adapter.Fill(ds); // 填充数据
Console.WriteLine(ds.Tables[0].Rows.Count);

using SqlConnection connection =
    new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21");
using SqlCommand cmd = new SqlCommand("select * from product",connection);
using SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
Console.WriteLine(ds.Tables[0].Rows.Count);

using SqlConnection connection =
    new SqlConnection("server=.;uid=sa;pwd=123456;database=unit21");
using SqlDataAdapter adapter = new SqlDataAdapter("select * from product",connection);
DataSet ds = new DataSet();
adapter.Fill(ds);
Console.WriteLine(ds.Tables[0].Rows.Count);

using SqlDataAdapter adapter2 = new SqlDataAdapter("select * from product",
    "server=.;uid=sa;pwd=123456;database=unit21");
DataSet ds = new DataSet();
adapter2.Fill(ds);
Console.WriteLine(ds.Tables[0].Rows.Count);

/**
 * 为什么要使用List
 * 1. List 更符合面向对象编程思想
 * 2. row 在操作列名的时候，有可能会将列名写错
 * 3. List集合操作速度要比DataTable要快，并且属于类型安全（C#高级中会有专门一个单元，给大家讲解泛型）
 */
using SqlDataAdapter adapter2 = new SqlDataAdapter("select * from product",
    "server=.;uid=sa;pwd=123456;database=unit21");
DataTable dataTable = new DataTable();
adapter2.Fill(dataTable);
​
List<Product> list = new ();
foreach (DataRow row in dataTable.Rows)
{
    Product product = new();
    product.Id = Convert.ToInt32(row["id"]);
    if (row["pname"] is not DBNull) // 判断非空
    {
        product.ProductName = row["pname"].ToString();
    }
     // 数字类型的一定要记得做判断
    if (row["price"] is not DBNull)
    {
        product.Price = Convert.ToDecimal(row["price"]);
    }
    list.Add(product);
}
Console.WriteLine(list.Count);
​
​
public class Product
{
    public int Id { get; set; }
    public string? ProductName { get; set; }
    public decimal Price { get; set; }
}

DataTable dataTable = new DataTable("商品表");
​
// 创建表头
dataTable.Columns.Add("id");
dataTable.Columns.Add("productname");
DataColumn column = new DataColumn("price");
dataTable.Columns.Add(column);
​
//创建行
var dataRow = dataTable.NewRow();
dataRow["id"] = 1;
dataRow["productname"]="口红";
dataRow["price"] = 269;
​
// 添加到DataTable中
dataTable.Rows.Add(dataRow);

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add key="siteName" value="任我行码农场"/>
    </appSettings>
</configuration>

var siteName = ConfigurationManager.AppSettings["siteName"].ToString();
Console.WriteLine(siteName);

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
   <connectionStrings>
      <add name="connString" connectionString="server=.;uid=sa;pwd=123456;database=unit21;pooling=false;"></add>
   </connectionStrings>
</configuration>

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
​
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
// 读取配置文件
var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
for (int i = 0; i < 10000; i++)
{
     using SqlConnection connection = new SqlConnection(connectionString);
     connection.Open();
}
​
stopwatch.Stop();
Console.WriteLine("总耗时(s):"+stopwatch.ElapsedMilliseconds/1000.0);
​
​
// 输出结果：
总耗时(s):11.897

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
   <connectionStrings>
      <add name="connString" connectionString="server=.;uid=sa;pwd=123456;database=unit21;pooling=true;"></add>
   </connectionStrings>
</configuration>

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
​
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
// 读取配置文件
var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
for (int i = 0; i < 10000; i++)
{
     using SqlConnection connection = new SqlConnection(connectionString);
     connection.Open();
}
​
stopwatch.Stop();
Console.WriteLine("总耗时(s):"+stopwatch.ElapsedMilliseconds/1000.0);
​
​
// 输出结果：
总耗时(s):0.28

create table Users
(
    Id int primary key identity,
    NickName varchar(20),
    Account varchar(20),
    Pwd varchar(20)
);
​
insert into Users values('张三','zhangsan','123');
insert into Users values('李四','lisi','123');
insert into Users values('王五','wangwu','123');
insert into Users values('任我行','renwoxing','123');
insert into Users values('关莹莹','guanyingying','123');

select * from Users where Account='admin' and Pwd = '123'

select * from Users where Account='admin' and Pwd = '' or 1='1'

select count(*) from Users where Account=@account and Pwd = @pwd

// 读取配置文件
var connectionString = ConfigurationManager.ConnectionStrings["localString"].ConnectionString;
​
using SqlConnection conn = new(connectionString);
conn.Open();
// 一定要记得加@符号
using SqlCommand cmd = new SqlCommand("select count(*) from Users where Account=@account and Pwd = @pwd",conn);
​
SqlParameter parameter1 = new SqlParameter();
parameter1.ParameterName = "account"; // 参数名,要与 上面的sql语句中的参数名要对应上，@符号可省略
parameter1.Size = 20; // 参数大小
parameter1.SqlDbType = SqlDbType.VarChar; // 类型
parameter1.Value = "zhangsan"; // 参数值
​
SqlParameter parameter2 = new SqlParameter();
parameter2.ParameterName = "pwd"; // 参数名,要与 上面的sql语句中的参数名要对应上，@符号可省略
parameter2.Size = 20; // 参数大小
parameter2.SqlDbType = SqlDbType.VarChar; // 类型
parameter2.Value = "123"; // 参数值
​
​
// 将参数添加至命令对象中
cmd.Parameters.Add(parameter1);
cmd.Parameters.Add(parameter2);
​
// 执行sql命令
var count = Convert.ToInt32(cmd.ExecuteScalar());
if (count > 0)
{
    Console.WriteLine("登录成功");
}
else
{
    Console.WriteLine("登录失败，用户名或者密码输入不正确");
}

var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
​
using SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
using SqlCommand cmd = new SqlCommand("insert into Users values(@nickName,@account,@pwd);",conn);
​
// 创建参数数组对象
SqlParameter[] parameters =
{
    new SqlParameter("nickName","李重文"),
    new SqlParameter("account","lichongwen"),
    new SqlParameter("pwd","123")
};
// 批量添加 参数对象数组 至 命令对象中
cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();

var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
using SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
using SqlCommand cmd = new SqlCommand("update Users set Account=@account,NickName=@nickName,Pwd=@pwd where Id=@id",conn);
​
// 创建参数数组对象
SqlParameter[] parameters =
{
    new SqlParameter("nickName",SqlDbType.VarChar,20),
    new SqlParameter("account",SqlDbType.VarChar,20),
    new SqlParameter("pwd",SqlDbType.VarChar,20),
    new SqlParameter("id",SqlDbType.Int,4)
};
parameters[0].Value = "张三丰";
parameters[1].Value = "zhangsanfeng";
parameters[2].Value = "123456";
parameters[3].Value = 1;
​
// 批量添加 参数对象数组 至 命令对象中
cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();

var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
// 创建参数数组对象
SqlParameter[] parameters =
{
    new SqlParameter("nickName",SqlDbType.VarChar),
    new SqlParameter("pwd",SqlDbType.VarChar)
};
Console.WriteLine("请输入需要查询的姓名：");
string? nickName = Console.ReadLine();
parameters[0].Value = $"%{nickName}%"; // 模糊查询要加%
parameters[1].Value = "123456";
​
SqlDataAdapter adapter = new("select * from Users where NickName like @nickName and Pwd=@pwd",connectionString);
// 批量添加参数对象
adapter.SelectCommand.Parameters.AddRange(parameters);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
​
Console.WriteLine("\nId\t姓名\t账号 \t \t密码");
Console.WriteLine("------------------------------------------------------");
foreach (DataRow row in dataTable.Rows)
{
    Console.WriteLine($"{row["id"]}\t{row["nickname"]}\t{row["account"]}\t{row["pwd"]}");
}

var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
​
using SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
using SqlCommand cmd = new SqlCommand("delete from Users where id=@id;",conn);
​
// 指添加 参数对象 命令对象中
cmd.Parameters.Add(new SqlParameter("id",1));
cmd.ExecuteNonQuery();

delete from Users where id in(1,2)

var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
​
using SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
using SqlCommand cmd = new SqlCommand("delete from Users where id=@id;",conn);
​
// 需要删除的数据
int[] idList = {1, 2, 3};
foreach (var id in idList)
{
    // 指添加 参数对象数组 至 命令对象中
    cmd.Parameters.Add(new SqlParameter("id",id));
    cmd.ExecuteNonQuery();
    cmd.Parameters.Clear(); // 必须清除掉之前的参数
}

var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
​
using SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
using SqlTransaction tran = conn.BeginTransaction(); // 开启事务
// 命令对象指定事务对象
using SqlCommand cmd = new SqlCommand("delete from Users where id=@id;",conn,tran);
​
try
{
    // 需要删除的数据
    int[] idList = {4, 5, 6};
    foreach (var id in idList)
    {
        // 指添加 参数对象数组 至 命令对象中
        cmd.Parameters.Add(new SqlParameter("id",id));
        cmd.ExecuteNonQuery();
        cmd.Parameters.Clear(); // 必须清除掉之前的参数
    }
    tran.Commit();// 提交事务
}
catch (Exception e)
{
    Console.WriteLine(e.Message);
    tran.Rollback(); // 数据回滚
}

create proc p_users_insert
(
    @nickName varchar(20),
    @account varchar(20),
    @pwd varchar(20)
) as
begin
    insert into Users values(@nickName,@account,@pwd)
end

var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
​
using SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
// 这里要写存储过程的名称
using SqlCommand cmd = new SqlCommand("p_users_insert", conn);
cmd.CommandType = CommandType.StoredProcedure;// 指定为存储过程类型
SqlParameter[] parameters =
{
    new SqlParameter("nickName","李重文"),
    new SqlParameter("account","lichongwen"),
    new SqlParameter("pwd","123")
};
// 批量添加 参数对象数组 至 命令对象中
cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();

alter proc p_users_insert
(
    @nickName varchar(20),
    @account varchar(20),
    @pwd varchar(20),
    @code int output,
    @msg varchar(20) output -- 输出参数
) as
begin
    insert into Users values(@nickName,@account,@pwd);
    set @msg = '添加成功';
    set @code=1;
end

var connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
​
using SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
// 这里要写存储过程的名称
using SqlCommand cmd = new SqlCommand("p_users_insert", conn);
cmd.CommandType = CommandType.StoredProcedure;// 指定为存储过程类型
SqlParameter[] parameters =
{
    new SqlParameter("nickName","孙婷婷"),
    new SqlParameter("account","suntingting"),
    new SqlParameter("pwd","123"),
    new SqlParameter("code",SqlDbType.Int,4), // 不给输出参数设置值
    new SqlParameter("msg",SqlDbType.VarChar,20)
};
parameters[3].Direction = ParameterDirection.Output; // 第4个参数设置为输出参数
parameters[4].Direction = ParameterDirection.Output; // 第5个参数设置为输出参数
​
// 批量添加 参数对象数组 至 命令对象中
cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();
Console.WriteLine("code的值为："+ parameters[3].Value); // 获取输出参数返回的值
Console.WriteLine("msg的值为："+ parameters[4].Value);

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    // 连接字符串
    "SqlServer": "server=.;uid=sa;pwd=123456;database=unit21;pooling=true;"
  }
}

public class HomeController : Controller
{
    // 注入配置对象（系统在启动时，已经在IOC容器中注册好了）
    private readonly IConfiguration _configuration;
​
    public HomeController(IConfiguration configuration)
    {
        _configuration = configuration;
    }
​
    public IActionResult Index()
    {
        // 读取连接字符串
        string connString = _configuration.GetConnectionString("SqlServer");
        return View();
    }   
}

public class HomeController : Controller
{
    // 注入配置对象（系统在启动时，已经在IOC容器中注册好了）
    private readonly IConfiguration _configuration;
​
    public HomeController(IConfiguration configuration)
    {
        _configuration = configuration;
    }
​
    public IActionResult Index()
    {
        // 读取连接字符串
        string connString = _configuration.GetConnectionString("SqlServer");
        // 创建参数数组对象
        SqlParameter[] parameters =
        {
            new SqlParameter("nickName",SqlDbType.VarChar),
            new SqlParameter("pwd",SqlDbType.VarChar)
        };
        Console.WriteLine("请输入需要查询的姓名：");
        string? nickName = Console.ReadLine();
        parameters[0].Value = $"%{nickName}%"; // 模糊查询要加%
        parameters[1].Value = "123456";
​
        SqlDataAdapter adapter = new("select * from Users where NickName like @nickName and Pwd=@pwd",connString);
// 批量添加参数对象
        adapter.SelectCommand.Parameters.AddRange(parameters);
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
​
        Console.WriteLine("\nId\t姓名\t账号 \t \t密码");
        Console.WriteLine("------------------------------------------------------");
        foreach (DataRow row in dataTable.Rows)
        {
            Console.WriteLine($"{row["id"]}\t{row["nickname"]}\t{row["account"]}\t{row["pwd"]}");
        }
        return View();
    }
​
   
}

using System.Data;
using System.Data.SqlClient;
using System.Reflection;

namespace WebApplication1;

public class DbHelper
{
    private static string? _connString;

    // 静态构造方法,
    // 第一次访问 这个的类的时候，会执行一次静态构造

    static DbHelper()
    {
        ConfigurationBuilder configuration = new ConfigurationBuilder(); //读取配置文件
        var config = configuration.SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile(file =>
        {
            file.Path = "/appsettings.json";
            file.Optional = false;
            file.ReloadOnChange = true;
        }).Build();
        _connString = config.GetConnectionString("SqlServer");
    }

    private static SqlCommand PrepareCommand(SqlConnection conn, string sql, CommandType cmdType,
        params SqlParameter[]? parameters)
    {
        using SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.CommandType = cmdType;
        if (parameters != null && parameters.Length > 0)
        {
            cmd.Parameters.AddRange(parameters);
        }

        return cmd;
    }


    /**
     * 执行增删改的操作
     */
    
    public static int ExecuteNonQuery(string sql,params SqlParameter[]? parameters)
    {
        using SqlConnection conn = new(_connString);
        conn.Open();
        return PrepareCommand(conn, sql, CommandType.Text, parameters).ExecuteNonQuery();
    }
    
    public static int ExecuteNonQuery(string sql, CommandType cmdType = CommandType.Text,
        params SqlParameter[]? parameters)
    {
        using SqlConnection conn = new(_connString);
        conn.Open();
        return PrepareCommand(conn, sql, cmdType, parameters).ExecuteNonQuery();
    }

    public static int ExecuteScalar(string sql,params SqlParameter[]? parameters)
    {
        using SqlConnection conn = new(_connString);
        conn.Open();
        return Convert.ToInt32(PrepareCommand(conn, sql, CommandType.Text, parameters).ExecuteScalar());
    }
    
    public static int ExecuteScalar(string sql, CommandType cmdType = CommandType.Text,
        params SqlParameter[]? parameters)
    {
        using SqlConnection conn = new(_connString);
        conn.Open();
        return Convert.ToInt32(PrepareCommand(conn, sql, cmdType, parameters).ExecuteScalar());
    }

    public static List<T> GetList<T>(string sql, CommandType cmdType = CommandType.Text,
        params SqlParameter[]? parameters)  where T : class, new()
    {
        using SqlDataAdapter adapter = new(PrepareCommand(
            new (_connString),sql, cmdType, parameters));

        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        return ToList<T>(dataTable);
    }
    
    public static List<T> GetList<T>(string sql, params SqlParameter[]? parameters)  where T : class, new()
    {
        using SqlDataAdapter adapter = new(PrepareCommand(
            new (_connString),sql, CommandType.Text, parameters));

        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        return ToList<T>(dataTable);
    }

    private static List<T> ToList<T>(DataTable dt) where T : class, new()
    {
        Type t = typeof(T);
        PropertyInfo[] propertys = t.GetProperties();
        List<T> lst = new List<T>();
        string typeName = string.Empty;

        foreach (DataRow dr in dt.Rows)
        {
            T entity = new T();
            foreach (PropertyInfo pi in propertys)
            {
                typeName = pi.Name;
                if (dt.Columns.Contains(typeName))
                {
                    if (!pi.CanWrite) continue;
                    object value = dr[typeName];
                    if (value == DBNull.Value) continue;
                    if (pi.PropertyType == typeof(string))
                    {
                        pi.SetValue(entity, value.ToString(), null);
                    }
                    else if (pi.PropertyType == typeof(int) || pi.PropertyType == typeof(int?))
                    {
                        pi.SetValue(entity, int.Parse(value.ToString()), null);
                    }
                    else if (pi.PropertyType == typeof(DateTime?) || pi.PropertyType == typeof(DateTime))
                    {
                        pi.SetValue(entity, DateTime.Parse(value.ToString()), null);
                    }
                    else if (pi.PropertyType == typeof(float))
                    {
                        pi.SetValue(entity, float.Parse(value.ToString()), null);
                    }
                    else if (pi.PropertyType == typeof(double))
                    {
                        pi.SetValue(entity, double.Parse(value.ToString()), null);
                    }
                    else
                    {
                        pi.SetValue(entity, value, null);
                    }
                }
            }

            lst.Add(entity);
        }

        return lst;
    }
}