/// <summary> /// 获取记录映射 /// </summary> /// <param name="objEntity">实体</param> /// <param name="conn">数据库连接</param> /// <param name="tran">数据库操作事务</param> /// <returns>实体</returns> private static EntityObjectBase Get(EntityObjectBase objEntity, SqlConnection conn, SqlTransaction tran) { TableAttributes taEntity = objEntity.GetEntityColumns(); string strKeyColumns = ""; ArrayList lstPara = new ArrayList(); for (int i = 0; i < taEntity.Columns.Count; i++) {// 组装参数 ColumnAttributes caCurrent = taEntity.Columns[i] as ColumnAttributes; if (caCurrent.IsPrimaryKey) { strKeyColumns += caCurrent.ColumnName + "=@" + caCurrent.ColumnName + " AND "; SqlParameter paraCurrent = new SqlParameter("@" + caCurrent.ColumnName, caCurrent.Value); lstPara.Add(paraCurrent); } } SqlParameter[] objPara = new SqlParameter[lstPara.Count]; for (int i = 0; i < lstPara.Count; i++) { objPara[i] = lstPara[i] as SqlParameter; } string strSql = "SELECT * FROM [TableName] WHERE [Key]"; // 替换当前表数据 strSql = strSql.Replace("[TableName]", taEntity.TableName); strSql = strSql.Replace("[Key]", strKeyColumns.Substring(0, strKeyColumns.Length - 5)); DataTable tbResult = null; if (null != conn) { tbResult = SqlHelper.ExecuteDataset(conn, CommandType.Text, strSql, objPara).Tables[0]; } else { tbResult = SqlHelper.ExecuteDataset(tran, CommandType.Text, strSql, objPara).Tables[0]; } if (tbResult.Rows.Count > 0) { Type type = objEntity.GetType(); EntityObjectBase objResult = Activator.CreateInstance(type) as EntityObjectBase; objResult.FromTable(tbResult); return(objResult); } else { return(null); } }
/// <summary> /// 删除记录映射 /// </summary> /// <param name="objEntity">实体</param> /// <param name="conn">数据库连接</param> /// <param name="tran">数据库操作事务</param> /// <returns>影响行数</returns> private static int Delete(EntityObjectBase objEntity, SqlConnection conn, SqlTransaction tran) { TableAttributes taEntity = objEntity.GetEntityColumns(); string strKeyColumns = String.Empty; ArrayList lstPara = new ArrayList(); for (int i = 0; i < taEntity.Columns.Count; i++) {// 组装参数 ColumnAttributes caCurrent = taEntity.Columns[i] as ColumnAttributes; if (caCurrent.IsPrimaryKey) { strKeyColumns += caCurrent.ColumnName + "=@" + caCurrent.ColumnName + " AND "; } else { continue; } SqlParameter paraCurrent = new SqlParameter("@" + caCurrent.ColumnName, caCurrent.Value); lstPara.Add(paraCurrent); } SqlParameter[] objPara = new SqlParameter[lstPara.Count]; for (int i = 0; i < lstPara.Count; i++) { objPara[i] = lstPara[i] as SqlParameter; } string strCondition = strKeyColumns; string strSql = "DELETE FROM [TableName] WHERE [Condition]"; // 替换当前表数据 strSql = strSql.Replace("[TableName]", taEntity.TableName); strSql = strSql.Replace("[Condition]", strCondition.Substring(0, strCondition.Length - 5)); int upCount = 0; if (null != conn) { upCount = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, strSql, objPara); } else { upCount = SqlHelper.ExecuteNonQuery(tran, CommandType.Text, strSql, objPara); } return(upCount); }
/// <summary> /// 执行更新查询 /// </summary> /// <param name="objEntity">更新值实体</param> /// <param name="queryCondition">查询条件</param> /// <param name="conn">数据库连接</param> /// <param name="tran">数据库操作事务</param> /// <returns>删除行数</returns> private static int ExcuteUpdate(EntityObjectBase objEntity, QueryConditionDecorator queryCondition, SqlConnection conn, SqlTransaction tran) { TableAttributes taEntity = objEntity.GetEntityColumns(); string strUpColumns = String.Empty; ArrayList lstPara = new ArrayList(); for (int i = 0; i < taEntity.Columns.Count; i++) { // 组装参数 ColumnAttributes caCurrent = taEntity.Columns[i] as ColumnAttributes; if (caCurrent.IsModify) { strUpColumns += caCurrent.ColumnName + "=@Up_" + caCurrent.ColumnName + ","; } else { continue; } //创建参数 SqlParameter paraCurrent = new SqlParameter("@Up_" + caCurrent.ColumnName, caCurrent.Value); lstPara.Add(paraCurrent); } if (String.Empty == strUpColumns.Trim()) { return(0); } SqlParameter[] objPara = new SqlParameter[lstPara.Count]; for (int i = 0; i < lstPara.Count; i++) { objPara[i] = lstPara[i] as SqlParameter; } // 生成更新条件 string strCondition = String.Empty; if (null != queryCondition) { QueryCondition condition = new QueryCondition(); condition.ConnectTo(queryCondition); ArrayList lstSqlParameter = new ArrayList(); strCondition += condition.MakeCondition(lstSqlParameter); SqlParameter[] paraQuery = new SqlParameter[lstSqlParameter.Count]; for (int i = 0; i < lstSqlParameter.Count; i++) { paraQuery[i] = lstSqlParameter[i] as SqlParameter; } // 组合参数 SqlParameter[] paraTemp = new SqlParameter[objPara.Length]; objPara.CopyTo(paraTemp, 0); objPara = new SqlParameter[paraTemp.Length + paraQuery.Length]; paraTemp.CopyTo(objPara, 0); paraQuery.CopyTo(objPara, paraTemp.Length); } string strSql = "UPDATE [TableName] SET [Columns] [Condition]"; // 替换当前表数据 strSql = strSql.Replace("[TableName]", taEntity.TableName); strSql = strSql.Replace("[Columns]", strUpColumns.Substring(0, strUpColumns.Length - 1)); strSql = strSql.Replace("[Condition]", strCondition); // 执行查询 if (null != conn) { return(SqlHelper.ExecuteNonQuery(conn, CommandType.Text, strSql, objPara)); } else { return(SqlHelper.ExecuteNonQuery(tran, CommandType.Text, strSql, objPara)); } }
/// <summary> /// 并发更新记录映射 /// </summary> /// <param name="objEntity">实体</param> /// <param name="conn">数据库连接</param> /// <param name="tran">数据库操作事务</param> /// <returns>影响的行数</returns> /// <exception cref="ConcurrentException">ConcurrentException</exception> private static int ConcurrentUpdate(EntityObjectBase objEntity, SqlConnection conn, SqlTransaction tran) { TableAttributes taEntity = objEntity.GetEntityColumns(); string strUpColumns = String.Empty; string strKeyColumns = String.Empty; string strOldVersion = String.Empty; string strKey = String.Empty; ArrayList lstPara = new ArrayList(); for (int i = 0; i < taEntity.Columns.Count; i++) {// 组装参数 ColumnAttributes caCurrent = taEntity.Columns[i] as ColumnAttributes; if (caCurrent.IsPrimaryKey) { strKeyColumns += caCurrent.ColumnName + "=@" + caCurrent.ColumnName + " AND "; strKey += caCurrent.ColumnName + " = " + caCurrent.Value.ToString() + " "; } else if (caCurrent.IsVersionNumber) { strUpColumns += caCurrent.ColumnName + "=" + caCurrent.ColumnName + " + 1,"; strOldVersion = caCurrent.ColumnName + "=@Old" + caCurrent.ColumnName + " AND "; //创建参数 SqlParameter paraOldCurrent = new SqlParameter("@Old" + caCurrent.ColumnName, caCurrent.Value); lstPara.Add(paraOldCurrent); continue; } else if (caCurrent.IsModify) { strUpColumns += caCurrent.ColumnName + "=@" + caCurrent.ColumnName + ","; } else { continue; } //创建参数 SqlParameter paraCurrent = new SqlParameter("@" + caCurrent.ColumnName, caCurrent.Value); lstPara.Add(paraCurrent); } SqlParameter[] objPara = new SqlParameter[lstPara.Count]; for (int i = 0; i < lstPara.Count; i++) { objPara[i] = lstPara[i] as SqlParameter; } string strCondition = strKeyColumns + strOldVersion; string strSql = "UPDATE [TableName] SET [Columns] WHERE [Condition]"; // 替换当前表数据 strSql = strSql.Replace("[TableName]", taEntity.TableName); strSql = strSql.Replace("[Columns]", strUpColumns.Substring(0, strUpColumns.Length - 1)); strSql = strSql.Replace("[Condition]", strCondition.Substring(0, strCondition.Length - 5)); int upCount = 0; if (null != conn) { upCount = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, strSql, objPara); } else { upCount = SqlHelper.ExecuteNonQuery(tran, CommandType.Text, strSql, objPara); } if (upCount <= 0) { throw new ConcurrentException(taEntity.TableName, strKey); } return(upCount); }
/// <summary> /// 新建记录映射 /// </summary> /// <param name="objEntity">实体</param> /// <param name="tran">数据库事务</param> /// <returns>自增字段值或影响行数</returns> private static long Create(EntityObjectBase objEntity, SqlConnection conn, SqlTransaction tran) { TableAttributes taEntity = objEntity.GetEntityColumns(); string strColumns = ""; string strParaColumns = ""; bool bIsIdentity = false; ArrayList lstPara = new ArrayList(); for (int i = 0; i < taEntity.Columns.Count; i++) {// 组装参数 ColumnAttributes caCurrent = taEntity.Columns[i] as ColumnAttributes; if (caCurrent.IsIdentity) { bIsIdentity = true; continue; } strColumns += caCurrent.ColumnName + ","; strParaColumns += "@" + caCurrent.ColumnName + ","; SqlParameter paraCurrent = null; if (caCurrent.IsVersionNumber) { paraCurrent = new SqlParameter("@" + caCurrent.ColumnName, 1); } else { //创建参数 paraCurrent = new SqlParameter("@" + caCurrent.ColumnName, caCurrent.Value); } lstPara.Add(paraCurrent); } SqlParameter[] objPara = new SqlParameter[lstPara.Count]; for (int i = 0; i < lstPara.Count; i++) { objPara[i] = lstPara[i] as SqlParameter; } string strSql = "INSERT INTO [TableName]([Columns]) VALUES([ParaColumns])"; // 替换当前表数据 strSql = strSql.Replace("[TableName]", taEntity.TableName); strSql = strSql.Replace("[Columns]", strColumns.Substring(0, strColumns.Length - 1)); strSql = strSql.Replace("[ParaColumns]", strParaColumns.Substring(0, strParaColumns.Length - 1)); if (bIsIdentity) { // 带自增字段的插入 strSql += " SELECT @@IDENTITY"; if (null != conn) { return(long.Parse(SqlHelper.ExecuteScalar(conn, CommandType.Text, strSql, objPara).ToString())); } else { return(long.Parse(SqlHelper.ExecuteScalar(tran, CommandType.Text, strSql, objPara).ToString())); } } else { // 不带自增字段的插入 if (null != conn) { return(SqlHelper.ExecuteNonQuery(conn, CommandType.Text, strSql, objPara)); } else { return(SqlHelper.ExecuteNonQuery(tran, CommandType.Text, strSql, objPara)); } } }