Creating a SQL Server Stored Procedure Generator using WPF
page 4 of 8
by Brian Mains
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 50870/ 207

Application Layout

At the top of the application is a grid which lays out the application horizontally. At the top is a Menu control, which contains the menu item to connect to the database. Clicking the connect button pops up a popup window, which takes the name of a database and a server (using windows authentication only).

Listing 4

<MenuItem Header="File">
      <MenuItem Header="Connect" x:Name="ConnectMenuItem"
        Click="OnConnectMenuItemClick"/>
      <MenuItem Command="ApplicationCommands.Close" Header="Exit"/>
</MenuItem>

Upon clicking the menu item, the following code runs:

Listing 5

this.ConnectPopup.IsOpen = true;
this.ConnectPopup.StaysOpen = false;

This opens the following popup, but ensures it does not stay open permanently.

Listing 6

<Popup x:Name="ConnectPopup" Grid.Row="0" PopupAnimation="Fade" 
  PlacementTarget="{Binding ElementName=TabLayout}" Placement="Bottom" 
  PlacementRectangle="20,25,40,30">
      <Grid Width="200" Height="100" Background="LightYellow">
            <Grid.RowDefinitions>
                  <RowDefinition Height="Auto" />
                  <RowDefinition Height="Auto" />
                  <RowDefinition Height="Auto" />
            </Grid.RowDefinitions>
            <Grid.ColumnDefinitions>
                  <ColumnDefinition Width="75" />
                  <ColumnDefinition Width="Auto" />
            </Grid.ColumnDefinitions>
                        
            <Label Grid.Row="0" Grid.Column="0" Width="50">Server</Label>
            <TextBox Name="Server" Grid.Row="0" Grid.Column="1" Width="50" />
            <Label Grid.Row="1" Grid.Column="0" Width="50">Database Name</Label>
            <TextBox Name="Database" Grid.Row="1" Grid.Column="1" Width="50" />
                        
            <Button Name="ConnectButton" Content="Connect" Grid.ColumnSpan="2" 
               Grid.Row="2" Click="OnConnectButtonClick" />
      </Grid>
</Popup>

Notice the popup definition above which has certain attributes. I explain about the popup in my blog, but I will reiterate some of the information here. The Popup control can take a container control as its child, which is commonly a grid (but could be another control as well). This grid control, if you are unfamiliar with it, specifies the grid-like structure that a group of controls will adapt to. The grid uses the RowDefinitions/ColumnDefinitions to setup the grid, and uses the Grid.Row or Grid.Column properties to set the child controls to appear in a specific region.

The popup control can specify the location to appear through the Placement, PlacementTarget, and the PlacementRectangle properties.  Upon entering the server/database and clicking connect, the tables are loaded into the sidebar shown below.

Listing 7

<DockPanel>
      <TreeView Name="TablesListing" DockPanel.Dock="Left"
        HorizontalAlignment="Left" Width="150px" Background="#FFF2F0CD" 
        BorderThickness="1,1,1,1" Foreground="#FF6182F5" 
        SelectedItemChanged="OnTablesListingSelectedItemChanged"/>
</DockPanel>

The TreeView tables/columns are loaded, and upon selecting each table, the string builder generates itself inside an expander control, within a text box so the user can copy/paste them into SQL Server. The expander groups control very nicely inside it, and allows the other stored procedures to remain hidden.

Listing 8

<StackPanel Orientation="Vertical" DockPanel.Dock="Top">
      <Expander Header="Create Procedure">
            <TextBox Name="CreateProcedure" Style="{StaticResource SPStyle}" />
      </Expander>
      <Expander Header="Delete Procedure">
            <TextBox Name="DeleteProcedure" Style="{StaticResource SPStyle}" />
      </Expander>
      <Expander Header="Update Procedure">
            <TextBox Name="UpdateProcedure" Style="{StaticResource SPStyle}" />
      </Expander>
      <Expander Header="Select Procedure">
            <TextBox Name="SelectProcedure" Style="{StaticResource SPStyle}" />
      </Expander>
</StackPanel>

View Entire Article

User Comments

Title: Good generator   
Name: Nilesh Gambhava
Date: 2011-02-09 5:09:14 AM
Comment:
Thanks Libal,

I was looking professional touch stored procedure generator and my all requirements are satisfied with tools4sql.
Title: Stored Procedure Generator   
Name: Libal
Date: 2010-06-24 10:24:55 AM
Comment:
I read your article, it sounds very interesting. But when I searched for more stored procedure generator, I found very interesting stored procedure generator named : T4S - Stored Procedure Generator found on www.tools4sql.net.

Thanks.....

Regards,
Libal
Title: Excellent stored procedure generator   
Name: Libal
Date: 2010-06-24 10:18:02 AM
Comment:
I read this article, this article is very nice. But when I searched for more stored procedure generator, I found very intersting & excellent stored procedure generator. It has rich configuration set & it is very easy to use. its name is T4S - Stored Procedure Generator & url : www.tools4sql.net
Title: Mondal   
Name: Chinmayee
Date: 2009-04-29 7:16:55 AM
Comment:
this is help ful
Title: how to get list of tables from database using vb6.0 reply   
Name: Brian
Date: 2009-03-08 2:53:20 PM
Comment:
The table_name is the field returned; in VB 6, you have to use ADO recordset to loop through the rows and get the table_name field, to get the list of the tables.
Title: how to get list of tables from database using vb6.0   
Name: Madhuri
Date: 2009-03-02 2:43:20 AM
Comment:
query1 = "select table_name as Name from INFORMATION_SCHEMA.Tables where TYPE ='S'"
wht next how to get the list of tables
Title: sql server   
Name: deepa
Date: 2008-10-22 6:45:33 AM
Comment:
it's good to way of teaching
Title: Great example   
Name: gabo
Date: 2008-09-01 3:59:25 PM
Comment:
As a matter of fact I use it all the time. Thanks.
Title: A good start   
Name: Ralph D. Wilson II
Date: 2007-09-19 10:36:05 AM
Comment:
This presents a good start toward creating Stored Procs; however, I can see several limitations in it. Perhaps the most glaring limitation is the fact that it appears to only facilitate the creation of very basic SP's and, while that is useful, I am assisting C#/ASP.Net developers in the creation of much more complex SP's.
Title: Useful   
Name: Niall
Date: 2007-08-30 3:32:49 AM
Comment:
This is interesting and I can see a very good use for this type of programming in my current project. One reservation I have though is the use of the WPF. Specifically the Presentation part of WPF.
Title: Code is Attached   
Name: Brian Mains
Date: 2007-08-26 7:26:33 PM
Comment:
The code is attached; see the downloads section.
Title: download this software   
Name: ankti
Date: 2007-08-26 4:22:23 PM
Comment:
i want to see this software






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-05-24 7:09:41 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search