Monday, September 29, 2014

SQL 2005 - Sql server stored procedure examples

1. Introduction


A "Stored Procedure" in Sqlserver is an executable program unit that can be stored in the database as objects. These stored procedures can be executed from client application programs to get good performance. The stored procedure can be called from other scripting or even from some other stored procedures.

The communication between SqlServer Stored procedure and client application happens through parameters and return values. The below picture shows that:

Figure 1: SqlServer Proc. & External App. Communication
Figure 1: SqlServer Proc. & External App. Communication



Client applications optionally sends data to the stored procedure and stored procedures perform processing based on the data sent by the client application. After the stored procedure finished its work it packs data back (Again optional) to its caller through various output techniques like:

  1. Sending data via Output parameter type
  2. Sending data via return statement
  3. Sending data by select statement

We will see all these stuff in this article.



2. Creating & EXEC Stored Procedure in Sql


You can create a stored procedure in the SQL Server Management Studio. Once the procedure is created it will be listed in the Programmability node of the database in which the procedure create statement is executed. To execute the Procedure use the “EXEC” followed by the procedure name.

The structure of the stored procedure is shown in Fig.2. While creating a procedure, the name of the procedure is given first followed by one or more parameters passed to it. The parameter(s) can be optional. After the parameter, the procedure body will be written to perform the required operations. The body can have local variables declared in it and these variables are local to the procedures (i.e) they can be viewed only inside the procedure body.

Figure 2: Basic Skeleton of SQLServer Stored Proc
Figure 2: Basic Skeleton of SQLServer Stored Proc


In the Fig.3 below, a simple stored procedure is shown skipping all the optional parts of the procedure. So to create a procedure, we need a procedure name and at least a single statement as procedure body. Note that it will be good practice to create and execute the procedure with the schema name qualifier. In the below example, the schema name qualifier is dbo.


Figure 3: A Simple Stored Proc. Example
Figure 3: A Simple Stored Proc. Example


The procedure body can have any kind of the SQL statements like creating a table, inserting one or more table row(s), setting the behaviour of the database etc.,. However, the procedure body restricts certain operations in it. Some of the important restrictions are listed below:

  1. The body should not create some other stored procedure
  2. The body should not create a view
  3. The body should not create any triggers.


The below-given video, shows creating and executing a simple stored procedure using SQL Server Management Studio:

Video 1: Creating a Simple Stored Procedure

3. Declaring Variable in Procedure Body

You can declare one or more variable(s) local to the procedure body and these variables are visible only inside the body of the procedure. You can declare variables anywhere in the body of the stored procedure. But, The good practice is declaring the variables in the beginning of stored procedure body as shown in Fig.2. Have a look at the Sample procedure given below:

Figure 4: Declaring Variables in Stored Proc.
Figure 4: Declaring Variables in Stored Proc.

In the above procedure, you can see variables Name1 and Name2 declared in the beginning of the procedure (Marked as 1). Here you can note that multiple variables are declared in a single line and each variable declaration is separated by a comma. Also, note that the variable has prefix @. In the procedure body you can declare variable wherever you want and in the example, the variable @Name3 is declared towards the end of the Procedure body. To assign a value to a declared variable, the "Set Keyword" is used (Marked as 3) and this is shown in above example. Unlike declaring more than one variable in a single line, only one set statement is allowed in a line. The result of executing the above procedure is shown below:

Figure 5: Executing the Procedure SayHello2
Figure 5: Executing the Procedure SayHello2

OK. In the procedure body, How do I assign multiple values in a single statement ? Well. The answer is using the “Select Var=value” pairs. And you can use these pairs by separating it with a comma. The below screen shot shows this:

Figure 6: Multiple Variable Assignments in Single Statement
Figure 6: Multiple Variable Assignments in Single Statement

4. SQL stored procedure parameters


In the previous examples, we saw creating a simple stored procedure and executing it. A procedure can accept parameters and the caller of the procedure passes values (But, Not always. We will see that soon) to it. Based on the passed in value, the procedure takes relevant action inside the body of it. For example, let us create a procedure that will accept City and State from the caller and tells how many authors belong to the passed-in City and State. The procedure will query the Authors table of the Pubs database to perform this author count. To have this database, Google it, or download SQL script from the SQL2005 page if this Web site. Below is the example procedure:
Figure 7: Executing Procedure with Parameters

In the above example procedure, the procedure accepts two parameters named @State and @City and this is marked as 1 in the above screen shot. The data type matches the type defined in the table definition for convenience. The procedure body has the internal variable @TotalAuthors and this variable is used to display the number authors. The parameters passed in are used to form “WHERE” clause of the select query that counts the number of authors. This is marked as 2 in the screenshot. Finally, the counted value is printed to the output window through the Print statement and the output is marked as 5.
There is two way to execute the procedure. The first way, marked as 3, shows passing the parameters as comma separated list after procedure name. In our example we are passing the values CA, Berkeley separated by a comma. These values are collected by the procedure parameter variables @State and @City. In this method, the parameter passing order is important. This first technique is what called “Passing Argument by Ordinal Position”. In the second way, marked as 4, after the procedure name, the parameters are directly assigned and in this case, the order is not important. This second technique is known as “Passing Named Arguments”.  This is explained in the below video:

Video 2: Executing procedure with Parameters.


The screen shot below shows creating a stored procedure with default parameters and executing it. The procedure is same as the one created in the previous screenshot and only the parameters as shifted. That is Parameter @City kept first and @State kept next with default value.  The default parameter is marked as 1 in the below screen shot. Look at the Procedure execution (2, 3) and in the first execution (3) we passed both the parameters. In this case, the passed-in parameter ‘UT’ replaces the default value ‘CA’ (Shown as 4).  In the second execution we passed only one argument value ‘Covelo’ for the parameter @City and the parameter @State takes the default value ‘CA’. It is good practice to have all the default parameters towards the end of the parameter list. Otherwise, the execution marked as 2 is not possible and you should go with Passing Named arguments.

Figure 8: SQL Stored Procedure with Default Parameters

5. SQL stored procedure returning data

The three important ways of sending the data to the caller of the stored procedure is listed below:
1) Stored Procedure Returning value
2) Stored Procedure Output parameter
3) Select from stored Procedure
We will look at each technique one by one.

5.1 SQL Stored Procedure Return statement


In this technique, the procedure assigns a value to a local variable and returns that. A procedure can directly return a constant value also. In below example, we created a procedure that returns the total number of author. When you compare this procedure with the previous one, you can see the Print statement is replaced by the return statement.

Figure 9: SQL Stored Procedure with return statement

OK. Now let us see how we can execute the procedure and print the value returned by the procedure. The procedure execution requires declaring a variable and printing that after the procedure execution. Note that instead of print statement you can use Select statement like “Select @RetValue as OutputValue”. The below code snippet shows both the way of checking the procedure return value:

Figure 10: Exec Stored procedure with return statement

5.2 SQL Stored Procedure OUTPUT parameter


The return statement can be used to return a single variable and that we saw in the previous example. Using the Output parameter type a procedure can send one or more variable values to the caller. The output parameter is indicated by the Keyword “Output” while creating the procedure. When a parameter is specified as an output parameter, the procedure body should assign a value to it. Have a look at the Procedure example given below:

Figure 11: Stored Procedure with Output Parameter type

In the above procedure, two output parameters named @TotalAuthors, @TotalNoContract is specified in the parameters list. These variables are assigned values inside the procedure body. When we use output parameters the caller can see the value that is assigned inside the body of the procedure. The below picture shows executing the Procedure and retrieving the values through the output parameter:

Figure 12: Exec stored procedure with output parameter

In the above script, two variables are declared to see the values packed by the stored procedure in the output parameter and this is marked as 1. Then the procedure is executed by supplying the normal parameter values CA and Berkeley. The third and fourth parameters are output parameters and hence the declared variables @OutputVar1, @OutputVar2 are passed to the procedure (Marked as 2). Note that while passing the variables the keyword output is specified here also. Once the procedure is successfully executed the values returned through the output parameters are printed to the messages window and this is marked as 3.

5.3 Select from stored procedure


The Select…From technique is used to return a set of values in the form of a table of data (RecordSet) to the caller of the stored procedure. In the below example the stored procedure accepts a parameter called @AuthID and queries the table Authors by filtering the records returned by using this @AuthId parameter. The Select statement is marked as 1 in the below picture decides what needs to be returned to the caller of the stored procedure. When executing the stored procedure “Author Id” is passed in (Shown as 2). The result of executing the stored procedure is marked as 3. The example procedure here always returns only one record or none. But a stored procedure does not have any restriction to return more than one record. Look at Fig.6 which shows returning data through select involving the computed variables.

Figure 13: Select from stored procedure

6. Conclusion


A stored procedure is powerful compared to a function in the front end doing the same thing. Since the stored procedure gets executed in the server itself, the data exchange in huge volumes between server and client application (for certain computations) can be avoided. In this article, combining the data return techniques (For Example- Procedure returning data through return statement as well as an output parameter) is not shown. But you can learn that yourself.  Bye for now.

Wednesday, September 10, 2014

C# - Dynamically updating the App.Config file

1. Introduction


In the last article, we saw how to use the app.config file, its hierarchy, and priority in picking the application settings. In this article, we will see updating the app.config file at runtime. The article example shows adding, deleting and updating a key entry the app.config file.

Have a look at the below screenshot taken from the app.config file:


The boxed item is one pair of setting; say for an example, an application by providing the key Key_4 can access the setting value value_4. In our article, we are going to see how do we add, delete and update these kinds of settings that come under "appSettings Section" of the configuration file.

2. About the example

The screenshot of the sample application is shown below:



The result of the action performed on the application configuration file (App.config) is communicated through the “Display Area”. To add (Through button Marked as 5) a setting to the application configuration file, the key and value should be entered in the Setting Key, Setting Value text boxes marked as 2 and 3 in the screenshot. To retrieve (Marked as 6) the setting, the key should be specified in the Setting Key text box. The retrieved key is displayed in the display area as well as in the Setting value. To modify (Through button marked as 7) an existing setting, specify the existing key and new modified value in the Setting Key, Setting Value respectively. To delete (Marked as 8) a key specify the key in the Setting Key. Note all these actions are performed at run time.

OK. Let us go through the coding part of this example.



3. Adding New Configuration Entry Dynamically

1) To add a new Application configuration entry, first we should make sure that the entry does not exist in the Application configuration. In the below code, first the application configuration file is opened by making a call to "OpenExeConfiguration() Method". Note that the parameter is passes is the executable file name. Then "KeyValueConfigurationCollection" object is retrieved from the opened configuration file through the "AppSettingsSection". The reference stored in app_settings now represent the entire key, value pair of the entries that present inside the <appSettings>. To get a particular entry in the <appSettings> section, the key name is given to the collection app_settings. In our case, the value entered in the “Setting Key” text box is supplied to the app_settings to retrieve single pair that resides in <AppSettings> and if the key does not exists, a null is returned instead of a valid "KeyValueConfigurationElement" object. Below is the code that makes sure the setting entry exists:

//2.1 Get AppSettings section from the Config
Configuration config = ConfigurationManager.OpenExeConfiguration("ReadConStr.exe");
AppSettingsSection App_Section = (AppSettingsSection)config.GetSection("appSettings");
KeyValueConfigurationCollection app_settings = App_Section.Settings;
KeyValueConfigurationElement element = app_settings[txtKey.Text];
if (element == null)
{

2) Once we validated that the setting key is new, we allow adding a new setting entry in the application setting file. A new key-value pair element is created and added to the instance of Key-Value configuration collection. Once the collection is updated with the new entry, the application configuration file is saved to store this new entry. Below is the code:

//2.2 Grab Key-Value pair & Make your Change
KeyValueConfigurationElement new_element = new KeyValueConfigurationElement(txtKey.Text, txtValue.Text);
app_settings.Add(new_element);

//2.3 Save the new appsection to App.Config
config.Save();
txtDisplayOutput.Text = "Saved";

3) Apart from this, remaining code is written to specify the result of the Action (Adding a Config. Entry). Below is entire code for adding a configuration entry:


//Sample 02: Adding application Settings to Config File
private void btnAdd_Click(object sender, EventArgs e)
{
    //2.1 Get AppSettings section from the Config
    Configuration config = ConfigurationManager.OpenExeConfiguration("ReadConStr.exe");
    AppSettingsSection App_Section = (AppSettingsSection)config.GetSection("appSettings");
    KeyValueConfigurationCollection app_settings = App_Section.Settings;
    KeyValueConfigurationElement element = app_settings[txtKey.Text];
    if (element == null)
    {
        //2.2 Grab Key-Value pair & Make your Change
        KeyValueConfigurationElement new_element = new KeyValueConfigurationElement(txtKey.Text, txtValue.Text);
        app_settings.Add(new_element);

        //2.3 Save the new appsection to App.Config
        config.Save();
        txtDisplayOutput.Text = "Saved";
    }
    else
    {
        txtDisplayOutput.Text = "Key Already Exists";
    }

    //2.4 Clean up
    txtKey.Text = "";
    txtValue.Text = "";
}


4. Retrieving the Configuration Entry without re-starting

When an application setting is added dynamically it can be retrieved dynamically. The code below retrieves the newly added application entry without re-launching the application again. Here we check that the key entry already exists in the application configuration file and if so, we display the corresponding value in the Display Area.

//Sample 03: Read Application settings
private void btnAppSettings_Click(object sender, EventArgs e)
{
    //3.1 Get AppSettings section from the Config
    Configuration config = ConfigurationManager.OpenExeConfiguration("ReadConStr.exe");
    AppSettingsSection App_Section = (AppSettingsSection)config.GetSection("appSettings");
    KeyValueConfigurationCollection app_settings = App_Section.Settings;

    //3.2 Retrieve Specific element by Key and Display it
    KeyValueConfigurationElement element = app_settings[txtKey.Text];
    if (element != null)
    {
        txtDisplayOutput.Text = element.Value;
        txtValue.Text = element.Value;
    }
    else
    {
        txtDisplayOutput.Text = "Setting Not found";
    }
}

5. Modifying the application configuration Entry

To modify the key-value pair in the application configuration, grab the key value pair, remove it and add the new one. In the below code we search for the specific key, grab the configuration entry and drop it. Then, a new entry with the same key name and modified value is pushed into the application configuration collection. As the code is similar to what you learned already, I am skipping the further explanation here. Below is the complete code for modifying the configuration entry:

//Sample 04: Modify Application Settings
private void btnModify_Click(object sender, EventArgs e)
{
    //4.1 Get AppSettings section from the Config
    Configuration config = ConfigurationManager.OpenExeConfiguration("ReadConStr.exe");
    AppSettingsSection App_Section = (AppSettingsSection)config.GetSection("appSettings");
    KeyValueConfigurationCollection app_settings = App_Section.Settings;
    KeyValueConfigurationElement element = app_settings[txtKey.Text];

    //4.2 Grab Key-Value pair & Make your Change
    if (element != null)
    {
        element.Value = txtValue.Text;
        app_settings.Remove(txtKey.Text);
        app_settings.Add(element);

        //4.3 Save the new appsection to App.Config
        config.Save();
        txtDisplayOutput.Text = "Setting Modified";
    }
    else
    {
        txtDisplayOutput.Text = "Setting Not Found";
    }

    //4.4 Clean up
    txtKey.Text = "";
    txtValue.Text = "";
}

6. Deleting the application configuration Entry

By this time, you may be aware how to do this. Yes, similar to previous section code except after dropping the configuration section we save the configuration file.

//Sample 05: Delete Application Settings
private void btnDelete_Click(object sender, EventArgs e)
{
    //5.1 Get AppSettings section from the Config
    Configuration config = ConfigurationManager.OpenExeConfiguration("ReadConStr.exe");
    AppSettingsSection App_Section = (AppSettingsSection)config.GetSection("appSettings");
    KeyValueConfigurationCollection app_settings = App_Section.Settings;
    KeyValueConfigurationElement element = app_settings[txtKey.Text];

    //5.2 Remove the setting by Key
    if (element != null)
    {
        app_settings.Remove(txtKey.Text);
        config.Save();
        txtDisplayOutput.Text = "Setting Removed";
    }
    else
    {
        txtDisplayOutput.Text = "Setting Not Found";
    }

    //5.4 Clean up
    txtKey.Text = "";
    txtValue.Text = "";
}

The video, given below show how the Example application works:


Source Code : Download

Tuesday, September 02, 2014

[ MFC ] - Get File Properties using CFileStatus and CFile::GetStatus

1. Introduction


We can get important file properties by making a call to the CFile’s static function GetFileStatus(). This function accepts CFileStatus object as a reference parameter and fills this object with the required information. In this article, I will show how do we use this object to retrieve some important file attributes.



2. About the example


The below screenshot shows the screen shot of example:




When the browse button is clicked, a file selection dialog is displayed. Once the file is picked from the file selection dialog, the file properties displayed in the dialog as described in the screen shot.



3. Creating the Application


Create a dialog based MFC application and name it as FileProp by accepting all the default settings in wizard pages. Once the application is created the class view looks like below:



The below-shown screen shot will help you in designing the application dialog. The dialog marked with number 1 is text boxes and all three check boxes marked as 3 are placed in the group box marked as number 2. Rename of the OK button as close and add a browse button marked as 4. That is all; the dialog is ready with the UI elements.





After the UI design, each control should be assigned with control variable and this association is shown in the below table:

UI Element
Control Variable Name
File Name Edit Box
m_ctrl_edit_filename
Created On Edit Box
m_ctrl_edit_filecreatedon
Modified On Edit Box
m_ctrl_edit_filemodifiedon
File Size Edit Box
m_ctrl_edit_filesize
Read Only Check Box
m_ctrl_chk_readonly
Hidden Check Box
m_ctrl_chk_hidden
System Check Box
m_ctrl_chk_system

To know how to add a control variable watch the video shown in the below link:


4.  Coding – Pick File


To select a file in the system, MFC’s common file dialog is used. An Instance of CfileDialog is created and then the dialog is displayed using the DoModal call. The displayed dialog is shown below:



The file name selected in this dialog is retrieved through CfileDialog’s member function GetPathName(). Even though the name states path name, we actually get filename with fully qualified path. The code for this is shown below:

//Sample 3.0: Get File Properties
void CFilePropDlg::OnBnClickedButtonBrowse()
{
                //Sample 3.1: Get Name of the File
                CFileDialog dlg ( TRUE, NULL, NULL, NULL, "All Files|*.*||" ) ;
                if ( dlg.DoModal( ) == IDOK )
                {
                                m_filename = dlg.GetPathName() ;       
                                m_ctrl_edit_filename.SetWindowText(m_filename);
                }



5. Coding – File Created and Modified time


We have the file name from the previous section of code. Now we will retrieve the properties of this selected file. As already specified in the introduction, CFileStatus object is passed to the GetStatus function of the CFile and this call fills the file properties in the CFileStatus structure. In the below code, m_ctime, m_mtime members are used to get the file created time as well as file modified time.

These members (m_ctime, m_mtime) are CTime instances and using the Format function of the CTime, the time (i.e.) created time and modified times are formatted in more readable format. Then this formatted time is displayed in the application example. Below is the code:

                //Sample 3.2: Get the File Status Object
                CFileStatus status;
                CFile::GetStatus(m_filename, status);

                //Sample 3.3: Get File Creation, Modification
                CString time_formatted;
                CTime time;
                time = status.m_ctime;
                time_formatted = time.Format("%d-%b-%Y   %H:%M:%S");
                m_ctrl_edit_filecreatedon.SetWindowText(time_formatted);
                time = status.m_mtime;
                time_formatted = time.Format("%d-%b-%Y   %H:%M:%S");
                m_ctrl_edit_filemodifiedon.SetWindowText( time_formatted);


6.  Coding – File Attributes and Size


The size of the file is retrieved through the member m_size. This member is formatted in a CString object then displayed on the dialog. Below is code for it:

//Sample 3.4: Get File Size
CString file_size;
file_size.Format("%ld", status.m_size );
m_ctrl_edit_filesize.SetWindowText(file_size);

The file attributes are collected by the Bitwise OR operation when we make a call to the GetStatus function. To make a check for the particular attributes we should do Bitwise AND operation. The hexadecimal decimal constant for each attributes are given below: 

enum Attribute {
   normal =    0x00,
   readOnly =  0x01,
   hidden =    0x02,
   system =    0x04,
   volume =    0x08,
   directory = 0x10,
   archive =   0x20
   };

OK. In the CFileStatus these attribute values are pushed into the member m_attribute and the constant values listed above are tested using the Bitwise AND operation. In the below code we do such a test and place/remove the check mark on the corresponding the checkboxes.

//Sample 3.5: Get the File Attributes
if ( (status.m_attribute & 0x01) == 0x01 )
                m_ctrl_chk_readonly.SetCheck(BST_CHECKED);
else
                m_ctrl_chk_readonly.SetCheck(BST_UNCHECKED);

if ( (status.m_attribute & 0x02) == 0x02)
                m_ctrl_chk_hidden.SetCheck(BST_CHECKED);
else
                m_ctrl_chk_hidden.SetCheck(BST_UNCHECKED);

if ( (status.m_attribute & 0x04) == 0x04)
                m_ctrl_chk_system.SetCheck(BST_CHECKED);
else

                m_ctrl_chk_system.SetCheck(BST_UNCHECKED);

The below video Explains and shows how the sample application works.

Watch How Sample works. Video

Source Code : Download

Monday, July 21, 2014

C# - App.config file in C-Sharp windows application

1. Introduction

Let us say you are developing an Internet modem device monitor utility application. The duty of the application will be monitoring for Upload and download activity of the modem. When there is heavy download say 500KB per second of download for past 10 Minutes, the device should intimate that to a user. The intimation can be done either by blinking a Red LED modem device or by making continues beep or by making the entire desktop with red transparent overlay (System got RED by download J). But, which option the utility should choose from? Now I hear you are saying keep it as an "Application Setting".

In old days, Legacy windows desktop software uses section based Initialization configuration files ("INI Files") and application reads and writes from it. The settings are placed on sections which are nothing but grouping the settings under a certain category. Now in dot net days, the INI files are replaced as application CONFIG files. Application settings are placed in this XML format application configurations files. In this article, I will explain how configuration files can be accessed by the application with an example.


2. Configuration File Hierarchy

The configuration file has a hierarchy and the application will search for required settings based on this hierarchy. Have a look at the below picture:



The first thing to be noted is that Web Application looks for settings in the web.Config” whereas windows desktop application will look for the settings in the “App.Config” file. Let us say a web server machine Johns-Kitten is hosting three websites and all three websites are from the same publishers.  Now the settings common to all three websites goes to “Root.Web.Config” and settings specific to one website or solution goes to the "web.config file".

The settings, which are common to both web application and desktop application, can go in “Machine.config File". The settings defined in the bottom most overrides the settings given in the above level. For example, the setting given in the web.config overrides the settings given in the “Machine.Config” file (i.e.) if the same setting is defined in both the machine.config and web.config, web.config setting takes the precedence. In this article, we are going to explore how do we use the App.Config file in desktop c# windows applications.


3. About the Example

Have a look at the below application screen shot:


Clicking the “Application Setting” button marked in the picture (as 4) will retrieve the application settings from the App.config file and displays that in the Display Output (Marked as 1).  A key will specify the setting that needs to be retrieved and that key will be entered in the textbox marked as 3. Clicking the button “Get Connection string” (Marked as 2) will retrieve the connection string defined in the app.config file and displays that in the display output. Specifying the name of the connection string will retrieve the connection string.  In our sample, connection string name defined in the App.config file is referred by the checkboxes marked as 5 in the screenshot.

4. Create the Project

Create a Windows C# application called ReadConStr. Once the exe project is created we can store all the application settings for this ReadConStr.exe into a configuration file called App.Config and read the settings from it through the “system.configuration” assembly. To add this assembly to the ReadConStr project:


  1. Open the Solution Explorer
  2. Right Click the Reference node
  3. Click on the “Add Reference…” menu item from the context menu.


This will display an “Add Reference” dialog. You can pick the System.Configuration assembly from this dialog as shown in the below screenshot. Once you pick the System.Configuration from the add reference dialog, the reference is added to the project and you can examine that by expanding the reference node.



After adding the reference, add the using statement to make use of the application configuration file:

//Sample 01: Required Config Entries
using System.Configuration;

5. Add Appsettings and ConnectionString

OK. Our application is ready to read configuration settings and where is the configuration file? We should add this configuration file to the application.  To add the application configuration file to the project follow the steps listed below:

1) Go to “Solution Explorer”
2) Right click the project name “ReadConStr” and chose “Add=>New Item…”





3) In the “Add New Item” dialog, select the option, “Application Configuration File” and leave the default name “App.Config” as it is. And click on the “Add” button. This will add the application configuration file to your project.


Once the configuration file (App.config) is added to the project, we can place our application wide project settings into this configuration file. To add the application settings, open the app.config file from the solution explorer and add the following code:

The application keeps all the settings inside the AppSettings tags. The setting goes with a "key-value pairs". For example, the application will ask for the setting by providing the key say “Key_4” and gets the corresponding setting value say, Value_5.  Like application settings, Connection string occupies a separate section as shown below:


In the above case, the database connection strings are enclosed between ConnectionStrings tags. The application specifies the “name” and retrieves the connection string and/or provider details. In the above example, by knowing the name “XYZ1”, the application can get connection string “XYZ2” and provider name “XYZ3”. If you are not expertise in the connection string, Watch the below video to place connection string in your App.Config file:


6. Reading AppSettings from App.Config

The event handler for the “Application Setting” button is given below:

//Sample 02: Read Application settings
private void btnAppSettings_Click(object sender, EventArgs e)
{
    string setting = ConfigurationManager.AppSettings[txtKey.Text];
    if (setting == null)
        setting = "Setting not found";
    txtDisplayOutput.Text = setting;
}

The "ConfigurationManager Class" has the collections AppSettings, ConnectionStrings. In this event handler, we refer the AppSettings map and retrieve the required setting value by supplying the key. The key will be specified text box noted as 3 in the application screenshot. The retrieved value is shown in the result display area.


7. Reading ConnectionString from App.Config

Now have a look at the code below, which reads the connection string setting from the App.Config file:

//Sample 03: Read Connection String
private void btnConString_Click(object sender, EventArgs e)
{
    //3.1 Declarations
    ConnectionStringSettings setting_constr = null;
    string con_str = "";

    //3.2 Get Connetion String for Pubs and/or NorthWnd
    if (chkPubs.Checked == true)
    {
        setting_constr = ConfigurationManager.ConnectionStrings["Pubs"];
        con_str = setting_constr.ConnectionString + Environment.NewLine;
    }
    if (chkNWnd.Checked == true)
    {
        setting_constr = ConfigurationManager.ConnectionStrings["NWind"];
        con_str = con_str + setting_constr.ConnectionString;
    }
    if (con_str == "")
        con_str = "Specify either Pubs or NWnd";

    //3.3 Display the output
    txtDisplayOutput.Text = con_str;
}

The above code runs the when the “Get Connection String” button is clicked in the form. Here also we refer the ConnectionStrings map from the ConfigurationManager object to get the corresponding "ConnectionStringSettings" object. This connection string map requires the name as the key. Look at our sample connection strings in the app.config; XYZ1 and abc1 are the names of the connection strings. The actual connection string is then retrieved from this ConnectionStringSettings object. In our example, we assign the ConnectionStringSetting object into setting_constr when the corresponding checkbox is checked. The “ConnectionString” property from this setting_constr will be referred to get actual connection string text. Once we have connection string setting that will be displayed in the display output.


8. Changing the fore color of display area

Now let us assign the fore color for the output display area. This setting shows a real-time example of how the configuration file will be used to change application behavior without re-compiling it. Have a look at the below code which reads the fore color setting from the configuration file and sets the multi-line textbox accordingly:


//Sample 4: Application Background Color
private void frmAppConfig_Load(object sender, EventArgs e)
{
    //4.1: Read setting
    string ColorSetting = ConfigurationManager.AppSettings["FontColor"];
    Color ForeColor;

    //4.2 Have a relevent Color
    if (ColorSetting == "Red")
        ForeColor = Color.Red;
    else if (ColorSetting == "Blue")
        ForeColor = Color.Blue;
    else
        ForeColor = Color.Green;

    //4.3 Assign it to the Text Display
    txtDisplayOutput.ForeColor = ForeColor;
}

The above code is written in the form load event handler. The code is similar to what you saw in section 6, but here we use the application setting to change the behavior of the textbox. Or simply, the text display color of the textbox will get changed based on the color setting given in the application configuration file.


9. Running the Application – Video

The below-given video shows the how the example works with the application configuration file. This video also shows changing the behavior of the textbox just by changing the configuration file.

Video: Running the Application 





Source Code: Download
Like this site? Tell it to your Firend :)