Можно использовать когда лень мэпить через ORM. Это всё же лучше, чем через обычные ADO.NET команды работать. Автомэппинг для Select IDataReader точно можно оставить.
Если что то не будет работать то надо замэпить в самом начале запуска приложения. Примерно так
//AutoMapper.Mapper.Reset();
//AutoMapper.Mapper.CreateMap<IDataReader, SubscribeReportLib.Models.QueryCash>();
Если ошибка с System.Core то надо поставить обновление нэт фрэймворк NDP40-KB2468871-v2-x64
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using AutoMapper;
namespace ADO_AutoMapping
{
class Program
{
static void Main(string[] args)
{
MSSQL_Helper _helper = new MSSQL_Helper();
var lst = _helper.ReadData<MarkType>("SELECT [id],[name],[inserted] FROM [marktype]");
MarkType o = new MarkType() { id=1, inserted=DateTime.Now, name="as99933"};
_helper.Insert<MarkType>(o, "id");
_helper.Update<MarkType>(o,"id", new List<MSSQL_Helper.FieldVal>()
{
new MSSQL_Helper.FieldVal
{
name="id",val=1
}
});
_helper.Delete<MarkType>(new List<MSSQL_Helper.FieldVal>()
{
new MSSQL_Helper.FieldVal
{
name="id",val=12
}
});
}
}
public class MarkType
{
public int id { get; set; }
public string name { get; set; }
public DateTime inserted { get; set; }
}
class MSSQL_Helper
{
public struct FieldVal
{
public object val { get; set; }
public string name { get; set; }
}
string constr;
public MSSQL_Helper()
{
constr = "Data Source=localhost\\sqlexpress;Initial Catalog=MyDataBase;User ID=myuser;Password=mypass";
}
public MSSQL_Helper(string _constr)
{
constr = _constr;
}
public void SetConnectionString(string _constr)
{
constr = _constr;
}
public object ReadData<T>(string queryString)
{
using (var connection =
new SqlConnection(constr))
using (var command =
new SqlCommand(queryString, connection))
{
connection.Open();
using (var reader = command.ExecuteReader())
if (reader.HasRows)
return Mapper.DynamicMap<IDataReader, IEnumerable<T>>(reader);
}
return null;
}
public void Insert<T>(object o, string AutoIncFiled)
{
var lst = typeof(T).GetProperties().ToList();
string param = "";
string fields = "";
var paramArray = new SqlParameter[lst.Count - 1];
int i = 0;
foreach (var prop in lst)
if (prop.Name != AutoIncFiled)
{
param += "@" + prop.Name + ",";
fields += prop.Name + ",";
paramArray[i++] = new SqlParameter("@" + prop.Name, prop.GetValue(o, null)?? DBNull.Value);
}
ExecuteNonQuery(String.Format("INSERT INTO {0}({1}) VALUES ({2})", typeof(T).Name, fields.Remove(fields.Length - 1), param.Remove(param.Length - 1)), paramArray);
}
public void Update<T>(object o, string AutoIncFiled, List<FieldVal> WhereFieldVal)
{
var lst = typeof(T).GetProperties().ToList();
string wherestring = "WHERE ";
string param = "";
var paramArray = new SqlParameter[lst.Count - 1 + (WhereFieldVal==null?0:WhereFieldVal.Count)];
int i = 0;
foreach (var prop in lst)
if (prop.Name != AutoIncFiled)
{
param += prop.Name+"=@" + prop.Name + ",";
paramArray[i++] = new SqlParameter("@" + prop.Name, prop.GetValue(o, null)?? DBNull.Value);
}
foreach (FieldVal fv in WhereFieldVal)
{
wherestring += fv.name + "=@" + fv.name + ",";
paramArray[i++] = new SqlParameter("@" + fv.name, fv.val);
}
ExecuteNonQuery(String.Format("UPDATE {0} SET {1} {2}", typeof(T).Name, param.Remove(param.Length - 1), wherestring.Remove(wherestring.Length - 1)), paramArray);
}
public void Delete<T>(List<FieldVal> WhereFieldVal)
{
string wherestring = "WHERE ";
var paramArray = new SqlParameter[WhereFieldVal.Count];
int i = 0;
foreach (FieldVal fv in WhereFieldVal)
{
wherestring += fv.name + "=@" + fv.name + ",";
paramArray[i++] = new SqlParameter("@" + fv.name, fv.val);
}
ExecuteNonQuery(String.Format("DELETE FROM {0} {1}", typeof(T).Name, wherestring.Remove(wherestring.Length - 1)), paramArray);
}
private void ExecuteNonQuery(string queryString,SqlParameter[] paramArray)
{
using (SqlConnection connection =
new SqlConnection(constr))
using (SqlCommand command =
new SqlCommand(queryString, connection))
{
connection.Open();
command.Parameters.AddRange(paramArray);
command.ExecuteNonQuery();
}
}
public void ExecuteNonQuery(string queryString)
{
using (SqlConnection connection =
new SqlConnection(constr))
using (SqlCommand command =
new SqlCommand(queryString, connection))
{
connection.Open();
command.ExecuteNonQuery();
}
}
}
}
Additional two methods
public List<T> Select<T>(List<FieldVal> WhereFieldVal, string DBName = "")
{
var lst = typeof(T).GetProperties().ToList();
string wherestring = "WHERE ";
string param = "";
var paramArray = new SqlParameter[lst.Count - 1 + (WhereFieldVal == null ? 0 : WhereFieldVal.Count)];
int i = 0;
foreach (var prop in lst)
param += prop.Name + ",";
foreach (FieldVal fv in WhereFieldVal)
{
wherestring += fv.name + "=@" + fv.name + ",";
paramArray[i++] = new SqlParameter("@" + fv.name, fv.val);
}
string _sql = String.Format("SELECT {1} FROM {0} {2}", DBName == "" ? typeof(T).Name : DBName, param.Remove(param.Length - 1), wherestring.Remove(wherestring.Length - 1));
return ReadData<T>(_sql);
}
public List<T> Select<T>(string WhereFieldVal, string DBName = "")
{
var lst = typeof(T).GetProperties().ToList();
string wherestring = "WHERE " + WhereFieldVal;
string param = "";
foreach (var prop in lst)
param += prop.Name + ",";
string _sql = String.Format("SELECT {1} FROM {0} {2}", DBName == "" ? typeof(T).Name : DBName, param.Remove(param.Length - 1), wherestring);
return ReadData<T>(_sql);
}
