Oracle+Ado.Net(二)
概要:更多详细的BaseDal请看Oracle+Ado.Net(一),这里只是对(一)的封装的东西进行简要介绍.
功能添加:
在BaseDal中添加了公共的新增,更新功能;
在Model层中添加以下代码:
/// <summary>
/// 标识自增长 /// </summary> private bool isAutoId = true; public bool IsAutoId { get { return isAutoId; } set { isAutoId = value; } } private string columns = "*"; public string Columns { get { return columns; } set { columns = value; } } private string where; public string Where { get { return where; } set { where = value; } }在namespace myOracle.Dal下添加一个参数化查询类:
using System.Data;
using System.Data.OracleClient; /// <summary> /// 参数化查询类 /// </summary> public class DbParam { /// <summary> /// 参数键 /// </summary> private string _ParamName = ""; /// <summary> /// 参数类型 /// </summary> private OracleType _ParamDbType; /// <summary> /// 参数值 /// </summary> private object _ParamValue = null; public string ParamName { get { return _ParamName; } set { _ParamName = value; } } public OracleType ParamDbType { get { return _ParamDbType; } set { _ParamDbType = value; } } public object ParamValue { get { return _ParamValue; } set { _ParamValue = value; } } }在BaseDal层的具体代码(使用反射技术):
StringBuilder sb = new StringBuilder();
StringBuilder ParamStr = new StringBuilder(); sb.AppendFormat("insert into {0} (",TableName); List<DbParam> list = new List<DbParam>(); PropertyInfo[] propertys = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public); for (int i = 0; i < propertys.Length; i++) { if(t.IsAutoId) { if (propertys[i].Name == PrimaryKey) continue; } //DateTime类型处理(日期最小不能小于1900.1.1) if (propertys[i].PropertyType == typeof(DateTime) && ((DateTime)propertys[i].GetValue(model, null)) < new DateTime(1900, 1, 1)) { propertys[i].SetValue(model, new DateTime(1900, 1, 1), null); } sb.Append(propertys[i].Name+","); ParamStr.Append(":"+propertys[i].Name+","); DbParam param = new DbParam() { ParamName=":"+propertys[i].Name, ParamDbType=TypeConvert.GetOracleDbType(propertys[i].PropertyType), ParamValue=propertys[i].GetValue(model,null) }; list.Add(param); } sb.Replace(",",")",sb.Length-1,1); ParamStr.Replace(",",")",ParamStr.Length-1,1); sb.Append(" values("); sb.Append(ParamStr);//在plsql虽然可以加上分号";",但是在这里不能加上分号";" if(t.IsAutoId) { /* * 先取得一个序列的下一个值： select myseq.nextval from dual; 然后再把这个值当成主键值插入数据表： insert into mytable (id, ...) values (id_val, ...) * */ } OracleConnection conn=DbAction.getConn(); OracleCommand com = new OracleCommand(sb.ToString(), conn); foreach (DbParam item in list) { com.Parameters.Add(DbHelper.CreateParam(item.ParamName,item.ParamValue)); //com.Parameters.Add(p); } OracleString rowid; conn.Open(); com.ExecuteOracleNonQuery(out rowid); conn.Close();至于在更新Update方法中,我们需要先封装一个方法:
/// <summary>
/// 是否字段值是否更新由BaseModel的columns定义===>推断出反射出来的属性是否需要更新 /// </summary> /// <param name="model">columns定义的列</param> /// <param name="val">反射的属性</param> /// <returns>是否更新</returns> private bool IsUpdateProperty(T model,string val) { bool result = false; string strs=model.Columns; if(strs=="*") { return true; } string[] cols=strs.Split(','); for (int i = 0; i < cols.Length; i++) { if(val.Equals(cols[i],StringComparison.OrdinalIgnoreCase)) { result = true; //跳出循环 break; } } return result; }在update方法中:需要进行判断一下是否
if(this.IsUpdateProperty(model,ps[i].Name))
{ sb.Append(ps[i].Name+"="+":"+ps[i].Name+","); list.Add( DbHelper.CreateParam(ps[i].Name, ps[i].GetValue(model, null))); }Update于Insert大致一样;
总结:在拼接sql语句的时候需要非常细心,中英文输入法,还有sql语句最后不要加";"
END