.net core创建webapi之----连接Oracle数据库
创建webapi时连接数据库是一个基础工作。这里记录一下连接数据库的过程及注意事项。
1，打开appsettings.json文件添加数据库连接字符串
"Appsettings": {
"ConnectionString": "data source=****/数据库实例名;user id=用户名;password=密码"
}
2，创建DBUtility文件夹这里放连接数据库的基本操作类，读取配置类
3，在DBUtility文件夹下创建读取配置类获取连接字符串，这里可以写获取appsettings.json文件中的任何配置，这里贴上获取数据库连接代码：
/// <summary>
/// 获取连接字符串
/// </summary>
public static string ConnectionString
{
get
{
try
{
string _connectionString = AppConfigurtaionServices.Configuration["Appsettings:ConnectionString"];
return _connectionString;
}
catch (Exception e)
{
throw e;
}
}
}
4，创建DbHelperOra.cs作为Oracle数据库的基础访问类,代码如下：
public class DbHelperOra : IDisposable
{
#region 私有变量
private DbConnection _conn;
private readonly string _connectionString;
private readonly DbProviderFactory _factory;
private DbTransaction _tran;
protected string separator = "@";
#endregion
#region 构造函数
public DbHelperOra(string configName)
{
_connectionString = PubConstant.ConnectionString;
if (_connectionString == null)
throw new Exception("无效的数据库连接");
_factory = OracleClientFactory.Instance;
if (_factory.GetType().FullName.Contains("Oracle"))
separator = ":";
}
public DbHelperOra(string connectionString, DbProviderFactory factory)
{
_connectionString = connectionString;
_factory = factory;
if (_factory.GetType().FullName.Contains("Oracle"))
separator = ":";
}
public DbHelperOra(string connectionString, string providerInvariantName)
: this(connectionString, DbProviderFactories.GetFactory(providerInvariantName))
{
}
public DbHelperOra(DbConnection conn)
{
_conn = conn;
_connectionString = conn.ConnectionString;
_factory = conn.GetType().GetProperty("DbProviderFactory", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(conn, null) as DbProviderFactory;
if (_factory.GetType().FullName.Contains("Oracle"))
separator = ":";
}
#endregion
#region 打开关闭
/// <summary>
/// 打开数据库连接
/// </summary>
public DbConnection Open()
{
if (_conn == null)
{
_conn = _factory.CreateConnection();
_conn.ConnectionString = _connectionString;
}
if (_conn.State == ConnectionState.Closed)
_conn.Open();
return _conn;
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close()
{
if (_conn.State != ConnectionState.Closed)
_conn.Close();
}
#endregion
#region 命令
/// <summary>
/// 创建Sql命令
/// </summary>
/// <returns>DbCommand</returns>
public DbCommand CreateCommand()
{
return _factory.CreateCommand();
}
/// <summary>
/// 创建Sql命令
/// </summary>
/// <param name="commandText">命令文本</param>
/// <param name="commandType">命令类型</param>
/// <returns>DbCommand</returns>
public DbCommand CreateCommand(string commandText, CommandType commandType)
{
var cmd = CreateCommand();
cmd.CommandText = commandText;
cmd.CommandType = commandType;
return cmd;
}
public DbCommand CreateCommand(string text, params object[] ps)
{
var cmd = CreateCommand();
var sql = text.ToUpper().Trim();
if (sql.Contains(" "))
cmd.CommandType = CommandType.Text;
else
cmd.CommandType = CommandType.StoredProcedure;
if (ps.Length > 0)
{
if (ps.All(c => c is DbParameter))
{
cmd.Parameters.AddRange(ps);
cmd.CommandText = text;
}
else
{
var vs = new DbParameter[ps.Length];
var ns = new object[ps.Length];
for (var i = 0; i < ps.Length; i++)
{
ns[i] = separator + "p" + i;
vs[i] = CreateParameter(separator + "p" + i, ps[i]);
}
cmd.CommandText = string.Format(text, ns);
cmd.Parameters.AddRange(vs);
}
}
else
cmd.CommandText = text;
return cmd;
}
#endregion
#region 参数
/// <summary>
/// 创建参数
/// </summary>
/// <returns>DbParameter</returns>
public DbParameter CreateParameter()
{
return _factory.CreateParameter();
}
/// <summary>
/// 创建参数
/// </summary>
/// <param name="name">参数名称</param>
/// <param name="value">参数值</param>
/// <returns>DbParameter</returns>
public virtual DbParameter CreateParameter(string name, object value)
{
var p = CreateParameter();
p.ParameterName = name;
p.Value = value;
if (_factory.GetType().FullName.Contains("Oracle"))
{
var pi = p.GetType().GetProperty("OracleDbType");
if (((int)pi.GetValue(p, null)) == 120)
{
pi.SetValue(p, 0x66, null);
}
}
return p;
}
#endregion
#region 事务
/// <summary>
/// 开启事务
/// </summary>
/// <returns></returns>
public DbTransaction Begin()
{
Open();
if (_tran == null)
{
_tran = _conn.BeginTransaction();
}
return _tran;
}
/// <summary>
/// 提交事务
/// </summary>
public void Commit()
{
if (_tran != null)
_tran.Commit();
_tran = null;
}
/// <summary>
/// 回滚事务
/// </summary>
public void Rollback()
{
if (_tran != null)
_tran.Rollback();
_tran = null;
}
/// <summary>
/// 完成事务
/// </summary>
public void Complete()
{
try
{
Commit();
}
catch
{
Rollback();
throw;
}
}
#endregion
#region 执行命令
/// <summary>
/// 封装命令，设置命令的连接和事务
/// </summary>
/// <param name="cmd">数据操作命令</param>
protected virtual void SetupCommand(DbCommand cmd)
{
Open();
cmd.Connection = _conn;
if (_tran != null)
cmd.Transaction = _tran;
}
/// <summary>
/// 无返回值执行命令
/// </summary>
/// <param name="cmd">数据操作命令</param>
/// <returns>影响条数</returns>
public virtual int ExecuteNonQuery(DbCommand cmd)
{
SetupCommand(cmd);
return cmd.ExecuteNonQuery();
}
/// <summary>
/// 无返回值执行命令
/// </summary>
/// <param name="text">命令文本</param>
/// <param name="args">命令参数</param>
/// <returns>影响条数</returns>
public virtual int ExecuteNonQuery(string text, params object[] args)
{
using (var cmd = CreateCommand(text, args))
{
return ExecuteNonQuery(cmd);
}
}
/// <summary>
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。所有其他的列和行将被忽略。
/// </summary>
/// <param name="cmd">数据操作命令</param>
/// <returns>结果集中第一行的第一列。</returns>
public virtual object ExecuteScalar(DbCommand cmd)
{
SetupCommand(cmd);
return cmd.ExecuteScalar();
}
/// <summary>
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。所有其他的列和行将被忽略。
/// </summary>
/// <param name="text">命令文本</param>
/// <param name="args">命令参数</param>
/// <returns>结果集中第一行的第一列</returns>
public virtual object ExecuteScalar(string text, params object[] args)
{
using (var cmd = CreateCommand(text, args))
{
return ExecuteScalar(cmd);
}
}
/// <summary>
/// 针对 System.Data.Common.DbCommand.Connection 执行 System.Data.Common.DbCommand.CommandText，并返回
/// </summary>
/// <param name="cmd">数据操作命令</param>
/// <returns>一个 System.Data.Common.DbDataReader 对象。</returns>
public virtual IDataReader ExecuteReader(DbCommand cmd)
{
SetupCommand(cmd);
return cmd.ExecuteReader();
}
/// <summary>
/// 针对 System.Data.Common.DbCommand.Connection 执行 System.Data.Common.DbCommand.CommandText，并返回
/// </summary>
/// <param name="text">命令文本</param>
/// <param name="args">命令参数</param>
/// <returns>一个 System.Data.Common.DbDataReader 对象。</returns>
public virtual IDataReader ExecuteReader(string text, params object[] args)
{
using (var cmd = CreateCommand(text, args))
{
return ExecuteReader(cmd);
}
}
/// <summary>
/// 执行查询，并返回查询结果集
/// </summary>
/// <param name="cmd">数据操作命令</param>
/// <returns>查询结果集</returns>
public virtual DataSet ExecuteDataSet(DbCommand cmd)
{
SetupCommand(cmd);
using (var adp = _factory.CreateDataAdapter())
{
adp.SelectCommand = cmd;
var ds = new DataSet();
adp.Fill(ds);
return ds;
}
}
/// <summary>
/// 执行查询，并返回查询结果集
/// </summary>
/// <param name="text">命令文本</param>
/// <param name="args">命令参数</param>
/// <returns>查询结果集</returns>
public virtual DataSet ExecuteDataSet(string text, params object[] args)
{
using (var cmd = CreateCommand(text, args))
{
return ExecuteDataSet(cmd);
}
}
#endregion
#region 泛型方法
/// <summary>
/// 执行查询并返回列表
/// </summary>
/// <typeparam name="T">元数据类型</typeparam>
/// <param name="text">执行的查询</param>
/// <param name="args">查询参数</param>
/// <returns>数据列表</returns>
public virtual List<T> Query<T>(string text, params object[] args)
{
using (var dr = ExecuteReader(text, args))
{
var lst = new List<T>();
if (typeof(IDynamicMetaObjectProvider).IsAssignableFrom(typeof(T)))
{
var cols = dr.GetSchemaTable().AsEnumerable().Select(c => c["ColumnName"].ToString()).ToArray();
while (dr.Read())
{
lst.Add(Read<T>(dr, cols));
}
}
else
{
var cols = dr.GetSchemaTable().AsEnumerable().Select(c => c["ColumnName"].ToString().ToUpper());
var ps = typeof(T).GetProperties().Where(c => cols.Contains(c.Name.ToUpper())).ToList();
while (dr.Read())
{
lst.Add(Read<T>(dr, ps));
}
}
return lst;
}
}
/// <summary>
/// 返回表中的所有数据
/// </summary>
/// <typeparam name="T">元数据类型</typeparam>
/// <returns>数据列表</returns>
public virtual List<T> Query<T>()
{
return Query<T>("SELECT * FROM " + typeof(T).Name);
}
/// <summary>
/// 根据条件返回数据
/// </summary>
/// <typeparam name="T">元数据类型</typeparam>
/// <param name="where">条件</param>
/// <param name="args">参数</param>
/// <returns>数据列表</returns>
public virtual List<T> Where<T>(string where = null, params object[] args)
{
if (string.IsNullOrEmpty(where))
return Query<T>();
return Query<T>(string.Format("SELECT * FROM {0} WHERE {1}", typeof(T).Name, where), args);
}
public virtual List<T> Where<T>(Expression<Func<T, bool>> predicate)
{
var qt = new QueryTranslator();
qt.Translate(predicate);
return Where<T>(qt.Where, qt.Arguments.ToArray());
}
/// <summary>
/// 判断数据是否存在
/// </summary>
/// <param name="text">执行的查询</param>
/// <param name="args">查询参数</param>
/// <returns>是否存在</returns>
public virtual bool Exists(string text, params object[] args)
{
using (var dr = ExecuteReader(text, args))
{
return dr.Read();
}
}
/// <summary>
/// 判断数据是否存在
/// </summary>
/// <typeparam name="T">元数据类型</typeparam>
/// <param name="where">查询条件</param>
/// <param name="args">条件参数</param>
/// <returns>是否存在</returns>
public virtual bool Exists<T>(string where = null, params object[] args)
{
var sql = "SELECT * FROM " + typeof(T).Name;
if (!string.IsNullOrEmpty(where))
sql += " WHERE " + where;
return Exists(sql, args);
}
/// <summary>
/// 根据id判断是否存在数据
/// </summary>
/// <typeparam name="T">元数据类型</typeparam>
/// <param name="ids">主键</param>
/// <returns></returns>
public virtual bool ExistsKeys<T>(params object[] ids)
{
var type = typeof(T);
var ps = type.GetProperties().Where(c => c.IsDefined(typeof(PrimaryKeyAttribute), true)).ToList();
if (ps.Count > 0)
{
var sql = "SELECT * FROM " + typeof(T).Name;
for (var i = 0; i < ps.Count; i++)
{
if (i == 0)
sql += " WHERE " + ps[i].Name + "={" + i + "}";
else
sql += " AND " + ps[i].Name + "={" + i + "}";
}
return Exists(sql, ids);
}
else
{
var p = type.GetProperty("id", BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase);
if (p == null)
throw new ArgumentException("T");
var sql = "SELECT * FROM " + typeof(T).Name + " WHERE ID={0}";
return Exists(sql, ids);
}
}
/// <summary>
/// 查找一行数据
/// </summary>
/// <typeparam name="T">元数据类型</typeparam>
/// <param name="text">查询语句</param>
/// <param name="args">查询参数</param>
/// <returns></returns>
public virtual T First<T>(string text, params object[] args)
{
using (var dr = ExecuteReader(text, args))
{
if (dr.Read())
{
if (typeof(IDynamicMetaObjectProvider).IsAssignableFrom(typeof(T)))
{
var cols = dr.GetSchemaTable().AsEnumerable().Select(c => c["ColumnName"].ToString()).ToArray();
return Read<T>(dr, cols);
}
else
{
var cols = dr.GetSchemaTable().AsEnumerable().Select(c => c["ColumnName"].ToString().ToUpper());
var ps = typeof(T).GetProperties().Where(c => cols.Contains(c.Name.ToUpper())).ToList();
return Read<T>(dr, ps);
}
}
return default(T);
}
}
public virtual T First<T>(Expression<Func<T, bool>> predicate)
{
var qt = new QueryTranslator();
qt.Translate(predicate);
var where = qt.Where;
var sql = "SELECT * FROM " + typeof(T).Name;
if (!string.IsNullOrWhiteSpace(where))
sql += " WHERE " + where;
return First<T>(sql, qt.Arguments.ToArray());
}
/// <summary>
/// 根据条件查询第一行数据
/// </summary>
/// <typeparam name="T">元数据类型</typeparam>
/// <param name="where">条件</param>
/// <param name="args">条件参数</param>
/// <returns></returns>
public virtual T Find<T>(string where = null, params object[] args)
{
var sql = "SELECT * FROM " + typeof(T).Name;
if (!string.IsNullOrEmpty(where))
sql += " WHERE " + where;
return First<T>(sql, args);
}
/// <summary>
/// 根据主键查找数据
/// </summary>
/// <typeparam name="T">元数据类型</typeparam>
/// <param name="keys">主键</param>
/// <returns></returns>
public virtual T FindByPrimaryKey<T>(params object[] keys)
{
var type = typeof(T);
var ps = type.GetProperties().Where(c => c.IsDefined(typeof(PrimaryKeyAttribute), true)).ToList();
if (ps.Count > 0)
{
var sql = "SELECT * FROM " + typeof(T).Name;
for (var i = 0; i < ps.Count; i++)
{
if (i == 0)
sql += " WHERE " + ps[i].Name + "={" + i + "}";
else
sql += " AND " + ps[i].Name + "={" + i + "}";
}
return First<T>(sql, keys);
}
else
{
var p = type.GetProperty("id", BindingFlags.Instance | BindingFlags.Public | BindingFlags.IgnoreCase);
if (p == null)
throw new ArgumentException("T");
var sql = "SELECT * FROM " + typeof(T).Name + " WHERE ID={0}";
return First<T>(sql, keys);
}
}
/// <summary>
/// 读取动态数据
/// </summary>
/// <typeparam name="T">动态数据原型</typeparam>
/// <param name="dr">IDataReader</param>
/// <param name="cols">需要读取的列</param>
/// <returns>数据对象实例</returns>
protected virtual T Read<T>(IDataReader dr, IEnumerable<string> cols)
{
dynamic row = Activator.CreateInstance<T>();
foreach (var col in cols)
{
var value = dr[col];
if (!Convert.IsDBNull(value))
{
row[col] = value;
}
}
return row;
}
/// <summary>
/// 读取对象
/// </summary>
/// <typeparam name="T">动态数据原型</typeparam>
/// <param name="dr">IDataReader</param>
/// <param name="ps">需要设置的属性</param>
/// <returns>数据对象实例</returns>
protected virtual T Read<T>(IDataReader dr, IEnumerable<PropertyInfo> ps)
{
var row = Activator.CreateInstance<T>();
foreach (var p in ps)
{
var value = dr[p.Name];
if (!Convert.IsDBNull(value))
{
p.SetValue(row, Common.ChangeType(value, p.PropertyType), null);
}
}
return row;
}
/// <summary>
/// 读取动态数据
/// </summary>
/// <typeparam name="T">动态数据原型</typeparam>
/// <param name="dr">DataRow</param>
/// <param name="cols">需要读取的列</param>
/// <returns>数据对象实例</returns>
protected virtual T Read<T>(DataRow dr, IEnumerable<string> cols)
{
dynamic row = Activator.CreateInstance<T>();
foreach (var col in cols)
{
var value = dr[col];
if (!Convert.IsDBNull(value))
{
row[col] = value;
}
}
return row;
}
/// <summary>
/// 读取对象
/// </summary>
/// <typeparam name="T">动态数据原型</typeparam>
/// <param name="dr">DataRow</param>
/// <param name="ps">需要设置的属性</param>
/// <returns>数据对象实例</returns>
protected virtual T Read<T>(DataRow dr, IEnumerable<PropertyInfo> ps)
{
var row = Activator.CreateInstance<T>();
foreach (var p in ps)
{
var value = dr[p.Name];
if (!Convert.IsDBNull(value))
{
p.SetValue(row, value, null);
}
}
return row;
}
#endregion
#region IDisposable
public void Dispose()
{
Dispose(true);
}
protected virtual void Dispose(bool state)
{
if (state)
{
if (_tran != null)
_tran.Dispose();
_tran = null;
if (_conn != null)
{
_conn.Close();
_conn.Dispose();
}
}
}
#endregion
#region QueryTranslator
internal class QueryTranslator : ExpressionVisitor
{
public void Translate(Expression expression)
{
buff = new StringBuilder();
args = new List<object>();
Visit(expression);
}
private StringBuilder buff;
private List<object> args;
public string Where
{
get { return buff.ToString(); }
}
public List<object> Arguments
{
get { return args; }
}
public override Expression Visit(Expression node)
{
return base.Visit(node);
}
protected override Expression VisitBinary(BinaryExpression node)
{
buff.Append("(");
Visit(node.Left);
switch (node.NodeType)
{
case ExpressionType.Equal: buff.Append(" = "); break;
case ExpressionType.NotEqual: buff.Append(" <> "); break;
case ExpressionType.AndAlso: buff.Append(" AND "); break;
case ExpressionType.OrElse: buff.Append(" OR "); break;
case ExpressionType.GreaterThan: buff.Append(" > "); break;
case ExpressionType.GreaterThanOrEqual: buff.Append(" >= "); break;
case ExpressionType.LessThan: buff.Append(" < "); break;
case ExpressionType.LessThanOrEqual: buff.Append(" <= "); break;
}
Visit(node.Right);
buff.Append(")");
return node;
}
protected override Expression VisitMember(MemberExpression node)
{
if (node.Expression.NodeType == ExpressionType.Parameter || node.Expression.NodeType == ExpressionType.Convert)
buff.Append(node.Member.Name);
else
{
buff.AppendFormat("{{{0}}}", args.Count);
args.Add(MemberAssess(node));
}
return node;
}
protected override Expression VisitConstant(ConstantExpression node)
{
buff.AppendFormat("{{{0}}}", args.Count);
args.Add(node.Value);
return node;
}
private object MemberAssess(Expression expression)
{
if (expression is ConstantExpression)
return (expression as ConstantExpression).Value;
else if (expression is MemberExpression)
{
var node = expression as MemberExpression;
if (node.Expression == null)
{
if (node.Member is PropertyInfo)
return (node.Member as PropertyInfo).GetValue(null, null);
return (node.Member as FieldInfo).GetValue(null);
}
if (node.Expression.NodeType == ExpressionType.Constant)
return (node.Member as FieldInfo).GetValue((node.Expression as ConstantExpression).Value);
if (node.Expression.NodeType == ExpressionType.MemberAccess)
{
var v = MemberAssess(node.Expression as MemberExpression);
if (node.Member is PropertyInfo)
return (node.Member as PropertyInfo).GetValue(v, null);
return (node.Member as FieldInfo).GetValue(v);
}
}
throw new NotImplementedException();
}
protected override Expression VisitMethodCall(MethodCallExpression node)
{
if (node.Method.Name == "ToUpper")
{
buff.Append("UPPER(");
Visit(node.Object);
buff.Append(")");
}
else if (node.Method.Name == "ToLower")
{
buff.Append("LOWER(");
Visit(node.Object);
buff.Append(")");
}
else if (node.Method.Name == "StartsWith")
{
Visit(node.Object);
buff.Append(" LIKE ");
var value = MemberAssess(node.Arguments[0]).ToString();
buff.AppendFormat("{{{0}}}", args.Count);
args.Add(value.Replace("%", "") + "%");
}
else if (node.Method.Name == "EndsWith")
{
Visit(node.Object);
buff.Append(" LIKE ");
var value = MemberAssess(node.Arguments[0]).ToString();
buff.AppendFormat("{{{0}}}", args.Count);
args.Add("%" + value.Replace("%", ""));
}
else if (node.Method.Name == "Contains")
{
if (node.Method == typeof(string).GetMethod("Contains"))
{
Visit(node.Object);
buff.Append(" LIKE ");
var value = MemberAssess(node.Arguments[0]).ToString();
buff.AppendFormat("{{{0}}}", args.Count);
args.Add("%" + value.Replace("%", "") + "%");
}
else
{
var value = (MemberAssess(node.Arguments[0] as MemberExpression) as IEnumerable<string>).ToArray();
var count = value.Length;
if (count == 0)
{
buff.Append("1=0");
}
else
{
Visit(node.Arguments[1]);
if (count == 1)
{
buff.AppendFormat("={{{0}}}", args.Count);
args.Add(value[0]);
}
else if (count == 2)
{
buff.AppendFormat("={{{0}}}", args.Count);
args.Add(value[0]);
buff.Append(" OR ");
Visit(node.Arguments[1]);
buff.AppendFormat("={{{0}}}", args.Count);
args.Add(value[1]);
}
else
{
buff.Append(" IN (");
for (var i = 0; i < count; i++)
{
if (i > 0)
buff.Append(",");
buff.AppendFormat("{{{0}}}", args.Count);
args.Add(value[i]);
}
buff.Append(")");
}
}
}
}
return node;
}
}
#endregion
注意这里的
_factory = OracleClientFactory.Instance;在.NET CORE里需要直接获取实例。
5，配置数据库基本处理类DbHelperOra.cs封装数据的操作方法。
/// <summary>
/// 基于DbHelperOra的实际操作类
/// </summary>
public class DbHelper:DbHelperOra
{
public DbHelper()
: base("Database")
{
}
public List<T> QueryLogicData<T>()
{
var p = typeof(T).GetProperty("DataState", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.IgnoreCase);
if (p == null)
return Query<T>();
return Where<T>("DataState='0'");
}
public override DataSet ExecuteDataSet(DbCommand cmd)
{
if (cmd.CommandType == CommandType.Text)
{
var text = cmd.CommandText.Trim().Trim(';');
if (text.IndexOf(';') > 0)
{
var lst = text.Split(';');
var ds = new DataSet();
foreach (var sql in lst)
{
var command = CreateCommand(sql, CommandType.Text);
var ms = Regex.Matches(sql, separator + @"\w+");
if (ms.Count > 0)
{
foreach (Match m in ms)
{
var p = cmd.Parameters.Cast<DbParameter>().First(c => c.ParameterName == m.Value);
command.Parameters.Add(CreateParameter(p.ParameterName, p.Value));
}
}
var t = base.ExecuteDataSet(command).Tables[0];
var dt = t.Copy();
if (ds.Tables.Count > 0)
{
dt.TableName = "Table" + ds.Tables.Count;
}
ds.Tables.Add(dt);
}
return ds;
}
else
return base.ExecuteDataSet(cmd);
}
else
return base.ExecuteDataSet(cmd);
}
public override DbParameter CreateParameter(string name, object value)
{
var p = base.CreateParameter(name, value);
var type = p.GetType();
var pi = type.GetProperty("OracleDbType");
if (value is byte[] && pi != null)
{
pi.SetValue(p, 102, null);
}
return p;
}
public bool Insert(BaseEntity row)
{
if (row != null)
{
var type = row.GetType();
var ps = type.GetProperties();
var cols = new List<string>();
var args = new List<object>();
var vs = new List<string>();
foreach (var p in ps)
{
if (!p.IsDefined(typeof(DataFieldAttribute), true))
continue;
var name = p.Name.ToUpper();
if (name == "ID")
continue;
if (p.GetValue(row, null) != null)
{
cols.Add(name);
vs.Add("{" + args.Count + "}");
args.Add(p.GetValue(row, null));
}
}
var result = ExecuteNonQuery(string.Format("INSERT INTO {0}({1}) VALUES({2})", type.Name, string.Join(",", cols), string.Join(",", vs)), args.ToArray());
return result > 0;
}
return false;
}
public int InsertReturn(BaseEntity row)
{
if (row != null)
{
var type = row.GetType();
var ps = type.GetProperties();
var cols = new List<string>();
var args = new List<object>();
var vs = new List<string>();
foreach (var p in ps)
{
if (!p.IsDefined(typeof(DataFieldAttribute), true))
continue;
var name = p.Name.ToUpper();
if (name == "ID")
continue;
if (p.GetValue(row, null) != null)
{
cols.Add(name);
vs.Add("{" + args.Count + "}");
args.Add(p.GetValue(row, null));
}
}
var result = ExecuteScalar(string.Format("INSERT INTO {0}({1}) VALUES({2}) ;SELECT @@Identity", type.Name, string.Join(",", cols), string.Join(",", vs)), args.ToArray());
//int Id = int.Parse(result.ToString());
return (int)result;
//return Id;
}
return 0;
}
public bool Update(BaseEntity row, string updateKey)
{
if (row != null)
{
var type = row.GetType();
var ps = type.GetProperties();
var kp = ps.FirstOrDefault(c => c.IsDefined(typeof(Utility.Redis.PrimaryKeyAttribute), true));
if (kp == null || updateKey == "ID")
{
kp = ps.FirstOrDefault(c => c.Name.ToUpper() == "ID");
}
if (kp == null)
throw new NotImplementedException("未设置主键");
var cols = new List<string>();
var args = new List<object>();
foreach (var p in ps)
{
if (!p.IsDefined(typeof(DataFieldAttribute), true))
continue;
if (p != kp)
{
var name = p.Name.ToUpper();
if (p.GetValue(row, null) != null)
{
cols.Add(name + "={" + args.Count + "}");
args.Add(p.GetValue(row, null));
}
}
}
args.Add(kp.GetValue(row, null));
var result = ExecuteNonQuery(string.Format("UPDATE {0} SET {1} WHERE {2}={3}", type.Name, string.Join(",", cols), kp.Name, "{" + (args.Count - 1) + "}"), args.ToArray());
return result > 0;
}
return false;
}
public bool Delete(BaseEntity row, string updateKey)
{
if (row != null)
{
var type = row.GetType();
var ps = type.GetProperties();
var kp = ps.FirstOrDefault(c => c.IsDefined(typeof(Utility.Redis.PrimaryKeyAttribute), true));
if (kp == null || updateKey == "ID")
{
kp = ps.FirstOrDefault(c => c.Name.ToUpper() == "ID");
}
if (kp == null)
throw new NotImplementedException("未设置主键");
var state = ps.FirstOrDefault(c => c.Name.ToUpper() == "DATASTATE");
if (state == null)
{
var result = ExecuteNonQuery(string.Format("DELETE FROM {0} WHERE {1}={2}", type.Name, kp.Name, "{0}"), kp.GetValue(row, null));
return result > 0;
}
else
{
var result = ExecuteNonQuery(string.Format("UPDATE {0} SET {1}='9' WHERE {2}={3}", type.Name, state.Name, kp.Name, "{0}"), kp.GetValue(row, null));
return result > 0;
}
}
return false;
}
}
基本数据访问完成数据库操作就结束了，接下来对数据库的操作直接调用DbHelpe里面的方法即可方便操作数据库。
ps:有小伙伴反应common类没有上传，下面将common类的代码附在下面，方便小伙伴们学习使用。
using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.Diagnostics; using System.IO; using System.IO.Compression; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Web; using System.Reflection; namespace Utility { public static class Common { public static TimeSpan TimeOffset = TimeSpan.Zero; public static DateTime Now { get { return DateTime.Now + TimeOffset; } } public static DateTime Today { get { return Now.Date; } } public static object ChangeType(object value, Type type) { if (value == null) return null; if (type.IsValueType) { if (type.IsGenericType) return Convert.ChangeType(value, type.GetGenericArguments()[0]); else return Convert.ChangeType(value, type); } return value; } /// <summary> /// 压缩字节数组 /// </summary> /// <param name="inputBytes"></param> /// <returns></returns> public static byte[] Compress(byte[] inputBytes) { using (MemoryStream outStream = new MemoryStream()) { using (GZipStream zipStream = new GZipStream(outStream, CompressionMode.Compress, true)) { zipStream.Write(inputBytes, 0, inputBytes.Length); zipStream.Close(); //很重要，必须关闭，否则无法正确解压 return outStream.ToArray(); } } } /// <summary> /// 解压缩字节数组 /// </summary> /// <param name="inputBytes"></param> public static byte[] Decompress(byte[] inputBytes) { using (MemoryStream inputStream = new MemoryStream(inputBytes)) { using (MemoryStream outStream = new MemoryStream()) { using (GZipStream zipStream = new GZipStream(inputStream, CompressionMode.Decompress)) { zipStream.CopyTo(outStream); zipStream.Close(); return outStream.ToArray(); } } } } public static string MapPath(string path) { if (path.IndexOf(":")>0) return path; return System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, path); } public static string MakeSureFolder(string path) { path = MapPath(path); if (!Directory.Exists(path)) Directory.CreateDirectory(path); return path; } public static void ShowInputPanel() { try { dynamic file = "C:\\Program Files\\Common Files\\microsoft shared\\ink\\TabTip.exe"; if (!System.IO.File.Exists(file)) return; Process.Start(file); } catch (Exception) { } } /// <summary> /// Unix时间戳 /// </summary> /// <param name="time">时间</param> /// <returns>long</returns> public static long ConvertDateTimeToInt() { System.DateTime time = DateTime.Now; System.DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1, 0, 0, 0, 0)); long t = (time.Ticks - startTime.Ticks) / 10000; //除10000调整为13位 return t; } /// <summary> /// 生成主键 /// </summary> /// <returns></returns> public static string GetEventId() { Int64 unixTime = ConvertDateTimeToInt(); String eventId = unixTime.ToString("x8").PadLeft(16, '0'); return eventId; } public static DataSet ToDataSet<T>(this IList<T> list) { Type elementType = typeof(T); var ds = new DataSet(); var t = new DataTable(); ds.Tables.Add(t); elementType.GetProperties().ToList().ForEach(propInfo => t.Columns.Add(propInfo.Name, Nullable.GetUnderlyingType(propInfo.PropertyType) ?? propInfo.PropertyType)); foreach (T item in list) { var row = t.NewRow(); elementType.GetProperties().ToList().ForEach(propInfo => row[propInfo.Name] = propInfo.GetValue(item, null) ?? DBNull.Value); t.Rows.Add(row); } return ds; } /// <summary> /// 将泛类型集合List类转换成DataTable /// </summary> /// <param name="entitys">泛类型集合</param> /// <returns></returns> public static DataTable ListToDataTable<T>(List<T> entitys) { //检查实体集合不能为空 if (entitys == null || entitys.Count < 1) { throw new Exception("需转换的集合为空"); } //取出第一个实体的所有Propertie Type entityType = entitys[0].GetType(); PropertyInfo[] entityProperties = entityType.GetProperties(); //生成DataTable的structure //生产代码中，应将生成的DataTable结构Cache起来，此处略 DataTable dt = new DataTable(); for (int i = 0; i < entityProperties.Length; i++) { //dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType); dt.Columns.Add(entityProperties[i].Name); } //将所有entity添加到DataTable中 foreach (object entity in entitys) { //检查所有的的实体都为同一类型 if (entity.GetType() != entityType) { throw new Exception("要转换的集合元素类型不一致"); } object[] entityValues = new object[entityProperties.Length]; for (int i = 0; i < entityProperties.Length; i++) { entityValues[i] = entityProperties[i].GetValue(entity, null); } dt.Rows.Add(entityValues); } return dt; } } }
