连接SQLServer数据库的方法
连接串:
//取json中的连接串
string SqlCon = "DataSource = IP地址;Database=数据库名;UserID=用户名:Password=密码;MultipleActiveResultSets=True";
使用泛型方法+反射实现查询:
/// <summary>
/// 全查
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static IList<T> SearchAll<T>()
{
IList<T> list = new List<T>();
//获取传入的类的type
Type type = typeof(T);
//声明SqlConnection连接数据库
using (SqlConnection con = new SqlConnection(SqlCon))
{
//打开数据库
con.Open();
//编写sql语句
string sql = "SELECT* FROM " + type.Name + " order by Id";
//声明SqlCommand并执行SQL语句
using (SqlCommand cmd = new SqlCommand(sql, con))
{
//获得结果集
SqlDataReader dr = cmd.ExecuteReader();
//判断是否存在数据
if (dr.HasRows)
{
//循环结果集
while (dr.Read())
{
//使用反射映射传入的类
object oPeople = Activator.CreateInstance(type);
//循环传入的类中的属性
foreach (var item in type.GetProperties())
{
//赋值
item.SetValue(oPeople, dr[item.Name]);
}
list.Add((T)oPeople);
}
}
}
}
return list;
}
/// <summary>
/// 条件查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
public T SearchPeople<T>(int id)
{
//获取传入的类的type
Type type = typeof(T);
//使用反射映射传入的类
Object obj = Activator.CreateInstance(type);
//声明SqlConnection连接数据库
using (SqlConnection con = new SqlConnection(SqlCon))
{
//打开数据库
con.Open();
//拼接SQL语句
string sql = "select * from " + type.Name + " where Id = " + id;
//声明SqlCommand并执行SQL语句
SqlCommand cmd = new SqlCommand(sql, con);
//获得结果集
SqlDataReader dr = cmd.ExecuteReader();
//判断是否有数据
if (dr.Read() && dr.HasRows)
{
//循环传入的类中的属性
foreach (var item in type.GetProperties())
{
//赋值
item.SetValue(obj, dr[item.Name]);
}
}
}
return (T)obj;
}
连接MySQL数据库的方法(按条件查)
连接属性设置:
/// <summary>
/// MySQL数据库连接设置
/// </summary>
MySqlConnectionStringBuilder sb = new MySqlConnectionStringBuilder()
{
Server = "IP地址",//IP地址
Database = "数据库名",//数据库名
UserID = "用户",//用户名
Password = "密码",//密码
Pooling = true//是否启用连接
};
使用泛型方法+反射实现查询:
/// <summary>
/// 按条件查询
/// 使用泛型方法实现 一个方法满足不同类的功能
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public T SearchPeople<T>(int id)
{
//使用反射获取传入的类类型中的属性
Type type = typeof(T);
Object oPeople = Activator.CreateInstance(type);
//使用MySqlConnection连接数据库
using (MySqlConnection con = new MySqlConnection(sb.ConnectionString))
{
//打开数据库
con.Open();
//将传入的类的属性的Name转为集合
IList<string> list = type.GetProperties().Select(p => "`" + p.Name + "`").ToList();
//集合转为字符串并用逗号分割
string pros = string.Join(",", list);
//拼接sql语句
//string sql = "select " + pros + " from " + type.Name + " where id = " + id;
//这里的泛型方法调用时用到的T是SearchPeople<T>中定义的T,所以符合调用泛型调用时必须明确类型的规则
ConnectionSqlsString<T> css = new ConnectionSqlsString<T>();
string sql = css.GetSearchSql(1);
//使用SqlCommand执行sql语句
using (MySqlCommand cmd = new MySqlCommand(sql, con))
{
//得到结果集
MySqlDataReader reader = cmd.ExecuteReader();
//判断是否查询到数据
if (reader.HasRows && reader.Read())
{
//循环传入的类中的属性并赋值给返回的对象
foreach (var item in type.GetProperties())
{
if (!string.IsNullOrEmpty(reader[item.Name].ToString()))
{
item.SetValue(oPeople, reader[item.Name]);
}
//item.SetValue(oPeople, reader[item.Name] is DBNull ? null : reader[item.Name]);
}
}
}
}
return (T)oPeople;
}
优化----使用泛型缓存避免多次生成重复的sql语句:
//使用泛型缓存优化查询(调用多次的情况下,sql语句只生成一次)
public class ConnectionSqlsString<T>
{
//通过静态函数生成sql语句
private static string ConString = null;
static ConnectionSqlsString()
{
Type type = typeof(T);
ConString = "select " + string.Join(",", type.GetProperties().Select(p => "`" + p.Name + "`").ToList()) + " from " + type.Name + " where id = ";
}
//外面调用的获取sql语句的方法
public string GetSearchSql( int id)
{
return ConString + id;
}
}