using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace OADemo.Tool
{
public class DapperHelper
{
/*
使用帮助，参数 param 是sql参数化设置，没有参数可以不写。

有1个参数@a param 写成 new {a = 值}
如多个参数@a @b new {a=值,b =值}
in类型的参数 id in (@x,@y,@z) 可改版成 id in @w 参数 new int[]{x,y,z }
*/
/// <summary>
/// 获取连接字符串
/// </summary>
/// <returns></returns>
public static DbConnection GetDbConnection()
{
return new SqlConnection(ConfigurationManager.ConnectionStrings["connStr"].ConnectionString);
}

/// <summary>
/// 查询操作
/// </summary>
/// <typeparam name="T">返回集合的类型</typeparam>
/// <param name="sql">sql语句</param>
/// <param name="param">参数化值</param>
/// <returns></returns>
public static IEnumerable<T> Query<T>(string sql, object param = null)
{
IEnumerable<T> _list = default(IEnumerable<T>);
if (!string.IsNullOrEmpty(sql))
{
using (DbConnection conn = GetDbConnection())
{
_list = conn.Query<T>(sql, param);
}
}
return _list;
}

/// <summary>
/// 和Query<T>的区别是 T之后返回能转换为T类型的集合，这里能返回所有。
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static IEnumerable<dynamic> Query(string sql, object param = null)
{
using (DbConnection conn = GetDbConnection())
{
return conn.Query(sql, param);
}
}
/// <summary>
/// 执行存储过程查询操作
/// </summary>
/// <typeparam name="T">返回集合的类型</typeparam>
/// <param name="storedName">存储过程</param>
/// <param name="param">参数化值</param>
/// <returns></returns>
public static IEnumerable<T> QueryStored<T>(string storedName, object param = null)
{
IEnumerable<T> _list = default(IEnumerable<T>);
if (!string.IsNullOrEmpty(storedName))
{
using (DbConnection conn = GetDbConnection())
{
_list = conn.Query<T>(storedName, commandType: CommandType.StoredProcedure);
}
}
return _list;
}

/// <summary>
/// 查询操作返回默认第一条数据(如返回null则创建默认类型)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static T FirstOrDefault<T>(string sql, object param = null)
{
var model = default(T);
if (!string.IsNullOrEmpty(sql))
{
using (DbConnection conn = GetDbConnection())
{
model = conn.Query<T>(sql, param).FirstOrDefault();
}
}
return model == null ? Activator.CreateInstance<T>() : model;
}

public static List<IEnumerable<dynamic>> QueryMultiple(string sql,int readTimes , object param = null)
{
List<IEnumerable<dynamic>> resutl = new List<IEnumerable<dynamic>>();
using (DbConnection conn = GetDbConnection())
{
using (var multi = conn.QueryMultiple(sql, param))
{
if (!multi.IsConsumed)
{
for (int i = 0; i < readTimes; i++)
{
resutl.Add( multi.Read());
}
}
}
}
return resutl;
}

/// <summary>
/// 非查询操作
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static int Excute(string sql, object param = null)
{
using (DbConnection conn = GetDbConnection())
{
return conn.Execute(sql, param);
}
}
/// <summary>
/// 返回首行首列
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static object ExecuteScalar(string sql, object param = null)
{
using (DbConnection conn = GetDbConnection())
{
return conn.ExecuteScalar(sql, param);
}
}

}
}

-------------------------

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace OADemo.Tool
{
public class SqlHelper
{
static string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
public SqlHelper()
{
}

public static int ExecuteNonquery(string cmdText, params SqlParameter[] ps)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand())
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(ps);
return cmd.ExecuteNonQuery();
}
}
}

public static object ExecuteScalar(string cmdText, params SqlParameter[] ps)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand())
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(ps);
return cmd.ExecuteScalar();
}
}
}

public static DataSet GetDsByAdapter(string cmdText, params SqlParameter[] ps)
{
DataSet ds = new DataSet();

using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand())
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(ps);
using (SqlDataAdapter apt = new SqlDataAdapter(cmd))
{
apt.Fill(ds);
}
}
}
return ds;
}

public static SqlDataReader GetReader(string cmdText, params SqlParameter[] ps)
{
//using (SqlConnection conn = new SqlConnection(connStr))
//{
// using (SqlCommand cmd = new SqlCommand())
// { conn.Open();
// cmd.Connection = conn;
// cmd.CommandText = cmdText;
// cmd.Parameters.AddRange(ps);

// return cmd.ExecuteReader(CommandBehavior.CloseConnection);
// }
//}

SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand();
conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; cmd.Parameters.AddRange(ps);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);

}

}
}

DapperHelper,SqlHelper