金蝶DBUtils的用法

原创已于 2024-07-16 11:38:05 修改 · 1.2k 阅读

21 ·

CC 4.0 BY-SA版权

文章标签：

#c# #金蝶云星空

于 2024-07-16 11:37:47 首次发布

金蝶云星空专栏收录该内容

2 篇文章

订阅专栏

Execute方法用于不需要返回数据的执行语句，如DDL、UPDATE、DELETE语句。返回值为影响的行数。 Execute方法每次仅执行一条语句，对SQLServer支持多条语句一起执行，因此多条语句拼在一起执行没有问题，但Oracle下执行会报错。如果需要执行多条语句，请调用批量执行方法：ExecuteBatch。如果要执行SELECT语句，建议使用ExecuteReader方法。

执行DDL：

DBUtils.Execute(context, "CREATE TABLE T_TEMP(FID INT NOT NULL, FNAME VARCHAR(10) NULL ) ");

以下语句未使用参数化，会导致数据库硬解析影响性能。

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);

}

执行带参数的语句：

sql = "INSERT INTO t_temp(FId, FType, FKernelXml, FName, FDevType) VALUES (@FId, @FType, @FKernelXml, @FName, @FDevType)";

List<SqlParam> paramList = new List<SqlParam>();

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);

file:///C:/Users/rd_weixy/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png ExecuteDynamicObject

执行指定的SQL并返回默认的一批使用DynamicObject集合描述的数据。

注：该方法内部是调用ExecuteDataReader，如果不是必须返回DynamicObject类型，建议使用ExecuteDataReader更好。

下面例子将获取的DynamicObject对象填充到单据体中。

//在表单插件中，应调用服务接口来获取数据，不能直接访问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方法取数更方便，不需要自己转换。

string sql = @"select FUserID, FName, FFORBIDSTATUS FROM t_sec_user ";

DynamicObjectCollection charts = DBUtils.ExecuteDynamicObject(ctx, sql);

...

ISaveService saveService = App.ServiceHelper.GetService<ISaveService>();

saveService.Save(ctx, charts.ToArray<DynamicObject>());

以下例子ExecuteDynamicObject应该用ExecuteDataReader更合适：

List<long> orgList = new List<long>();

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获取实体类数据，按实体类定义的属性获取。

/// 用户的实体类

[Serializable, DataEntityType(Alias = "t_sec_user")]

public class User : DynamicObjectView

{

    /// <summary>KDUser的动态类型</summary>

    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;

    }

    /// <summary>支持从DynamicObject隐式转化到KDUser类型</summary>

    /// <param name="obj">要隐式转换的动态实体</param>

    /// <returns>新构建的KDUser对象</returns>

    public static implicit operator User(DynamicObject obj)

    { return new User(obj); }

    #region FUserID  User的主键

    /// <summary>FUserID的属性描述符</summary>

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

    /// <summary>

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

    /// </summary>

    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];

file:///C:/Users/rd_weixy/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png ExecuteReader

执行指定的SQL并返回DataReader数据。

支持KSQL语法，若要执行SQLServer或Oracle语法，请在语句前加方言标识/*dialect*/。

1. 批量查询

方法：

IDataReader ExecuteReader(Context ctx, BatchSqlParam param, string selectFieldSql, string where = "")

用于不同参数批量取数，例如：

参数说明：

BatchSqlParam - 批量参数（批量执行参数。仅用于单表批量执行，where只支持简单And逻辑）

BatchSqlParam类说明如下：

/// <summary>

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

/// </summary>

/// <param name="tableName">要操作的表名（查询时是查询的主表，即from的表）</param>

/// <param name="dt">需要批量查询、更新、删除的数据</param>

/// <param name="createTempTableSQL">创建临时表sql,字段名必须与dt列名一致（仅在sqlserver使用，可不指定，默认只支持Varchar(200),decimal(23,10), bigint, datetime ）</param>

BatchSqlParam(string tableName, DataTable dt, string createTempTableSQL = "")

/// <summary>

/// update语句Set字段

/// </summary>

/// <param name="columnName">列名，必须与DataTable列名匹配</param>

/// <param name="dbType">参数类型</param>

/// <param name="fieldName">真实字段名(要set的字段)</param>

/// <param name="value"></param>

AddSetExpression(string columnName, KDDbType dbType, string fieldName)

/// <summary>

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

/// </summary>

/// <param name="columnName">列名，必须与DataTable列名匹配</param>

/// <param name="dbType">参数类型</param>

/// <param name="fieldName">真实字段名</param>

/// <param name="tableAliases"></param>

AddWhereExpression(string columnName, KDDbType dbType, string fieldName, string tableAliases = "")

/// <summary>

/// Join 语句

/// </summary>

/// <param name="joinExpr">完整join语句，例如： "left join t_bas_Object t2 on t1.fid = t2.fid " </param>

AddJoinExpression(string joinExpr)

调用示例如下：

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. 执行语句：

Overload	Description
ExecuteReader(Context,String)	流方式读SQL查询结果
ExecuteReader(Context,String,CommandType)	返回DataReader，指定命令类型
ExecuteReader(Context,String,SqlParam)	返回DataReader，指定参数
ExecuteReader(Context,String,SqlParam,CommandType)	返回DataReader，指定参数和命令类型
ExecuteReader(Context,String,List<SqlParam>)	返回DataReader，指定多个参数
ExecuteReader(Context,String,IEnumerable<SqlParam>,CommandType)	返回DataReader，指定多个参数和命令类型

示例：

根据id取用户参数。

List<SqlParam> paramList = new List<SqlParam>();

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);

    }

}

执行指定的SQL并返回第一行第一列数据。

Overload	Description
ExecuteScalar(Context,String,List<SqlParam>)	返回数据的第一行第一列
ExecuteScalar<T>(Context,String,T,SqlParam[])	执行某个SQL并获取第一行第一列的值，如果数据未找到或为DBNULL，那么将返回缺省值

参数说明：

/// <summary>

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

/// </summary>

/// <typeparam name="T">返回的数据类型</typeparam>

/// <param name="ctx">上下文</param>

/// <param name="strSql">要执行的SQL语句</param>

/// <param name="defaultValue">如果数据未找到或为DBNULL，那么将返回此值。如果T是int?，那么可以指定此参数为null，从而实现没有记录时为null而不是0的功能。</param>

/// <param name="paramList">参数</param>

/// <returns>结果</returns>

T ExecuteScalar<T>(Context ctx, string strSql, T defaultValue, params SqlParam[] paramList)

示例：

string sql = @"SELECT COUNT(1) FROM T_DEMO ";

int count = DBUtils.ExecuteScalar(Context, sql, null);

下面场景循环删除调汇记录，改为批量删除提升性能。

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<SqlParam>()));

    //再insert

    List<SqlParam> para = new List<SqlParam>()

    {

        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);

}

改为如下：

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);