sábado, 12 de novembro de 2011

Creating a web-site (Sign-up, delete, select existing itens and update) - Part 3

Good night my friends...
I 'm sorry for my absence , but I'm very very very busy to update this blog recently, but I promisse that I will try to post more things!
This week I changed my job and I started to work in another company called "Simpress".
A good company wich is giving me a good knowledge, challenges and...
This is very good because I will teach you all of I am learning.
Let's go then,

Today we will begin to develop the second layer: DAL
But first, We need to get the string connection.
This point is very important and necessary because we will to use this "string connection" to connect the Visual Studio 2010 with Sql Server 2008 Express.
Follow the steps:
1.Create a new file: "Connection.txt"
2.Rename this file: "Connection.udl"
3.Open the file
Picture 1: Discovering your string connection

Open your Sql Server 2008 and look your server name
Picture 2: Identifying the name of your "server name"

Save that name in your mind, and open again your "Connection.udl".

Follow the steps:
1. Select or enter a server name: Put here your server name \SqlExpress. 
2. Select the database on the server: Here is where you need to select which database stays their tables,  storeds procedures about this project.
3. Now, click in this button to check if the connection is correct (Look the picture 4 and observe one thing: if your connection stays correct, a message like in this picture will appear in your screen)
4. Finally, click in button "ok".

Picture 3:  Discovering your string connection [2]

Picture 4: Discovering your string connection [3]

Rename the file "Connection.udl" to "Connection.txt" and open.
Picture 5:  Discovering your string connection [4]

Ok...
We discovered the connection string, now open your Microsoft Visual Studio 2010
DataSet
DataSet is always a bulky object that requires lot of memory space compare to DataReader. We can say the dataset as a small database coz it stores the schema and data in the application memory area. DataSet fetches all data from the datasource at a time to its memory area. So we can traverse through the object to get required data like qureying database.


DataAdapter
The data adapter stores your command (query) and connection and using these connect to the database when asked, fetch the result of query and store it in the local dataset.

Class Connection.cs


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


namespace DAL
{
    public class Connection
    {
        //Put your connection string here
        string strConexao = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Prod;Data Source=Juninho-pc\\SqlExpress;";
        SqlConnection connection; //creating an sqlconnection object

        /*
         * Method name: AbreConexao()
         * Description: open the connection with the Sql Server 2008 Express
         * Return: SqlConnection object
        */
        private SqlConnection AbreConexao()
        {
            connection.ConnectionString = strConexao; //the property ConnectionString of the object "connection" receives the connection string
            connection.Open(); //open the connection
            return connection;
        }

        /*
         * Method name: FechaConexao
         * Description: close the connection with the Sql Server 2008 Express
         * Parameters: An SqlConnection object
        */
        private void FechaConexao(SqlConnection connection)
        {
            //Here we check if there is a connection open
            if (connection.State == System.Data.ConnectionState.Open)
            {
                connection.Close(); //if exist, we can close the connection
            }
        }
        /*
         * Method name: ExecutaComando
         * Description: The commands of the database: insert, update and delete will going to                   be execute here.
         * Parameters: An SqlCommand Object
        */
        public void ExecutaComando(SqlCommand cmd)
        {
            try
            {
                AbreConexao(); //first we need to open our connection calling the method "AbreConexao()"
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Connection = connection;
                cmd.ExecuteNonQuery(); //Executing the SqlCommand Object
            }
            catch (Exception e) //if an error occur
            {
                throw e;
            }
            finally //and last...
            {
                FechaConexao(connection); //we can close our connection

            }
        }
        /*
         * Method name: ExecutaDataSet
         * Description: The command of the database: select will going to be execute here.
         * Parameters: An SqlCommand Object
         * Returns: DataSet Object, because here we will list the registers to the user.
        */
        public DataSet ExecutaDataSet(SqlCommand cmd) //retorna um conjunto de registros
        {
            try
            {
                AbreConexao();

                SqlDataAdapter da = new SqlDataAdapter(); //creating a SqlDataAdapter Object
                DataSet ds = new DataSet(); //creating a SqlDataSet Object

                cmd.CommandType = CommandType.Text;
                cmd.Connection = connection;
                da.SelectCommand = cmd;
                cmd.ExecuteNonQuery();
                da.Fill(ds); //filling the DataSet Object

                return ds; //return the registers of our database
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                FechaConexao(connection);
            }
        }

    }
}


Class Department.cs
Add a new class to our project and put the name of: Department.cs
Reference this project to Web.BO:
Picture 6:  Adding a new reference for this project

Picture 7:  Adding a new reference for this project


Now, We can back to the code:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Web.BO;

namespace DAL
{
    public class Department: DepatmentBO
    {
        Connection conn = new Connection(); //creating a Connection Object
        /*
         * Method name: selectDepartament
         * Parameters: DepartmentBO Object
         * Description: consult the database and returns the departments
         * Return: DataSet object wich contains all of the departments
        */
        public DataSet selectDepartament(DepatmentBO department)
        {
            SqlCommand cmd = new SqlCommand();
            DataSet ds = new DataSet();

            //Parameters of our consult, observe the syntax: @parameter (same name of the database), Department Object.(encapsulated field of the same field)
            cmd.Parameters.AddWithValue("@name", department.Name);
            /* Here we need to insert the string "EXEC (for execute our stored procedure)", + (name of the stored procedure),
             * + @parameter (same name of the database) = @Parameter (same name of our encapsulate field in Web.BO/DepartmentBO.cs
             * */
            cmd.CommandText = "EXEC" + department.Sp_department_sel + "@name=@Name";
            ds = conn.ExecutaDataSet(cmd); //The datasource object receive the result of the cmd execute
            return ds; //return the registers of the consult
        }

        /*
         * Method name: insertDepartment
         * Parameters: DepartmentBO Object
         * Description: insert a new Department in the table of the database
        */
        public void insertDepartment(DepatmentBO department)
        {
            SqlCommand cmd = new SqlCommand();

            //Parameters of our consult, observe the syntax: @parameter (same name of the database), Department Object.(encapsulated field of the same field)
            cmd.Parameters.AddWithValue("@name", department.Name);
            cmd.Parameters.AddWithValue("@phone", department.Phone);
            cmd.CommandText = "EXEC" + department.Sp_department_ins + "@name=@Name; @phone=@Phone";
            conn.ExecutaComando(cmd);
        }

        /*
         * Method name: updateDepartment
         * Parameters: DepartmentBO Object
         * Description: update the fields of an exist department in the table of the database
        */
        public void updateDepartment(DepatmentBO department)
        {
            SqlCommand cmd = new SqlCommand();
            //Parameters
            cmd.Parameters.AddWithValue("@name", department.Name);
            cmd.Parameters.AddWithValue("@phone", department.Phone);
            cmd.CommandText = "EXEC" + department.Sp_department_update + "@name=@Name; @phone=@Phone";
            conn.ExecutaComando(cmd);
        }
        /*
         * Method name: deleteDepartment
         * Parameters: DepartmentBO Object
         * Description: delete a department in the table of the database
        */
        public void deleteDepartment(DepatmentBO department)
        {
            SqlCommand cmd = new SqlCommand();

            cmd.Parameters.AddWithValue("@cod_depto", department.IdDepto);
            cmd.CommandText = "EXEC" + department.Sp_department_delete + "@cod_depto=@IdDepto";
            conn.ExecutaComando(cmd);
        }
    }
}

Class Staff.cs


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Web.BO;

namespace DAL
{
    public class Staff : StaffBO
    {
        Connection conn = new Connection();
        /*
         * Method name: selectStaff
         * Parameters: StaffBO Object
         * Description: consult the database and returns the staffs
         * Return: DataSet object wich contains all of the staffs
        */
        public DataSet selectStaff(StaffBO staff)
        {
            SqlCommand cmd = new SqlCommand();
            DataSet ds = new DataSet();
            //Parameters
            cmd.Parameters.AddWithValue("@cod_staff", staff.Cod_staff);
            cmd.CommandText = "EXEC" + staff.Sp_staff_sel + "@cod_staff=@Cod_staff";
            ds = conn.ExecutaDataSet(cmd);
            return ds;
        }

        /*
         * Method name: insertStaff
         * Parameters: StaffBO Object
         * Description: insert a new Staff in the table of the database
        */
        public void insertStaff(StaffBO staff)
        {
            SqlCommand cmd = new SqlCommand();

            //Parameters
            cmd.Parameters.AddWithValue("@name", staff.Name);
            cmd.Parameters.AddWithValue("@wage", staff.Wage);
            cmd.Parameters.AddWithValue("@position", staff.Position);
            cmd.Parameters.AddWithValue("@admission_date", staff.Admission_date);
            cmd.CommandText = "EXEC" + staff.Sp_staff_insert + "@name=@Name; @wage=@Wage; @position=@Position; @admission_date=@AdmissionDate";
            conn.ExecutaComando(cmd);
        }

        /*
         * Method name: updateStaff
         * Parameters: StaffBO Object
         * Description: update the fields of an exist staff in the table of the database
        */
        public void updateStaff(StaffBO staff)
        {
            SqlCommand cmd = new SqlCommand();

            //Parameters
            cmd.Parameters.AddWithValue("@name", staff.Name);
            cmd.Parameters.AddWithValue("@wage", staff.Wage);
            cmd.Parameters.AddWithValue("@position", staff.Position);
            cmd.Parameters.AddWithValue("@admission_date", staff.Admission_date);
            cmd.Parameters.AddWithValue("@cod_depto", staff.Cod_depto);
            cmd.Parameters.AddWithValue("@cod_func", staff.Cod_staff);

            cmd.CommandText = "EXEC" + staff.Sp_staff_update + "@name=@Name; @wage=@Wage; @position=@Position; @admission_date=@AdmissionDate; @cod_depto=@Cod_depto; @cod_func=@Cod_staff";
            conn.ExecutaComando(cmd);
        }
        /*
         * Method name: deleteStaff
         * Parameters: StaffBO Object
         * Description: delete a staff in the table of the database
        */
        public void deleteStaff(StaffBO staff)
        {
            SqlCommand cmd = new SqlCommand();

            cmd.Parameters.AddWithValue("@cod_func", staff.Cod_staff);
            cmd.CommandText = "EXEC" + staff.Sp_staff_delete + " @cod_func=@Cod_staff";
            conn.ExecutaComando(cmd);
        }
    }
}

Uff. That's all!!!
See you guys in the next post.
Hugs \o