1、建立解决方案
2、 工具-》NutGet程序包管理=》管理解决方案的NutGet程序包
输入关键字 dapper 搜索。选择后安装
3、添加 DataBase.cs文件
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Dapper;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.IO;
namespace DAL
{
public class DataBase
{
#region Parameters
private IDbConnection con = null;
private string _connString;
private string _paramPrefix = "@";
private string _dataType = "SQLSERVER";
#endregion
#region DataBase
public DataBase()
{
_connString = ConfigurationManager.AppSettings["Ticket"];
_paramPrefix = GetParamPrefix(_dataType);
}
public DataBase(string appSettingKey)
{
try
{
_connString = ConfigurationManager.AppSettings[appSettingKey];
}
catch (Exception)
{
_connString = ConfigurationManager.AppSettings["Ticket"];
}
_paramPrefix = GetParamPrefix(_dataType);
}
public DataBase(string dataType, string connString)
{
_dataType = dataType;
_connString = connString;
_paramPrefix = GetParamPrefix(_dataType);
}
#endregion
#region Functions
public IDbConnection GetConn()
{
return con;
}
public IDbConnection GetConn(string type, string connString)
{
IDbConnection con = null;
type = type.ToUpper().Trim();
if (type == "SQLSERVER")
{
con = new SqlConnection(connString);
}
return con;
}
public string GetParamPrefix(string type)
{
type = type.ToUpper().Trim();
if (type == "ORACLE")
{
return ":";
}
else if (type == "SQLSERVER")
{
}
else if (type == "MYSQL")
{
}
else if (type == "SQLITE")
{
}
return "@";
}
public void Open()
{
if (con == null)
{
con = GetConn(_dataType, _connString);
try
{
con.Open();
}
catch (Exception)
{
}
}
}
public void Close()
{
if (con != null)
{
try
{
con.Close();
con = null;
}
catch (Exception)
{
}
}
}
public IDbCommand GetCmd()
{
return con.CreateCommand();
}
public IDbDataAdapter GetDataAdapter(IDbCommand cmd)
{
IDbDataAdapter dbadapter = null;
string type = _dataType.ToUpper().Trim();
if (type == "SQLSERVER")
{
dbadapter = new SqlDataAdapter((SqlCommand)cmd);
}
return dbadapter;
}
public IEnumerable<dynamic> GetAllTableRows(string table)
{
Open();
IEnumerable<dynamic> result = null;
try
{
result = con.Query("select * from " + table);
}
catch (Exception)
{
}
finally
{
Close();
}
return result;
}
public IEnumerable<T> GetAllTableRows<T>(string table)
{
Open();
IEnumerable<T> result = null;
try
{
result = con.Query<T>("select * from " + table);
}
catch (Exception)
{
}
finally
{
Close();
}
return result;
}
public IEnumerable<dynamic> GetTableRowsByNameValue(string table, string name0, object value0, params object[] nvs)
{
Open();
IEnumerable<dynamic> result = null;
try
{
var dictionary = new Dictionary<string, object>();
string sqlstr = name0 + "=" + _paramPrefix + name0;
dictionary.Add(name0, value0);
if (nvs.Length > 1)
{
for (var i = 0; i < nvs.Length / 2; i++)
{
dictionary.Add(nvs[i * 2].ToString(), nvs[i * 2 + 1]);
sqlstr += " and " + nvs[i * 2].ToString() + "=" + _paramPrefix + nvs[i * 2].ToString();
}
}
DynamicParameters p = new DynamicParameters();
p.AddDynamicParams(dictionary);
result = con.Query("select * from " + table + " where " + sqlstr, p);
}
catch (Exception)
{
}
finally
{
Close();
}
return result;
}
public IEnumerable<T> GetTableRowsByNameValue<T>(string table, string name0, object value0, params object[] nvs)
{
Open();
IEnumerable<T> result = null;
try
{
var dictionary = new Dictionary<string, object>();
string sqlstr = name0 + "=" + _paramPrefix + name0;
dictionary.Add(name0, value0);
if (nvs.Length > 1)
{
for (var i = 0; i < nvs.Length / 2; i++)
{
dictionary.Add(nvs[i * 2].ToString(), nvs[i * 2 + 1]);
sqlstr += " and " + nvs[i * 2].ToString() + "=" + _paramPrefix + nvs[i * 2].ToString();
}
}
DynamicParameters p = new DynamicParameters();
p.AddDynamicParams(dictionary);
result = con.Query<T>("select * from " + table + " where " + sqlstr, p);
}
catch (Exception)
{
}
finally
{
Close();
}
return result;
}
public IEnumerable<T> GetTableRowsByNameValueAndLastUpdateDate<T>(string table, string name0, object value0, params object[] nvs)
{
Open();
IEnumerable<T> result = null;
try
{
var dictionary = new Dictionary<string, object>();
string sqlstr = name0 + "=" + _paramPrefix + name0;
dictionary.Add(name0, value0);
if (nvs.Length > 1)
{
for (var i = 0; i < nvs.Length / 2; i++)
{
dictionary.Add(nvs[i * 2].ToString(), nvs[i * 2 + 1]);
sqlstr += " and " + nvs[i * 2].ToString() + "=" + _paramPrefix + nvs[i * 2].ToString();
}
}
sqlstr += " and LastUpdateDate IS NULL";
DynamicParameters p = new DynamicParameters();
p.AddDynamicParams(dictionary);
result = con.Query<T>("select * from " + table + " where " + sqlstr, p);
}
catch (Exception)
{
}
finally
{
Close();
}
return result;
}
public int AddTableRow(string table, string name0, object value0, params object[] nvs)
{
Open();
int result = 0;
try
{
var dictionary = new Dictionary<string, object>();
string sqlstr = name0;
string sqlstrvalues = _paramPrefix + name0;
dictionary.Add(_paramPrefix + name0, value0);
if (nvs.Length > 1)
{
for (var i = 0; i < nvs.Length / 2; i++)
{
dictionary.Add(_paramPrefix + nvs[i * 2].ToString(), nvs[i * 2 + 1]);
sqlstr += "," + nvs[i * 2].ToString();
sqlstrvalues += "," + _paramPrefix + nvs[i * 2].ToString();
}
}
DynamicParameters p = new DynamicParameters();
p.AddDynamicParams(dictionary);
result = con.Execute("insert into " + table + " (" + sqlstr + ") values (" + sqlstrvalues + ")", p);
}
catch (Exception)
{
}
finally
{
Close();
}
return result;
}
public int UpdateTableRow(string table, string conditionname0, object conditionvalue0, string name0, object value0, params object[] nvs)
{
Open();
int result = 0;
try
{
var dictionary = new Dictionary<string, object>();
string sqlstr = name0 + "=" + _paramPrefix + name0;
dictionary.Add(name0, value0);
if (nvs.Length > 1)
{
for (var i = 0; i < nvs.Length / 2; i++)
{
dictionary.Add(nvs[i * 2].ToString(), nvs[i * 2 + 1]);
sqlstr += " , " + nvs[i * 2].ToString() + "=" + _paramPrefix + nvs[i * 2].ToString();
}
}
dictionary.Add(conditionname0, conditionvalue0);
DynamicParameters p = new DynamicParameters();
p.AddDynamicParams(dictionary);
result = con.Execute("update " + table + " set " + sqlstr + " where " + conditionname0 + "=" + _paramPrefix + conditionname0, p);
}
catch (Exception)
{
}
finally
{
Close();
}
return result;
}
public int DeleteTableRow(string table, string name0, object value0, params object[] nvs)
{
Open();
int result = 0;
try
{
var dictionary = new Dictionary<string, object>();
string sqlstr = " 1=1 and " + name0 + "=" + _paramPrefix + name0;
dictionary.Add(name0, value0);
if (nvs.Length > 1)
{
for (var i = 0; i < nvs.Length / 2; i++)
{
dictionary.Add(nvs[i * 2].ToString(), nvs[i * 2 + 1]);
sqlstr += " and " + nvs[i * 2].ToString() + "=" + _paramPrefix + nvs[i * 2].ToString();
}
}
DynamicParameters p = new DynamicParameters();
p.AddDynamicParams(dictionary);
result = con.Execute("delete from " + table + " where " + sqlstr, p);
}
catch (Exception)
{
}
finally
{
Close();
}
return result;
}
public IEnumerable<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
{
Open();
IEnumerable<T> result = null;
try
{
result = con.Query<T>(sql, param: param, transaction: transaction, buffered: buffered, commandTimeout: commandTimeout, commandType: commandType);
}
catch (Exception ex)
{
}
finally
{
Close();
}
return result;
}
public IEnumerable<dynamic> Query(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
{
Open();
IEnumerable<dynamic> result = null;
try
{
result = con.Query(sql, param: param, transaction: transaction, buffered: buffered, commandTimeout: commandTimeout, commandType: commandType);
}
catch (Exception ex)
{
}
finally
{
Close();
}
return result;
}
public T ExecuteAndGetOutput<T>(string sql, string outputName,DbType outputType,int? size=11 )
{
var p = new DynamicParameters();
p.Add(outputName, dbType: outputType, direction: ParameterDirection.Output,size: size);
Open();
try
{
con.Execute(sql, p, commandType: CommandType.StoredProcedure);
}
catch (Exception ex)
{
}
finally
{
Close();
}
return p.Get<T>(outputName);
}
public T ExecuteScalar<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
{
Open();
T result = default(T);
try
{
result = con.ExecuteScalar<T>(sql, param: param, transaction: transaction, commandTimeout: commandTimeout, commandType: commandType);
}
catch (Exception ex)
{
}
finally
{
Close();
}
return result;
}
public dynamic ExecuteScalar(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
{
Open();
dynamic result = null;
try
{
result = con.ExecuteScalar(sql, param: param, transaction: transaction, commandTimeout: commandTimeout, commandType: commandType);
}
catch (Exception)
{
}
finally
{
Close();
}
return result;
}
public int ExcuteSql(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
{
Open();
int result = 0;
try
{
result = con.Execute(sql, param: param, transaction: transaction, commandTimeout: commandTimeout, commandType: commandType);
}
catch (Exception e)
{
}
finally
{
Close();
}
return result;
}
public DataSet GetDataSet(string CommandText)
{
Open();
DataSet dataSet = new DataSet();
IDbCommand command = GetCmd();
command.CommandType = CommandType.Text;
command.CommandText = CommandText;
IDataAdapter adapter = GetDataAdapter(command);
try
{
adapter.Fill(dataSet);
}
catch
{
dataSet.Clear();
throw;
}
finally
{
command.Dispose();
Close();
}
return dataSet;
}
public DataSet GetDataSet(string CommandText, params IDataParameter[] cmdParms)
{
Open();
DataSet dataSet = new DataSet();
IDbCommand command = GetCmd();
command.CommandType = CommandType.Text;
command.CommandText = CommandText;
if (cmdParms != null)
{
foreach (IDataParameter parm in cmdParms)
command.Parameters.Add(parm);
}
IDataAdapter adapter = GetDataAdapter(command);
try
{
adapter.Fill(dataSet);
}
catch
{
dataSet.Clear();
throw;
}
finally
{
command.Dispose();
Close();
}
return dataSet;
}
public int ExecuteProcedure(string procedureName, params IDataParameter[] cmdParms)
{
Open();
int result = 0;
try
{
IDbCommand cmd = GetCmd();
cmd.CommandText = procedureName;
cmd.CommandType = CommandType.StoredProcedure;
foreach (IDataParameter parm in cmdParms)
cmd.Parameters.Add(parm);
result = cmd.ExecuteNonQuery();
}
catch (Exception)
{
}
finally
{
Close();
}
return result;
}
#endregion
}
}
3、在 config 文件的 configuration 节点下面的 appSettings节点下面 添加 上面所用到的 连接选项
<configuration>
<appSettings>
<add key="Ticket" value="Server=11.11.11.11;uid=sa;password=aaaaa;DataBase=taaaestdataaa" />
</appSettings>
<system.codedom>
4、建立类型 。
添加数据库对应的类
public class Car
{
public string ID { get; set; }
public string UserName { get; set; }
public String UserId { get; set; }
public String state { get; set; }
}
5、写一下测试方法
public void test()
{
var database = new DAL.DataBase();
IEnumerable<Car> list = database.Query<Car>("select * from Car");
Console.WriteLine(list.ToList().Count);
Console.ReadKey();
}