SQL - How to modify system-versioned temporal table schema in SQL Server

In SQL Server, Temporal Table is used to keep the full history of data change for table without doing any extra coding or efforts. It will allow to you get table data at any point of time of past. For more information about SQL Server temporal tables, please visit temporal table in SQL Server .

If you want to modify (add or modify or delete column) the existing temporal table schema, you need to make sure history table schema should be sync.

Here are steps how to add new column ‘ZipCode’ in system-version temporal table.

1.   Turn off System Versioning

ALTER TABLE [dbo].[Office] SET (SYSTEM_VERSIONING = OFF);

2.   Modify the existing both Tables schema ( Office and OfficeHistory )

ALTER TABLE [dbo].[Office] ADD ZipCode varchar(10) NULL;

ALTER TABLE [dbo].[OfficeHistory] ADD ZipCode varchar(10) NULL;

3.   If data update is needed in history table, do it

Update [dbo].[OfficeHistory] SET ZipCode = '43212' where City = 'NYK'

4.   Turn on System Version option

        ALTER TABLE [dbo].[Office]

        SET

         (

            SYSTEM_VERSIONING = ON

           ( HISTORY_TABLE = [dbo].[OfficeHistory])

         );

Tables:

CREATE TABLE dbo.OfficeHistory(

     OfficeID int NOT NULL 

   , OfficeName varchar(50) NOT NULL

   , Street   varchar(50) NOT NULL

   , City varchar(50) NOT NULL

   , State varchar(2) NOT NULL

   ,SysStartTime   datetime2  NOT NULL,

   ,SysEndTime datetime2  NOT NULL,

)

CREATE TABLE Office  

(   

    OfficeID int NOT NULL PRIMARY KEY CLUSTERED

   , OfficeName varchar(50) NOT NULL

   , Street   varchar(50) NOT NULL

   , City varchar(50) NOT NULL

   , State varchar(2) NOT NULL

   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL

   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL

   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)    

)  

WITH   

   (  

      SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OfficeHistory)  

   )

Other blogs related to Temporal Table:

SQL - How to create temporal table in SQL Server

SQL - How to truncate system version temporal table

C# Regular Expression - Starting and Ending words

In C # programming, regular expression is used to validate user input and to validate the starting of words, we use caret (^) and for ending words, we use dollar ($).

In C#, The Regex class is used to verify the specific character pattern and it offers several methods and properties to parse input string and verify the specific patterns.

Match the beginning of the string:

Caret (^) is used to matches the beginning of the string

     string regExPattern = "^t";

            Regex regex = new Regex(regExPattern);

            Console.WriteLine(regex.IsMatch("tom"));

            // true

            Console.WriteLine(regex.IsMatch("dom"));

            // false

            Console.WriteLine(regex.IsMatch("jon"));

            // false

Match the ending of the string:

Dollar ($) is used to matches the ending of the string

      string regExPattern = @".com$";

            Regex regex = new Regex(regExPattern);

            Console.WriteLine(regex.IsMatch("tom@mail.com"));

            // true

            Console.WriteLine(regex.IsMatch("dom"));

            // false

            Console.WriteLine(regex.IsMatch("jom@mail.com"));

            // true

Blog Links Hub

This page is a links hub of all major post related to ASP.NET, MVC, C#, ASP.NET Web API, SQL Server and SSRS/SSIS

ASP.NET /ASP.NET MVC:	ASP.NET WEB API:
ASP.NET Page Life Cycle What is ASP.NET HttpRuntime URL.Action return &in URL ASP.NET MVC:  HTML.ACTIONLINK VS URL.ACTION ASP.NET MVC : Anchor Tag IN  MVC Razor View ASP.NET MVC : OutPutCache Action Filter ASP.NET MVC how to disable Caching     ASP.NET MVC - Ajax Get  Method not hitting the ASP.NET MVC Controllers ASP.NET MVC  - View not refreshing after postback	The Lifecycle of an ASP.NET Web API Web API over WCF How To Create ASP.NET Web API Install Unity in Web API ASP.Net Web API : Principles of Designing Restful APIs ASP.NET Web API : HTTP status code standard of restful API Parameter Binding or Model Binding in ASP.NET Web API ASP.NET Web API : Steps To Improve ASP.NET Web API Performance ASP.NET Web API Action Filter with parameters ASP.NET Web API  Action Filter Optional parameters in ASP.NET Web API ASP.NET Web API Caching
C# :	SQL :
C# : Singleton Vs Static Class The SOLID Principles SOLID Principles - Single Responsibility Principle SOLID Principles - Open Closed Principle SOLID Principles - Liskov Substitution Principle SOLID Principles -  Interface Segregation Principle SOLID Principles - Dependency Inversion Principle
WCF :	SSRS/SSIS

                                                      

SSRS: How to export multiple RDLC report in a single excel with multiple spreadsheet

In this blog, we will discuss how to generate a single word or excel file from multiple RDLC report by using sub report control.

A sub report is a report control which is used to display another report inside a main report, it helps to re-use the existing RDLC report

Here are few basic steps to create a single excel file with multiple spreadsheet from multiple RDLC reports

Steps:

1.    Create a main RDLC report – Summary.RDLC :

2.    Add Rectangle Table Control :

3.    Add Sub-Report control inside rectangle control and include the existing RDLC report ( Child1.rdlc & Child2.rdlc) :

4.    Set Page Break Option for each rectangle control :

a.    Break Location  -  (End option as shown below )

b.    Page Name (Excel Spreadsheet Name : Child1)

5.    Set Data For Child1 and Child2 rdlc report :

There is a SubreportProcessingEventHandler event, which is getting fired for each sub reports, what Main RDLC report have. It means, if Main Report have two sub Report, it is getting called two times

In event handler, we can easily bind data source for each sub-reports.

Here is an example, how to bind the DataSource for two child reports :

        viewer.LocalReport.SubreportProcessing +=

        new SubreportProcessingEventHandler(exampleSubreportProcessingEventHandler);

        void exampleSubreportProcessingEventHandler(object sender, SubreportProcessingEventArgs e)

        {

           if (e.DataSourceNames.FirstOrDefault() == "dsChild1")

            {

               e.DataSources.Add(new ReportDataSource("dsChild1", itemCollection1));                    

            }

           if (e.DataSourceNames.FirstOrDefault() == "dsChild2")

           {                     

               e.DataSources.Add(new ReportDataSource("dsChild2", itemCollection2));

           }

       }

6.    Generate Summary report :

Run the below code to generate Summary.xlsx report, which contain two sheet (child1 and Child2 as shown below)

        static void Main(string[] args)

        {        

            MemoryStream memoryStream = GenerateReport();

            WriteFile("Summary.xlsx", memoryStream);   

        }

       public static MemoryStream GenerateReport()

        {

            // Variables

            Microsoft.Reporting.WinForms.Warning[] warnings;

            string[] streamIds;

            string mimeType = string.Empty;

            string encoding = string.Empty;

            string extension = string.Empty;

            string deviceInfo =

          "" +

          "  PDF" +

          "

";

            // Setup the report viewer object and get the array of bytes

            ReportViewer viewer = new ReportViewer();

            try

            {

                string fileDownloadType = "EXCELOPENXML";            

                string exeFolder = System.AppDomain.CurrentDomain.BaseDirectory;

                string reportPath = Path.Combine(exeFolder, string.Format(@"Summary.rdlc"));

                viewer.ProcessingMode = ProcessingMode.Local;

                viewer.LocalReport.ReportPath = reportPath;

                List itemCollection1 = new List

                {

                     new Item{ Name = "Rajeev"}

                };               

                List itemCollection2 = new List

                {

                     new Item{ Name = "Rajeev Tiwari"},

                     new Item{ Name = "Shyam Tiwari"}

                };

                // Set Sub-reports Dataset

                viewer.LocalReport.SubreportProcessing +=

                new SubreportProcessingEventHandler(exampleSubreportProcessingEventHandler);

                void exampleSubreportProcessingEventHandler(object sender, SubreportProcessingEventArgs e)

                {

                   

                    e.DataSources.Add(new ReportDataSource("dsChild1", itemCollection1));

                    e.DataSources.Add(new ReportDataSource("dsChild2", itemCollection2));

                }

                viewer.LocalReport.Refresh();

                byte[] bytes = viewer.LocalReport.Render(fileDownloadType, deviceInfo, out mimeType, out encoding, out extension, out streamIds, out warnings);

                return new MemoryStream(bytes);

            }

            catch (Exception ex)

            {

                throw;

            }

            finally

            {

                viewer.LocalReport.Dispose();

                viewer = null;

            }

        }

protected static void WriteFile(string filename, MemoryStream notice)

        {

            try

            {

                using (FileStream file = new FileStream(filename, FileMode.Create))

                {

                    notice.WriteTo(file);

                    file.Close();

                    notice.Dispose();

                    notice = null;

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }