You can find Dapper on Google Code here: and the GitHub distro here: .
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
////// 追加记录 /// /// ///public int InsertBookPlaceList(BookPlaceListInfo bookPlaceList) { int ret = 0; try { List list=new List (); 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 (str.ToString(),list, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return ret; } /// /// 存储过程 /// 追加记录 /// /// ///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; } /// /// 追加多条记录 /// /// ///public int InsertBookPlaceListMore(List 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 (str.ToString(), bookPlaceList, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return ret; } /// ///修改记录 /// /// ///public int UpdateBookPlaceList(BookPlaceListInfo bookPlaceList) { int ret = 0; try { List list = new List (); 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 (str.ToString(), list, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return ret; } /// /// 存储过程 /// /// ///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; } /// /// 删除记录 /// /// ///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; } /// /// 存储过程 /// /// ///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; } /// /// 查询记录 /// /// ///public BookPlaceListInfo SelectBookPlaceList(int bookPlaceID) { BookPlaceListInfo bookPlaceList = null; try { StringBuilder str = new StringBuilder(); str.Append("SELECT * FROM BookPlaceList WHERE BookPlaceID = @BookPlaceID"); bookPlaceList = SqlMapperUtil.SqlWithParamsSingle (str.ToString(), new { BookPlaceID = bookPlaceID }, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return bookPlaceList; } /// /// 存储过程查询记录 /// 涂聚文 /// 20150726 /// /// ///public BookPlaceListInfo SelectBookPlaceListProc(int bookPlaceID) { BookPlaceListInfo bookPlaceList = null; try { string strProc = "proc_Select_BookPlaceList";//存储过程 bookPlaceList = SqlMapperUtil.StoredProcWithParamsSingle (strProc, new { BookPlaceID = bookPlaceID }, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return bookPlaceList; } /// /// 查询所有记录 /// ///public List SelectBookPlaceListAll() { List list = new List (); try { string str = "SELECT * FROM BookPlaceList"; list = SqlMapperUtil.SqlWithParams (str, null, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return list; } /// /// 存储过程 /// Geovin Du /// 查询所有记录 /// ///public List SelectBookPlaceListProc() { List list = new List (); try { string strProc = "proc_Select_BookPlaceListAll"; //存储过程 list = SqlMapperUtil.StoredProcWithParams (strProc, null, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return list; }
测试
////// 编辑 /// /// /// 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(); //存储过程 } } ////// 添加 /// /// /// 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(); //存储过程 } } ////// 删除 /// /// /// 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"); } } ////// 查询 /// /// /// 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); } } ////// 添加,编辑 /// /// /// 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; } } } }
////// 插入有返回值 /// /// /// ///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 ("@BookPlaceID"); } catch (SqlException ex) { throw ex; } return ret; }
////// 查询所有记录 /// ///public DataTable SelectBookPlaceListDataTableAll() { DataTable dt = new DataTable(); List list = new List (); try { string strProc = "proc_Select_BookPlaceListAll"; //存储过程 list = SqlMapperUtil.StoredProcWithParams (strProc, null, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable (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/
ChinookCode: MassiveCode: Dapper.NetCode: Simple.DataCode: Docs: Peta PocoCode: Docs: 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 in ConnectionStrings section if using this, as otherwise it would be the first one. private static string connectionString = ConfigurationManager.ConnectionStrings[0].ConnectionString; /// /// Gets the open connection. /// /// The name of the connection string (optional). /// (string sql, IEnumerable 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 (this IList 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 (IDbConnection connection, Action 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); } /// /// Stored proc. /// /// StoredProcWithParams (string procname, dynamic parms, string connectionName = null) { using (SqlConnection connection = GetOpenConnection(connectionName)) { return connection.Query (procname, (object)parms, commandType: CommandType.StoredProcedure).ToList(); } } /// /// Stored proc with params returning dynamic. /// /// The procname. /// The parms. /// Name of the connection. /// StoredProcWithParamsDynamic(string procname, dynamic parms, string connectionName=null) { using (SqlConnection connection = GetOpenConnection(connectionName)) { return connection.Query(procname, (object)parms, commandType: CommandType.StoredProcedure).ToList(); } } /// /// Stored proc insert with ID. /// /// The type of object /// The Type of the ID /// Name of the proc. /// instance of DynamicParameters class. This should include a defined output parameter /// U - the @@Identity value from output parameter public static U StoredProcInsertWithID (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 ("@ID"); } } /// /// SQL with params. /// /// SqlWithParams (string sql, dynamic parms,string connectionnName=null) { using (SqlConnection connection = GetOpenConnection( connectionnName)) { return connection.Query (sql, (object)parms).ToList(); } } /// /// Insert update or delete SQL. /// /// The SQL. /// The parms. /// /// Insert update or delete stored proc. /// /// Name of the proc. /// The parms. /// /// SQLs the with params single. /// /// ( string sql, dynamic parms, string connectionName=null) { using (SqlConnection connection = GetOpenConnection(connectionName)) { return connection.Query (sql, (object) parms).FirstOrDefault(); } } /// /// proc with params single returning Dynamic object. /// /// (string sql, dynamic parms, string connectionName=null) { using (SqlConnection connection = GetOpenConnection(connectionName)) { return connection.Query(sql, (object)parms,commandType: CommandType.StoredProcedure ).FirstOrDefault(); } } /// /// proc with params returning Dynamic. /// /// DynamicProcWithParams (string sql, dynamic parms, string connectionName=null) { using (SqlConnection connection = GetOpenConnection(connectionName)) { return connection.Query(sql, (object)parms, commandType: CommandType.StoredProcedure); } } /// /// Stored proc with params returning single. /// /// (string procname, dynamic parms, string connectionName=null) { using (SqlConnection connection = GetOpenConnection(connectionName)) { return connection.Query (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{ /// /// 郵件聯系人表Customer表数据访问层 ///生成時間2017-05-19 10:15:54 ///塗聚文(Geovin Du) /// public class CustomerDAL : ICustomer { /// /// /// private IDbConnection db = new SqlConnection(SqlHelper.ConnectionString); /// ///SQL 追加记录 /// ///输入参数:CustomerInfo /// 返回添加的记录条数 public int InsertCustomerSql(CustomerInfo customer) { int ret = 0; try { List list = new List (); 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 (str.ToString(), list, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return ret; } /// /// 存储过程 追加记录 /// /// /// /// /// /// /// 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; } /// /// 追加多条记录 /// /// /// 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 (str.ToString(), CustomerList, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return ret; } /// ///存储过程 追加记录返回值 /// /// 输入参数:CustomerInfo /// 返回参数:Id /// 返回是否添加的个数 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 ("@Id"); } } catch (SqlException ex) { throw ex; } return ret; } /// /// /// /// /// /// list = new List (); //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 (str.ToString(), list, SqlHelper.ConnectionString); //using (SqlConnection connection = SqlMapperUtil.GetOpenConnection(SqlHelper.ConnectionString)) //{ // SqlMapperUtil.SetIdentity (connection, idd => customer.Id = idd); //} //if (ret > 0) //{ // id = customer.Id;// pamar.Get ("@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; } /// ///存储过程 修改记录 /// /// 输入参数:CustomerInfo /// 返回修改的多少记录数 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; } /// /// /// /// /// list = new List (); 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 (str.ToString(), list, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return ret; } /// ///存储过程 删除记录 /// /// 输入参数:Id /// 返回删除记录条数 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; } /// ///SQL 删除记录 /// /// /// ///存储过程 删除多条记录 /// /// 输入参数:Id /// 返回删除多少记录 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; } /// ///存储过程 查询记录 /// /// 输入参数:Id /// 返回CustomerInfo public CustomerInfo SelectCustomer(int id) { CustomerInfo customer = null; try { string strProc = "dbo.proc_Select_Customer";//存储过程 customer = SqlMapperUtil.StoredProcWithParamsSingle (strProc, new { Id = id }, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return customer; } /// /// SQL 查询记录 /// /// /// (str.ToString(), new { Id = id }, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return customer; } /// ///存储过程 查询所有记录 /// /// 无输入参数 /// 返回表所有记录(List)CustomerInfo public List SelectCustomerAll() { List list = new List (); try { string strProc = "dbo.proc_Select_CustomerAll"; //存储过程 list = SqlMapperUtil.StoredProcWithParams (strProc, null, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return list; } /// /// Sql 查询所有记录 /// /// SelectSqlCustomerAll() { List list = new List (); try { string str = "SELECT * FROM dbo.Customer"; list = SqlMapperUtil.SqlWithParams (str, null, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return list; } /// ///存储过程 查询所有记录 /// /// 无输入参数 /// 返回(DataTable)Customer表所有记录 public DataTable SelectCustomerDataTableAll() { DataTable dt = new DataTable(); List list = new List (); try { string strProc = "dbo.proc_Select_CustomerAll"; //存储过程 list = SqlMapperUtil.StoredProcWithParams (strProc, null, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable (list); } catch (SqlException ex) { throw ex; } return dt; } /// /// Sql 查询所有记录 /// /// list = new List (); try { string str = "SELECT * FROM dbo.Customer"; list = SqlMapperUtil.SqlWithParams (str, null, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable (list); } catch (SqlException ex) { throw ex; } return dt; } /// /// SQL script查询分页 /// /// 每页页数 /// 当前页码 /// 查询的条件 /// 排序字段 /// 每页的记录数 /// list = new List (); int topNum = pageSize * currentPage; StringBuilder strSql = new StringBuilder(); strSql.Append("select * FROM dbo.Customer"); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } recordCount = SqlMapperUtil.SqlWithParamsSingle (PagingHelper.CreateCountingSql(strSql.ToString()),null, SqlHelper.ConnectionString); list = SqlMapperUtil.SqlWithParams (PagingHelper.CreatePagingSql(recordCount, pageSize, currentPage, strSql.ToString(), filedOrder), null, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable (list); DataSet ds = new DataSet(); ds.Tables.Add(dt); return ds; } /// /// 获得查询分页数据(搜索用到) 无搜索条件无排序 /// /// /// /// /// list = new List (); 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 (PagingHelper.CreateCountingSql(strSql.ToString()), null, SqlHelper.ConnectionString); list = SqlMapperUtil.SqlWithParams (PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder), null, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable (list); DataSet ds = new DataSet(); ds.Tables.Add(dt); return ds; } /// /// QL 获得查询分页无排序 /// /// /// /// /// /// list = new List (); 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 (PagingHelper.CreateCountingSql(strSql.ToString()), null, SqlHelper.ConnectionString); list = SqlMapperUtil.SqlWithParams (PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder), null, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable (list); DataSet ds = new DataSet(); ds.Tables.Add(dt); return ds; } /// ///存储过程 模糊查询 /// /// 显示字段列表 /// 输入的关键字 /// list = new List (); 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 (strProc, pamar, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable (list); } catch (SqlException ex) { throw ex; } return dt; } /// ///存储过程 是否存在该记录 /// /// /// ///存储过程 返回数据总数 /// /// 查询条件 /// ///存储过程 返回视图数据总数 /// /// 查询条件 /// ///存储过程 更新一列数据 /// /// /// 字段名=值 /// ///存储过程 返回指字字段的字串 /// /// /// 字段名 /// (strProc, pamar, SqlHelper.ConnectionString); } catch (SqlException ex) { throw ex; } return title; } /// /// 存储过程分页 /// /// Where条件 /// 排序字段 /// 开始页码 /// 每页页数 /// 输出总页数 /// list = new List (); 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 (strProc, pamar, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable (list); RecordCount = dt.Rows.Count; } catch (SqlException ex) { RecordCount = 0; throw ex; } return dt; } /// /// 存储过程分页 无排序 /// /// /// /// /// list = new List (); 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 (strProc, pamar, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable (list); RecordCount = dt.Rows.Count; } catch (SqlException ex) { RecordCount = 0; throw ex; } return dt; } /// /// 存储过程分页 无搜索条件无排序 /// /// /// /// /// list = new List (); 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 (strProc, pamar, SqlHelper.ConnectionString); dt = SqlMapperUtil.ToDataTable (list); RecordCount = dt.Rows.Count; } catch (SqlException ex) { RecordCount = 0; throw ex; } return dt; } }}