* Kindly view my Youtube Video Link to learn my Easy form Design at Runtime.
Introduction
In my previous article (Windows Form Design at Run Time)
I have explained about how to design form at runtime. I have extended the program to next advance version with more functionality. This is one of my best projects I have made. Hope you all like this. Now let’s see what’s new in my Easy Form Design @ Run Time
Limitation in my previous article:
In my previous article it has limitation like user needs to design and add code for events like binding grid and etc.
user can load only one form. Adding parameter to SQL query is not possible.
New features in Easy Form Design at Run Time:
This has all my previous article features but with more new different functionality like.
1. New program has 2 part 1st is Form View part where user can view all there dynamically created forms by selecting Menu from the tree view.
2. 2nd Form Design part is to Add New/Edit/Save and Open Form dynamically.
3. User no needs to write any code.
4. Easy form design.
5. New Feature has options to select a Stored Procedure for their form.
6. User can select parameter and select dynamic textbox control for parameter, which needs to be used in SQL server Stored Procedure.
7. Save and Open Form.
The Easy Form Design at Run Time software application was developed to design your own Form using panel Control to, add Label, Button, TextBox, DataGridView and etc. User can select the Stored procedure which need to be used for the Form for example .Now user design there simple Search Form with a TextBox, Button and a Datagridview. For this user need to pass the Textbox as search parameter to Stored Procedure and the result need to be finally bound to Datagridview by clicking on the Button. In form design screen user can add a TextBox, Label, Button and DataGridView and from the Tools user can clicks on SQL Setting Tool Menu. User can select his Search Stored procedure and assign the controls to the parameter matching and finally save the form.
In main Form now user can able to view his saved Form Name in Tree view when he clicks on the menu. His saved form will be displayed .When user clicks on the button he can view all the details will be loaded from the data base using the Stored procedure which he or she has assign to the form. This simple C# application allows the user to add:
1. Create New Form
2. Save Form as XML file.
3. Open Form from XML file.
4. Cut, Copy, and Paste all controls.
5. Delete All Control.
6. Delete Selected Control.
7. Add/Change Back Color for Form (Here Panel used as Form).
8. Controls Bring to Front and Send to back options.
9. Add Label Control and using Property window design the Label.
10. Add Button Control and using Property window design the Button.
11. Add CheckBox Control and using Property window design the CheckBox..
12. Add Button Control and using Property window design the Button..
13. Add ComboBox Control and using Property window design the ComboBox.
14. Add DataGridView Control and using Property window design the DataGridView.
15. Add DataTimePicker Control and using Property window design the DataTimePicker.
16. Add Panel Control and using Property window design the ListBox.
17. Add PictureBox Control and using Property window design the PictureBox.
18. Add RadioButton Control and using Property window design the RadioButton.
19. Add TextBox Control and using Property window design the TextBox.
20. SQL Setting This is new feature in this application. Now user no needs to write any code here. You can write your Stored Procedure at your data base and using the SQL Setting tools menu, you can select your stored procedure for your form. Assign the controls property as parameter to the Stored Procedure.
21. Bind/Insert/Edit and Delete Data from database using Stored Procedure.
New Easy Form Design has 5 Parts to design your From at Runtime.
1st Form View part
2nd Form Design part
3rd SQL Stored Procedure Setting for your Form
4th Save Form
5th open Existing Form to edit.
Now let’s see one by one in detail here.
Section 1st Form View part: In the below image I have pointed each part by Numbers.
1) When user clicks on “New Form Design” Button, Form will be open where users can Add, Edit, Open and Save Forms. We can see more detail in Form Design part.
2) In Tree view I will list all the saved Form Name. Whenever users create new Form and save. The saved filename will be listed in Treeview .When user click on Treeview Node related form will be loaded in right side.
3) Here for example we can see that, now user have clicked on “NewItem” from Treeview and the related form has been loaded. For this form I have selected the ItemInsert stored procedure and passed all the Textbox controls as parameter to the SP.
Here is the animated image which explains in more detail.
Section 2nd Form Design part:
In this Application we can see Toolbar at the Left which is used to add controls to Form (here our Panel).Center has a Form(Panel) where user can add and design there controls. In the Right it has Property window which used to add all design to selected controls..
Toolbar: Here we can see all the list of controls which can be add to form at Runtime. Other features like Create New Form, Save Form, Open Form,Cupt,Copy and Paste Controls .At last control you can see is the SQL Setting ,that is very important tool to set our form controls with Stored procedure to perform our CRUD Operations.
When user click on “New Form Design” Button the form design will be open. You can refer the above image for how the Design form looks. This form plays the main role for creating dynamic form at runtime. Here users can Add New, Edit, Open and Save Forms. In the above image I have pointed each part by Numbers.
1) In the Left side we have the Tool Menu where user can add Textbox, Label, Button ,Panel, DataGridView and etc at runtime to design the form.
2) User can design their Form here at runtime. User can drag and drop controls, resize controls.
3) Using the property grid users can change the property of each selected controls BackColor, FontColor, Text and etc.
4) This is to select Stored Procedure for a Form to perform CRUD Operation and set the parameter of SP with Controls. We will see detail about this part in Section 3rd part.
Easy Form Design at Run Time will allow users to Design Form at Run Time, Open and Save Form and reuse the existing Form. User can add controls at Runtime from the Toolbar, Design there Form, Select the Stored procedure, Match and add Parameters with controls to perform some Action like Select, Insert, Update and Delete.
Now for Example user can add a DataGridView and a Button Control at Run time with Textbox for search the User by UserCode and UserName.
Section 3rd SQL Stored Procedure Setting for your Form:
After we have design our form now it’s time to assign our Stored Procedure to our form and pass the control parameters to SP to perform CRUD Operations.
To set the Stored Procedure and assign parameters to SP. Click the SQL Settings menu from Tool Bar.
When user clicks on the SQL Setting a new form will be open like below. In the below image I have marked as Numbers and let’s see each use one by one.
1) In the Select Procedure Combo Box I will display all the Stored Procedure names from our Database. Note in our application Bin folder we can find the “DBConnection.txt” text file which has the default Database Connection String, user can change the default Connection string as per your database server Name, Database Name, SQL User id and PWD.
2) Once the Stored Procedure for our form is selected click the Parameter Button. Here we can see, I have selected the “USP_USER_SELECT” which will be used in our form to search and bind the user details.
3) When we click on parameter I will display all the parameter for the selected Stored Procedure. Note everything will be as dynamic so user no needs to add any parameter here. I will list all the parameter which is declared in Stored Procedure. If user needs to add or delete any parameter they can update their Stored Procedure and redesign the form here.
4) Select SQL parameter one by one and click the down arrow button. When user click on the Down arrow, SQL parameter will be displayed in parameter TextBox.
5) In Control Name List, I will display all the text Box control names which needs to be assign to each SQL parameter.
6) Same here select the appropriate control for the SQL parameter and click the No 6th down button. The Control Name will be added to the Control textbox.
7) Once the SQL parameter and Control has been selected click the Add Button one by one to add the final SQL parameter list which needs to be saved for our Form.
8) All the SQL Parameter for the Selected Stored Procedure will be added here.
9) Confirm the list and Click Ok to save the form.
Here is the sample gif image which has an example for the User search bind.
Section 4th Save Form
In the Toolbar user has option to save the worked Form to be viewed from the main screen. You can refer the below image, When users click on Save Toolbar button list of Main Menu names will be displayed, here all the previous saved File Name (I will be using the file name as Menu name to display in the main screen Treeview) .If users want to save the Form as Submenu to an existing menu then user can select the Main Menu name and enter the new Form name to save ,If user wants the Form should be as a Main Menu then he can select the “Select” from ComboBox and enter the New Form Name .Once the Form is saved.The Menu will be added with new Form Name on main screen.
Note: I will save the form as XML file. In the application root bin folder you can see 2 folders as “XMLFILE” and “XMLForms”. In “XMLFILE” folder you can see “NewFormNameList.XML” In this file; I will store all the File Name which will be used to list in Main Menu TreeView.
In “XMLForms” folder for each form you can see 2 XML files .One XML file I will used to stored the form Control details with all properties and in another XML file I will store each form Stored Procedure names with parameter details.
Here you can see a sample ItemAdd XML file which has all controls information with property like Location, Font Size, FontColor, BackColor, Image and etc.
“ItemAdd_query.XML” This xml file has all the details of Form Stored procedure name with parameter and Control Names.
When user enters the existing form name to save, then I will overwrite the existing form with new saved version. I will be saving the forms as XML files.
5th open Existing Form to edit.
In the Toolbar user has option to open an existing Form for modification. You can refer the below image, When users click on Open Toolbar button list of menu names(which is our Form Name) will be displayed, User can select their Form Name from the list which needs to be open for modification. If user knows the Form name then they can directly enter the Form name in textbox and click open to modify the existing form.
Using the code
The article is extension of my previous article (Windows Form Design at Run Time) .Mostly I have reused the same code part with few more new modification. The form design part is same like previous article refer to my previous article about adding controls at runtime and performing Drag, Drop ,resize, delete control and etc from my previous article.
In this code part let’s see how to
1) Assign stored procedure to form and Add Parameter
In form design Tool Bar Button Click event I will open the new Form to perform the SP Settings.
Form Design SQL Setting Tool Bar Click Event
In SQL Setting Button Click I will get all the Textbox Control names from the design form and add all the control names to List and pass the Control names list to FrmParameterSetting.
private void toolStripButton1_Click_1(object sender, EventArgs e)
{
ControlNames.Clear();
if (pnControls.Controls.Count > 0)
{
foreach (TextBox tb in pnControls.Controls.OfType<TextBox>())
{
ControlNames.Add(tb.Name.ToString());
}
FrmParameterSetting obj = new FrmParameterSetting(ControlNames);
if (obj.ShowDialog() == DialogResult.OK)
{
}
}
}
FormParameterSetting Form Load event
In FrmParameterSetting Form Load I will get the entire Stored Procedure name and bind to ComboBox. Bind all the Control Names list to the List view to assign the parameter.
private void FrmParameterSetting_Load(object sender, EventArgs e)
{
LoadSPCopmbo();
listView2.Items.Clear();
foreach (string prime in ControlNames)
{
ListViewItem lvi = new ListViewItem(prime);
this.listView2.Items.Add(lvi);
}
}
In LoadSPCopmbo Function I will get all the SP Names using the below query. Here I pass the query to returnDataDatable function.
public void LoadSPCopmbo()
{
comboBox1.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
comboBox1.AutoCompleteSource = AutoCompleteSource.ListItems;
String Query = "SELECT name FROM dbo.sysobjects WHERE (type = 'P') ORDER BY name";
DataTable dt = returnDataDatable(Query);
comboBox1.ValueMember = "name";
comboBox1.DisplayMember = "name";
comboBox1.DataSource = dt;
}
In “returnDataDatable” I will read the Connection string from the text file “DBConnection.txt”.As I have explained you before in this article that the Connection string will be stored as a Textfile in application root folder.
public DataTable returnDataDatable(String Query)
{
String ConnectionString = ReadConnectionString();
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(Query, con);
cmd.CommandType = CommandType.Text;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
return dt;
}
I will check for textfile. If the file exists in the folder then I will read the connection string from the file if not exist I will write default connection string. So kindly check for the “DBConnection.txt” in root folder, and change the connection string as per your system Database setting.
private String ReadConnectionString()
{
string path = Application.StartupPath + @"\DBConnection.txt";
String connectionString = "";
if (!File.Exists(path))
{
using (StreamWriter tw = File.CreateText(path))
{
tw.WriteLine("Data Source=YOURDBServerName;Initial Catalog=YOURDBNAME;User id = YOURUSERNAME;password=YOURPASSWORD");
tw.Close();
}
}
else
{
TextReader tr = new StreamReader(path);
connectionString = tr.ReadLine();
tr.Close();
}
return connectionString;
}
When User clicks on Parameter Button’s, I will bind the List of parameter related to the Stored Procedure which has been selected. In the code part you can see I have used the query to get all the Parameter for the selected Stored Procedure and bind the final result to the List.
FormParameterSetting Parameter Button Click event
private void button2_Click(object sender, EventArgs e)
{
listView1.Items.Clear();
String Query = "SELECT p.name AS Name, t.name AS Type, p.max_length AS Length FROM sys.parameters AS p JOIN sys.types AS t ON t.user_type_id = p.user_type_id WHERE object_id = OBJECT_ID('" + txtSPNAME.Text.Trim().ToString() +"')";
DataTable dt = returnDataDatable(Query);
foreach (DataRow dr in dt.Rows)
{
ListViewItem lvi = new ListViewItem(dr["Name"].ToString());
lvi.SubItems.Add(dr["Type"].ToString());
lvi.SubItems.Add(dr["Length"].ToString());
this.listView1.Items.Add(lvi);
}
}
FormParameterSetting Ok Button Click:
Here I will get all the final Stored Procedure name, parameter List and Controls for parameter list and bind the result to public List class to use in our form design for save the file.
private void button5_Click(object sender, EventArgs e)
{
for (int i = 0; i < listView3.Items.Count; i++)
{
ShanuEasyFormDesign.Class.ControlList obj1 = new ShanuEasyFormDesign.Class.ControlList(txtSPNAME.Text.Trim(), listView3.Items[i].SubItems[0].Text.ToString(), listView3.Items[i].SubItems[1].Text.ToString());
ShanuEasyFormDesign.Class.ControlList.objDGVBind.Add(obj1);
}
}
Form Design Save:In save button click I will get all the controls details with Property and save the form as XML file.
private void toolSaves_Click(object sender, EventArgs e)
{
if (pnControls.Controls.Count > 0)
{
frmSave obj = new frmSave();
if (obj.ShowDialog() == DialogResult.OK)
{
if (obj.SaveFileName != "")
{
String NewFileName_Query = Application.StartupPath + @"\XMLForms\" + obj.SaveFileName + "_Query.XML";
if(!File.Exists(NewFileName_Query))
{
if (ShanuEasyFormDesign.Class.ControlList.objDGVBind.Count <= 0)
{
MessageBox.Show("procedure and parameter need to be set before save");
}
}
SavetoXML(obj.SaveFileName);
}
}
}
}
Form Open: To open the existing form .I will load all form controls from the selected XML file.
private void toolOpens_Click(object sender, EventArgs e)
{
frmOpen obj = new frmOpen();
if (obj.ShowDialog() == DialogResult.OK)
{
if (obj.OpenFileName != "")
{
xmlFileName = Application.StartupPath + @"\XMLForms\" + obj.OpenFileName + ".XML";
xmlFileName_Query = Application.StartupPath + @"\XMLForms\" + obj.OpenFileName + "_Query.XML";
pnControls.Controls.Clear();
loadXMLFILE();
}
}
}
Main Form CRUD operations: In main form we can see dynamically CRUO operation will be performed for each form here is the code which I used to perform it dynamically.
TreeView Node Click: When user clicks on File Name from the TreeView I will read the appropriate Saved XML file and add all the controls to panel as Form.
private void treeMenu_AfterSelect(object sender, TreeViewEventArgs e)
{
fileName = Application.StartupPath + @"\XMLForms\" + treeMenu.SelectedNode.Text + ".XML";
xmlFileName_Query = Application.StartupPath + @"\XMLForms\" + treeMenu.SelectedNode.Text + "_Query.XML";
pnlMain.Controls.Clear();
loadXMLFILE();
}
catch (Exception ex)
{
}
}
From Xml File I will check for the Button Control and create a dynamic Click event for the Button Control as below.
case "System.Windows.Forms.Button":
{
System.Drawing.Color myBackColor = new System.Drawing.Color();
myBackColor = System.Drawing.ColorTranslator.FromHtml(gParam[8]);
Button ctrl = new Button();
ctrl.BackColor = myBackColor;
ctrl.Name = gParam[10];
ctrl.Location = new Point(System.Convert.ToInt32(gParam[1]), System.Convert.ToInt32(gParam[2]));
ctrl.Text = gParam[5];
ctrl.Size = new System.Drawing.Size(System.Convert.ToInt32(gParam[3]), System.Convert.ToInt32(gParam[4]));
if (gParam[11] == "Front")
{
ctrl.BringToFront();
}
else
{
ctrl.SendToBack();
}
ctrl.Click += new EventHandler(control_Click);
pnlMain.Controls.Add(ctrl);
}
In the Button Click event I will read the Stored Procedure details from the XML file and bind the final result to the Datagridview. All CRUD operation will be managed in this Click Event.
private void control_Click(object sender, EventArgs e)
{
try
{
if (xmlFileName_Query != "")
{
XmlDocument xmlQuery = new XmlDocument();
xmlQuery.Load(xmlFileName_Query);
XmlNode xnQueryList = xmlQuery.SelectSingleNode("ShanuQuerySave");
int i = 0;
String txtBox1Name = "";
String ParameterName1 = "";
String ControltoBindName = "";
String Query = "";
foreach (XmlNode xn in xnQueryList)
{
Query = xn["ProcedureName"].InnerText;
}
DataTable dt = new DataTable();
String ConnectionString = ReadConnectionString();
SqlConnection con = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(Query, con);
cmd.CommandType = CommandType.StoredProcedure;
foreach (XmlNode xn in xnQueryList)
{
txtBox1Name = xn["cntrlName"].InnerText;
ParameterName1 = xn["ParameterName"].InnerText;
ControltoBindName = xn["cntrltoBind"].InnerText;
Query = xn["ProcedureName"].InnerText;
if (txtBox1Name != "")
{
Control control = returnTextBox(txtBox1Name);
if (control is TextBox)
{
txtBox1Name = control.Text.Trim().ToString();
}
}
cmd.Parameters.AddWithValue(ParameterName1, txtBox1Name);
}
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
if (dt.Columns[0].ColumnName == "Result")
{
MessageBox.Show("Record :" + dt.Rows[0].ItemArray[0].ToString());
return;
}
}
if (ControltoBindName != "")
{
foreach (Control pnlCntl in pnlMain.Controls)
{
if (pnlCntl is DataGridView)
{
if (pnlCntl.Name == ControltoBindName)
{
DataGridView grid = (DataGridView)pnlCntl;
grid.DataSource = dt;
}
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
Conclusion:
Note: Things to do before run my application.
1) Connection String: Before run the application kindly update the connection string from the application root folder to your local SQL Server database.
I have used SQL Server 2008 R2 and Visual studio 2010.
2) Stored procedures: In attached zip file you can find the folder name “SQLScripts” which has all Table, Database and Stored Procedure Creation script file. Run all the script file one by one to your SQL Server Database.
3) Run the program
OutPut :
1) Item Add: I have used the below Sp to Insert the Item details.
Note after insert I will return the message as “Inserted” for confirmation.
ALTER PROCEDURE [dbo].[USP_Item_Insert]
(
@Item_Code VARCHAR(50) = '',
@Item_Name VARCHAR(50) = '',
@Price INT=0 ,
@TAX1 INT=0 ,
@Discount INT=0 ,
@Description VARCHAR(50) = '',
@USR_Name VARCHAR(50) = ''
)
AS
BEGIN
IF NOT EXISTS (SELECT * FROM ItemMasters WHERE Item_Code=@Item_Code and Item_Name=@Item_Name)
BEGIN
INSERT INTO [ItemMasters]
([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]
,[IN_USR_ID],[UP_DATE],[UP_USR_ID])
VALUES
(@Item_Code,@Item_Name,@Price,@TAX1,@Discount,@Description,GETDATE(),@USR_Name
,GETDATE(),@USR_Name)
select 'Inserted' as 'Result'
END
END
For more clear understanding I have added the image as animated gif file.
2) Item Edit: I have used the below Sp to Update the Item details.
Note after update I will return the message as “updated” for confirmation.
ALTER PROCEDURE [dbo].[USP_Item_Update]
(
@Item_Code VARCHAR(50) = '',
@Item_Name VARCHAR(50) = '',
@Price INT=0 ,
@TAX1 INT=0 ,
@Discount INT=0 ,
@Description VARCHAR(50) = '',
@USR_Name VARCHAR(50) = ''
)
AS
BEGIN
UPDATE [ItemMasters]
SET [Item_Name]=@Item_Name,
[Price]=@Price,
[TAX1]=@TAX1,
[Discount]=@Discount,
[Description]=@Description,
[UP_DATE]=GETDATE(),
[UP_USR_ID]=@USR_Name
WHERE
Item_Code=@Item_Code
select 'Updated' as 'Result'
END
For more clear understanding I have added the image as animated gif file.
History
ShanuEasyFormDesignV1.0 - 2015.04.21
