ADO.NET_WorkshopEnrollmentApp_HandsOn1 C# Cognizant Solutions

Hands On: Data Insert Operation with ADO.NET

Scenario:

 Your organization has decided to enroll trainees for a workshop on ART of LIVING. Help admin by creating an application to register trainees for the workshop.

Hands- On Description:

Here you will practice how to store data into Sql Server database using ADO.NET and C# by implementing 3-layer application architecture.

Data Design:

Table name: tblTrainee
Column Name	Data type	Constraints
Trainee_Id	bigint	Primary Key, Not Null
Trainee_Name	varchar(100)	Not Null
Batch_Code	varchar(100)	Not Null

The database connection information is stored in the “App.config” file, which is also provided as part of code skeleton/template.

Component Specification:

Create a class called TraineeBO (business object) with the below public auto-implemented properties:

Type (Class)	DataType	Properties
TraineeBO	long	TraineeId
string	TraineeName
string	BatchCode

Also add a default constructor and parameterized constructor in this class.

Create a class called TraineeDA with the below method: (This class contains data operations logic such as insert, update, delete or retrieve records to/from database)

Type (Class)	Method	Responsibility
TraineeDA	public bool AddTraineeDetails(TraineeBO objBO)	This method should accept a TraineeBO object and execute a sql query to insert a trainee record into the database. It returns true if the insertion is successful and false if it is failed to insert.

Use (as it is) the below code snippet in order to read the connection string values from the App.config file.

public string ConnectionString

{

  get

  {

       return ConfigurationManager.ConnectionStrings[“SqlCon”].ConnectionString;

  }

}

Create a class called TraineeBL with the below method: (This class contains business logic, validations and calculations related to the data. The Presentation Layer will communicate to Data Layer via this Business Layer)

Type (Class)	Method	Responsibility
TraineeBL	public bool SaveTraineeDetails(TraineeBO objBO)	This method should accept a TraineeBO object and call the TraineeDA’s AddTraineeDetails method that returns true if it successfully inserts data to the database; else returns false.

From the Program class’s Main method, call the TraineeBL’s method and test your application.

Business Rule:

 

Use try… catch block while working with TraineeDA and TraineeBLclass methods to catch exception.

TraineeBL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WorkShopEnrollmentApp
{
    //Write your code here
    public class TraineeBL
    {
        public bool SaveTraineeDetails(TraineeBO objBO)
        {
            TraineeDA tDA = new TraineeDA();
            bool res = tDA.AddTraineeDetails(objBO);
            if (res)
                return true;
            else
                return false;
        }
    }
}

TraineeBO.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WorkShopEnrollmentApp
{
    //Write your code here
    public class TraineeBO
    {
        public long traineeId;
        public string traineeName;
        public string batchCode;

        public long TraineeId { get; set; }
        public string TraineeName { get; set; }
        public string BatchCode { get; set; }

        public TraineeBO()
        {

        }

        public TraineeBO(long traineeId, string traineeName, string batchCode)
        {
            this.traineeId = traineeId;
            this.traineeName = traineeName;
            this.batchCode = batchCode;
        }
    }
}

TraineeDA.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace WorkShopEnrollmentApp
{
    //Write your code here

    public class TraineeDA
    {
        public string ConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["SqlCon"].ConnectionString;
            }
        }

        public bool AddTraineeDetails(TraineeBO objBO)
        {
            try
            {
                SqlConnection con = new SqlConnection(ConnectionString);
                con.Open();
                string query = "insert into tblTrainee values(" + objBO.TraineeId + ",'" + objBO.TraineeName + "','" + objBO.BatchCode + "')";
                SqlCommand cmd = new SqlCommand(query, con);
                int i = cmd.ExecuteNonQuery();
                if (i > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception e)
            {
                return false;
            }
        }
    }
}