话不多说,直接上代码
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace CommonUtils
{
class SqlServerConnect
{
//连接字符串
//Data Source=127.0.0.1;
//Initial Catalog=Object; 数据库名
//User=sa; 用户名
//PassWord=123456 密码
private static readonly string constr = "Data Source=192.168.66.3;Initial Catalog=Object;User=sa;PassWord=123456";
/// <summary>
/// 增、删、改的方法[ExecuteNonQuery] 返回所影响的行数,执行其他
/// 执行成功返回受影响的行数 执行失败返回-1
/// </summary>
/// <param name="sql">Sql命令</param>
/// <param name="pms">参数</param>
/// <returns>返回所影响的行数</returns>
public static int ExecuteNonQuery(string sql, params SqlParameter[] pms)
{
int i = -1;
try
{
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
//存储过程或者Sql语句
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
con.Open();
i = cmd.ExecuteNonQuery();
}
}
}
catch (Exception)
{
throw;
}
return i;
}
/// <summary>
/// 执行查询、查询多少条数据;返回第一行,第一列方法[ExecuteScalar] 返回-1执行失败
/// 多用于聚合函数如;clount(*) sum()
/// </summary>
/// <param name="sql">Sql命令</param>
/// <param name="pms"></param>
/// <returns></returns>
public static object ExecuteScalar(string sql, params SqlParameter[] pms)
{
try
{
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
con.Open();
return cmd.ExecuteScalar();
}
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 执行查询,返回多行、多列的方法ExecuteReader()
/// </summary>
/// <param name="sql">Sql命令</param>
/// <param name="pms"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms)
{
try
{
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
con.Open();
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 执行返回多个查询时使用,返回DataTable类型
/// </summary>
/// <param name="sql">Sql命令</param>
/// <param name="pms">参数</param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string sql,params SqlParameter[] pms)
{
DataTable dt = new DataTable();
try
{
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, constr))
{
if (pms != null)
{
adapter.SelectCommand.Parameters.AddRange(pms);
}
adapter.Fill(dt);
return dt;
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 执行返回多个查询时使用,返回List<T>类型(待试验)
/// </summary>
/// <typeparam name="T">对应数据库的模型类</typeparam>
/// <param name="sql">sql语句</param>
/// <param name="pms">参数</param>
/// <returns>返回List<T>类型</returns>
public List<T> QueryList<T>(string sql, params SqlParameter[] pms)
{
DataTable dt = new DataTable();
List<T> list = new List<T>();
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, constr))
{
if (pms != null)
{
adapter.SelectCommand.Parameters.AddRange(pms);
}
adapter.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
T _t = Activator.CreateInstance<T>();
PropertyInfo[] propertyInfo = _t.GetType().GetProperties();
for (int j = 0; j < dt.Columns.Count; j++)
{
foreach (PropertyInfo info in propertyInfo)
{
if (dt.Columns[j].ColumnName.ToUpper().Equals(info.Name.ToUpper()))
{
if (dt.Rows[i][j] != DBNull.Value)
{
info.SetValue(_t, dt.Rows[i][j], null);
}
else
{
info.SetValue(_t, null, null);
}
break;
}
}
}
list.Add(_t);
}
}
return list;
}
}
}