dapper给存储过程传《用户自定义表类型》table
dapper给存储过程传 用户自定义表类型table
dapper给存储过程传 table
存储过程 InsertUserInfo ，该存储过程接受《用户定义的表类型》 Type_UserType 的列表。
CREATE PROCEDURE dbo.InsertUserInfo
@userType AS Type_UserType READONLY
AS
BEGIN
-- RETURN COUNT OF INSERTED ROWS
END
《用户定义的表类型》 Type_UserType表
CREATE TYPE dbo.Type_UserType
AS TABLE
(
id int null,
name varchar(800) not null,
);
从Dapper调用此存储过程。
using (var conn = SqlHelper.SqlConnectionTService())//打开数据库方式交给框架执行
{
DataTable dt = new DataTable();
//给dt赋值的代码
//参数
var filters = new { userType = dt};
var res = conn.Query<string>("InsertUserInfo ", filters, commandType: CommandType.StoredProcedure).ToList();//sql 存储过程
}
你可能还需要
c# Dapper,添加,修改,删除,查询,事务,存储过程,含数据库(从入门到高级)_橙cplvfx-技术踩坑记-CSDN博客c# Dapper高级编程；添加方法3个,修改方法2个,删除方法2个,
model数据库实体类转DataTable类型
Conversion<DBUserInfoModel> helper = new Conversion<DBUserInfoModel>();
List<DBUserInfoModel> Modellist = new List<DBUserInfoModel>();
Modellist.Add("你的数据模型实体变量参数");
DataTable dt = helper.FillDataTable(Modellist);
常用数据格式转换帮助类
/// <summary>
/// 常用数据格式转换帮助类
/// </summary>
public class Conversion<T> where T : new()
{
#region DataTable转换成实体类
/// <summary>
/// 填充对象列表：用DataSet的第一个表填充实体类
/// </summary>
/// <param name="ds">DataSet</param>
/// <returns></returns>
public List<T> FillModel(DataSet ds)
{
if (ds == null || ds.Tables[0] == null || ds.Tables[0].Rows.Count == 0)
{
return null;
}
else
{
return FillModel(ds.Tables[0]);
}
}
/// <summary>
/// 填充对象列表：用DataSet的第index个表填充实体类
/// </summary>
public List<T> FillModel(DataSet ds, int index)
{
if (ds == null || ds.Tables.Count <= index || ds.Tables[index].Rows.Count == 0)
{
return null;
}
else
{
return FillModel(ds.Tables[index]);
}
}
/// <summary>
/// 填充对象列表：用DataTable填充实体类
/// </summary>
public List<T> FillModel(DataTable dt)
{
if (dt == null || dt.Rows.Count == 0)
{
return null;
}
List<T> modelList = new List<T>();
foreach (DataRow dr in dt.Rows)
{
//T model = (T)Activator.CreateInstance(typeof(T));
T model = new T();
for (int i = 0; i < dr.Table.Columns.Count; i++)
{
PropertyInfo propertyInfo = model.GetType().GetProperty(dr.Table.Columns[i].ColumnName);
if (propertyInfo != null && dr[i] != DBNull.Value)
propertyInfo.SetValue(model, dr[i], null);
}
modelList.Add(model);
}
return modelList;
}
/// <summary>
/// 填充对象：用DataRow填充实体类
/// </summary>
public T FillModel(DataRow dr)
{
if (dr == null)
{
return default(T);
}
//T model = (T)Activator.CreateInstance(typeof(T));
T model = new T();
for (int i = 0; i < dr.Table.Columns.Count; i++)
{
PropertyInfo propertyInfo = model.GetType().GetProperty(dr.Table.Columns[i].ColumnName);
if (propertyInfo != null && dr[i] != DBNull.Value)
propertyInfo.SetValue(model, dr[i], null);
}
return model;
}
#endregion
#region 实体类转换成DataTable
/// <summary>
/// 实体类转换成DataSet
/// </summary>
/// <param name="modelList">实体类列表</param>
/// <returns></returns>
public DataSet FillDataSet(List<T> modelList)
{
if (modelList == null || modelList.Count == 0)
{
return null;
}
else
{
DataSet ds = new DataSet();
ds.Tables.Add(FillDataTable(modelList));
return ds;
}
}
/// <summary>
/// 实体类转换成DataTable
/// </summary>
/// <param name="modelList">实体类列表</param>
/// <returns></returns>
public DataTable FillDataTable(List<T> modelList)
{
if (modelList == null || modelList.Count == 0)
{
return null;
}
DataTable dt = CreateData(modelList[0]);
foreach (T model in modelList)
{
DataRow dataRow = dt.NewRow();
foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
{
dataRow[propertyInfo.Name] = propertyInfo.GetValue(model, null);
}
dt.Rows.Add(dataRow);
}
return dt;
}
/// <summary>
/// 根据实体类得到表结构
/// </summary>
/// <param name="model">实体类</param>
/// <returns></returns>
private DataTable CreateData(T model)
{
DataTable dataTable = new DataTable(typeof(T).Name);
foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
{
//dataTable.Columns.Add(new DataColumn(propertyInfo.Name, propertyInfo.PropertyType));
dataTable.Columns.Add(new DataColumn(propertyInfo.Name));
}
return dataTable;
}
#endregion
#region dataTable转换成Json格式
/// <summary>
/// dataTable转换成Json格式
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static string DataTable2Json(DataTable dt)
{
StringBuilder jsonBuilder = new StringBuilder();
//jsonBuilder.Append("{\"");
//jsonBuilder.Append(dt.TableName);
//jsonBuilder.Append("\":[");
jsonBuilder.Append("[");
for (int i = 0; i < dt.Rows.Count; i++)
{
jsonBuilder.Append("{");
for (int j = 0; j < dt.Columns.Count; j++)
{
jsonBuilder.Append("\"");
jsonBuilder.Append(dt.Columns[j].ColumnName);
jsonBuilder.Append("\":\"");
jsonBuilder.Append(dt.Rows[i][j].ToString());
jsonBuilder.Append("\",");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("},");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("]");
//jsonBuilder.Append("}");
return jsonBuilder.ToString();
}
#endregion
#region Json转实体
/// <summary>
/// 把JSON字符串还原为对象
/// </summary>
/// <typeparam name="T">对象类型</typeparam>
/// <param name="szJson">JSON字符串</param>
/// <returns>对象实体</returns>
public static T ParseFormJson<T>(string szJson)
{
T obj = Activator.CreateInstance<T>();
using (MemoryStream ms = new MemoryStream(Encoding.UTF8.GetBytes(szJson)))
{
DataContractJsonSerializer dcj = new DataContractJsonSerializer(typeof(T));
obj = (T)dcj.ReadObject(ms);
return obj;
}
}
/// <summary>
/// 把JSON字符串反序列化为对象-----------[这个比较好用]
/// </summary>
/// <typeparam name="T">实体类对象</typeparam>
/// <param name="szJson">json字符串</param>
/// <returns></returns>
public static T JsonToModel<T>(string szJson)
{
T model = JsonConvert.DeserializeObject<T>(szJson);//反序列化
return model;
}
public static T Deserialize<T>(string json)
{
T obj = Activator.CreateInstance<T>();
using (MemoryStream ms = new MemoryStream(Encoding.UTF8.GetBytes(json)))
{
DataContractJsonSerializer serializer = new DataContractJsonSerializer(obj.GetType());
return (T)serializer.ReadObject(ms);
}
}
#endregion Json转实体
#region 实体类对象转Json
/// <summary>
/// 把对象序列化 JSON 字符串
/// </summary>
/// <typeparam name="T">对象类型</typeparam>
/// <param name="obj">对象实体</param>
/// <returns>JSON字符串</returns>
public static string GetJson<T>(T obj)
{
//记住 添加引用 System.ServiceModel.Web
/**
* 如果不添加上面的引用,System.Runtime.Serialization.Json; Json是出不来的哦
* */
DataContractJsonSerializer json = new DataContractJsonSerializer(typeof(T));
using (MemoryStream ms = new MemoryStream())
{
json.WriteObject(ms, obj);
string szJson = Encoding.UTF8.GetString(ms.ToArray());
return szJson;
}
}
#endregion
}
引入的命名空间
using Newtonsoft.Json;标签：model,存储,return,C#,jsonBuilder,new,NET,null,dt From： https://blog.51cto.com/cplvfx/5928920
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Runtime.Serialization.Json;