Click here to Skip to main content
Click here to Skip to main content

Visual Studio LightSwitch Application Database Script Generator

By , 23 Jan 2012
Rate this:
Please Sign up or sign in to vote.
ExportLSDbasScript

Introduction

In this article, we shall see about a tool which is used to generate Sql server DB script from the LightSwitch internal database as well as External data sources.

Take a situation, we are developing the LightSwitch business application and we are using the internal database [ApplicationData] for storing Data. Lately, we decided to move the LightSwitch internal database to Sql Server.

First way to move the internal database to Sql server is like using the Publish wizard in Visual Studio LightSwitch. when we publish the application, there will be a step in which we can specify the path to generate a DB script to deploy the LightSwitch internal Db as Sql Server DB.

The second way is to create a tool to generate script from LightSwitch internal database. So i tried the second way and giving you now....

Background

Whenever we add a Data Source to LightSwitch application, say ApplicationData, The LightSwitch runtime would create structure for the created Data Source in XML format in the following files when we build the application.

  • ApplicationData.SSDL
  • ApplicationData.MSL
  • ApplicationData.CSDL
These three files are generated when we build the application and located in \ServerGenerated\GeneratedArtifacts\.. These files describe the table [entity] structure which we have created.

Designing the Tool

We are going to develop a tool using WPF. The below Xaml shows the main screen for our tool which will have a Browse button and a RichTextBox to display the generated script.

 <Grid Margin="20,20,20,20">
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="*" />
        </Grid.ColumnDefinitions>
        <Grid.RowDefinitions>
            <RowDefinition Height="25"/>
            <RowDefinition Height="*" />
            <RowDefinition Height="25" />
        </Grid.RowDefinitions>
        
        <StackPanel Orientation="Horizontal" 
                    Background="Wheat" 
                    Grid.Column="0" 
                    Grid.Row="0" 
                    HorizontalAlignment="Stretch">
            <TextBlock Name="txtbFolder" 
                       Margin="0,5,0,0" 
                       FontSize="14" 
                       FontFamily="Calibri" 
                       FontWeight="Bold" 
                       TextOptions.TextRenderingMode="Grayscale" 
                       Text="Please select the folder where your LightSwitch application is.." />
            <Button Content="Browse" 
                    Margin="30,0,30,0" 
                    HorizontalAlignment="Right"
                    Name="btnBrowse" 
                    Click="btnBrowse_Click"  />
        </StackPanel>

        <RichTextBox  Name="rtxtTables" 
                      Foreground="Blue" 
                      IsReadOnly="True" 
                      Grid.Row="1" 
                      HorizontalAlignment="Stretch" 
                      VerticalAlignment="Stretch" 
                      HorizontalScrollBarVisibility="Auto" 
                      VerticalScrollBarVisibility="Auto" />
        
        <StackPanel  Grid.Row="2" Background="Wheat" >
            <TextBlock Name="txtProg" 
                       Margin="0,5,0,0" 
                       Foreground="Red" 
                       FontWeight="ExtraBold" 
                       FontFamily="Calibri" 
                       FontSize="14" />
        </StackPanel>

</Grid>

When you click on Browse button, Folder browser will pop up and will ask you to select the LightSwitch application folder. Once you select the LightSwitch folder, we need to pop up a window which will list the Data Sources available for the LightSwitch application selected.

<Popup Name="popupListDB" Height="350" Width="550" Placement="Center">
            <Grid>
                <Grid.ColumnDefinitions>
                    <ColumnDefinition Width="*" />
                </Grid.ColumnDefinitions>
                <Grid.RowDefinitions>
                    <RowDefinition Height="25" />
                    <RowDefinition Height="*" />
                    <RowDefinition Height="25"/>
                </Grid.RowDefinitions>
                <TextBlock Name="txtbList" 
                           Grid.Column="0" 
                           Grid.Row="0" 
                           Margin="0,3,0,0" 
                           FontWeight="ExtraBold"  
                           Foreground="FloralWhite" 
                           Text="Select the database(s) to Export as script" />
                <ListBox Name="lstData" 
                         Grid.Row="1" 
                         Grid.Column="0" 
                         SelectionMode="Multiple" 
                         SelectionChanged="lstData_SelectionChanged">
                    <ListBox.ItemTemplate>
                        <DataTemplate>
                            <StackPanel Orientation="Horizontal">
                                <TextBlock Text="{Binding DatabaseName}" Width="Auto" />
                                <TextBlock Text="{Binding DatabaseSize}" Margin="25,0,0,0"/>
                                <CheckBox Margin="25,0,0,0" 
                                          Name="chkDrop" 
                                          Click="chkDrop_Click" 
                                          IsChecked="True"  
                                          Content="Drop if already exists." />
                            </StackPanel>
                        </DataTemplate>
                    </ListBox.ItemTemplate>
                </ListBox>

                <StackPanel Orientation="Horizontal" Grid.Row="2" Grid.Column="0">
                    <TextBlock Name="txtError" 
                               Margin="0,3,0,0" 
                               FontWeight="ExtraBold" 
                               HorizontalAlignment="Right" 
                               Foreground="Red" />
                </StackPanel>

                <StackPanel Orientation="Horizontal" HorizontalAlignment="Right" Grid.Row="2" Grid.Column="0">
                    <Button Name="btnOk" 
                            Width="50" 
                            Content="OK" 
                            Click="btnOk_Click"/>
                    <Button Name="btnCancel" 
                            Content="Cancel" 
                            Width="50" 
                            Margin="30,0,0,0" 
                            Click="btnCancel_Click"/>
                </StackPanel>
            </Grid>
        </Popup> 

In the above pop up window code snippet, we have a ListBox in which we will add the available data sources.

The .SSDL file structure

As we know, the .SSDL file has XML content, we shall discuss about the nodes in it.

The root of the .SSDL file is Schema. There are three child nodes which are listed below.

  • EntityContainer
  • EntityType
  • Association
The EntityType node describes about the Entities used in the Data Source. If you have three tables in your Data Source then the Schema root node will have three EntityType child nodes.

<EntityType Name="Employee">
    <Key>
      <PropertyRef Name="Id" />
    </Key>
    <Property Name="Id" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
    <Property Name="Name" Type="nvarchar" Nullable="false" MaxLength="255" />
    <Property Name="Gender" Type="nvarchar" Nullable="false" MaxLength="255" />
    <Property Name="DOB" Type="datetime" Nullable="false" />
    <Property Name="Email" Type="nvarchar" Nullable="false" MaxLength="255" />
    <Property Name="Mobile" Type="nvarchar" Nullable="false" MaxLength="255" />
    <Property Name="Employee_Designation" Type="int" Nullable="false" />
    <Property Name="Employee_Project" Type="int" Nullable="false" />
  </EntityType> 

You can see the sample Entity structure in the .SSDL file as shown in the above snippet. The EntityType node has two child nodes namely Key and Property. The Key node describes about the Primary Key and the Property node describes about the columns in the table.

<Association Name="Employee_Designation">
    <End Role="Designation" Type="ApplicationDataStore.Designation" Multiplicity="1" />
    <End Role="Employee" Type="ApplicationDataStore.Employee" Multiplicity="*" />
    <ReferentialConstraint>
      <Principal Role="Designation">
        <PropertyRef Name="Id" />
      </Principal>
      <Dependent Role="Employee">
        <PropertyRef Name="Employee_Designation" />
      </Dependent>
    </ReferentialConstraint>
  </Association> 

Next the Association child node describes the association between the entities. The above snippet shows the association between Employee entity and Destination entity. The Association node has two child nodes. The Dependent child node of the ReferencialContraint node describes the Foreign Key and the Principal child node describes about the reference for the Foreign key.

The Code-Behind

Once you select the LightSwitch application folder, we are searching for the .SSDL files which will have the entity details.

            FolderBrowserDialog fbd = new FolderBrowserDialog();

            fbd.ShowNewFolderButton = false;

            fbd.RootFolder = Environment.SpecialFolder.MyComputer; // default location

            if (fbd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {

                DBDetails db = new DBDetails(); // An entity which has the available Data Sources details

                txtProg.Text = "Please wait.....";

                var files = Directory.GetFiles(fbd.SelectedPath, "*.ssdl", SearchOption.AllDirectories); // get all .SSDL files

                if (files.Length > 0)
                {
                    foreach (var item in files)
                    {

                        FileInfo fi = new FileInfo(item);

                        db.DBDetailsCollection.Add(

                            new DBDetails() {

                                                DatabaseName = fi.Name.Replace(".ssdl", string.Empty),

                                                DatabaseSize = fi.Length.ToString() + " Bytes in size",

                                                DatabaseFilename = fi.FullName, IsDropChecked = true

                                             });

                    }

                    lstData.ItemsSource = db.DBDetailsCollection;

                    popupListDB.IsOpen = true; // Display the available Data Sources in pop up window.

                    txtProg.Text = "";
                }
                else
                {
                    txtProg.Text =  "No data sources found in the selected LightSwitch application !"; // Status message
                }
            } 

In the above code snippet, we are getting all the .SSDL files and adding the details of the files to the DBDetails class instance. DBDetails entity has the properties to have information about the Data Sources files available.

        class DBDetails
        {
            public string DatabaseName { get; set; }
            public string DatabaseSize { get; set; }
            public bool IsDropChecked { get; set; }
            public string DatabaseFilename { get; set; }
            
            public DBDetails()
            {
                DatabaseFilename = string.Empty;
                DatabaseName = string.Empty;
                IsDropChecked = true;
                DatabaseSize = string.Empty;
            }
            
        } 

The above code snippet shows the DBDetails entity which has properties to have the file information. Once you select the ApplicationData files listed in the pop up window, we are generating the script from the .SSDL file.

To generate the DB script, we have three methods,

  • CreateColumn
  • GenerateEntityScript
  • GenerateConstraintScript
  • GenerateContraintCheckScript

The CreateColumn method is used to create the column for the table.

 private string CreateColumn(XmlNode item)
        {
            StringBuilder column = new StringBuilder(string.Empty);

            foreach (XmlAttribute xAttri in item.Attributes)
            {
                switch (xAttri.Name)
                {
             // Name of the Column
                    case "Name":
                        column.Append(string.Format("{0}[{1}] ", Environment.NewLine, xAttri.Value));
                        break;
                    // Type of the Column
                    case "Type":
                        column.Append(xAttri.Value.ToUpper() + " ");
                        if (item.Attributes["MaxLength"] != null) // Size of the Column
                        {
                            column.Append(string.Format("( {0} ) ",item.Attributes["MaxLength"].Value));
                        }
                        break;
                    // Is the Column nullable
                    case "Nullable": 
                        column.Append(string.Format("{0} ",xAttri.Value.Equals("false") ? "NOT NULL " : "NULL "));
                        break;
                    // Is it Identity Column
                    case "StoreGeneratedPattern":
                        column.Append(string.Format("{0} ( 1, 1 ) ", xAttri.Value.ToUpper()));
                        break;
                }
            }

            if (item.ParentNode.FirstChild.HasChildNodes) // Creating Primary Key
            {
                if (item.ParentNode.FirstChild.ChildNodes[0].Attributes["Name"].Value.Equals(item.Attributes["Name"].Value))
                {
                    column.Append(string.Format(
                                             "{1}PRIMARY KEY CLUSTERED ( [{0}] ASC ) "
                                            +"{1}WITH ("
                                            +"{1}       ALLOW_PAGE_LOCKS = ON, "
                                            +"{1}       ALLOW_ROW_LOCKS = ON, " 
                                            +"{1}       PAD_INDEX = OFF, " 
                                            +"{1}       IGNORE_DUP_KEY = OFF, "
                                            +"{1}       STATISTICS_NORECOMPUTE = OFF "
                                            +"{1}      ) ", item.Attributes["Name"].Value,Environment.NewLine));
                }
            }

            return string.Format("{0}",column.Insert(column.Length, ", ").ToString());
        }

The GenerateEntityScript method is used to generate the script for the tables created in the Data Sources.

private string GenerateEntityScript(XmlNodeList xNodeList)
        {
            StringBuilder script = new StringBuilder(string.Empty);
          
            foreach (XmlNode item in xNodeList)
            {
                if (item.HasChildNodes)
                {
                    string scrt = GenerateEntityScript(item.ChildNodes);

                    if (!item.Name.Equals("Key"))
                    {
                        strScript.Append(string.Format(
                                                        "IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{1}]') AND type in (N'U'))"
                                                       +"{0}BEGIN"
                                                       +"{0}CREATE TABLE [dbo].[{1}] ( {2} " 
                                                       +"{0});"
                                                       +"{0}     PRINT 'Table ''{1}'' created successfully !.'"
                                                       +"{0}END"
                                                       +"{0}ELSE"
                                                       +"{0}BEGIN"
                                                       +"{0}     PRINT 'The Table ''{1}'' already exists ! Please drop it & try again.'"
                                                       +"{0}END{0}",
                                                       Environment.NewLine,
                                                       item.Attributes["Name"].Value, 
                                                       scrt.ToString().Trim().TrimEnd(','))
                                                       ); 
                    }
                }
                else
                {
                    if (item.Name.Equals("Property"))
                    {
                        script.Append(string.Format("{0}",CreateColumn(item)));
                    }

                }
            }

            return script.ToString();
        } 

We are passing the EntityType nodes as parameter to the above GenerateEntityScript method.

Next we need to create Foreign Key scripts. The GenerateConstraintScript method is used to create the Foreign keys defined for the Tables. The final method we have is GenerateContraintCheckScript.

private string GenerateConstraintCheckScript(XmlNodeList xmlNLAssociation)
        {
            StringBuilder script = new StringBuilder(Environment.NewLine + "--Creating CHECK constraints for the tables starts here--");

            foreach (XmlNode item in xmlNLAssociation)
            {
                string[] names = item.Attributes["Name"].Value.StartsWith("FK_", StringComparison.CurrentCulture) ? item.Attributes["Name"].Value.Split('_') : ("FK_" + item.Attributes["Name"].Value).Split('_');

                if (names.Length >1)
                {
                    script.Append(string.Format(
                                                  "{0}IF  EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[{2}]') AND parent_object_id = OBJECT_ID(N'[dbo].[{1}]'))"
                                                 +"{0}BEGIN"
                                                 +"{0}      ALTER TABLE [dbo].[{1}]"
                                                 +"{0}      WITH CHECK CHECK CONSTRAINT {2} "
                                                 +"{0}      PRINT 'CHECK executed successfully on ''{2}''..'"
                                                 +"{0}END"
                                                 +"{0}ELSE"
                                                 +"{0}BEGIN"
                                                 +"{0}     PRINT 'The Foreign Key ''{2}'' does not exists ! Cant execute CHECK.'"
                                                 +"{0}END",
                                                 Environment.NewLine,
                                                 names[1],
                                                 item.Attributes["Name"].Value.StartsWith("FK_", StringComparison.CurrentCulture) ? item.Attributes["Name"].Value : ("FK_" + item.Attributes["Name"].Value))); 
                }
            }

            return script.Append(Environment.NewLine + "--Creating CHECK constraints for the tables ends here--" ).ToString();
        } 

We have all the methods needed to create the complete DB script from the selected LightSwitch application. We need to call those methods when we click on Ok button in the pop up window.

 private void btnOk_Click(object sender, RoutedEventArgs e)
        {
            txtProg.Text = "";

            IList fileNames = (IList)lstData.SelectedItems;

            if (fileNames.Count > 0)
            {
                popupListDB.IsOpen = false;

                rtxtTables.Document.Blocks.Clear();

                txtProg.Text = "Generating script.....";

                foreach (DBDetails fileName in fileNames)
                {
                    XmlDocument d = new XmlDocument();

                    d.Load(new FileStream(fileName.DatabaseFilename, FileMode.Open, FileAccess.Read, FileShare.Read));

                    string script = string.Empty;

                    XmlNodeList xmlNLEntityType = d.DocumentElement.GetElementsByTagName("EntityType") // Getting the EntityType nodes 

                    GenerateEntityScript(xmlNLEntityType); // Generating script for creating table.

                    colScript.Add(strScript.ToString()); // Adding the partial script to the string collection

                    XmlNodeList xmlNLAddAssociation = d.DocumentElement.GetElementsByTagName("Association"); // Getting the Association nodes

                    if (xmlNLAddAssociation.Count > 0)
                    {
                        strScript = new StringBuilder(string.Empty);

                        GenerateConstraintScript(xmlNLAddAssociation,false); // Generating the Foreign Key script
                       
                        strScript = new StringBuilder(string.Empty);

                        colScript.Add(GenerateConstraintCheckScript(xmlNLAddAssociation));

                        foreach (var item in colScript)
                        {
                            rtxtTables.AppendText(item.ToString() + Environment.NewLine); // Displaying the completely generated script in the RichTextBox.
                        }
                    }
                }
                txtProg.Text = "Done.....";
            }
            else
            {
                txtError.Text = "Please select a database ! ";
            }

        }

That’s it. We have created the completed tool for generating the Sql Server DB script from LightSwitch Application.

Once you are ready with the code and screen design, Just build the application and start to use it.

You can get this tool from Visual Studio Gallery.

Happy Coding....

History

  • Added binary file [exe] of this project - 24-01-2012

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Jaganathan Bantheswaran
Software Developer
India India
Be simple & sample
 
http://www.jagan-bhathri.com
http://about.me/jaganathan
Follow on   Twitter

Comments and Discussions

 
GeneralExcellent, just what I wanted PinmemberMr Yossu3-Nov-13 4:49 
GeneralRe: Excellent, just what I wanted PinmemberJaganathan Bantheswaran10-Nov-13 4:21 
GeneralMy vote of 4 PinmemberMember 16474026-Sep-12 19:17 
GeneralRe: My vote of 4 PinmemberJaganathan Bantheswaran26-Sep-12 22:55 
GeneralMy vote of 3 Pinmembertigercont23-Jan-12 21:15 
GeneralRe: My vote of 3 PinmemberJaganathan Bantheswaran24-Jan-12 0:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140415.2 | Last Updated 24 Jan 2012
Article Copyright 2012 by Jaganathan Bantheswaran
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid