Click here to Skip to main content
12,896,108 members (51,414 online)
Click here to Skip to main content
Add your own
alternative version


7 bookmarked
Posted 23 Jan 2012

Visual Studio LightSwitch Application Database Script Generator

, 23 Jan 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
A tool to generate Sql server DB script from the LightSwitch internal database


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....


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">
            <ColumnDefinition Width="*" />
            <RowDefinition Height="25"/>
            <RowDefinition Height="*" />
            <RowDefinition Height="25" />
        <StackPanel Orientation="Horizontal" 




            <TextBlock Name="txtbFolder" 






                       Text="Please select the folder where your LightSwitch application is.." />
            <Button Content="Browse" 




                    Click="btnBrowse_Click"  />

        <RichTextBox  Name="rtxtTables" 







                      VerticalScrollBarVisibility="Auto" />
        <StackPanel  Grid.Row="2" Background="Wheat" >
            <TextBlock Name="txtProg" 





                       FontSize="14" />


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">
                    <ColumnDefinition Width="*" />
                    <RowDefinition Height="25" />
                    <RowDefinition Height="*" />
                    <RowDefinition Height="25"/>
                <TextBlock Name="txtbList" 






                           Text="Select the database(s) to Export as script" />
                <ListBox Name="lstData" 




                            <StackPanel Orientation="Horizontal">
                                <TextBlock Text="{Binding DatabaseName}" Width="Auto" />
                                <TextBlock Text="{Binding DatabaseSize}" Margin="25,0,0,0"/>
                                <CheckBox Margin="25,0,0,0" 




                                          Content="Drop if already exists." />

                <StackPanel Orientation="Horizontal" Grid.Row="2" Grid.Column="0">
                    <TextBlock Name="txtError" 




                               Foreground="Red" />

                <StackPanel Orientation="Horizontal" HorizontalAlignment="Right" Grid.Row="2" Grid.Column="0">
                    <Button Name="btnOk" 



                    <Button Name="btnCancel" 





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">
      <PropertyRef Name="Id" />
    <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" />

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="*" />
      <Principal Role="Designation">
        <PropertyRef Name="Id" />
      <Dependent Role="Employee">
        <PropertyRef Name="Employee_Designation" />

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);


                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 = "";
        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));
                   // 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));
                   // Is the Column nullable
                   case "Nullable":
                       column.Append(string.Format("{0} ",xAttri.Value.Equals("false") ? "NOT NULL " : "NULL "));
                   // Is it Identity Column
                   case "StoreGeneratedPattern":
                       column.Append(string.Format("{0} ( 1, 1 ) ", xAttri.Value.ToUpper()));

           if (item.ParentNode.FirstChild.HasChildNodes) // Creating Primary Key
               if (item.ParentNode.FirstChild.ChildNodes[0].Attributes["Name"].Value.Equals(item.Attributes["Name"].Value))
                                            "{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"))
                                                        "IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{1}]') AND type in (N'U'))"
                                                       +"{0}CREATE TABLE [dbo].[{1}] ( {2} " 
                                                       +"{0}     PRINT 'Table ''{1}'' created successfully !.'"
                                                       +"{0}     PRINT 'The Table ''{1}'' already exists ! Please drop it & try again.'"
                    if (item.Name.Equals("Property"))


            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)
                                                  "{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}      ALTER TABLE [dbo].[{1}]"
                                                 +"{0}      WITH CHECK CHECK CONSTRAINT {2} "
                                                 +"{0}      PRINT 'CHECK executed successfully on ''{2}''..'"
                                                 +"{0}     PRINT 'The Foreign Key ''{2}'' does not exists ! Cant execute CHECK.'"
                                                 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;


               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);


                       foreach (var item in colScript)
                           rtxtTables.AppendText(item.ToString() + Environment.NewLine); // Displaying the completely generated script in the RichTextBox.
               txtProg.Text = "Done.....";
               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....


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


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

You may also be interested in...

Comments and Discussions

QuestionHow to Reload Emtity Pin
Member 122559982-Mar-16 23:08
memberMember 122559982-Mar-16 23:08 
GeneralExcellent, just what I wanted Pin
Mr Yossu3-Nov-13 4:49
memberMr Yossu3-Nov-13 4:49 
GeneralRe: Excellent, just what I wanted Pin
Jaganathan Bantheswaran10-Nov-13 4:21
memberJaganathan Bantheswaran10-Nov-13 4:21 
GeneralMy vote of 4 Pin
Member 16474026-Sep-12 19:17
memberMember 16474026-Sep-12 19:17 
GeneralRe: My vote of 4 Pin
Jaganathan Bantheswaran26-Sep-12 22:55
memberJaganathan Bantheswaran26-Sep-12 22:55 
GeneralMy vote of 3 Pin
tigercont23-Jan-12 21:15
membertigercont23-Jan-12 21:15 
GeneralRe: My vote of 3 Pin
Jaganathan Bantheswaran24-Jan-12 0:50
memberJaganathan Bantheswaran24-Jan-12 0:50 

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

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

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