csharp:Dapper Sample
You can find Dapper on Google Code here: http://code.google.com/p/dapper-dot-net/ and the GitHub distro here: https://github.com/SamSaffron/dapper-dot-net.
https://github.com/zzzprojects/Dapper-Plus
https://github.com/tmsmith/Dapper-Extensions
.NET Core
https://www.microsoft.com/net/download/core
https://blogs.msdn.microsoft.com/dotnet/2016/05/16/announcing-net-core-rc2/
https://github.com/dotnet/core
https://github.com/aspnet/Tooling/blob/master/known-issues-vs2015.md#missing-sdk
http://dapper-tutorial.net/
https://github.com/zzzprojects/dapper-tutorial/releases
https://github.com/henkmollema/Dapper-FluentMap
http://www.cnblogs.com/starluck/p/4542363.html
http://blog.maskalik.com/asp-net/sqlite-simple-database-with-dapper/
https://github.com/mercury2269/SQLiteDemo
https://github.com/senjacob/dapper-dot-net
https://github.com/senjacob/StackExchange.Redis
tps://github.com/autofac
AutoMapper:
https://github.com/AutoMapper/AutoMapper
https://github.com/SlapperAutoMapper/Slapper.AutoMapper
https://github.com/nreco/data
https://archive.codeplex.com/?p=nlite
https://archive.codeplex.com/?p=emitmapper
http://dapper-plus.net/
https://github.com/MoonStorm/Dapper.FastCRUD
https://github.com/ericdc1/Dapper.SimpleCRUD
https://github.com/ryanwatson/Dapper.Extensions.Linq
http://www.cnblogs.com/netcasewqs/archive/2011/04/13/2014684.html
https://stackoverflow.com/questions/28046528/dapper-complex-mapping-dapper-extensions-dapper-fluentmap
https://stackoverflow.com/questions/38127880/dapper-insert-or-update
https://stackoverflow.com/questions/9518119/mapping-entity-in-dapper
https://stackoverflow.com/questions/44775320/automapper-vs-dapper-for-mapping
https://github.com/alexander-87/Dapper.FluentColumnMapping
https://github.com/henkmollema/Dapper-FluentMap
https://github.com/dotarj/Dapper.Mapper
///<summary> /// 追加记录 ///</summary> ///<param name="BookPlaceListInfo"></param> ///<returns></returns> public int InsertBookPlaceList(BookPlaceListInfo bookPlaceList) { int ret = 0; try { List<BookPlaceListInfo> list=new List<BookPlaceListInfo>(); list.Add(bookPlaceList); StringBuilder str = new StringBuilder(); str.Append("INSERT INTO BookPlaceList "); str.Append("([BookPlaceName] ,[BookPlaceCode] ,[BookPlaceParent]) VALUES "); str.Append("(@BookPlaceName ,@BookPlaceCode,@BookPlaceParent)"); ret=SqlMapperUtil.InsertMultiple<BookPlaceListInfo>(str.ToString(),list, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return ret; } /// <summary> /// 存储过程 /// 追加记录 /// </summary> /// <param name="bookPlaceList"></param> /// <returns></returns> public int InsertBookPlaceListProc(BookPlaceListInfo bookPlaceList) { int ret = 0; try { string strProc = "proc_Insert_BookPlaceList";//存储过程 var pamar = new { BookPlaceName = bookPlaceList.BookPlaceName, BookPlaceCode = bookPlaceList.BookPlaceCode, BookPlaceParent = bookPlaceList.BookPlaceParent }; ret = SqlMapperUtil.InsertUpdateOrDeleteStoredProc(strProc, pamar, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return ret; } /// <summary> /// 追加多条记录 /// </summary> /// <param name="bookPlaceList"></param> /// <returns></returns> public int InsertBookPlaceListMore(List<BookPlaceListInfo> bookPlaceList) { int ret = 0; try { StringBuilder str = new StringBuilder(); str.Append("INSERT INTO BookPlaceList "); str.Append("([BookPlaceName] ,[BookPlaceCode] ,BookPlaceParent]) VALUES "); str.Append("(@BookPlaceName ,@BookPlaceCode,@BookPlaceParent)"); ret = SqlMapperUtil.InsertMultiple<BookPlaceListInfo>(str.ToString(), bookPlaceList, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return ret; } ///<summary> ///修改记录 ///</summary> ///<param name="BookPlaceListInfo"></param> ///<returns></returns> public int UpdateBookPlaceList(BookPlaceListInfo bookPlaceList) { int ret = 0; try { List<BookPlaceListInfo> list = new List<BookPlaceListInfo>(); list.Add(bookPlaceList); StringBuilder str = new StringBuilder(); str.Append("UPDATE BookPlaceList SET "); str.Append("[BookPlaceName]=@BookPlaceName ,"); str.Append("[BookPlaceCode]=@BookPlaceCode,"); str.Append("[BookPlaceParent]=@BookPlaceParent"); str.Append(" where "); str.Append("[BookPlaceID]=@BookPlaceID"); ret = SqlMapperUtil.InsertMultiple<BookPlaceListInfo>(str.ToString(), list, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return ret; } /// <summary> /// 存储过程 /// </summary> /// <param name="bookPlaceList"></param> /// <returns></returns> public int UpdateBookPlaceListProc(BookPlaceListInfo bookPlaceList) { int ret = 0; try { string strProc = "proc_Update_BookPlaceList";//存储过程 var pamar = new { BookPlaceName = bookPlaceList.BookPlaceName, BookPlaceCode = bookPlaceList.BookPlaceCode, BookPlaceParent = bookPlaceList.BookPlaceParent, BookPlaceID=bookPlaceList.BookPlaceID }; ret = SqlMapperUtil.InsertUpdateOrDeleteStoredProc(strProc, pamar, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return ret; } ///<summary> /// 删除记录 ///</summary> ///<param name="bookPlaceIDInfo"></param> ///<returns></returns> public bool DeleteBookPlaceList(int bookPlaceID) { bool ret = false; try { int temp = 0; StringBuilder str = new StringBuilder(); str.Append("DELETE BookPlaceList WHERE BookPlaceID = @BookPlaceID"); temp = SqlMapperUtil.InsertUpdateOrDeleteSql(str.ToString(), new { BookPlaceID = bookPlaceID }, SqlHelper.ConnectionString); if (temp != 0) { ret = true; } } catch (SqlException ex) { throw ex; } return ret; } /// <summary> /// 存储过程 /// </summary> /// <param name="bookPlaceID"></param> /// <returns></returns> public bool DeleteBookPlaceListProc(int bookPlaceID) { bool ret = false; try { int temp = 0; string strProc = "proc_Delete_BookPlaceList";//存储过程 var pamar = new { BookPlaceID = bookPlaceID }; temp = SqlMapperUtil.InsertUpdateOrDeleteStoredProc(strProc, new { BookPlaceID = bookPlaceID }, SqlHelper.ConnectionString); if (temp != 0) { ret = true; } } catch (SqlException ex) { throw ex; } return ret; } ///<summary> /// 查询记录 ///</summary> ///<param name="bookPlaceIDInfo"></param> ///<returns></returns> public BookPlaceListInfo SelectBookPlaceList(int bookPlaceID) { BookPlaceListInfo bookPlaceList = null; try { StringBuilder str = new StringBuilder(); str.Append("SELECT * FROM BookPlaceList WHERE BookPlaceID = @BookPlaceID"); bookPlaceList = SqlMapperUtil.SqlWithParamsSingle<BookPlaceListInfo>(str.ToString(), new { BookPlaceID = bookPlaceID }, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return bookPlaceList; } /// <summary> /// 存储过程查询记录 /// 涂聚文 /// 20150726 /// </summary> /// <param name="bookPlaceID"></param> /// <returns></returns> public BookPlaceListInfo SelectBookPlaceListProc(int bookPlaceID) { BookPlaceListInfo bookPlaceList = null; try { string strProc = "proc_Select_BookPlaceList";//存储过程 bookPlaceList = SqlMapperUtil.StoredProcWithParamsSingle<BookPlaceListInfo>(strProc, new { BookPlaceID = bookPlaceID }, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return bookPlaceList; } ///<summary> /// 查询所有记录 ///</summary> ///<returns></returns> public List<BookPlaceListInfo> SelectBookPlaceListAll() { List<BookPlaceListInfo> list = new List<BookPlaceListInfo>(); try { string str = "SELECT * FROM BookPlaceList"; list = SqlMapperUtil.SqlWithParams<BookPlaceListInfo>(str, null, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return list; } /// <summary> /// 存储过程 /// Geovin Du /// 查询所有记录 /// </summary> /// <returns></returns> public List<BookPlaceListInfo> SelectBookPlaceListProc() { List<BookPlaceListInfo> list = new List<BookPlaceListInfo>(); try { string strProc = "proc_Select_BookPlaceListAll"; //存储过程 list = SqlMapperUtil.StoredProcWithParams<BookPlaceListInfo>(strProc, null, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return list; }
测试
/// <summary> /// 编辑 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e) { EditForm edit = new EditForm(); edit.Text = ""; edit.Operator = 2; edit.BookPlaceCode =(!object.Equals(dataGridView1.Rows[e.RowIndex].Cells["BookPlaceCode"].Value,null))?dataGridView1.Rows[e.RowIndex].Cells["BookPlaceCode"].Value.ToString():""; edit.BookPlaceID = int.Parse(dataGridView1.Rows[e.RowIndex].Cells["BookPlaceID"].Value.ToString()); edit.BookPlaceParent = int.Parse(dataGridView1.Rows[e.RowIndex].Cells["BookPlaceParent"].Value.ToString()); edit.BookPlaceName = dataGridView1.Rows[e.RowIndex].Cells["BookPlaceName"].Value.ToString(); if (edit.ShowDialog() == DialogResult.OK) { this.dataGridView1.DataSource = bookPlaceListBLL.SelectBookPlaceListAll(); //this.dataGridView1.DataSource = bookPlaceListBLL.SelectBookPlaceListProc(); //存储过程 } } /// <summary> /// 添加 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button1_Click(object sender, EventArgs e) { EditForm edit = new EditForm(); edit.Text = ""; edit.Operator = 1; edit.BookPlaceParent = int.Parse(dataGridView1.CurrentRow.Cells["BookPlaceParent"].Value.ToString()); if (edit.ShowDialog() == DialogResult.OK) { this.dataGridView1.DataSource = bookPlaceListBLL.SelectBookPlaceListAll(); //this.dataGridView1.DataSource = bookPlaceListBLL.SelectBookPlaceListProc(); //存储过程 } } /// <summary> /// 删除 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button2_Click(object sender, EventArgs e) { int id = int.Parse(dataGridView1.CurrentRow.Cells["BookPlaceID"].Value.ToString()); //bool k = bookPlaceListBLL.DeleteBookPlaceList(id);//SQL bool k = bookPlaceListBLL.DeleteBookPlaceListProc(id);//存储过程 if (k) { this.dataGridView1.DataSource = bookPlaceListBLL.SelectBookPlaceListAll(); MessageBox.Show("ok"); } } /// <summary> /// 查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button3_Click(object sender, EventArgs e) { int id = int.Parse(dataGridView1.CurrentRow.Cells["BookPlaceID"].Value.ToString()); BookPlaceListInfo info = new BookPlaceListInfo(); //info = bookPlaceListBLL.SelectBookPlaceList(id);//SQL info = bookPlaceListBLL.SelectBookPlaceListProc(id);//存储过程 涂聚文注 if (!object.Equals(info, null)) { MessageBox.Show(info.BookPlaceName); } } /// <summary> /// 添加,编辑 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button1_Click(object sender, EventArgs e) { BookPlaceListInfo info = new BookPlaceListInfo(); info.BookPlaceCode = this.textBox2.Text.Trim(); info.BookPlaceName = this.textBox1.Text.Trim(); info.BookPlaceParent = int.Parse(this.textBox3.Text.Trim()); int k = 0; if (Operator == 1) { //k = bookPlaceListBLL.InsertBookPlaceList(info);//SQL k = bookPlaceListBLL.InsertBookPlaceListProc(info);//添加,存储过程 if (k > 0) { DialogResult dresult = MessageBox.Show("添加記錄成功!", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); if (dresult == DialogResult.OK) { this.Close(); this.DialogResult = DialogResult.OK; } } } if (Operator == 2) { info.BookPlaceID = BookPlaceID; //k = bookPlaceListBLL.UpdateBookPlaceList(info);//SQL k = bookPlaceListBLL.UpdateBookPlaceListProc(info);//编辑存储过程 if (k > 0) { // DialogResult dresult = MessageBox.Show("修改記錄成功!", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); if (dresult == DialogResult.OK) { this.Close(); this.DialogResult = DialogResult.OK; } } } }
/// <summary> /// 插入有返回值 /// </summary> /// <param name="bookPlaceList"></param> /// <param name="bookPlaceID"></param> /// <returns></returns> public int InsertBookPlaceListProcOut(BookPlaceListInfo bookPlaceList, out int bookPlaceID) { int ret = 0; try { string strProc = "proc_Insert_BookPlaceListOut";//存储过程 var pamar = new DynamicParameters(); //var pamar = new { BookPlaceName = bookPlaceList.BookPlaceName, BookPlaceCode = bookPlaceList.BookPlaceCode, BookPlaceParent = bookPlaceList.BookPlaceParent }; pamar.Add("@BookPlaceName",bookPlaceList.BookPlaceName); pamar.Add("@BookPlaceCode",bookPlaceList.BookPlaceCode); pamar.Add("@BookPlaceParent",bookPlaceList.BookPlaceParent); pamar.Add("@BookPlaceID",dbType: DbType.Int32, direction: ParameterDirection.Output); ret = SqlMapperUtil.InsertUpdateOrDeleteStoredProc(strProc, pamar, SqlHelper.ConnectionString); bookPlaceID = pamar.Get<int>("@BookPlaceID"); } catch (SqlException ex) { throw ex; } return ret; }
///<summary> /// 查询所有记录 ///</summary> ///<returns></returns> public DataTable SelectBookPlaceListDataTableAll() { DataTable dt = new DataTable(); List<BookPlaceListInfo> list = new List<BookPlaceListInfo>(); try { string strProc = "proc_Select_BookPlaceListAll"; //存储过程 list = SqlMapperUtil.StoredProcWithParams<BookPlaceListInfo>(strProc, null, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable<BookPlaceListInfo>(list); } catch (SqlException ex) { throw ex; } return dt; }
与SqlHelper比批量数据插入快近一半
https://github.com/jrsoftware/issrc
http://sourceforge.net/projects/ibatisnet/files/ibatisnet/
http://sourceforge.net/projects/nhibernate/files/?source=navbar
http://sourceforge.net/projects/castleproject/files/?source=navbar
https://github.com/castleproject/
http://www.codeproject.com/Articles/212274/A-Look-at-Dapper-NET
http://www.springframework.net/
https://github.com/spring-projects/spring-net
http://skyarch.codeplex.com/SourceControl/latest
http://www.codeproject.com/Articles/656657/NET-Application-Framework-Spring-net-plus-ibatis-n
https://github.com/dotnet/core
https://github.com/microsoft/
http://netcore.codeplex.com/
Chinook Code: http://chinookdatabase.codeplex.com/ Massive Code: https://github.com/robconery/massive Dapper.Net Code: http://code.google.com/p/dapper-dot-net/ Simple.Data Code: https://github.com/markrendle/Simple.Data Docs: http://simplefx.org/simpledata/docs/ Peta Poco Code: https://github.com/toptensoftware/petapoco Docs: http://www.toptensoftware.com/petapoco/
https://github.com/xuanye/Vulcan
https://github.com/ryankirkman/DapperLite
https://github.com/LukeTillman/cqlpoco
https://github.com/fatmakoc/Dapper.NET
https://github.com/ijrussell/MicroORM
https://github.com/xliang/dapper-net-sample
https://github.com/beardeddev/dapper-fluent
a simple object mapper for .Net
https://github.com/sebastienros/dapper-dot-net
https://github.com/zzzprojects/Dapper-Plus
using System; using System.Collections.Generic; using System.ComponentModel; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Reflection; using System.Text; using Dapper; namespace Dapper { public static class SqlMapperUtil { // Remember to add <remove name="LocalSqlServer" > in ConnectionStrings section if using this, as otherwise it would be the first one. private static string connectionString = ConfigurationManager.ConnectionStrings[0].ConnectionString; /// <summary> /// Gets the open connection. /// </summary> /// <param name="name">The name of the connection string (optional).</param> /// <returns></returns> public static SqlConnection GetOpenConnection( string name = null) { string connString = ""; connString= name==null?connString = ConfigurationManager.ConnectionStrings[0].ConnectionString:connString = ConfigurationManager.ConnectionStrings[name].ConnectionString; var connection = new SqlConnection(connString); connection.Open(); return connection; } public static int InsertMultiple<T>(string sql, IEnumerable<T> entities, string connectionName=null) where T : class, new() { using (SqlConnection cnn = GetOpenConnection(connectionName )) { int records = 0; foreach (T entity in entities) { records += cnn.Execute(sql, entity); } return records; } } public static DataTable ToDataTable<T>(this IList<T> list) { PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T)); DataTable table = new DataTable(); for (int i = 0; i < props.Count; i++) { PropertyDescriptor prop = props[i]; table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType); } object[] values = new object[props.Count]; foreach (T item in list) { for (int i = 0; i < values.Length; i++) values[i] = props[i].GetValue(item) ?? DBNull.Value; table.Rows.Add(values); } return table; } public static DynamicParameters GetParametersFromObject( object obj, string[] propertyNamesToIgnore) { if(propertyNamesToIgnore ==null)propertyNamesToIgnore = new string[]{String.Empty}; DynamicParameters p = new DynamicParameters(); PropertyInfo[] properties = obj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance); foreach (PropertyInfo prop in properties) { if( !propertyNamesToIgnore.Contains(prop.Name )) p.Add("@" + prop.Name, prop.GetValue(obj, null)); } return p; } public static void SetIdentity<T>(IDbConnection connection, Action<T> setId) { dynamic identity = connection.Query("SELECT @@IDENTITY AS Id").Single(); T newId = (T)identity.Id; setId(newId); } public static object GetPropertyValue(object target, string propertyName ) { PropertyInfo[] properties = target.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance); object theValue = null; foreach (PropertyInfo prop in properties) { if (string.Compare(prop.Name, propertyName, true) == 0) { theValue= prop.GetValue(target, null); } } return theValue; } public static void SetPropertyValue(object p, string propName, object value) { Type t = p.GetType(); PropertyInfo info = t.GetProperty(propName); if (info == null) return ; if (!info.CanWrite) return; info.SetValue(p, value, null); } /// <summary> /// Stored proc. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="procname">The procname.</param> /// <param name="parms">The parms.</param> /// <returns></returns> public static List<T> StoredProcWithParams<T>(string procname, dynamic parms, string connectionName = null) { using (SqlConnection connection = GetOpenConnection(connectionName)) { return connection.Query<T>(procname, (object)parms, commandType: CommandType.StoredProcedure).ToList(); } } /// <summary> /// Stored proc with params returning dynamic. /// </summary> /// <param name="procname">The procname.</param> /// <param name="parms">The parms.</param> /// <param name="connectionName">Name of the connection.</param> /// <returns></returns> public static List<dynamic> StoredProcWithParamsDynamic(string procname, dynamic parms, string connectionName=null) { using (SqlConnection connection = GetOpenConnection(connectionName)) { return connection.Query(procname, (object)parms, commandType: CommandType.StoredProcedure).ToList(); } } /// <summary> /// Stored proc insert with ID. /// </summary> /// <typeparam name="T">The type of object</typeparam> /// <typeparam name="U">The Type of the ID</typeparam> /// <param name="procName">Name of the proc.</param> /// <param name="parms">instance of DynamicParameters class. This should include a defined output parameter</param> /// <returns>U - the @@Identity value from output parameter</returns> public static U StoredProcInsertWithID<T,U>(string procName, DynamicParameters parms, string connectionName=null) { using (SqlConnection connection = SqlMapperUtil.GetOpenConnection(connectionName)) { var x = connection.Execute(procName, (object)parms, commandType: CommandType.StoredProcedure); return parms.Get<U>("@ID"); } } /// <summary> /// SQL with params. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql">The SQL.</param> /// <param name="parms">The parms.</param> /// <returns></returns> public static List<T> SqlWithParams<T>(string sql, dynamic parms,string connectionnName=null) { using (SqlConnection connection = GetOpenConnection( connectionnName)) { return connection.Query<T>(sql, (object)parms).ToList(); } } /// <summary> /// Insert update or delete SQL. /// </summary> /// <param name="sql">The SQL.</param> /// <param name="parms">The parms.</param> /// <returns></returns> public static int InsertUpdateOrDeleteSql(string sql, dynamic parms, string connectionName=null) { using (SqlConnection connection = GetOpenConnection(connectionName)) { return connection.Execute(sql, (object)parms); } } /// <summary> /// Insert update or delete stored proc. /// </summary> /// <param name="procName">Name of the proc.</param> /// <param name="parms">The parms.</param> /// <returns></returns> public static int InsertUpdateOrDeleteStoredProc(string procName, dynamic parms, string connectionName =null) { using (SqlConnection connection = GetOpenConnection( connectionName)) { return connection.Execute(procName, (object)parms, commandType: CommandType.StoredProcedure ); } } /// <summary> /// SQLs the with params single. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql">The SQL.</param> /// <param name="parms">The parms.</param> /// <param name="connectionName">Name of the connection.</param> /// <returns></returns> public static T SqlWithParamsSingle<T>( string sql, dynamic parms, string connectionName=null) { using (SqlConnection connection = GetOpenConnection(connectionName)) { return connection.Query<T>(sql, (object) parms).FirstOrDefault(); } } /// <summary> /// proc with params single returning Dynamic object. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql">The SQL.</param> /// <param name="parms">The parms.</param> /// <param name="connectionName">Name of the connection.</param> /// <returns></returns> public static System.Dynamic.DynamicObject DynamicProcWithParamsSingle<T>(string sql, dynamic parms, string connectionName=null) { using (SqlConnection connection = GetOpenConnection(connectionName)) { return connection.Query(sql, (object)parms,commandType: CommandType.StoredProcedure ).FirstOrDefault(); } } /// <summary> /// proc with params returning Dynamic. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql">The SQL.</param> /// <param name="parms">The parms.</param> /// <param name="connectionName">Name of the connection.</param> /// <returns></returns> public static IEnumerable<dynamic> DynamicProcWithParams<T>(string sql, dynamic parms, string connectionName=null) { using (SqlConnection connection = GetOpenConnection(connectionName)) { return connection.Query(sql, (object)parms, commandType: CommandType.StoredProcedure); } } /// <summary> /// Stored proc with params returning single. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="procname">The procname.</param> /// <param name="parms">The parms.</param> /// <param name="connectionName">Name of the connection.</param> /// <returns></returns> public static T StoredProcWithParamsSingle<T>(string procname, dynamic parms, string connectionName=null) { using (SqlConnection connection = GetOpenConnection(connectionName)) { return connection.Query<T>(procname, (object) parms, commandType: CommandType.StoredProcedure).SingleOrDefault(); } } } }
http://www.nullskull.com/a/10399923/sqlmapperhelper--a-helper-class-for-dapperdotnet.aspx
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Drawing; using Geovin.Du.Model; using Geovin.Du.Common; using Geovin.Du.Interface; using Dapper; namespace Geovin.Du.DALDapper { /// <summary> /// 郵件聯系人表Customer表数据访问层 ///生成時間2017-05-19 10:15:54 ///塗聚文(Geovin Du) ///</summary> public class CustomerDAL : ICustomer { /// <summary> /// /// </summary> private IDbConnection db = new SqlConnection(SqlHelper.ConnectionString); ///<summary> ///SQL 追加记录 ///</summary> ///<param name="Customer">输入参数:CustomerInfo</param> ///<returns>返回添加的记录条数</returns> public int InsertCustomerSql(CustomerInfo customer) { int ret = 0; try { List<CustomerInfo> list = new List<CustomerInfo>(); StringBuilder str = new StringBuilder(); str.Append("INSERT INTO dbo.Customer "); str.Append("([RealName] ,[Email] ,[Sex],[Title],[Tel]) VALUES "); str.Append("(@RealName ,@Email,@Sex,@Title,@Tel)"); list.Add(customer); ret = SqlMapperUtil.InsertMultiple<CustomerInfo>(str.ToString(), list, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return ret; } /// <summary> /// 存储过程 追加记录 /// </summary> /// <param name="customer"></param> /// <returns></returns> public int InsertCustomer(CustomerInfo customer) { int ret = 0; try { string strProc = "dbo.proc_Insert_Customer";//存储过程 var pamar = new { RealName = customer.RealName, Email = customer.Email, Sex = customer.Sex, Title=customer.Title, Tel=customer.Tel }; ret = SqlMapperUtil.InsertUpdateOrDeleteStoredProc(strProc, pamar, SqlHelper.ConnectionString); //http://www.c-sharpcorner.com/uploadfile/4d9083/mapping-stored-procedures-and-getting-multiple-records-from/ } catch (SqlException ex) { throw ex; } return ret; } /// <summary> /// /// </summary> /// <param name="CustomerList"></param> /// <returns></returns> public int InsertCustomerList(List<CustomerInfo> CustomerList) { int ret = 0; try { string strProc = "dbo.proc_Insert_Customer";//存储过程 //var pamar = new { RealName = customer.RealName, Email = customer.Email, Sex = customer.Sex, Title = customer.Title, Tel = customer.Tel }; ret = SqlMapperUtil.InsertUpdateOrDeleteStoredProc(strProc, CustomerList, SqlHelper.ConnectionString); //http://www.c-sharpcorner.com/uploadfile/4d9083/mapping-stored-procedures-and-getting-multiple-records-from/ } catch (SqlException ex) { throw ex; } return ret; } /// <summary> /// 追加多条记录 /// </summary> /// <param name="CustomerInfo"></param> /// <returns></returns> public int InsertCustomerMore(List<CustomerInfo> CustomerList) { int ret = 0; try { StringBuilder str = new StringBuilder(); str.Append("INSERT INTO dbo.Customer "); str.Append("([RealName] ,[Email] ,[Sex],[Title],[Tel]) VALUES "); str.Append("(@RealName ,@Email,@Sex,@Title,@Tel)"); ret = SqlMapperUtil.InsertMultiple<CustomerInfo>(str.ToString(), CustomerList, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return ret; } ///<summary> ///存储过程 追加记录返回值 ///</summary> ///<param name="Customer">输入参数:CustomerInfo</param> ///<param name="Id">返回参数:Id</param> ///<returns>返回是否添加的个数</returns> public int InsertCustomerOutput(CustomerInfo customer, out int id) { int ret = 0; id = 0; try { string strProc = "dbo.proc_Insert_CustomerOutput";//存储过程 var pamar = new DynamicParameters(); pamar.Add("@RealName", customer.RealName); pamar.Add("@Email", customer.Email); pamar.Add("@Sex", customer.Sex); pamar.Add("@Title", customer.Title); pamar.Add("@Tel", customer.Tel); pamar.Add("@Id", dbType: DbType.Int32, direction: ParameterDirection.Output); ret = SqlMapperUtil.InsertUpdateOrDeleteStoredProc(strProc, pamar, SqlHelper.ConnectionString); if (ret > 0) { id = pamar.Get<int>("@Id"); } } catch (SqlException ex) { throw ex; } return ret; } /// <summary> /// /// </summary> /// <param name="customer"></param> /// <param name="id"></param> /// <returns></returns> public int InsertCustomerSqlOutput(CustomerInfo customer, out int id) { int ret = 0; id = 0; try { //1. //List<CustomerInfo> list = new List<CustomerInfo>(); //StringBuilder str = new StringBuilder(); //str.Append("INSERT INTO dbo.Customer "); //str.Append("([RealName] ,[Email] ,[Sex],[Title],[Tel]) VALUES "); //str.Append("(@RealName ,@Email,@Sex,@Title,@Tel)"); //ret = SqlMapperUtil.InsertMultiple<CustomerInfo>(str.ToString(), list, SqlHelper.ConnectionString); //using (SqlConnection connection = SqlMapperUtil.GetOpenConnection(SqlHelper.ConnectionString)) //{ // SqlMapperUtil.SetIdentity<int>(connection, idd => customer.Id = idd); //} //if (ret > 0) //{ // id = customer.Id;// pamar.Get<int>("@Id"); //} //2. http://developerpublish.com/dapper-net-guide-inserting-data/ string sql = "INSERT INTO dbo.Customer([RealName] ,[Email] ,[Sex],[Title],[Tel]) VALUES(@RealName ,@Email,@Sex,@Title,@Tel); select @Id=@@IDENTITY)"; var returnId = this.db.Query(sql, customer).SingleOrDefault(); customer.Id = returnId; ret = returnId; } catch (SqlException ex) { throw ex; } return ret; } ///<summary> ///存储过程 修改记录 ///</summary> ///<param name="Customer">输入参数:CustomerInfo</param> ///<returns>返回修改的多少记录数</returns> public int UpdateCustomer(CustomerInfo customer) { int ret = 0; try { string strProc = "dbo.proc_Update_Customer";//存储过程 var pamar = new { Id=customer.Id, RealName = customer.RealName, Email = customer.Email, Sex = customer.Sex, Title = customer.Title, Tel = customer.Tel }; ret = SqlMapperUtil.InsertUpdateOrDeleteStoredProc(strProc, pamar, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return ret; } /// <summary> /// /// </summary> /// <param name="customer"></param> /// <returns></returns> public int UpdateSqlCustomer(CustomerInfo customer) { int ret = 0; try { List<CustomerInfo> list = new List<CustomerInfo>(); list.Add(customer); StringBuilder str = new StringBuilder(); str.Append("UPDATE dbo.Customer SET "); str.Append("[RealName]=@RealName ,"); str.Append("[Email]=@Email,"); str.Append("[Sex]=@Sex"); str.Append("[Title]=@Title"); str.Append("[Tel]=@Tel"); str.Append(" where "); str.Append("[Id]=@Id"); ret = SqlMapperUtil.InsertMultiple<CustomerInfo>(str.ToString(), list, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return ret; } ///<summary> ///存储过程 删除记录 ///</summary> ///<param name="id">输入参数:Id</param> ///<returns>返回删除记录条数</returns> public bool DeleteCustomer(int id) { bool ret = false; try { int temp = 0; string strProc = "dbo.proc_Delete_Customer";//存储过程 var pamar = new { Id = id }; // temp = SqlMapperUtil.InsertUpdateOrDeleteStoredProc(strProc, new { Id = id }, SqlHelper.ConnectionString); temp = SqlMapperUtil.InsertUpdateOrDeleteStoredProc(strProc, pamar, SqlHelper.ConnectionString); if (temp != 0) { ret = true; } } catch (SqlException ex) { throw ex; } return ret; } /// <summary> ///SQL 删除记录 /// </summary> /// <param name="id"></param> /// <returns></returns> public bool DeleteSqlCustomer(int id) { bool ret = false; try { int temp = 0; StringBuilder str = new StringBuilder(); str.Append("DELETE dbo.Customer WHERE Id = @Id"); temp = SqlMapperUtil.InsertUpdateOrDeleteSql(str.ToString(), new { Id = id }, SqlHelper.ConnectionString); if (temp != 0) { ret = true; } } catch (SqlException ex) { throw ex; } return ret; } ///<summary> ///存储过程 删除多条记录 ///</summary> ///<param name="id">输入参数:Id</param> ///<returns>返回删除多少记录</returns> public bool DeleteCustomerId(string id) { bool ret = false; try { int temp = 0; string strProc = "dbo.proc_Delete_CustomerId";//存储过程 var pamar = new { Id = id }; temp = SqlMapperUtil.InsertUpdateOrDeleteStoredProc(strProc, new { Id = id }, SqlHelper.ConnectionString); if (temp != 0) { ret = true; } } catch (SqlException ex) { throw ex; } return ret; } ///<summary> ///存储过程 查询记录 ///</summary> ///<param name="id">输入参数:Id</param> ///<returns>返回CustomerInfo</returns> public CustomerInfo SelectCustomer(int id) { CustomerInfo customer = null; try { string strProc = "dbo.proc_Select_Customer";//存储过程 customer = SqlMapperUtil.StoredProcWithParamsSingle<CustomerInfo>(strProc, new { Id = id }, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return customer; } /// <summary> /// SQL 查询记录 /// </summary> /// <param name="id"></param> /// <returns></returns> public CustomerInfo SelectSqlCustomer(int id) { CustomerInfo customer = null; try { StringBuilder str = new StringBuilder(); str.Append("SELECT * FROM dbo.Customer WHERE Id = @Id"); customer = SqlMapperUtil.SqlWithParamsSingle<CustomerInfo>(str.ToString(), new { Id = id }, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return customer; } ///<summary> ///存储过程 查询所有记录 ///</summary> ///<param name="id">无输入参数</param> ///<returns>返回表所有记录(List)CustomerInfo</returns> public List<CustomerInfo> SelectCustomerAll() { List<CustomerInfo> list = new List<CustomerInfo>(); try { string strProc = "dbo.proc_Select_CustomerAll"; //存储过程 list = SqlMapperUtil.StoredProcWithParams<CustomerInfo>(strProc, null, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return list; } /// <summary> /// Sql 查询所有记录 /// </summary> /// <returns></returns> public List<CustomerInfo> SelectSqlCustomerAll() { List<CustomerInfo> list = new List<CustomerInfo>(); try { string str = "SELECT * FROM dbo.Customer"; list = SqlMapperUtil.SqlWithParams<CustomerInfo>(str, null, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return list; } ///<summary> ///存储过程 查询所有记录 ///</summary> ///<param name="id">无输入参数</param> ///<returns>返回(DataTable)Customer表所有记录</returns> public DataTable SelectCustomerDataTableAll() { DataTable dt = new DataTable(); List<CustomerInfo> list = new List<CustomerInfo>(); try { string strProc = "dbo.proc_Select_CustomerAll"; //存储过程 list = SqlMapperUtil.StoredProcWithParams<CustomerInfo>(strProc, null, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable<CustomerInfo>(list); } catch (SqlException ex) { throw ex; } return dt; } /// <summary> /// Sql 查询所有记录 /// </summary> /// <returns></returns> public DataTable SelectSqlCustomerDataTableAll() { DataTable dt = new DataTable(); List<CustomerInfo> list = new List<CustomerInfo>(); try { string str = "SELECT * FROM dbo.Customer"; list = SqlMapperUtil.SqlWithParams<CustomerInfo>(str, null, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable<CustomerInfo>(list); } catch (SqlException ex) { throw ex; } return dt; } /// <summary> /// SQL script查询分页 /// </summary> /// <param name="pageSize">每页页数</param> /// <param name="currentPage">当前页码</param> /// <param name="strWhere">查询的条件</param> /// <param name="filedOrder">排序字段</param> /// <param name="recordCount">每页的记录数</param> /// <returns></returns> public DataSet GetPageList(int pageSize, int currentPage, string strWhere, string filedOrder, out int recordCount) { DataTable dt = new DataTable(); List<CustomerInfo> list = new List<CustomerInfo>(); int topNum = pageSize * currentPage; StringBuilder strSql = new StringBuilder(); strSql.Append("select * FROM dbo.Customer"); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } recordCount = SqlMapperUtil.SqlWithParamsSingle<int>(PagingHelper.CreateCountingSql(strSql.ToString()),null, SqlHelper.ConnectionString); list = SqlMapperUtil.SqlWithParams<CustomerInfo>(PagingHelper.CreatePagingSql(recordCount, pageSize, currentPage, strSql.ToString(), filedOrder), null, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable<CustomerInfo>(list); DataSet ds = new DataSet(); ds.Tables.Add(dt); return ds; } /// <summary> /// 获得查询分页数据(搜索用到) 无搜索条件无排序 /// </summary> /// <param name="pageSize"></param> /// <param name="pageIndex"></param> /// <param name="recordCount"></param> /// <returns></returns> public DataSet GetPageList(int pageIndex, int pageSize, out int recordCount) { DataTable dt = new DataTable(); List<CustomerInfo> list = new List<CustomerInfo>(); string strWhere = ""; string filedOrder = " Id desc"; StringBuilder strSql = new StringBuilder(); strSql.Append("select * FROM dbo.Customer"); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } recordCount = SqlMapperUtil.SqlWithParamsSingle<int>(PagingHelper.CreateCountingSql(strSql.ToString()), null, SqlHelper.ConnectionString); list = SqlMapperUtil.SqlWithParams<CustomerInfo>(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder), null, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable<CustomerInfo>(list); DataSet ds = new DataSet(); ds.Tables.Add(dt); return ds; } /// <summary> /// QL 获得查询分页无排序 /// </summary> /// <param name="strWhere"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="recordCount"></param> /// <returns></returns> public DataSet GetPageList(int pageIndex, int pageSize, string strWhere, out int recordCount) { DataTable dt = new DataTable(); List<CustomerInfo> list = new List<CustomerInfo>(); strWhere = StringConvert.getStrWhere("RealName,Email,Title,Tel", strWhere); ; string filedOrder = " Id desc"; StringBuilder strSql = new StringBuilder(); strSql.Append("select * FROM dbo.Customer"); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } recordCount = SqlMapperUtil.SqlWithParamsSingle<int>(PagingHelper.CreateCountingSql(strSql.ToString()), null, SqlHelper.ConnectionString); list = SqlMapperUtil.SqlWithParams<CustomerInfo>(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder), null, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable<CustomerInfo>(list); DataSet ds = new DataSet(); ds.Tables.Add(dt); return ds; } /// <summary> ///存储过程 模糊查询 /// </summary> /// <param name="filedlist">显示字段列表</param> /// <param name="strkey">输入的关键字</param> /// <returns></returns> public DataTable GetDataTableCustomerFuzzySearch(string filedlist, string strkey) { DataTable dt = new DataTable(); List<CustomerInfo> list = new List<CustomerInfo>(); try { string strProc = "dbo.proc_Select_CustomerFuzzySearch"; //存储过程 var pamar = new DynamicParameters(); pamar.Add("@FieldList", filedlist); pamar.Add("@where", StringConvert.getStrWhere("RealName,Email,Title,Tel", strkey)); list = SqlMapperUtil.StoredProcWithParams<CustomerInfo>(strProc, pamar, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable<CustomerInfo>(list); } catch (SqlException ex) { throw ex; } return dt; } /// <summary> ///存储过程 是否存在该记录 /// </summary> /// <param name="Id"></param> /// <returns></returns> public bool Exists(int id) { bool isok = false; int count = 0; try { string strProc = "dbo.proc_Select_CustomerExists";//存储过程 count = SqlMapperUtil.StoredProcWithParamsSingle<int>(strProc, new { Id = id }, SqlHelper.ConnectionString); if (count > 0) isok = true; } catch (SqlException ex) { throw ex; } return isok; } /// <summary> ///存储过程 返回数据总数 /// </summary> /// <param name="strWhere">查询条件</param> /// <returns></returns> public int GetCount(string where) { int count = 0; try { string strProc = "dbo.proc_Select_CustomerCount";//存储过程 var pamar = new DynamicParameters(); pamar.Add("@where", where); count = SqlMapperUtil.StoredProcWithParamsSingle<int>(strProc, pamar, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return count; } /// <summary> ///存储过程 返回视图数据总数 /// </summary> /// <param name="strWhere">查询条件</param> /// <returns></returns> public int GetCountView(string where) { int count = 0; try { string strProc = "dbo.proc_Select_CustomerCountView";//存储过程 var pamar = new DynamicParameters(); pamar.Add("@where", where); count = SqlMapperUtil.StoredProcWithParamsSingle<int>(strProc, pamar, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return count; } /// <summary> ///存储过程 更新一列数据 /// </summary> /// <param name="Id"></param> /// <param name="strValue">字段名=值</param> /// <returns></returns> public int UpdateField(int Id, string fieldValue) { int ret = 0; try { string strProc = "dbo.proc_Update_CustomerField";//存储过程 var pamar = new DynamicParameters(); pamar.Add("@Id", Id); pamar.Add("@FieldValue", fieldValue); ret = SqlMapperUtil.InsertUpdateOrDeleteStoredProc(strProc, pamar, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return ret; } /// <summary> ///存储过程 返回指字字段的字串 /// </summary> /// <param name="Id"></param> /// <param name="fieldName">字段名</param> /// <returns></returns> public string GetTitle(int Id, string fieldName) { string title = string.Empty; try { string strProc = "dbo.proc_Select_CustomerCountView";//存储过程 var pamar = new DynamicParameters(); pamar.Add("@FieldName", fieldName); pamar.Add("@Id", Id); title = SqlMapperUtil.StoredProcWithParamsSingle<string>(strProc, pamar, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return title; } /// <summary> /// 存储过程分页 /// </summary> /// <param name="strwhere">Where条件</param> /// <param name="aecdesc">排序字段</param> /// <param name="pageIndex">开始页码</param> /// <param name="pageSize">每页页数</param> /// <param name="RecordCount">输出总页数</param> /// <returns></returns> public DataTable GetDataPage(string strwhere, string aecdesc, int pageIndex, int pageSize, out int RecordCount) { DataTable dt = new DataTable(); List<CustomerInfo> list = new List<CustomerInfo>(); try { string strProc = "dbo.GetPagedRecordFor2005_2008";//存储过程 var pamar = new DynamicParameters(); pamar.Add("@Table", "Customer"); pamar.Add("@TIndex", "Id"); pamar.Add("@Column", " * "); pamar.Add("@Sql", strwhere); pamar.Add("@PageIndex", pageIndex); pamar.Add("@PageSize", pageSize); pamar.Add("@Sort", aecdesc); list = SqlMapperUtil.StoredProcWithParams<CustomerInfo>(strProc, pamar, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable<CustomerInfo>(list); RecordCount = dt.Rows.Count; } catch (SqlException ex) { RecordCount = 0; throw ex; } return dt; } /// <summary> /// 存储过程分页 无排序 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="RecordCount"></param> /// <returns></returns> public DataTable GetDataPage(string strwhere, int pageIndex, int pageSize, out int RecordCount) { strwhere = StringConvert.getStrWhere("RealName,Email,Title,Tel", strwhere); List<CustomerInfo> list = new List<CustomerInfo>(); DataTable dt = new DataTable(); try { string strProc = "dbo.GetPagedRecordFor2005_2008";//存储过程 var pamar = new DynamicParameters(); pamar.Add("@Table", "Customer"); pamar.Add("@TIndex", "Id"); pamar.Add("@Column", " * "); pamar.Add("@Sql", strwhere); pamar.Add("@PageIndex", pageIndex); pamar.Add("@PageSize", pageSize); pamar.Add("@Sort", " Id desc"); list = SqlMapperUtil.StoredProcWithParams<CustomerInfo>(strProc, pamar, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable<CustomerInfo>(list); RecordCount = dt.Rows.Count; } catch (SqlException ex) { RecordCount = 0; throw ex; } return dt; } /// <summary> /// 存储过程分页 无搜索条件无排序 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="RecordCount"></param> /// <returns></returns> public DataTable GetDataPage(int pageIndex, int pageSize, out int RecordCount) { DataTable dt = new DataTable(); List<CustomerInfo> list = new List<CustomerInfo>(); try { string strProc = "dbo.GetPagedRecordFor2005_2008";//存储过程 var pamar = new DynamicParameters(); pamar.Add("@Table", "Customer"); pamar.Add("@TIndex", "Id"); pamar.Add("@Column", " * "); pamar.Add("@Sql", " 1=1 "); pamar.Add("@PageIndex", pageIndex); pamar.Add("@PageSize", pageSize); pamar.Add("@Sort", " Id desc"); list = SqlMapperUtil.StoredProcWithParams<CustomerInfo>(strProc, pamar, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable<CustomerInfo>(list); RecordCount = dt.Rows.Count; } catch (SqlException ex) { RecordCount = 0; throw ex; } return dt; } } }
Development environment(开发环境), Integration environment(集成环境),Testing environment (测试环境), QA (quality assurance) ensures(QA验证) , Staging environment(模拟环境),Production environment(生产环境)
Cloud Architecture, Cloud Solutions, Product Development, Software Architecture, Software Development Life Cycle, Technical Consulting
DTAP (Development, Testing, Acceptance, and Production)
DEV — Development [Software developer] SIT — System Integration Test [Software developer and QA engineer] UAT — User Acceptance Test [Client] PROD — Production [Public user]