Visual Studio LightSwitch Application Database Script Generator

• Download ExportLSDbasScript - 55.4 KB
• Download binary

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.

The 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 am 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 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 four 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

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