Address Book Tutorial in C#.Net using SQL Server Database : Part 2

This is a tutorial series on c#.net for developing full software using sql server as backend and c# in front end, this is desktop application but you can easily create this same application in ASP.Net or even on ASP.Net MVC application the only place you need to work is the front end part. In Web based application you need to design front page using html and css. So the target audience of this c# tutorial is not only desktop but also web based application developer or learner. Now lets start or second part of the tutorial. For the first part you can visit this link. Address Book Tutorial in C#.Net using SQL Server Database : Part 1

 

Last time we designed windows form in visual studio this time we will add code for connection and adding new address data in sql server database.

Fire up you last visual studio project, i.e AddressBook project, now add new class file named 'Connection.cs' , we will add our connection code here. Put the following code for connecting to sql server database. 

using System;
using System.Configuration;
using System.Data.SqlClient;

namespace AddressBook
{
    class Connection
    {
        static SqlConnection conn = new SqlConnection();
        public static string sqlError { get; set; }

        private Connection(){}
      

        public static SqlConnection Get_Connection()
        {

            if (conn.State == System.Data.ConnectionState.Closed && conn.State!= System.Data.ConnectionState.Connecting )
            {

                try
                {
                    string strconnection = ConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString;
                    conn.ConnectionString = strconnection;
                    conn.Open();
                    return conn;
                }
                catch (SqlException ex)
                {
                    
                        sqlError = ex.Message;
                        Log.Save( "Get_Connection:" +sqlError);
                        return null;

                }
                catch(Exception ex)
                {
                    sqlError = ex.Message;
                    Log.Save("Get_Connection_1:" + sqlError);
                    return null;
                }



            }
            return conn;
        }
    }
}

In the above code we are reusing same connection over and over again instead of creation new one so our static function Get_Connection first checks if there is already a connection made earlier or if there is any active connection then instead of connecting to sql server its returning back the connection object to reuse. Before opening a connection we have to specify connection string , in the above example we are actually storing connection string in a configuration file and getting value from that file. The configuration file is App.config file. let me show you how the App.config file looks like.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
  <connectionStrings>
    <add name="SQLServer" connectionString="Server=YAM-PC\SQLEXPRESS;Database=lxinqdatabase;Trusted_Connection=true;"/>
    
  </connectionStrings>
</configuration>

Please note in my App.config file the connectionStrings bock "Server=YAM-PC\SQLEXPRESS;Database=lxinqdatabase;Trusted_Connection=true;" is the string responsible to connect to the database here my database name is lxinqdatabase but if you are following this tutorial then the database name should be addressbook, similarly YAM-PC is my pc name and SQLEXPRESS is the sql instance running in my computer , you need to modify these settings according to your computer configuration. If you dont have App.config file you can create one right clicking project in solution explorer and add new item and select Application Configuration File, as shown in the following screenshot.

Microsoft Visual Studio configuration file

Now lets create another helper class which will use our connection static class to run any sql command. 

Create new class named Address_Data and write the following code.

using System;
using System.Data;
using System.Data.SqlClient;

namespace AddressBook
{
    class Address_Data
    {

        public string sqlError { get; set; }

        public int ExecuteSQL(SqlCommand cmd)
        {

            SqlConnection conn = Connection.Get_Connection();
            if (conn.State != System.Data.ConnectionState.Open)
            {
                sqlError = Connection.sqlError;
                return -1;

            }
            cmd.Connection = conn;
            try
            {
                return cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                sqlError = ex.Message;
                Log.Save("ExecuteSQL:" + sqlError);
                return -1;
            }
            catch (Exception ex)
            {
                sqlError = ex.Message;
                Log.Save("ExecuteSQL_1:" + sqlError);
                return -1;
                

            }
        }

    }
}

Here you can see this class is utilizing the Connection class we created before for getting the sql connection.

Finally lets create another class named Address which holds the code responsible to add new data.

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace AddressBook
{
    public class Address
    {
        Address_Data adata = new Address_Data();

        public int id { get; set; }

        public string Name { get; set; }

        public string Phone { get; set; }

        public string Address_ { get; set; }

        public string sqlError { get; set; }

        public Address() { }

        public Address(int id, string name, string Phone, string Address)
        {
            this.id = id;
            this.Name = name;
            this.Phone = Phone;
            this.Address_ = Address;

        }

     

        public bool Add()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = @"INSERT INTO Address(Name,Phone,Address)
                                    VALUES(@Name,@Phone,@Address)";
            cmd.Parameters.AddWithValue("@Name", Name);
            cmd.Parameters.AddWithValue("@Phone", Phone);
            cmd.Parameters.AddWithValue("@Address", Address_);



            int result = adata.ExecuteSQL(cmd);
          
            if (result == 1)
            {
                return true;

            }
            sqlError = adata.sqlError;
            return false;
        }
}
}

and finally we can use this class from our front end add button. The code for add button goes like this:

   Address address = new Address
            {
                Name = txtName.Text.Trim(),
                Phone = txtPhone.Text.Trim(),
                Address_ = txtAddress.Text.Trim(),
               

            };
bool result = address.Add();

if(result)
MessageBox.Show("Address Saved!");
else
MessageBox.Show("Error Saving Address!, " + address.sqlError);

You might want to add few data checking before saving address book , like phone, name or address length. In this point if you run your software and click the add button after writing name , phone and address in the respective textbox you will be presented by dialog box saying "Address Saved" or if there is any error then "Error Saving Address!" along with the error description.

This ends our C#.Net tutorial part two, in the next C#.Net tutorial i will add more functionality in this program. If you have any suggestion or query please free to contact me via contact us page.