62,046
社区成员
发帖
与我相关
我的任务
分享
/// <summary>
/// 数据提供入口
/// </summary>
public class DataProvider
{
/// <summary>
/// 数据库枚举类型
/// </summary>
public enum DataProviderType
{
/// <summary>
/// My SQL data provider
/// </summary>
MySqlDataProvider,
/// <summary>
/// The SQL Server data provider
/// </summary>
MsSqlDataProvider,
/// <summary>
/// The oracle data provider
/// </summary>
OracleDataProvider,
}
/// <summary>
/// 建立数据提供商
/// </summary>
/// <param name="dataProviderType">数据提供商类型提供</param>
/// <returns>数据提供商</returns>
public static IDataProvider CreateProvider(DataProviderType dataProviderType)
{
switch (dataProviderType)
{
case DataProviderType.MySqlDataProvider:
return new MySqlDataProvider();
case DataProviderType.MsSqlDataProvider:
return new MsSqlDataProvider();
default:
return null;
}
}
}
/// <summary>
/// SQL数据提供者
/// </summary>
public interface IDataProvider
{
/// <summary>
/// (无参)检测是否存在.
/// </summary>
/// <param name="strSql">The string SQL.</param>
/// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
bool Exists(string strSql);
/// <summary>
/// 检测是否存在.
/// </summary>
/// <param name="strSql">The string SQL.</param>
/// <param name="dataParameters">The data parameters.</param>
/// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
bool Exists(string strSql, IDataParameter[] dataParameters);
/// <summary>
/// 对连接执行 Transact-SQL 语句并返回受影响的行数
/// </summary>
/// <param name="strSql">The string SQL.</param>
/// <returns>System.Int32.</returns>
int ExecuteNonQuery(string strSql);
/// <summary>
/// Executes the non query.
/// </summary>
/// <param name="strSql">The string SQL.</param>
/// <param name="dataParameters">The data parameters.</param>
/// <returns>System.Int32.</returns>
int ExecuteNonQuery(string strSql, IDataParameter[] dataParameters);
/// <summary>
/// Queries the specified to string.
/// </summary>
/// <param name="toString">To string.</param>
/// <returns>DataSet.</returns>
DataSet Query(string toString);
/// <summary>
/// 返回一个DataSet(查询相关).
/// </summary>
/// <param name="toString">To string.</param>
/// <param name="parameters">The parameters.</param>
/// <returns>DataSet.</returns>
DataSet Query(string toString, IDataParameter[] parameters);
/// <summary>
/// (无参)执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行.
/// </summary>
/// <param name="strSql">The string SQL.</param>
/// <returns>System.Object.</returns>
object ExecuteScalar(string strSql);
/// <summary>
/// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行.
/// </summary>
/// <param name="strSql">The string SQL.</param>
/// <param name="dataParameters">The data parameters.</param>
/// <returns>System.Object.</returns>
object ExecuteScalar(string strSql, IDataParameter[] dataParameters);
/// <summary>
/// 添加一条数据
/// </summary>
/// <typeparam name="T">Model</typeparam>
/// <param name="t1">Model model</param>
/// <returns>System.Int32.</returns>
int Add<T>(T t1);
/// <summary>
/// 更新一条或多条数据
/// </summary>
/// <typeparam name="T">Model</typeparam>
/// <param name="t1">Model model</param>
/// <param name="strWhere">查询条件
/// (注:不用加where)</param>
/// <param name="whereObjects">where条件的参数
/// 如:where 条件 Name=@Name 在此为new{Name=name}</param>
/// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
bool Update<T>(T t1, string strWhere = null, object whereObjects = null);
/// <summary>
/// 更新一条数据
/// </summary>
public static bool Update(User model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update user set ");
strSql.Append("Name=@Name,");
strSql.Append("AddTime=@AddTime");
strSql.Append(" where Id=@Id");
IDataParameter[] parameters = {
new MySqlParameter("@Name", MySqlDbType.VarChar,45),
new MySqlParameter("@AddTime", MySqlDbType.DateTime),
new MySqlParameter("@Id", MySqlDbType.Int32,11)};
parameters[0].Value = model.Name;
parameters[1].Value = model.AddTime;
parameters[2].Value = model.Id;
int rows = myDataProvider.ExecuteNonQuery(strSql.ToString(), parameters);
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 更新一条或多条数据
/// </summary>
/// <typeparam name="T">Model</typeparam>
/// <param name="t1">Model model</param>
/// <param name="strWhere">查询条件
/// (注:不用加where)</param>
/// <param name="whereObjects">where条件的参数
/// 如:where 条件 Name=@Name 在此为new{Name=name}</param>
/// <returns></returns>
public bool Update<T>(T t1, string strWhere = null, object whereObjects = null)
{
Type t = t1.GetType();
StringBuilder strSql = new StringBuilder();
strSql.Append("update " + t.Name + " set ");
List<string> members = new List<string>();
var listWhere = new List<string>();
var l = new List<IDataParameter>();
//遍历所有属性
foreach (PropertyInfo pi in t.GetProperties())
{
var value = pi.GetValue(t1, null);//用pi.GetValue获得值
var name = pi.Name; //获得属性的名字,后面就可以根据名字判断来进行些自己想要的操作
//没有复制的属性和Id都不予以生成sql主句
if (value == null)
{
continue;
}
l.Add(new MySqlParameter("@" + name, value));
//如果Id(主键)被赋值,那么将主键作为where语句
if (name == "Id")
{
listWhere.Add(" and Id=@Id");
continue;
}
members.Add(name + " = @" + name);
}
strSql.Append(string.Join(",", members.ToArray()));
strSql.Append(" where 1=1 ");
strSql.Append(string.Join("", listWhere.ToArray()));
if (whereObjects != null)
{
strSql.Append(" and " + strWhere);
PropertyInfo[] pis = whereObjects.GetType().GetProperties();
foreach (var pi in pis)
{
l.Add(new MySqlParameter("@" + pi.Name, pi.GetValue(whereObjects, null)));
}
}
int rows = ExecuteNonQuery(strSql.ToString(), l.ToArray());
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
public class User :BaseModel<User>
{
public int? Id { get; set; }
public string Name { get; set; }
public DateTime? AddTime { get; set; }
}
public class BaseModel<T> where T : new()
{
/// <summary>
/// 数据库提供商选择
/// </summary>
protected IDataProvider MySqlDataProvider =
DataProvider.CreateProvider(DataProvider.DataProviderType.MySqlDataProvider);
public virtual int Add(T model)
{
return MySqlDataProvider.Add(model);
}
}
调用方法:
//添加一条
sw.Restart();
User u4 = new User();
u4.Name = "董西刚";
u4.AddTime = DateTime.Now;
//Console.WriteLine(Add<User>(u4));
Console.WriteLine(u4.Add(u4));
sw.Stop();
Console.WriteLine("更新运行时间:" + sw.Elapsed.TotalMilliseconds);
这样的调用已经开始方便起来了,对于以后的优化,目前还没有思路。
有好的建议吗?