link static data exception
tanglixu
Posts: 2
please repair this bug,tks
1.execute this sql
2.link static data
3.throw exception
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
Thanks for your post!
I have logged a support ticket for you and will email shortly!
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com