3.1 数据访问 - DBUtils常用方法

ExecuteExecute方法用于不需要返回数据的执行语句，如DDL、UPDATE、DELETE语句。返回值为影响的行数。 Execute方法每次仅执行一条语句，对SQLServer支持多条语句一起执行，因此多条语句拼在一起执行没有问题，但Oracle下执行会报错。 如果需要执行多条语句，请调用批量执行方法：ExecuteBatch。 如果要执行SELECT语句，建议使用ExecuteReader方法。执行DDL：
[tr][td=65%]C#[td]
[tr][td=2,1]DBUtils.Execute(context, "CREATE TABLE T_TEMP(FID INT NOT NULL, FNAME VARCHAR(10) NULL ) ");
以下语句未使用参数化，会导致数据库硬解析影响性能。
[tr][td=65%]C#[td]
[tr][td=2,1]foreach (FieldValue val in fieldValues){ string sql = string.Format("UPDATE T_DEMO set FValue = {0} where FID = {1} ", val.Value, val.Id); //注意此处val.Value不应该拼接，应使用参数化 DBUtils.Execute(Context, sql);}
执行带参数的语句：
[tr][td=65%]C#[td]
[tr][td=2,1]sql = "INSERT INTO t_temp(FId, FType, FKernelXml, FName, FDevType) VALUES (@FId, @FType, @FKernelXml, @FName, @FDevType)";List paramList = new List();paramList.Add(new SqlParam("@FId", KDDbType.AnsiString, formID)); //非unicode（varchar类型）paramList.Add(new SqlParam("@FType", KDDbType.Int64, metadata.Type));paramList.Add(new SqlParam("@FKernelXml", KDDbType.Xml, xml));paramList.Add(new SqlParam("@FName", KDDbType.String, metadata.Name)); //unicode字符（nvarchar类型）paramList.Add(new SqlParam("@FDevType", KDDbType.Int32, (int)metadata.DevType)); DBUtils.Execute(this.Context, sql, paramList);

ExecuteDynamicObject执行指定的SQL并返回默认的一批使用DynamicObject集合描述的数据。注：该方法内部是调用ExecuteDataReader，如果不是必须返回DynamicObject类型，建议使用ExecuteDataReader更好。 下面例子将获取的DynamicObject对象填充到单据体中。
[tr][td=65%]C#[td]
[tr][td=2,1]//在表单插件中，应调用服务接口来获取数据，不能直接访问App服务DynamicObjectCollection dcObjs = DBServiceHelper.ExecuteDynamicObject(this.View.Context, sql, null, null, System.Data.CommandType.Text, param); //获取单据体数据包DynamicObjectCollection dyCollection = (DynamicObjectCollection)this.View.Model.DataObject[EitityKey];dyCollection.Clear(); DynamicObject dyObj;foreach (var obj in dcObjs){ dyObj = (DynamicObject)dyCollection.DynamicCollectionItemPropertyType.CreateInstance(); dyObj["FDataCenterId"] = obj["FDataCenterId"]; dyObj["FDCNumber"] = obj["FNumber"]; dyObj["FDCName"] = obj["FName"]; if (!string.IsNullOrWhiteSpace(_currentDataCenterId) && dyObj["FDataCenterId"].Equals(_currentDataCenterId)) { dyObj["FStatus"] = Kingdee.BOS.Resource.ResManager.LoadKDString("启用", "002014030004624", Kingdee.BOS.Resource.SubSystemType.BOS); _isEnable = true; } else { dyObj["FStatus"] = ""; } dyCollection.Add(dyObj);}
服务接口使用的DynamicObject类型时，用ExecuteDynamicObject方法取数更方便，不需要自己转换。
[tr][td=65%]C#[td]
[tr][td=2,1]string sql = @"select FUserID, FName, FFORBIDSTATUS FROM t_sec_user ";DynamicObjectCollection charts = DBUtils.ExecuteDynamicObject(ctx, sql); ... ISaveService saveService = App.ServiceHelper.GetService();saveService.Save(ctx, charts.ToArray());
以下例子ExecuteDynamicObject应该用ExecuteDataReader更合适：
[tr][td=65%]C#[td]
[tr][td=2,1]List orgList = new List(); string sql = "SELECT FCreateOrgId FROM t_org_bdctrlpolicy WHERE FBaseDataTypeId = @formId ";SqlParam[] paramList = new SqlParam[1];paramList[0] = new SqlParam("@formId", DbType.String, formId); DynamicObjectCollection collections = DBUtils.ExecuteDynamicObject(ctx, sql, paramList: paramList); foreach (DynamicObject dr in collections){ //此处用不到DynamicObject特性，不应该用ExecuteDynamicObject方法 orgList.Add(Convert.ToInt64(dr["FCreateOrgId"]));}
使用ExecuteDynamicObject获取实体类数据，按实体类定义的属性获取。
[tr][td=65%]C#[td]
[tr][td=2,1]/// 用户的实体类[Serializable, DataEntityType(Alias = "t_sec_user")]public class User : DynamicObjectView{ ///

KDUser的动态类型

public static readonly DynamicObjectType UserType = new DynamicObjectType( "User", attributes: new DataEntityTypeAttribute() { Alias = "t_sec_user" }); public User(DynamicObject obj) : base(obj) { } public static User Create() { User kdUser = new User(new DynamicObject(UserType)); return kdUser; } ///

支持从DynamicObject隐式转化到KDUser类型

/// 要隐式转换的动态实体 /// 新构建的KDUser对象 public static implicit operator User(DynamicObject obj) { return new User(obj); } #region FUserID User的主键 ///

FUserID的属性描述符

public static DynamicProperty UserIDProperty = UserType.RegisterSimpleProperty("UserID", typeof(int), attributes: new SimplePropertyAttribute(true) { Alias = "FUserID" }); ///

/// 返回/设置 FUserID的主键 ///

public int UserID { get { return (int)UserIDProperty.GetValue(this.DataEntity); } set { UserIDProperty.SetValue(this.DataEntity, value); } } #endregion //用户实体的其他属性...} //调用示例：//通过User.UserType获取User实体对象string sql = @"select FUserID, FName, FFORBIDSTATUS FROM t_sec_user where FUserID = @FUserID"; //与实体定义中的属性匹配，否则对应的实体属性会为空DynamicObjectCollection userCollection = DBUtils.ExecuteDynamicObject(ctx, sql, User.UserType, paramList: new SqlParam[] { new SqlParam("@FUserID", DbType.Int64, ctx.UserId) });User user = userCollection[0];

ExecuteReader执行指定的SQL并返回DataReader数据。支持KSQL语法，若要执行SQLServer或Oracle语法，请在语句前加方言标识/*dialect*/。 1. 批量查询方法：IDataReader ExecuteReader(Context ctx, BatchSqlParam param, string selectFieldSql, string where = "")用于不同参数批量取数，例如：参数说明：BatchSqlParam - 批量参数（批量执行参数。仅用于单表批量执行，where只支持简单And逻辑）BatchSqlParam类说明如下：
[tr][td=65%]C#[td]
[tr][td=2,1]///

/// 表名和更新的数据。sqlserver数据库dt的列名将做为临时表列名///

/// 要操作的表名（查询时是查询的主表，即from的表）/// 需要批量查询、更新、删除的数据/// 创建临时表sql,字段名必须与dt列名一致（仅在sqlserver使用，可不指定，默认只支持Varchar(200),decimal(23,10), bigint, datetime ）BatchSqlParam(string tableName, DataTable dt, string createTempTableSQL = "") ///

/// update语句Set字段///

/// 列名，必须与DataTable列名匹配/// 参数类型/// 真实字段名(要set的字段)/// AddSetExpression(string columnName, KDDbType dbType, string fieldName) ///

/// where条件，只支持And逻辑///

/// 列名，必须与DataTable列名匹配/// 参数类型/// 真实字段名/// AddWhereExpression(string columnName, KDDbType dbType, string fieldName, string tableAliases = "") ///

/// Join 语句///

/// 完整join语句，例如： "left join t_bas_Object t2 on t1.fid = t2.fid " AddJoinExpression(string joinExpr)
调用示例如下：
[tr][td=65%]C#[td]
[tr][td=2,1]DataTable dt = new DataTable();dt.Columns.Add("f1");DataRow row = dt.NewRow();row[0] = 16394;dt.Rows.Add(row);row = dt.NewRow();row[0] = 80043;dt.Rows.Add(row);//BatchSqlParam构造函数TableName为 from 的表名BatchSqlParam batchParam = new BatchSqlParam("T_BAS_OPERATELOG", dt);batchParam.TableAliases = "t1";batchParam.AddWhereExpression("f1", KDDbType.Int32, "FUSERID", "t1");batchParam.AddJoinExpression("inner join t_sec_user t2 on t1.fuserid = t2.fuserid");using (IDataReader dr = DBUtils.ExecuteReader(ctx, batchParam, "t1.FID, t1.FUserId, t2.FName", " t1.flogonorgid = 1 ")){ while (dr.Read()) { object obj = dr[0]; object userid = dr[1]; object name = dr[2]; }}
以上执行的语句：SELECT t1.FID, t1.FUserId, t2.FName FROM T_BAS_OPERATELOG t1 inner join t_sec_user t2 on t1.fuserid = t2.fuserid where t1.FUSERID = :f1 and t1.flogonorgid = 1 注意：Datatable的列名"f1"必须与AddWhereExpression的参数"f1"一致。 2. 执行语句：
[tr][td=65%]Overload[td=33%]Description
[tr][td=65%]ExecuteReader(Context,String)[td=33%]流方式读SQL查询结果
[tr][td=65%]ExecuteReader(Context,String,CommandType)[td=33%]返回DataReader，指定命令类型
[tr][td=65%]ExecuteReader(Context,String,SqlParam)[td=33%]返回DataReader，指定参数
[tr][td=65%]ExecuteReader(Context,String,SqlParam,CommandType)[td=33%]返回DataReader，指定参数和命令类型
[tr][td=65%]ExecuteReader(Context,String,List)[td=33%]返回DataReader，指定多个参数
[tr][td=65%]ExecuteReader(Context,String,IEnumerable,CommandType)[td=33%]返回DataReader，指定多个参数和命令类型
示例：根据id取用户参数。
[tr][td=65%]C#[td]
[tr][td=2,1]List paramList = new List();string sql = @"SELECT FKEY, FUSERID, FPARAMETERS FROM T_BAS_USERPARAMTER WHERE FID = @Fid";paramList.Add(new SqlParam("@FId", DbType.AnsiString, sid)); using (IDataReader rs = DBUtils.ExecuteReader(Context, sql, paramList)){ while (rs.Read()) { ParamInfo userParam = new ParamInfo(); userParam.Key = dr.GetString("FKEY"); userParam.UserId = dr.GetString("FUSERID"); userParam.Parameter = dr.GetString("FPARAMETERS"); userParamList.Add(userParam); }}

ExecuteScalar执行指定的SQL并返回第一行第一列数据。
[tr][td]Overload[td]Description
[tr][td]ExecuteScalar(Context,String,List)[td=70%]返回数据的第一行第一列
[tr][td]ExecuteScalar(Context,String,T,SqlParam[])[td=70%]执行某个SQL并获取第一行第一列的值，如果数据未找到或为DBNULL，那么将返回缺省值
参数说明：
[tr][td=65%]C#[td]
[tr][td=2,1]///

/// 执行某个SQL并获取第一行第一列的值，如果数据未找到或为DBNULL，那么将返回缺省值///

/// 返回的数据类型/// 上下文/// 要执行的SQL语句/// 如果数据未找到或为DBNULL，那么将返回此值。如果T是int?，那么可以指定此参数为null，从而实现没有记录时为null而不是0的功能。/// 参数/// 结果T ExecuteScalar(Context ctx, string strSql, T defaultValue, params SqlParam[] paramList)
示例：
[tr][td=65%]C#[td]
[tr][td=2,1]string sql = @"SELECT COUNT(1) FROM T_DEMO ";int count = DBUtils.ExecuteScalar(Context, sql, null);

代码优化应用案例下面场景循环删除调汇记录，改为批量删除提升性能。
[tr][td]C#[td]
[tr][td=2,1]foreach (var data in lstData){ //先删除 sbDelete.Clear(); sbDelete.AppendFormat(" Delete from {0} where FACCOUNTBOOKID= {1}", TableConst.GL_ALLOCATEEXCHANGE, bookId); sbDelete.AppendFormat(" and FYEAR={0} and FPERIOD={1}", iYear, iPeriod); sbDelete.AppendFormat(" and FACCOUNTID={0} and FDETAILID={1} and FCURRENCYID={2}", data.AccountId, data.DetailId, data.CurrencyId); lstSQL.Add(new SqlObject(sbDelete.ToString(), new List())); //再insert List para = new List() { new SqlParam("@FACCOUNTBOOKID", DbType.Int64, bookId), new SqlParam("@FYEAR", DbType.Int32, iYear), new SqlParam("@FPERIOD", DbType.Int32, iPeriod), new SqlParam("@FACCOUNTID", DbType.Int64, data.AccountId), new SqlParam("@FDETAILID", DbType.Int64, data.DetailId), new SqlParam("@FCURRENCYID", DbType.Int64, data.CurrencyId), new SqlParam("@FENDBALANCEFOR", DbType.Decimal, data.EndBalance) , new SqlParam("@FENDBALANCE", DbType.Decimal, data.EndBalanceFor), new SqlParam("@FDIFFERENCE", DbType.Decimal, data.Difference) }; lstSQL.Add(new SqlObject(strInsertSQL, para));}if (lstSQL.Count > 0){ //如果lstData有1000条记录，此处将执行1000条删除再执行1000条插入 DBUtils.ExecuteBatch(ctx, lstSQL);}
改为如下：
[tr][td]C#[td]
[tr][td=2,1]DataTable dtDelete = this.CreateBatchTable();DataTable dtInsert = this.CreateBatchTable();dtInsert.TableName = TableConst.GL_ALLOCATEEXCHANGE;foreach (AllocateExData data in lstData){ //删除参数 DataRow drDelete = dtDelete.NewRow(); drDelete["FACCOUNTBOOKID"] = bookId; drDelete["FYEAR"] = iYear; drDelete["FPERIOD"] = iPeriod; drDelete["FACCOUNTID"] = data.AccountId; drDelete["FDETAILID"] = data.DetailId; drDelete["FCURRENCYID"] = data.CurrencyId; dtDelete.Rows.Add(drDelete); //insert参数 DataRow drInsert = dtInsert.NewRow(); drInsert["FACCOUNTBOOKID"] = bookId; drInsert["FYEAR"] = iYear; drInsert["FPERIOD"] = iPeriod; drInsert["FACCOUNTID"] = data.AccountId; drInsert["FDETAILID"] = data.DetailId; drInsert["FCURRENCYID"] = data.CurrencyId; drInsert["FENDBALANCEFOR"] = data.EndBalance; drInsert["FENDBALANCE"] = data.EndBalanceFor; drInsert["FDIFFERENCE"] = data.Difference; dtInsert.Rows.Add(drInsert);}BatchSqlParam delSqlParam = new BatchSqlParam(TableConst.GL_ALLOCATEEXCHANGE, dtDelete);delSqlParam.AddWhereExpression("FACCOUNTBOOKID", KDDbType.Int64, "FACCOUNTBOOKID");delSqlParam.AddWhereExpression("FYEAR", KDDbType.Int32, "FYEAR");delSqlParam.AddWhereExpression("FPERIOD", KDDbType.Int32, "FPERIOD");delSqlParam.AddWhereExpression("FACCOUNTID", KDDbType.Int64, "FACCOUNTID");delSqlParam.AddWhereExpression("FDETAILID", KDDbType.Int64, "FDETAILID");delSqlParam.AddWhereExpression("FCURRENCYID", KDDbType.Int64, "FCURRENCYID"); DBUtils.BatchDelete(ctx, delSqlParam);//批量插入DBUtils.BulkInserts(ctx, dtInsert);