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.SSDLApplicationData.MSLApplicationData.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:
EntityContainerEntityTypeAssociation
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;
if (fbd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
DBDetails db = new DBDetails();
txtProg.Text = "Please wait.....";
var files = Directory.GetFiles(fbd.SelectedPath,
"*.ssdl", SearchOption.AllDirectories);
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;
txtProg.Text = "";
}
else
{
txtProg.Text = "No data sources found in the
selected LightSwitch application !";
}
}
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:
CreateColumnGenerateEntityScriptGenerateConstraintScriptGenerateContraintCheckScript
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)
{
case "Name":
column.Append(string.Format("{0}[{1}] ",
Environment.NewLine, xAttri.Value));
break;
case "Type":
column.Append(xAttri.Value.ToUpper() + " ");
if (item.Attributes["MaxLength"] != null)
{
column.Append(string.Format("( {0} ) ",
item.Attributes["MaxLength"].Value));
}
break;
case "Nullable":
column.Append(string.Format("{0} ",xAttri.Value.Equals("false") ?
"NOT NULL " : "NULL "));
break;
case "StoreGeneratedPattern":
column.Append(string.Format("{0} ( 1, 1 ) ", xAttri.Value.ToUpper()));
break;
}
}
if (item.ParentNode.FirstChild.HasChildNodes)
{
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")
GenerateEntityScript(xmlNLEntityType);
colScript.Add(strScript.ToString());
XmlNodeList xmlNLAddAssociation = d.DocumentElement.GetElementsByTagName
("Association");
if (xmlNLAddAssociation.Count > 0)
{
strScript = new StringBuilder(string.Empty);
GenerateConstraintScript(xmlNLAddAssociation,false);
strScript = new StringBuilder(string.Empty);
colScript.Add(GenerateConstraintCheckScript(xmlNLAddAssociation));
foreach (var item in colScript)
{
rtxtTables.AppendText(item.ToString() +
Environment.NewLine);
}
}
}
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
