C# SafeDataReader.GetDouble方法代码示例

        public override void CopyNavigationMenu()
        {
            var values = new List<string>();
            const string commandText = @"
SELECT Id, Name, SystemName, LastModifiedOn, IconURL, Sequence, IconId from NavigationGroups
";
            using (var ctx = GetMetaDatabaseConnectionManager())
            {
                using (var cmd = new SqlCommand(commandText, ctx.Connection))
                {
                    using (var reader = new SafeDataReader(cmd.ExecuteReader()))
                    {
                        while (reader.Read())
                        {
                            values.Add(string.Format("({0},'{1}','{2}','{3}','{4}',{5},{6})", reader.GetInt32(0), reader.GetString(1), reader.GetString(2),
                                reader.GetDateTime(3).ToString("yyyy-MM-dd HH:mm:ss"), reader.GetString(4), reader.GetDouble(5), reader.IsDBNull(6) ? "NULL" : reader.GetInt32(6).ToString()));
                        }
                    }
                }
            }

            var script = string.Format(@"
  
SET IDENTITY_INSERT __NavigationGroup ON
MERGE INTO [dbo].[__NavigationGroup] AS TARGET USING (
    VALUES {0}
                                                     ) 
        AS Source([Id], [Name], [SystemName], [LastModifiedOn], [IconURL], [Sequence],[Icon]) ON TARGET.id = Source.Id -- update matched rows
 WHEN MATCHED THEN
UPDATE
SET [Name] = Source.[Name],
    [SystemName] = Source.[SystemName],
    [LastModifiedOn] = Source.[LastModifiedOn],
    [IconURL] = Source.[IconURL],
    [Sequence] = Source.[Sequence],
    [Icon] = Source.[Icon],
    [IsRemoved] = 0  -- insert new rows
 WHEN NOT MATCHED BY TARGET THEN
INSERT ([Id],
        [Name],
        [SystemName],
        [LastModifiedOn],
        [IconURL],
        [Sequence],
        [Icon],
        [IsRemoved])
VALUES ([Id],
        [Name],
        [SystemName],
        [LastModifiedOn],
        [IconURL],
        [Sequence],
        [Icon],
        0)

 -- delete rows that are in the target but not the source
WHEN NOT MATCHED BY SOURCE THEN
DELETE ;

SET IDENTITY_INSERT __NavigationGroup OFF
", string.Join(",", values));

            ExecuteSql(script);

            values.Clear();
            const string navItemsCommandText = @"
SELECT ni.id, 
       ni.guid, 
       ni.NAME, 
       p.systemname, 
       NULL, 
       ni.lastmodifiedon, 
       sequence, 
       navigationgroupid, 
       ni.[description], 
       c.[Color],
       ni.IconId
            
   
FROM   [dbo].[navigationitems] ni 
       INNER JOIN publishedprocesses pp 
               ON pp.id = ni.publishedprocessid 
       INNER JOIN processes p 
               ON pp.processid = p.id AND p.IsPublishedCopy = 1
       LEFT OUTER JOIN  colors as c on c.Id = p.[ColorId]
       WHERE p.IsRemoved = 0
      
";
            using (var ctx = GetMetaDatabaseConnectionManager())
            {
                using (var cmd = new SqlCommand(navItemsCommandText, ctx.Connection))
                {
                    using (var reader = new SafeDataReader(cmd.ExecuteReader()))
                    {
                        while (reader.Read())
                        {
                            values.Add(string.Format(CultureInfo.InvariantCulture, "({0}, '{1}', '{2}', '{3}', '{4}', '{5}', {6}, {7}, '{8}', {9}, {10})", reader.GetInt32(0), reader.GetGuid(1), Escape(reader.GetString(2)), reader.GetString(3), reader.GetString(4),
                                reader.GetDateTime(5).ToString("yyyy-MM-dd HH:mm:ss"), reader.GetDouble(6), reader.GetInt32(7), Escape(reader.GetString(8)), reader.GetInt64(9), reader.IsDBNull(10) ? "null" : reader.GetInt32(10).ToString()));
                        }
                    }
//.........这里部分代码省略.........

        /// <summary>
        /// Retrieves numeric required step.
        /// </summary>
        /// <param name="processName">The process name.</param>
        /// <param name="fieldName">The field name.</param>
        /// <param name="isPublishedCopy">The is published copy.</param>
        /// <returns>The <see cref="NumericRequiredStepDto" />.</returns>
        public NumericRequiredStepDto FetchNumericRequiredStep(string processName, string fieldName, bool isPublishedCopy = false)
        {
            const string CmdText =
                @"
DECLARE @fieldId AS INT

SELECT @fieldId = f.Id
FROM
    [dbo].[Processes] p
    INNER JOIN [dbo].[Sections] s ON s.ProcessId = p.Id
    INNER JOIN [dbo].[Fields] f ON f.SectionId = s.Id
WHERE p.[SystemName] = @processName AND p.IsRemoved = 0 AND p.IsPublishedCopy = @isPublishedCopy AND f.SystemName = @fieldName;

EXEC [dbo].[GetNumericRequiredStep] @FieldId = @fieldId;
";

            using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
            {
                using (var cmd = new SqlCommand(CmdText, ctx.Connection))
                {
                    cmd.Parameters.AddWithValue("@processName", processName);
                    cmd.Parameters.AddWithValue("@fieldName", fieldName);
                    cmd.Parameters.AddWithValue("@isPublishedCopy", isPublishedCopy);

                    using (var reader = new SafeDataReader(cmd.ExecuteReader()))
                    {
                        if (reader.Read())
                        {
                            var dto = new NumericRequiredStepDto
                                          {
                                              Id = reader.GetInt32(0),
                                              NumericType = reader.GetInt32(1),
                                              NumberOfDigits = reader.GetInt32(2),
                                              Minimum = reader.GetDouble(3),
                                              Maximum = reader.GetDouble(4)
                                          };

                            return dto;
                        }
                    }
                }
            }

            return null;
        }

        /// <summary>
        /// Retrieves reverse cross ref required step.
        /// </summary>
        /// <param name="processName">The process name.</param>
        /// <param name="fieldName">The field name.</param>
        /// <param name="isPublishedCopy">The is published copy.</param>
        /// <returns>The <see cref="ReverseCrossRefRequiredStepDto" />.</returns>
        public ReverseCrossRefRequiredStepDto FetchReverseCrossRefRequiredStep(string processName, string fieldName, bool isPublishedCopy = false)
        {
            const string CmdText =
                @"
DECLARE @fieldId AS INT

SELECT @fieldId = f.Id
FROM
    [dbo].[Processes] p
    INNER JOIN [dbo].[Sections] s ON s.ProcessId = p.Id
    INNER JOIN [dbo].[Fields] f ON f.SectionId = s.Id
WHERE p.[SystemName] = @processName AND p.IsRemoved = 0 AND p.IsPublishedCopy = @isPublishedCopy AND f.SystemName = @fieldName;

EXEC [dbo].[GetReverseCrossRefRequiredStep] @FieldId = @fieldId;
";

            using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
            {
                using (var cmd = new SqlCommand(CmdText, ctx.Connection))
                {
                    cmd.Parameters.AddWithValue("@processName", processName);
                    cmd.Parameters.AddWithValue("@fieldName", fieldName);
                    cmd.Parameters.AddWithValue("@isPublishedCopy", isPublishedCopy);

                    using (var reader = new SafeDataReader(cmd.ExecuteReader()))
                    {
                        if (reader.Read())
                        {
                            var dto = new ReverseCrossRefRequiredStepDto
                                          {
                                              Id = reader.GetInt32(0),
                                              FieldId = reader.GetInt32(1),
                                              ReverseCrossRefProcessId = reader.GetNullableInt(2),
                                              CrossRefFieldName = reader.GetString(3),
                                              DisplayFieldName = reader.GetString(4),
                                              DisplayMultiple = reader.GetBoolean(5)
                                          };

                            if (reader.NextResult())
                            {
                                while (reader.Read())
                                {
                                    var df = new ReverseCrossReferenceDisplayFieldDto
                                                 {
                                                     Id = reader.GetInt32(0),
                                                     DisplayName = reader.GetString(1),
                                                     FullPath = reader.GetString(2),
                                                     Order = reader.GetDouble(3)
                                                 };

                                    dto.SelectedDisplayFields.Add(df);
                                }
                            }

                            return dto;
                        }
                    }
                }
            }

            return null;
        }

        /// <summary>
        /// Fetches the navigation group edit list.
        /// </summary>
        /// <param name="currentLocalizationId">The current localization identifier.</param>
        /// <returns>IList{NavigationGroupEditDto}.</returns>
        public IList<NavigationGroupEditDto> FetchNavigationGroupEditList(int currentLocalizationId)
        {
            if (currentLocalizationId == 0)
                currentLocalizationId = GetLocIdByCultureName(Thread.CurrentThread.CurrentUICulture.Name);

            var result = new List<NavigationGroupEditDto>();

            const string sql =
                @"
SELECT
     [Id]
    ,[Guid]
    ,ISNULL(l.[Name], g.[Name]) as Name
    ,[SystemName]
    ,[Sequence]
    ,[IconURL]
    ,[IconId]
FROM
    [dbo].[NavigationGroups] g left outer join [dbo].[NavigationGroupLocalizations] l on l.NavigationGroupId = g.Id and l.LocalizationId = @locId
";

            using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database.VeyronMeta, false))
            {
                var cn = ctx.Connection;
                if (cn.State != ConnectionState.Open)
                {
                    cn.Open();
                }

                using (var cmd = new SqlCommand(sql, cn))
                {
                    cmd.Parameters.AddWithValue("@locId", currentLocalizationId);

                    using (var reader = new SafeDataReader(cmd.ExecuteReader()))
                    {
                        while (reader.Read())
                        {
                            var navigationGroupDto = new NavigationGroupEditDto
                                                         {
                                                             Id = reader.GetInt(0),
                                                             Guid = reader.GetGuid(1),
                                                             Name = reader.GetString(2),
                                                             SystemName = reader.GetString(3),
                                                             Sequence = reader.GetDouble(4),
                                                             IconURL = reader.GetString(5),
                                                             IconId = reader.GetNullableInt(6)
                                                         };

                            //FetchGroupNavigationItems(cn, navigationGroupDto);
                            //FetchNavigationGroupSecurityConfigurations(cn, navigationGroupDto);

                            result.Add(navigationGroupDto);
                        }
                    }

                    foreach (var navigationGroupDto in result)
                    {
                        FetchGroupNavigationItems(cn, navigationGroupDto, currentLocalizationId);
                        FetchNavigationGroupSecurityConfigurations(cn, navigationGroupDto);
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// Fetches the group navigation items.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="dto">The dto.</param>
        /// <param name="currentLocalizationId">The current localization identifier.</param>
        private static void FetchGroupNavigationItems(SqlConnection connection, NavigationGroupEditDto dto, int currentLocalizationId)
        {
            const string sql =
                @"
 SELECT
      nv.[Id]
     ,nv.[Guid]
     ,ISNULL(pl.ProcessName, p.[Name]) + (CASE WHEN ISNULL(pvl.Name, pv.Name) IS NULL THEN '' ELSE ' - ' + ISNULL(pvl.Name, pv.Name) END)
     ,p.[SystemName]
     ,nv.[Sequence]
     ,nv.[PublishedProcessId]
     ,nv.[IconURL]
     ,p.[IconId]
     ,nv.[ProcessViewGuid]
 FROM
    [dbo].[NavigationItems] nv
	INNER JOIN [dbo].[PublishedProcesses] pp ON nv.[PublishedProcessId] = pp.[Id]
	INNER JOIN [dbo].[Processes] p ON pp.[ProcessId] = p.[Id]
    INNER JOIN Processes p2 ON pp.ProcessGuid = p2.[Guid] and p2.[IsPublishedCopy] = 0
	LEFT JOIN [dbo].[ProcessViews] pv ON pv.[Guid] = nv.ProcessViewGuid AND pv.ProcessId = p.Id
	LEFT OUTER JOIN dbo.ProcessLocalizations pl ON p2.Id = pl.ProcessId AND pl.LocalizationId = @locId
	LEFT OUTER JOIN dbo.ProcessViewLocalizations pvl ON p2.Id = pl.ProcessId AND pvl.LocalizationId = @locId
WHERE 
       p.[IsRemoved] = 0 AND
       p.IsInactive = 0 AND
       nv.[NavigationGroupId] = @groupId
UNION

SELECT
     nv.[Id]
    ,nv.[Guid]
    ,nv.[Name]
    ,NULL
    ,nv.[Sequence]
    ,NULL
    ,nv.[IconURL]
    ,NULL
    ,NULL
FROM
    [dbo].[NavigationItems] nv
WHERE nv.[SystemName] IS NULL AND 
	  nv.[PublishedProcessId] IS NULL AND
      nv.[NavigationGroupId] = @groupId;
";

            using (var cmd = new SqlCommand(sql, connection))
            {
                cmd.Parameters.AddWithValue("@groupId", dto.Id);
                cmd.Parameters.AddWithValue("@locId", currentLocalizationId);

                using (var reader = new SafeDataReader(cmd.ExecuteReader()))
                {
                    while (reader.Read())
                    {
                        var navigationItemDto = new NavigationItemEditDto
                                                    {
                                                        Id = reader.GetInt(0),
                                                        Guid = reader.GetGuid(1),
                                                        Name = reader.GetString(2),
                                                        Sequence = reader.GetDouble(4),
                                                        PublishedProcessId = reader.GetNullableInt(5),
                                                        IconURL = reader.GetString(6),
                                                        IconId = reader.GetNullableInt(7),
                                                        ProcessViewGuid = reader.GetNullableGuid(8)
                                                    };

                        //FetchNavigationItemSecurityConfigurations(connection, navigationItemDto);
                        dto.NavigationItems.Add(navigationItemDto);
                    }
                }

                foreach (var navigationItemDto in dto.NavigationItems)
                {
                    FetchNavigationItemSecurityConfigurations(connection, navigationItemDto);
                }
            }
        }

        /// <summary>
        /// Reads process view fields.
        /// </summary>
        /// <param name="process">The process.</param>
        /// <param name="sr">The reader.</param>
        private static void ReadProcessViewFields(ProcessEditDto process, SafeDataReader sr)
        {
            sr.NextResult();

            int? sectionId = null;
            ProcessViewSectionEditDto section = null;

            while (sr.Read())
            {
                var fieldDto = new ProcessViewFieldEditDto
                {
                    Id = sr.GetInt32(0),
                    SectionId = sr.GetInt32(1),
                    Guid = sr.GetGuid(2),
                    TemplateFieldGuid = sr.GetGuid(3),
                    FieldSystemName = sr.GetString(4),
                    DisplayOrder = sr.GetDouble(5),
                    DisplayType = sr.GetString(6),
                    IconId = sr.GetNullableInt(7),
                    CustomConfig = sr.GetString(8)
                };

                if (fieldDto.SectionId != sectionId)
                {
                    section = process.ViewList.SelectMany(v => v.SectionList).First(s => s.Id == fieldDto.SectionId);
                    sectionId = fieldDto.SectionId;
                }

                section.FieldList.Add(fieldDto);
            }
        }

        /// <summary>
        /// The read KPIs.
        /// </summary>
        /// <param name="process">The process.</param>
        /// <param name="sr">The reader.</param>
        private static void ReadKpis(ProcessEditDto process, SafeDataReader sr)
        {
            sr.NextResult();

            while (sr.Read())
            {
                process.Kpis.Add(new ProcessKpiEditDto
                {
                    Id = sr.GetInt32(0),
                    ProcessId = sr.GetInt32(1),
                    MetricGuid = sr.GetString(2),
                    LastModifiedOn = sr.GetDateTime(3),
                    GuidId = sr.GetString(4),
                    Name = sr.GetString(5),
                    TargetValue = sr.GetDouble(6),
                    FavorableDirection = sr.GetString(7),
                    GreenIconUrl = sr.GetString(8),
                    GreenIconId = sr.GetInt32(9, null),
                    GreenValue = sr.GetDouble(10),
                    YellowIconUrl = sr.GetString(11),
                    YellowIconId = sr.GetInt32(12, null),
                    YellowValue = sr.GetDouble(13),
                    RedIconUrl = sr.GetString(14),
                    RedIconId = sr.GetInt32(15, null),
                    FilterGuid = sr.GetString(16, null),
                    FilterDefinition = sr.GetString(17, null),
                    Documentation = sr.GetString(18),
                });
            }
        }

        /// <summary>
        /// Reads fields.
        /// </summary>
        /// <param name="process">The process.</param>
        /// <param name="sr">The reader.</param>
        private void ReadFields(ProcessEditDto process, SafeDataReader sr)
        {
            sr.NextResult();

            int? sectionId = null;
            SectionDto section = null;
            var times = new List<double>();

            while (sr.Read())
            {
                var start = DateTime.Now;
                var fieldDto = new FieldDto
                {
                    Id = sr.GetInt32(0),
                    Name = sr.GetSafeString(1, string.Empty).Replace(@"""", "''"),
                    FieldTypeId = sr.GetInt32(2),
                    SectionId = sr.GetInt32(3),
                    Width = sr.GetDouble(4),
                    RowSpan = sr.GetInt(5),
                    ShowInList = sr.GetBoolean(6),
                    IncludeInFilter = sr.GetBoolean(7),
                    HideFromDetails = sr.GetBoolean(8),
                    SystemName = sr.GetString(9),
                    Position = sr.GetInt32(10),
                    CopyFieldValueOnCopyItem = sr.GetBool(11),
                    DeepCopy = sr.GetBool(12),
                    Guid = sr.GetGuid(13),
                    SearchPosition = sr.GetInt(14),
                    SearchWidth = sr.GetInt(15),
                    IsBase = sr.GetBoolean(16),
                    UseInGlobalSearch = sr.GetBoolean(19),
                    PublishedCopyId = sr.GetNullableInt(21),
                    AllowLocalizedData = sr.GetBoolean("AllowLocalizedData")
                };

                if (fieldDto.SectionId != sectionId || section == null)
                {
                    section = process.Sections.First(s => s.Id == fieldDto.SectionId);
                    sectionId = fieldDto.SectionId;
                }

                fieldDto.FieldTypeInfo = new FieldTypeDto
                {
                    Id = fieldDto.FieldTypeId,
                    Name = sr.GetString(17),
                    DataType = sr.GetString(18),
                    CanBeRequired = sr.GetBoolean(20)
                };

                section.FieldList.Add(fieldDto);

                times.Add((DateTime.Now - start).TotalMilliseconds);
            }

            Profiler.Profile(() => this.ReadFieldEditors(process, sr));
        }

        /// <summary>
        /// Reads sections.
        /// </summary>
        /// <param name="process">The process.</param>
        /// <param name="reader">The reader.</param>
        private void ReadSections(ProcessEditDto process, SafeDataReader reader)
        {
            reader.NextResult();
            while (reader.Read())
            {
                var sectionDto = new SectionDto
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    Guid = reader.GetGuid(2),
                    PaperclipsEnabled = reader.GetBoolean(3),
                    IsBase = reader.GetBoolean(4),
                    Position = reader.GetDouble(6)
                };

                process.Sections.Add(sectionDto);
            }

            Profiler.Profile(() => this.ReadFields(process, reader));
        }

        /// <summary>
        /// The read states.
        /// </summary>
        /// <param name="process">The process.</param>
        /// <param name="sr">The reader.</param>
        private static void ReadStates(ProcessEditDto process, SafeDataReader sr)
        {
            sr.NextResult();

            while (sr.Read())
            {
                var stateDto = new StateDto
                {
                    Id = sr.GetInt32(0),
                    Name = sr.GetString(1),
                    Documentation = sr.GetString(2),
                    DesignPositionLeft = sr.GetDouble(3),
                    DesignPositionTop = sr.GetDouble(4),
                    DesignDecisionPositionLeft = sr.GetDouble(5),
                    DesignDecisionPositionTop = sr.GetDouble(6),
                    Guid = sr.GetGuid(7),
                    MetaId = sr.GetInt32(8),
                    Color = sr.GetInt64("Color")
                };

                process.States.Add(stateDto);
            }

            ReadConnectors(process, sr);
        }