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;
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 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)
{
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
- Added binary file [exe] of this project - 24-01-2012
Submit an article or tip
Post your Blog
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Rant 
Admin 
