Oracle+Ado.Net(五)

框架功能的继续扩展

概要:

1.在日常中可能会有批量操作的需要,在项目就加了批量新增,批量更新,批量删除,其中,批量更新会全部字段一起更新,批量插入需要补全数据.

2.实现了一个类似分页插件的分页效果(PageNo类),配合之前实现分页存储过程,根据相应的PageIndex,PageSize就能在同步一个分页控件;

一,批量更新和批量插入(共用,使用IsAdd区分)

首先当然是拼接insert/update语句(一条所有列的操作)

/// <summary>
 /// AddOrUpdateSql,操作列为"*"
 /// </summary>
 /// <param name="IsAdd"></param>
 /// <returns></returns>
 public virtual string GetAddUpdateSql(bool IsAdd)
 {
 StringBuilder strSql = new StringBuilder();
 if (IsAdd)
 {
 StringBuilder strParameter = new StringBuilder();
 strSql.Append(string.Format("insert into {0}(", TableName));
 PropertyInfo[] pis =
 typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
 for (int i = 0; i < pis.Length; i++)
 {
 if (t.IsAutoId)
 {
 if (t.PrimaryKey == pis[i].Name)
 continue;
 }
 strSql.Append(pis[i].Name + ","); //构造SQL语句前半部份
 strParameter.Append(":" + pis[i].Name + ","); //构造参数SQL语句
 }
 strSql = strSql.Replace(",", ")", strSql.Length - 1, 1);
 strParameter = strParameter.Replace(",", ")", strParameter.Length - 1, 1);
 strSql.Append(" values (");
 strSql.Append(strParameter.ToString());
 }
 else
 {
 strSql.Append("update " + TableName + " set ");
 PropertyInfo[] pis =
 typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
 for (int i = 0; i < pis.Length; i++)
 {
 if (pis[i].Name != PrimaryKey)
 {
 strSql.Append(pis[i].Name + "=" + ":" + pis[i].Name + ",");
 }
 //strSql.Append(" ");
 }
 strSql = strSql.Replace(",", " ", strSql.Length - 1, 1);
 strSql.Append(" where " + PrimaryKey + "=:" + PrimaryKey);
 }
 return strSql.ToString();
 }
 一般都是参数化查询,构造参数列表(有多少个列就有多少个参数)

/// <summary>
 /// 新增和更新需要的参数列表
 /// </summary>
 /// <param name="model"></param>
 /// <param name="IsAdd"></param>
 /// <returns></returns>
 public virtual List<DbParam> AddOrUpdateParamList(T model, bool IsAdd)
 {
 PropertyInfo[] ps = typeof(T).GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public);
 List<DbParam> list = new List<DbParam>();
 if (ps != null)
 {
 for (int i = 0; i < ps.Length; i++)
 {
 if (IsAdd)
 {
 if (t.IsAutoId)
 {
 if (ps[i].Name == PrimaryKey)
 {
 continue;
 }
 }
 }
 if (model != null)
 {
 DbParam param = new DbParam()
 {
 ParamName = ps[i].Name,
 ParamDbType = TypeConvert.GetOracleDbType(ps[i].PropertyType),
 ParamValue = ps[i].GetValue(model, null)
 };
 list.Add(param);
 }
 else
 {
 DbParam param = new DbParam()
 {
 ParamName = ps[i].Name,
 ParamDbType = TypeConvert.GetOracleDbType(ps[i].PropertyType),
 ParamValue = null
 };
 list.Add(param);
 }
 }
 }
 return list;
 }

有了sql语句和相应的参数列表之后,就可以来写批量更新和批量新增了:

public virtual int AddUpdateList(List<T> list, EnumAction.AddUpdateType eAdd)
 {
 int iCount = 0;
 bool IsAdd = false;
 if (eAdd == EnumAction.AddUpdateType.Add)
 IsAdd = true;
 OracleConnection conn = DbAction.getConn();
 using (OracleCommand com = new OracleCommand(GetAddUpdateSql(IsAdd), conn))
 {
 //初始化,每一个参数的值ParamValue为null
 List<DbParam> listParam = AddOrUpdateParamList(null, IsAdd);
 foreach (DbParam dbpm in listParam)
 {
 com.Parameters.Add(dbpm); //===>其实可以写为一句
 }
 //循环执行sql
 foreach (T model in list)
 {
 //数据来自model
 listParam = AddOrUpdateParamList(model, IsAdd);
 foreach (DbParam dbpm in listParam)
 {
 //为每一个参数赋值
 com.Parameters[dbpm.ParamName].Value = dbpm.ParamValue; // ====>其实可以写为一句
 }
 if (com.ExecuteNonQuery() > 0)
 {
 iCount++;
 }
 }
 return iCount;
 }
 }

接下来就是批量删除了:

根据主键批量删除

/// <summary>
 /// 批量删除,以逗号分隔
 /// example: 19,21,11
 /// </summary>
 /// <param name="ids"></param>
 /// <returns></returns>
 public int DeleteList(string ids)
 {
 StringBuilder sb = new StringBuilder();
 sb.AppendFormat("delete from {0} ", TableName);
 if (!string.IsNullOrEmpty(ids))
 {
 sb.AppendFormat(" where {0} in (", PrimaryKey);
 sb.Append(ids + " )");
 }
 else
 {
 return 0;
 }
 OracleConnection conn = DbAction.getConn();
 OracleCommand com = new OracleCommand(sb.ToString(), conn);

 conn.Open();
 int isok = com.ExecuteNonQuery();
 conn.Close();
 return isok;
 }

根据自定义列名批量删除:

/// <summary>
 /// 除主键外,批量删除
 /// example: DeleteList("Fname","Francis,Lyfeng,Harry");
 /// </summary>
 /// <param name="colName"></param>
 /// <param name="colValues"></param>
 /// <returns></returns>
 public int DeleteList(string colName, string colValues)
 {
 StringBuilder sb = new StringBuilder();
 sb.AppendFormat("delete from {0} ", TableName);
 if (!string.IsNullOrEmpty(colName))
 {
 PropertyInfo[] ps = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.DeclaredOnly | BindingFlags.Instance);
 bool Flag = false;
 for (int i = 0; i < ps.Length; i++)
 {
 if (ps[i].Name.ToLower() == colName.ToLower())
 {
 Flag = true;
 break;
 }
 }
 if (!Flag)
 {
 throw new Exception("数据表找不到对colName指定的列值定义,请确定colName是否在数据表中列存在");
 }
 sb.AppendFormat(" where {0} in (", colName);
 string[] values = colValues.Split(',');
 for (int i = 0; i < values.Length; i++)
 {
 values[i] = "'" + values[i] + "'";
 }
 sb.Append(values + " )");
 }
 else
 {
 return 0;
 }
 OracleConnection conn = DbAction.getConn();
 OracleCommand com = new OracleCommand(sb.ToString(), conn);

 conn.Open();
 int isok = com.ExecuteNonQuery();
 conn.Close();
 return isok;
 }

二.现在的分页技术有很多,现在企业的项目一般都是Ajax来配合JQuery(或者ExtJS)实现分页的展示效果,这里我写一种Url重定向的分页效果

通过一个PageNo类来描述这个分页:

具体的一些签名如下:

private static int PageCount(int RecordsCount, int PageSize);

public static int PageIndex;

private static int _Size = 4;

public static void SetSize(int _Size);

public static string PassedParameters;//重定向之后url带的参数

public static string cn_Pageing(int CurrentPage, int RecordsCount, string Url, int PageSize, string Css);//分页条

其中主要的代码如下:

public static string cn_Pageing(int CurrentPage, int RecordsCount, string Url, int PageSize, string Css)
 {
 //判断是否有记录集
 if (RecordsCount <= 0) { return null; }

 System.Text.StringBuilder sb = new System.Text.StringBuilder();
 //获取总页数
 int PCount = PageCount(RecordsCount, PageSize);

 sb.Append("");
 //显示样式page 1 of 12
 sb.AppendFormat(" 共 {0} / {1} 页  ", CurrentPage, PCount);

 if ((PCount > 0) && (CurrentPage > 1))
 {
 sb.AppendFormat("<a class='{0}' href='{1}page=1'>首页</a> ", Css, Url);
 }
 //上一页
 if (CurrentPage > 1)
 {
 int i_CurrentPage = CurrentPage - 1;
 sb.AppendFormat("<a class='{0}' href='{1}page={2}'>上一页</a> ", Css, Url, i_CurrentPage);
 }
 //前半部分的页号
 for (int i = CurrentPage - 5; i <= (CurrentPage - 1); i++)
 {
 if (i <= 0) { continue; }

 sb.AppendFormat("<a class='{0}' href='{1}page={2}'>{3}</a>-", Css, Url, i, i);
 }

 //当前的页号
 sb.AppendFormat("{0}", CurrentPage);

 //后半部分的页号
 for (int i = CurrentPage + 1; i < (CurrentPage + 1) + 5; i++)
 {
 // System.Web.HttpContext.Current.Response.Write("<li>"+i.ToString());

 if (i > PCount)
 { break; }

 sb.AppendFormat("-<a class='{0}' href='{1}page={2}'>{3}</a>", Css, Url, i, i);
 }
 //下一页
 if (CurrentPage < PCount)
 {
 int i_CurrentPage = CurrentPage + 1;
 sb.AppendFormat(" <a class='{0}' href='{1}page={2}'>下一页</a> ", Css, Url, i_CurrentPage);
 }
 //尾页
 if (CurrentPage != PCount)
 {
 sb.AppendFormat("<a class='{0}' href='{1}page={2}'>尾页</a> ", Css, Url, PCount);
 }

 sb.AppendFormat("  总记录数:{0}条", RecordsCount);
 //sb.Append("");
 return sb.ToString();

 }

/// <summary>
 /// 获取通过QueryString[key]传过来的参数键值对,(不包含page参数)
 /// 因为page参数是在最后一个加上去的,把QueryString[key].Count-1,就能实现不传page参数并传其他的参数(如果有的话)
 /// </summary>
 public static string PassedParameters
 {
 get
 {
 string parm = string.Empty;
 int PageCount = System.Web.HttpContext.Current.Request.QueryString.Count;

 for (int i = 0; i < PageCount; i++)
 {
 if (System.Web.HttpContext.Current.Request.QueryString.Keys[i].ToLower().Equals("page"))
 {
 PageCount = System.Web.HttpContext.Current.Request.QueryString.Count - 1;
 break;
 }
 }
 for (int j = 0; j < PageCount; j++)
 {
 parm += string.Format("{0}={1}&", System.Web.HttpContext.Current.Request.QueryString.Keys[j], System.Web.HttpContext.Current.Request.QueryString[j]);
 }
 return parm;
 }
 }

这样在客户端调用的时候,根据PageIndex,PageSize就可以实现分页条和储存过程同步:

UserInfo user = new UserInfo();
 user.PageIndex = PageNo.PageIndex;
 user.PageSize = PageNo.PageSize=2;
 int count = 0;
 List<UserInfo> list=bll.GetDataByProcedure(user, out count);
 this.Label1.Text = PageNo.cn_Pageing(user.PageIndex, count, "/Index.aspx?"+PageNo.PassedParameters, user.PageSize, "");
 this.GridView1.DataSource = list;
 this.GridView1.DataBind();

一种传统老式的分页就这样完成了,一般很少人这么做,因为需要重定向,页面每次请求都要全部刷新再显示,在性能和交互方面都不是很好.(推荐Ajax实现分页).