link static data exception

tanglixutanglixu Posts: 2
please repair this bug,tks

1.execute this sql
2.link static data
3.throw exception

CREATE TABLE dbo.SM_SqlTemplet_Main_Bak
	(
	CatalogID          VARCHAR (50) NOT NULL,
	TempletID          VARCHAR (100) NOT NULL,
	TempletName        NVARCHAR (100) NOT NULL,
	TempletContent     VARCHAR (max),
	TempletKind        INT NOT NULL,
	DispOrder          INT,
	GenerateScriptWay  VARCHAR (50),
	TempletExecuteMode VARCHAR (50),
	TempletReturnMode  VARCHAR (50),
	IsTransaction      INT NOT NULL,
	IsWriteLog         INT NOT NULL,
	EncryptConfig      VARCHAR (max),
	ServiceVisitMode   VARCHAR (50),
	ServiceTimeoutUrl  VARCHAR (max),
	CONSTRAINT PK_SM_SqlTemplet_Main_Bak PRIMARY KEY (TempletID)
	)
GO

EXEC(N'INSERT INTO [dbo].[SM_SqlTemplet_Main_Bak] ([TempletID], [CatalogID], [TempletName], [TempletContent], [TempletKind], [DispOrder], [GenerateScriptWay], [TempletExecuteMode], [TempletReturnMode], [IsTransaction], [IsWriteLog], [EncryptConfig], [ServiceVisitMode], [ServiceTimeoutUrl]) VALUES (N''1B7010C7584F4F21A077D79CBF26DF78'', N''57810B7CED14458AA114B05B5E0F21FC'', N''花名册数据_绑定数据'', cast(N''using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using MySoft.Common.Function;
using MySoft.HR.Utils.Modal.Core;
using MySoft.HR.Utils.Modal.Daily;

internal class DataService : DataServiceBase
{
    public string MusterID { get; set; }
    public string SetID { get; set; }
    public string DataWhere { get; set; }

    public object Execute(Dictionary<string, object> args)
    {
        MusterID = args.GetValueIf("MusterID", "A3C67A9EAAD2417EACE82A63AC99F952");
        SetID = args.GetValueIf("SetID", "A01");
        DataWhere = args.GetValueIf("DataWhere", "1=1");
        var dt = GetDataTable();
        return dt;
    }

    public MusterMainEntity GetMusterMainEntity()
    {
        var result = new MusterMainEntity();
        var sql = "SELECT * FROM Com_MusterSimple WHERE MusterID=''''{0}''''".FormatMe(MusterID);
        var dt = RS.Core.DirectDB.Select(sql);
        if (dt != null)
        {
            foreach (DataRow row in dt.Rows)
            {
                result.MusterID = row.GetString("MusterID", "");
                result.MusterName = row.GetString("MusterName", "");
                result.MusterModule = row.GetString("MusterModule", "");
                result.MusterIsPublic = row.GetString("MusterIsPublic", "");
                result.MusterSortFields = row.GetString("MusterSortFields", "ORDER BY DispOrder");
                result.MusterUserID = row.GetString("MusterUserID", "");
                result.MusterDataLimit = row.GetString("MusterDataLimit", "");
                result.DispOrder = row.GetInt32("DispOrder");
            }
        }
        return result;
    }
    public DataTable GetDataTable()
    {
        var entity = GetMusterMainEntity();
        var selectField = GetSelectFieldString();
        var sql = "SELECT {0} FROM  {1} Where {2}  ORDER BY {3}".FormatMe(selectField, SetID, DataWhere, entity.MusterSortFields);
        var dt = RS.Core.DirectDB.Select(sql);
        return dt;
    }

    public string GetSelectFieldString()
    {
        var strList = new List<string>();
        var sql =
            @"SELECT SM_SetItems.SetID,SM_SetItems.ItemID,SM_SetItems.ItemType,SM_SetItems.CodeID FROM Com_MusterSimpleFields  LEFT JOIN SM_SetItems ON  SM_SetItems.SetID=Com_MusterSimpleFields.SetID AND SM_SetItems.ItemID=Com_MusterSimpleFields.ItemID
              WHERE Com_MusterSimpleFields.MusterID=''''{0}''''".FormatMe(MusterID);
        var dt = RS.Core.DirectDB.Select(sql);
        if (dt != null)
        {
            foreach (DataRow row in dt.Rows)
            {
                var itemType = row.GetString("ItemType");
                var itemId = row.GetString("ItemID");
                var setId = row.GetString("SetID");
                var codeId = row.GetString("CodeID");
                if (itemType != "B" && itemType != "G")
                {
                    strList.Add("{0}.{1}".FormatMe(setId, itemId));
                }
                switch (itemType)
                {
                    case "C": //代码型
                        if (!codeId.IsNullOrEmpty() && (codeId == "N" || codeId == "M"))
                        {
                            strList.Add("(SELECT UnitName FROM B01 WHERE UnitID={0}.{1}) AS C_N_{1}".FormatMe(setId,
                                itemId));
                        }
                        else if (!codeId.IsNullOrEmpty() && codeId == "J")
                        {
                            strList.Add("(SELECT JobName FROM G01 WHERE JobID={0}.{1}) AS C_N_{1}".FormatMe(setId, itemId));
                        }
                        else if (!codeId.IsNullOrEmpty() && codeId == "C")
                        {
                            strList.Add("(SELECT PClassName FROM SM_PersonClass WHERE PClassID={0}.{1}) AS C_N_{1}".FormatMe(setId, itemId));
                        }
       '' COLLATE Chinese_PRC_CI_AS as varchar(8000))
+cast(N''                 else
                        {
                            strList.Add(
                                "(SELECT CodeItemName FROM SM_CodeItems WHERE CodeID=''''{0}'''' AND CodeItemID={1}.{2}) AS C_N_{2}"
                                    .FormatMe(
                                        codeId, setId, itemId));
                        }
                        break;
                    case "B": //多媒体型
                    case "G": //图片型
                        strList.Add("(CASE WHEN {0} IS NULL  THEN 0 ELSE 1 END ) AS {0}".FormatMe(itemId));
                        break;
                }
            }
        }
        var str = string.Empty;
        if (strList.Count > 0)
        {
            str = string.Join(",", strList);
        }

        return str;
    }

    public class MusterMainEntity
    {
        public string MusterName = string.Empty;
        public string MusterID = string.Empty;
        public string MusterSortFields = string.Empty;
        public string MusterModule = string.Empty;
        public string MusterIsPublic = string.Empty;
        public string MusterUserID = string.Empty;
        public string MusterDataLimit = string.Empty;
        public int DispOrder;
    }
}'' COLLATE Chinese_PRC_CI_AS as varchar(8000))
, 2, 1220, NULL, N''SelectDataTable'', N''DataTable'', 0, 0, NULL, NULL, NULL)')

Comments

Sign In or Register to comment.