Learn creating and running MS Unit in 7 days


Figure 1:
Developer-Tester conversation.

Introduction: As the title specifies “Learn MS Unit in 7 days” I will be learning and going deep down in executing unit test and writing article for the same, the intended focus is towards beginners so that they can easily grasp the Unit testing concepts and be ready test their application before deploying QA. So let’s get started with Day 1. In case you feel I have left the very important topic feel free to comment and any corrective measures and implementation are most welcomed. I will be using VS 2015 and C# programming language as for writing the programs.

Day1 Agenda    

  1. What is a Unit Test?
  2. Benefits of Using Unit Test
  3. Testing without Unit Test
  4. Drawbacks of Manual Testing
  5. Creating Your First Unit Test
  6. Running our Unit Test?

1.1 What is a Unit Test?

Unit test is a methodology of checking that your code is working as expected by creating and running unit test. This methodology is termed as Unit Test because we break down our behavior of our program into discrete testable behaviors so that we can test each and every scenario. This discrete testable behavior can be a class or a function.

Unit Testing Framework for C#

  • MS Test
  • CSUnit
  • NUnit
  • XUnit.Net

*Note: You will thinking that we can test our application by (Manual Testing) running our application and performing the test by input the value in the page (in case of web application), sending request using postman (if it’s a API) etc. The most important benefit of Unit Testing is that we don’t need to run application in order to perform our Unit Test, we test our application isolated from the UI theres when Unit test comes into picture.

Unit test belongs to the white box testing category.

In this whole series, we will be doing Unit Testing using MS Test. MS Test is integrated with visual studio ide.

1.2 Benefits of using Unit Test:

  • Unit testing help us in finding bugs in the early stage of the Project development.
  • Unit testing make our code and function easy to understand.
  • Easier to change and test the same.
  • Provide documentation for easy understanding.

With the help of above mentioned benefits of unit testing we can create our application bug free.

1.3 Testing without Unit Test

In order to test our application we do the Manual testing. Manual testing is the process of manually testing the application for defects. It requires tester to act as end user and test all the features of the application end to end.

Simple Small Manual Testing case, In order to test your function, class you need to create a simple application. The application can be any kind of application whether it’s web, console, service, mobile etc. Unit testing is required irrespective for the type of Application. So let’s start with a simple basic console application to demonstrate Manual testing with respect of developer.

Objective: We have Class called ArithmeticOperations which performs basic Arithmetic operations, as of now we have only one operation in the class called Addition i.e. which take two number and return the result of those numbers. You have to test the ArithmeticOperations class and its respective operations.

Step1. In order to start the testing we need to create the Console Application which will be our main UI application as show below:

Figure 2:
Creating Console Application.

Step2: Once our project gets loaded, let add new class library to the solution.

Figure 3:
Adding Class Library.

Step3: Now add Class Library project and name it as Arithmetic

Figure 4:
Select Class Library.

Step 4: Once the class library is created create your Class called ArithmeticOperations and create respective properties as shown below:

namespace Arithmetic
{
 ///
 <summary>
 /// ArithmeticOperations class is responsible for performing 
 /// all Arithmetic operations
 ///
 </summary>
 public class ArithmeticOperations
 {

 public int FirstNumber { get; set; }
 public int SecondNumber { get; set; }
 public int Result { get; set; }
 ///
 <summary>
 /// Addition method performs Addition of two numbers
 ///
 </summary>
 ///
 <param name="_firstNum"></param>
 ///
 <param name="_secondNumber"></param>
 ///
 <returns>Sum of two numbers</returns>
 public int Addition(int _firstNum,int _secondNumber)
 {
 this.FirstNumber = _firstNum;
 this.SecondNumber = _secondNumber;
  return this.Result = this.FirstNumber + this.SecondNumber;
} }}

So here we have created a Simple class called ArithmeticOperations
with its properties and one method Addition which take two integers as a parameter and return the Sum of both.

Now in order to use this class library in our program we need to add reference of Arithmetic class library into our main project as shown below:


Figure 5:
Add reference of class library


Figure 6: Adding library to the Console application.

And check the checkbox Arithmetic Class library. So now we are ready to consume the namespace Arithmetic in our project.

Now in order to call the Addition method we need to Instantiate the ArithmeticOperations class and pass the required numbers as shown below:

using System;
using Arithmetic;
namespace MainProject
{
 class Program
 {
 static void Main(string[] args)
 {
 try
 {
 ArithmeticOperations arithmetic = new ArithmeticOperations();
 Console.WriteLine("Enter first number");
 arithmetic.FirstNumber = int.Parse(Console.ReadLine());
 Console.WriteLine("Enter second number");
 arithmetic.SecondNumber = int.Parse(Console.ReadLine());
  arithmetic.Result= arithmetic.Addition(arithmetic.FirstNumber, arithmetic.SecondNumber);
 Console.WriteLine($"The sum of {arithmetic.FirstNumber} and {arithmetic.SecondNumber} = {arithmetic.Result}");
 }
catch (Exception ex)
 {
 Console.WriteLine(ex.Message.ToString());
 }
 } }}

So now in order to check the sum I need to run the application and see whether the Sum of both the number is correct or not. If not then I need to run the whole application again. Let think if we have many classes and their function and we have to navigate to the desired function via UI which will take a lot of time to do, as we are doing above to check the result we are executing our application as shown below, press ctrl+F5 or F5.


Figure 7:
Output of the operation performed.

We can see the desired result and its fine as Sum of 10 and 20 is 30. What if in a hurry in Addition function we changed + to -. The whole meaning of method will change and will result in a bug.


 public int Addition(int _firstNum,int _secondNumber)
 {
 this.FirstNumber = _firstNum;
 this.SecondNumber = _secondNumber;
 return this.Result = this.FirstNumber - this.SecondNumber;
} 

Now once we run our application, we will find that 10 +20=-10 i.e. there is a bug in our application. So we will change the same to + again.


Figure 8:
Output after bug in the application.

1.4 Drawbacks of Manual Testing:

  • Time Consuming
  • If something goes wrong we need to stop our application and change the specific code and re run our application from start.
  • As it’s time consuming process we are not able to test all the scenarios.
  • Boring and lots of human intervention- No developer would like to keep filling the same forms again and again in order to test the scenario.

Now we have seen the testing application by running the whole application and testing the scenario from the UI to the desired functionality step by step. We can see the drawbacks of full loading of application and navigating to the desired UI to test the functionality, now what if I don’t want to load whole application and just want to check whether my Addition method is correct or not by passing the integer parameter from the code and specifying my expected output to the application. Here when MS Unit test comes into picture.

1.5 Creating your first Unit Test

As we have our class library Arithmetic and now in order to perform a unit test we will add a new project to the solution called Test as shown below:


Figure 9: Creating UNIT Test

Once done click ok and we are ready with our ArithmeticTest, We will see that Visual studio creates a Class UnitTest1 for us as shown below:

using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
namespace ArithmeticTest
{
 [TestClass]
 public class
 UnitTest1
 {
 [TestMethod]
 public void TestMethod1()
 {
 }
 }
}

We can also create a Unit Test just by right click on our Class method and select Create Unit Tests as shown below:


Figure 10: Other options available for Creating Unit Test.

Rename the project name or other fields as per you requirement. Code for Test Method options will provide you the default functionality like Assert.Fail() or throw
new
NotImplementedException();


Figure 11: Test Screen.

Here I will be following our Step by Step Process as we started earlier for the Unit test.

Let change the Name of our class ArithmeticTests

namespace ArithmeticTest
{
 [TestClass]
 public class ArithmeticTests
 {
 [TestMethod]
 public void TestMethod1()
 {
 }
 }
}

Now our ArithmeticTests class will contain all our test methods, in future if we add Divide, Subtraction, and Multiplication etc. We can add test cases for them as well in this class. Visual studio has placed an Attribute called TestMethod which specifies the information that this is a TestMethod, now we are given with TestMethod1 which as per of function is not meaning full so we will change the same to Test_Addition as shown below:

namespace ArithmeticTest
{
 [TestClass]
 public class ArithmeticTests
 {
 [TestMethod]
 public void Test_Addition()
 {
 }
}}

Now in order to test our ArithmeticOperations Class we need to address reference in our test project as shown below:

Figure 12: adding reference of Arithmetic Class library in our Unit test.

Enter ok and we ready to create our test for ArithmeticOperations.

Before writing our tests we need to understand AAA pattern in Unit Testing. AAA pattern stands for Arrange, Act and Assert patter.

For more information: http://c2.com/cgi/wiki?ArrangeActAssert

  • Arrange: Arrange the requirement or dependencies, properties, parameters, class initialization that are needed for the test to run. In our test we need two numbers to be passed to the Addition function.
  • Act: Invoke the code that is needed to be tested.
  • Assert: specifies the expectation from the test and if test doesn’t behaves as expected then result it into fail.

MS Test follows this pattern as it leverages us with many benefits as mentioned in above Url.

Let’s follow the AAA principle and write the code as shown below for the test:

 [TestMethod]
 public void Test_Addition()
 {
 //Arrange- Arrange the requirement
 int expectedSum = 30;
 ArithmeticOperations arithmetic = new ArithmeticOperations();
 arithmetic.FirstNumber = 10;
 arithmetic.SecondNumber = 20;
 //Act--Invoke the code that is needed to be tested
 int actualSum =arithmetic.Addition(arithmetic.FirstNumber, arithmetic.SecondNumber);
 //Assert- Expectation compared with actual result
 Assert.AreEqual(expectedSum, actualSum);
 }
 

We can see that how AAA pattern simplify our unit test code in more documented and readable manner.

Assert class has many static methods which we will discuss in our upcoming article, as of now Assert.AreEqual Verifies that specified values are equal.

1.5 Running our Unit Test

Now once we are ready with our AAA pattern we are ready to run our test, in order to run the test we can right click on testName and select Run Tests as shown below:

Figure 13: Running our First Unit Test.

As soon as we select this option vs will start running all are test present in our test project as shown below with the desired result pass or fail.

Figure 14: Result output of Unit test.

Running on the selected Test:

In order to run the selected test than go to Test menu bar and then go to window tab as shown below:

Figure 15: Running selected Unit test.

Test Explorer will open as shown below where we can select the test we want to run.

Figure 16: Test explorer window

Right Click on the test you want to run and then right click on test and select run selected test as shown below:

Figure 17: Running selected tests

The    specifies the test has passed the expectation, while if we change the expectedSum value to 20 and again run our test we can see the   failed scenario as well as shown below:

Figure 17: Showing Failed test.

Acknowledgements

We found how easy and efficient is to create a Unit test, as a practice I would suggest all developers to create unit test for best practices, in my scenario I was the developer and tester also so creating unit test cases gave me confident of my work. We have finally created our first Unit test. The test source code is uploaded to github https://github.com/SailleshPawar/MS-Unit-Test-Day1. Stay tuned for upcoming articles

Learn C# in 7 Days – Day 3

What we have learned last time?

In Day 2 we have learned following things

  1. Namespaces
  2. Classes
  3. Abstraction
  4. Encapsulation
  5. Inheritance
  6. Properties
  7. Access Modifiers

Before going ahead I would strongly recommend you to watch and learn .net in 60 days series for in depth practical knowledge of c#.


Day 3 Agenda

  1. Interface
  2. Abstract class
  3. Abstract classes vs Interface


In Day 3 we have kept few topics but these topics are important that need in depth knowledge in order to understand them, I hope that you will enjoy Day 3 of Learn tiny bit of C#.

Interface

Interface is like a class that cannot contains the definition i.e. it contains empty events, methods and properties. We create Interface using interface keyword. Just like classes interface contains properties, methods, members, delegates or event but only declaration and no implementation.

In one term we call Interface as a Contract because they force the class to follow the contract between Class and Interface.

SYNTAX:

interface
 ICustomer //Write Interface and name of Interface
{
void Bonus();//definition of method
}
 
  • Interface cannot contain Fields(variables)

E.g.

 public interface ICustomer
 {
 int CustomerName;
 }

If we try to declare a field inside an Interface we face a compile time error saying Interface cannot contain fields. We can declare a public


  • Interface member cannot have a definition.

This means that a function of Interface cannot have its definition of its own.

E.g.

 public interface ICustomer
  { void Print()
 {
 Console.WriteLine("Hello");
 } }.
 


How to implement Interface

As we inherit a class in C# using (:) same we have to do when we want to implement an Interface.

  • We need to Implement the Interface members

If we just implement the Interface and does not implement the properties or function it will threw a compile time error as shown below:

 public interface ICustomer
 {
 int _Name { get; set; }
 void Print();
  }
 public class Customer : ICustomer
 {
 }


  • When we implement the Interface the members should be Public

E.g.

 public interface ICustomer
 {
 int _Name { get; set; }
 void Print();
 }
  public class Customer : ICustomer
 {
  public int _Name {
 get { throw new NotImplementedException();
 }
 set {
 throw new NotImplementedException();
 }
 }
 void Print() {
 throw new NotImplementedException();
 }
 }


public interface ICustomer
 {
  int _Name { get; set; }
  void Print();
 }
 public class Customer : ICustomer {
 public int _Name
 {
 get {
 throw new NotImplementedException();
 }
 set
 {
 throw new NotImplementedException();
 }
  }
  public void Print()
 {
  throw new NotImplementedException();
  }
 }
 
namespace Interface
{
  interface ICustomer
  {
  void Bonus();
 }
 class Customer:ICustomer //implementing Interface
 {
 public void Bonus() //implementing the function of ICustomer Interface
 {
 Console.WriteLine("Bonus of this year");
 Console.ReadLine();
 }
 }

Interface member are public by default, and they allow explicit modifiers if a class inherits from an interface it must provide implementation for all interface members. Otherwise, we get a compiler error, as shown below.


Now once we implement the interface the above mentioned error gets resolved.


We can see the command prompt window with the output.

  • What if a Class implementing an Interface inherits another Interface?
using System;
namespace Interface
{
  interface ICustomer
 {
  void Print();
 }
  interface Icustomer2:ICustomer
 {
void Print2();
 }
  public class Customer:Icustomer2
 {
 public void Print2()
 {
  Console.WriteLine("ICustomer2 Method");
 }
 }
  class Program
 {
  static void Main(string[] args)
 {
 Customer objCustomer = new
 Customer();
 objCustomer.Print2();
 }
 }
}
 

Now once I try to build the same I face the below error:


I.E. when a Class implements an Interface which inherits the other Interface than the Class has to provide the implementation for both the Interface methods.

public class Customer:Icustomer2
 {
  public void Print2()
 {
 Console.WriteLine("ICustomer2 Method");
 }
  public void Print()
 {
 Console.WriteLine("ICustomer1 Method");
 }
 }

Now once I implement the other Interface method and try to build the application. The application gets successfully build.



  • We cannot create an instance of Interface, but an Interface reference variable can be point towards a class object which is implementing the Interface.

Now the questions comes why we should use of Interface

  1. ENFORCE the Standardization/ consistency(Contract)
  2. Decoupling

ENFORCE the Standardization/ consistency (Contract).

In law, a contract (or informally known as an agreement in some jurisdictions) is an agreement having a lawful object entered into voluntarily by two or more parties, each of whom intends to create one or more legal obligations between them. Source Wikipedia

Basically by the words contract we mean that class will implement all methods of Interface. Class that implements the Interface here by signs the Contract that I will implement all the methods, etc. of the Interface. We will be showing the example for the same in below example.

Decoupling

Decoupling as the word specifies dissociate (something) from something else, so here in programming world decoupling stand where we separate the dependency between our business layer concrete classes , UI layer and DAL Layer as shown below. As we have seen we cannot create an object of the Interface, but an Interface reference variable can point towards a derived class object.

So let’s get started we will create a Registration Page where will be inserting the User Details into the database without directly calling BAL or DAL from the concrete classes rather we will call these classes via an Interface. This topic will be little bit difficult to understand please focus a try to do practical of the same.

So switch to Visual studio.

  • I have created a web Application Named TestInterface which is basically a Web Form based application.
  • We create a UI as shown below


So when we create an Enterprise application we should make them in 3 layer architecture which means UI, BAL(Business Layer or Concrete classes) and DAL Data Access Layer.

So we have created our UI which generally talks to the concrete classes and then the concrete classes talks with DAL class to insert or update to delete information vice versa. In this scenario we make a strong coupling between our Layers that means that our Layers are not decoupled from each other in order to make them decoupled we make use of an Interface.

  • Now we will create our Business Class called User.cs which will contain properties related to the User like (FirstName, LastName, Address and MobileNo).
  • We create an Interface called ICRUD(Create, Read, Update and Delete) where we will define our Methods that are will be enforced to be implemented by the User Class.
  • We create our IRegister Interface which will implement ICRUD and will contains properties as shown below
public interface IRegister
 {
 string FirstName { get; set; }
 string LastName { get; set; }
 string Address { get; set; }
 string MobileNo { get; set; }
 }
  • Now we will also create an Interface which responsible for communicating between our BAL and DAL layer and will also decide which DB to be used. (sql server or oracle.
public interface IDb
 {
 ///
 

  /// Insert to method will take 4 parameters to save the information to the db 
 /// and return the generated ID for the User and same will be displayed to the User
 ///
 

 


 ///
 Coming from BAL RegisterUser _FirstName property
 ///
 Coming from BAL RegisterUser _LastName property
 ///
 Coming from BAL RegisterUser _Address property
///
 Coming from BAL RegisterUser _MobileNo property
 ///
 
  int Insert(string _FirstName, string _LastName, string _Address, string _MobileNo);
 }

Here I have only created on method while we can create all the operations that we need to perform as per our Business requirements.

Now once we have create our Interface lets implement the Interface in our BAL class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Interface;
 namespace BAL
 {
 public class User:IRegister
 {
  public string FirstName
 {
  get
 {
 throw new NotImplementedException();
 }
 set
 {
  throw new NotImplementedException();
 }
 }
  public string LastName
 {
 get
 {
 throw new NotImplementedException();
 }
  set
{
  throw new NotImplementedException();
 }
 }
  public string Address
  {
 get
 {
 throw new NotImplementedException();
 }
  set {
 throw new NotImplementedException();
 }
 }
 public string MobileNo
 {
 get
 {
 throw new NotImplementedException();
 }
 set{
  throw new NotImplementedException();
 }
}
  public int InsertUser()
 {
 throw new NotImplementedException();
  } 
}
}

*Note-
Our IRegister Interface implements ICRUD interface so now class has to implement both the functionality of IRegister as well as ICRUD Interface as shown above.

We will write our implementation functionality for UserClass .

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

namespace BAL
{
  public class User:IRegister
 {
  private int _UserId;
  private string _FirstName;
  private string _Address;
  private string _LastName;
  private string _MobileNo;
  public string FirstName
 {
 get
 {
 return _FirstName;
 }
 set
 {
 _FirstName = value;
 }
 }
  public string LastName
 {
 get
 {
 return _LastName;
 }
 set
 {
 _LastName = value;
 }
 }
  public string Address
 {
 get
 {
 return _Address;
 }
  set
{
 _Address = value;
  }
 }
  public string MobileNo
 {
 get
 {
 return _MobileNo;
 }
 set
 {
 _MobileNo = value;
 }
 }
  public int InsertUser(IDb obj)
 {
 _UserId= obj.Insert(_FirstName,_LastName,_Address,_MobileNo);
 return _UserId;
 } }}

*Note: Here IDb Interface will communicate between BAL and DAL layer and will also decide which DB server will be used Sql server or Oracle.

Now we will create or DAL layer. Here I am not using stored procedure to insert User Records to the Database but will request you to use stored procedure for the same.

using System;
 

using System.Collections.Generic;
 

using System.Linq;
 

using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;
using Interface;
using System.Configuration;
namespace DAL
{
 public class SqlServer : IDb
 {
 //connection string 
  static private string connectionString;
  //sql connection 
  static private SqlConnection con;
 static SqlServer()
 {
  connectionString = ConfigurationManager.ConnectionStrings["SqlDB"].ConnectionString;
 con = new SqlConnection(connectionString);
  }

 public int Insert(string firstName, string lastName, string mobileNo, string addreess)
 {
 try
 {
 string query = "insert into tblRegistration values ('" + firstName + "','" + lastName + "','" + mobileNo + "','" + addreess + "')";
 //creating sql command object and passing query and connection object
 SqlCommand cmd = new
 SqlCommand(query, con);
 con.Open();
 cmd.ExecuteScalar();
 string query2 = "select IDENT_CURRENT('tblRegistration')";
 SqlCommand cmd2 = new
 SqlCommand(query2, con);
 decimal value = (decimal)cmd2.ExecuteScalar();
  return Convert.ToInt32(value);
 }
 catch (Exception ex)
 {
  throw new Exception(ex.Message.ToString());
 }
 finally
 {
 con.Close();
 }
 }
 }
  public class Oracle : IDb
 {
 static private string connectionString;
 
 static private OracleConnection con;
  static Oracle()
 {
 connectionString = ConfigurationManager.ConnectionStrings["OracleDB"].ConnectionString;
 con = new
 OracleConnection(connectionString);
 }
  public int Insert(string firstName, string lastName, string mobileNo, string addreess)
 {
 try
 {
 string query = "insert into tblRegistration values ('" + firstName + "','" + lastName + "','" + mobileNo + "','" + addreess + "')";
 OracleCommand cmd = new
 OracleCommand(query, con);
 con.Open();
 cmd.ExecuteScalar();
 string query2 = "select INCR_USERID.currval from dual";
 OracleCommand cmd2 = new
 OracleCommand(query2, con);
  decimal value = (decimal)cmd2.ExecuteScalar();
 return Convert.ToInt32(value);
  }
 catch (Exception ex)
 {
  throw new Exception(ex.Message.ToString());
 }
 finally
 {
  con.Close();
 }
 } }}

Now here we have created our DAL class which implements IDb Interface which works as a communication between BAL and DAL layer. It has only one method as shown above:

Now in the UI rather than creating an object of the Business class we give this job to a Factory Class which will be responsible for creating objects of the desired class which is needed. Factory class basically is a type of creational pattern. As we know that Interface cannot create object of its own but it can reference to the object of the implemented class. Here we have only one class but in real time project we will have lots of classes this may vary for the same and can be improvise with other design patterns.

using Interface;
using BAL;
 namespace Factory
 {
 public class Factory
 {
  public static IRegister getRegisterobject()
 {
  return new User();
}
 }
}

Now once we have implemented the Factory class let’s get the Information from UI and try to insert the same.

using System;
using Interface;
using System.Configuration;
using DAL;
 namespace TestInterface
{
 public partial class Register : System.Web.UI.Page
 {
 IDb objIDB = null;
  string DBname=ConfigurationManager.AppSettings["DB"].ToString().ToLower();
 protected void Page_Load(object sender, EventArgs e)
 {
 if(DBname=="oracle")
 {
  objIDB = new Oracle();
 }
else
 {
  objIDB = new SqlServer();
 }
 }
  protected void Button1_Click(object sender, EventArgs e)
 {
  IRegister objRegister = Factory.Factory.getRegisterobject();
 setObjectFromUi(objRegister);
 int Id = objRegister.InsertUser(objIDB);
  Response.Write("User Registered successfully with UserId " + Id);
 }
  private void setObjectFromUi(IRegister userInformation)
{
 userInformation.FirstName = txtFirstName.Text;
  userInformation.LastName = txtLastName.Text;
 userInformation.MobileNo = txtMobile.Text;
  userInformation.Address = txtAddress.Text;
 }
 }
}

Here at the start of the application we check from the web.config file which DB to be used based upon the entry IDb Interface will reference towards the Sql Server or Oracle Server.

 IDb objIDB = null;
 string DBname=ConfigurationManager.AppSettings["DB"].ToString().ToLower();
// retrieving information about Db from the app settings
 protected void Page_Load(object sender, EventArgs e)
 {
 if(DBname=="oracle")
 {
//if string contains oracle than reference towards Oracle else towards Sqlserver
 objIDB = new Oracle();
 }
 else
 {
  objIDB = new SqlServer();
 }
 }

Now once the User enters the information IRegister will call Factory class to create object of User and IRegister will refer towards User Class.

IRegister objRegister = Factory.Factory.getRegisterobject();
 Than setObjectFromUi(objRegister);
//All user information will be set to Properties.
 private void setObjectFromUi(IRegister userInformation)
{
 userInformation.FirstName = txtFirstName.Text;
  userInformation.LastName = txtLastName.Text;
 userInformation.MobileNo = txtMobile.Text;
 userInformation.Address = txtAddress.Text;
 }
 int Id = objRegister.InsertUser(objIDB);
IRegiter reference object will call InsertUser method and pass IDb reference object and at the User class same will be received and than Idb will communicate with DAL layer. 
public int InsertUser(IDb obj)
 {
 _UserId= obj.Insert(_FirstName,_LastName,_Address,_MobileNo);
  return _UserId;
 }

We can see that there is no object creation of Concrete class and Db classes rather Interface has been used to reference to those classes making our project decoupled from each other. Let’s run our application and see how it works.

Phase 1

Phase 2: IDb referencing towards SqlServer Class

Phase 3: Loading connection string

Phase 4: Register Page Appear

Phase 5: As soon as User Enters the information.

Factory class getRegisterObject function will be called and Iregister reference variable will point towards User class

Phase 6: creation of User Object


Phase 7. Setting User Information into properties of Class

We can clearly see that all work is done by Interface we are not creating any object of class Interface is communicating with all.


Phase 8. Class method InsertUser will be called and Reference variable of IDb will passed which which will point towards the DB which we want to use.


Phase 9: Here we can see Idb ibj contains referenece variable towards Sql Server, now all the fields value will passed to the db Insert function as shown below we have used _UsedId field to receive the generated UserId of the User.

Phase 10: Now Swl server class metho will be called by IDb interface and the queries are executed and the genrated UserId will be returned back to User UI screen.


Phase 11: Showing UserId generated and same will be returned to User UI.



So here we learnt how Interface helps us to follow the standardization of Vocabulary and helps in Decoupling. I hope this all phases would be helpful so that you can learn them phase by phase. I have attached the project and will request you to execute the same.

Explicit Interface

Think of a scenario where we have two Interfaces with same method name then how we will be able to tell the method which we are implemented is of which Interface?

 public interface IPrintSony
 {
void Print();
 }
 public interface IPrintHP
 {
  void Print();
 }
  public class Customer:IPrintHP,IPrintSony
 {
 public void Print()
 {
 Console.WriteLine("I am an Interface function");
  Console.ReadLine();
 }
 public static void Main()
  {
 Customer objCustomer = new Customer();
  objCustomer.Print();
}
  }

Here as you can see there are two Interface IPrintSony and IPrintHP. Customer class is implementing both the Interface and implementing the methods, we run the program and found that it runs. So how do we figure out which interface method was called so in order to achieve that we make use of Explicit Interfaces as shown below.

namespace ConsoleApplication1
{
  public interface IPrintSony
  {
 void Print();
 }
 public interface IPrintHP
  { void Print();
 }
  public class Customer:IPrintHP,IPrintSony
 {
 void IPrintSony.Print()
 {
 Console.WriteLine("I am an IPrintSony Interface function");
 Console.ReadLine();
 }
  void IPrintHP.Print()
 {
  Console.WriteLine("I am an IPrintHP Interface function");
 Console.ReadLine();
 }
 public static void Main()
 {
  Customer objCustomer = new Customer();
 ((IPrintHP)objCustomer).Print();
  ((IPrintSony)objCustomer).Print();
 }
  }
}


Here by I conclude that once the class explicitly implements the Interface the Interface member can no longer be accessed through class reference variable, but only via Interface reference variable as shown above.

Abstract Class

An Abstract class is a half defined Base class in C# and we cannot create the object of the Abstract class. You can create your own Abstract class by using the modifier called Abstract. An abstract class means we cannot create of object of the same but we can inherit the Abstract class in our Child classes.

  • Abstract Class only comes in Inheritance hierarchy i.e. Is A relationship.
  • Abstract is used in order to remove duplication in Inheritance model using Generalization and Specialization relationship.

Abstract keyword is used to create Abstract class eg:

abstract class Customer
 {
  }

An Abstract Class can contain abstract methods i.e. that only contain definition they don’t have implementation of their own, abstract methods implementation is done in Child or Derived classes otherwise it will throw error note(all the child has to implement all abstract methods). Abstract method can also contain non abstract methods.

namespace AbstractClass
{
  abstract class Customer
 {
  public abstract void print();
 }
} 

But if I try to give implementation to the abstract class we will face an Error. We cannot define an Abstract class as static or sealed(it will not be inherited). We cannot define the access modifier for Abstract class it is by default Public.


I.e. Abstract methods cannot contain implementation.

As discussed above we cannot create object of the Abstract class let us try the same, so as soon as we create an instance of the Abstract class. Abstract class can be used as a Base class for other classes.


When we declare a method as Abstract and we inherit the Abstract class we have to implement the Abstract method otherwise it will threw an exception.

namespace Abstract
{
 abstract class PERSON
 {
 public abstract void Print();
 }
 class Program:PERSON
 {
 static void Main(string[] args)
 {
 }
  }
 }


But if we don’t want to provide the implementation for the Abstract method we can put Abstract keyword in class Program, it will not throw an error and will mark it as an Abstract class i.e. that means this Abstract class has some Abstract members.

In order to implement abstract methods in Derived class we have to use override in the abstract methods of Abstract class. Small example is shown below:


Here Employee is a generalized entity which has generalized properties which later inherited and override by the Child entity to defined specialized relationship.

Like Consultant Employee, FullTime Employee.

using System;
namespace AbstractClass
{
 abstract class Customer
 {
  public int ID { get; set; }
  public String UserName { get; set; }
 public abstract void Details();
 }
  class Consultant:Customer
 {
 public int HourlySalary { get; set; }
 public override void Details()
 {
 Console.WriteLine("The Consultant name {0} , id {1} and Hourly salary is {2}",this.ID,this.UserName,this.HourlySalary);
 }
 }
 class Fulltime : Customer
 {
  public int YearlySalary { get; set; }
  public override void Details()
 {
 Console.WriteLine("The Consultant name {0} , id is {1} and yearly salary is {2}", this.ID, this.UserName,this.YearlySalary);
 }
 }
}

using System;
namespace AbstractClass
{
 class Program
 {
  static void Main(string[] args)
 {
 Consultant objcons = new
 Consultant();
  objcons.HourlySalary = 4000;
 objcons.ID = 1;
 objcons.UserName = "Akansha Bakshi";
 objcons.Details();
 Console.ReadLine();
 }
 }
}

In derived class the abstract method of Abstract class should be of same return type and definition otherwise it will show error.

  • Non Abstract Methods:

If we declare method without Abstract keyword it is a Non Abstract method i.e. that means it’s not mandatory to have Abstract members in Abstract class.

  • We cannot create an object of the Abstract class but we can point the Abstract class object to the child class object as shown in below example:
public class Program:PERSON
 {
  public override void Print()
 {
 Console.WriteLine("I am child class inheriting abstract class");
  }
 static void Main(string[] args)
 {
 PERSON objPerson = new Program();
 objPerson.Print();
 Console.ReadLine();
  }
 }
  • Abstract class cannot be Sealed.


Why and when should be use Abstract class?

Lets’ take an example if we implement the above example doing Inheritance method and creating a concrete class as base and child class Fulltime Employee and Parttime Employee inheriting the Base Class BaseEmployee.

We would be able to create the object of the BaseEmployeeClass, where in real time we only have two employees FullTime and PartTime so it would be wrong if we allow developers the object creation of BaseEmployeeClass. So we want prevent them to create instance of the BaseEmployee to solve this problem we use the concept of Abstract class. Once we mark the Class as Abstract we cannot create object the Abstract classes, preventing us from creating object of BaseEmployeeClass.

Hence I can conclude that we should use Abstract class when we want to move common functionality of two or more classes into a single base class and when we don’t want anyone to instantiate the base class.

Abstract classes vs Interface


Abstract

Interface

  • Abstract classes can have implementation for some of its members
  • Interface cannot have implementation for any of its members.
  • Abstract class members are by default Private
  • Interface members are by default Public (they cannot have access modifiers).
  • Abstract class can have fields
  • Interface cannot contain fields.
  • A class can implement multiple interfaces at the same time.

    • A class cannot inherit multiple classes at the same time.
  • Abstract classes can be inherited from a class
  • Interface can only be inherited from an Interface.
  •  Abstract classes are used when we want to share common functionality in parent child relationship, which cannot be initiated.
  • Interfaces are used to define the contract enforce the standardization, decoupling
  • Abstract classes are inherited
  • Interface are implemented
  • Abstract class member can have Access Modifiers.
  • Interface members are Public because purpose of an interface is to enable other types to access a class or struct. No access modifiers can be applied to interface members.


Learn C# in 7 Days – Day 2

Introduction

We have successfully completed day1 before coming here.

Day 2 Agenda

  1. Namespaces
  2. Classes
  3. Abstraction
  4. Encapsulation
  5. Inheritance
  6. Properties
  7. Access Modifiers

If would recommend watching videos from below image for full in depth topics. Just click on the below image you would be navigated to the Videos.



Namespaces

Why Namespace?

  • Namespace help us organize our programs.
  • They help us to avoid name clashing.
  • To control the scope of the class and method name in our Projects.

Syntax: Namespace nameofNamespace

“Namespace is a collection of classes and are used to organize them.”

System.Console.WriteLine(“Learn C# in 7 days!”);

Here System is a Namespace and Console is a class present in System.


Fig 1.0 Represent a Shape Namespace and its corresponding Classes

Let’s take example where we have two teams which are working on same project with same class so in order to organize them we create namespace with project name and then the namespace for subclasses as shown below:

namespace ProjectA //project name
 {
  namespace PromoTeam //to signify whose Team class is this
 {
 class A
 {
 public static void print()
 {
 System.Console.WriteLine("heelo i am PromoTeam");
 Console.ReadLine();
 }
 } }
}
 namespace ProjectA//project name
{
  namespace OMSTeam //to signify whose Team class is this
 {
  class B
 {
 public static void print()
 {
 Console.WriteLine("heelo i am OMSTeam");
 Console.ReadLine();
 }
 }
 }
}

So now when we want to access the print function of OMSTeam we will just use below code

class Program
 {
  static void Main(string[] args)
 {
  ProjectA.OMSTeam.B.print(); //Namespace.Namespace.ClassName.Method
 }
  }

Else we can use using statement for the same.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ProjectA.OMSTeam;
using ProjectA.PromoTeam;

class Program
 
 {
 static void Main(string[] args)
 {
 A.print();
 }
 }
namespace ProjectA
{
 namespace PromoTeam
 {
  class A
 {
 public static void print()
 {
 System.Console.WriteLine("heelo i am PromoTeam");
 Console.ReadLine();
 }
 }
 }
}
 
namespace ProjectA
{
 namespace OMSTeam
 {
  class A
 {
 public static void print()
 {
 Console.WriteLine("heelo i am OMSTeam");
  Console.ReadLine();
 }
 }
 }
 } 

But when we try to access the OMSTeam class the compiler shows following error


In order to resolve this we can use fully qualified name.

class Program
 {
  static void Main(string[] args)
 {
 ProjectA.OMSTeam.A.print();
  ProjectA.PromoTeam.A.print();
 }
  }

Namespace Aliases

We can create alias name of the Namespace by using the using Statement through which we can resolve this problem using alias name of Namespace as shown below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Omsteam=ProjectA.OMSTeam;//giving alias name to Namespace
using PromoTeam = ProjectA.PromoTeam; //giving alias name to Namespace
class Program
 {
 static void Main(string[] args)
 {
 Omsteam.A.print();
  Omsteam.A.print();
 }
  }

Now we can do the same using class Libraries as shown below:



And name the class as article.

*note I have changed the class name so that it will look more Business related.

namespace ProjectA.OmsTeam
{
 public class Article
 {
 public static void print()

 {
 Console.WriteLine("heelo i am OMSTeam");
 Console.ReadLine();
 }
 }
}

And same we will create for PromoTeam

namespace ProjectA.PromoTeam
{
  public class Article
 {
  public static void print()
 {
  System.Console.WriteLine("heelo i am PromoTeam");
 Console.ReadLine();
 }
 }
 }

Now in order to use these Namespace we need to add them in our project by adding them in references.



We will check the checkbox and select both of them to be used in our project.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using OmsTeam= ProjectA.OmsTeam; //using the namespace with alias name
using PromoTeam=ProjectA.PromoTeam; //using the namespace with alias name
 class Program
 {
  static void Main(string[] args)
  {
  OmsTeam.Article.print(); //calling the print method
  PromoTeam.Article.print();//calling the print method
 Console.ReadLine();
 }
 }


Classes and Objects in C#


When we store the list of Information regarding a type we make use of classes e.g. Employee so Employee has the varieties of Information that are needed to be stored i.e. EmployeeName, EmployeeId, EmployeePhoneNo, EmployeeAddress, Gender, Jobtitle etc. A class is a template for creating object. Class can also contain methods to perform operations related to Employee that is termed as behavior. Class is basically a template which is a repository of information and that information can be called using a Class Objects.

A class consists of state and behavior. Class data is represented by Properties/Fields and behavior is represented by methods.

Syntax: Class ClassName

Go to your project and right click and add NewItem, select class and give your Class name.

using System;

namespace ClassDef
{
 class Employee
 {
 }
}

Now add some fields and methods to an Employee class. Now I declare some data as shown below and I am using Constructor to set the value to the class data.

*note- Constructor is creation of object at run time, as soon as the object is created the Class Constructor is called. Constructors are used for object initialization and memory, field’s allocation of its class.

using System;

namespace ClassDef
{
 class Employee
 {
  string _Name;
 int _Age;
 public Employee( string Name,int Age
 {
 this._Name = Name; //this is points towards object of this classs
  this._Age = Age;
 }
  public void Information()
  {
  Console.WriteLine("The Information of the Employee is Name={0} and Age={1}",_Name,_Age);
  Console.ReadLine();
 }
 }
}

Passing the field values to the object.

using System;
 
 namespace ClassDef
{
 class Program
 {
 static void Main(string[] args)
 {
 Employee obj = new
 Employee("Saillesh",26);
 obj.Information();
 }
 }
}


As we go ahead we will see how to use properties and assigning value to the properties.

Objects

An object is instance of a class, i.e. an instance has properties for identifying its state (attributes) and methods for behavior and event for depicting the change of state defined by the class

Object Entity

Object Entity means that every object is unique and can be differentiated from other, each time the object is created an Object entity is defined. What we always see and are able to distinguish from each other is an Object.


When we talk about Saillesh Desktop, we are referring to an object of Class Desktop that can subdivide in to child classes like Desktop of Hp or Sony etc. But as here we are talking about Saillesh Desktop us talking about only this desktop and its attributes and methods.

We can see below diagram to see Saillesh Desktop behavior and Attributes.


As I said the object is instance of a class, i.e. we can say that a birth or creation of object is called instantiation.

Sample examples of a class : Girl



Circle Class

Static Classes – A static class is same as non-static class, the only difference is we cannot create object of Static class.

Note: Static class should have all the methods, fields and properties as static

Benefits:

  • We don’t need to make instance of the class all the methods, fields and properties are easily accessible using Class name.
  • No OOP principles are applicable to Static class.
  • Do not map to Real world objects, it’s just a collection of unrelated methods or logic so that we can increase the reusability in our project.
  • If you want cache data.

*Note: Constructor runs only once in Static class.

Declaration:

A static class is created by using keyword ‘Static’ as shown here:

Static class ClassName
{
//list of  properties and methods

}

Abstraction

“A view of a problem that extracts the essential information
relevant to a particular purpose and ignores the remainder of
 the information.”

 — [IEEE, 1983]

Abstraction is the selective examination of certain aspects of
    a problem. The goal of abstraction is to isolate those aspects
    that are important for some purpose and suppress those aspects
    that are unimportant.”

        — [Rumbaugh et al, 1991]

“[A] simplified description, or specification, of a system that
    emphasizes some of the system’s details or properties while
    suppressing others. A good abstraction is one that emphasizes
    details that are significant to the reader or user and suppress
    details that are, at least for the moment, immaterial or
    diversionary.”

        — [Shaw, 1984]

Source: http://www.tonymarston.co.uk/php-mysql/abstraction.txt

Encapsulation

“Encapsulation is used as a generic term for techniques which
  realize data abstraction. Encapsulation therefore implies the
    provision of mechanisms to support both modularity and information
    hiding. There is therefore a one to one correspondence in this
    case between the technique of encapsulation and the principle of
    data abstraction.”

        — [Blair et al, 1991]

“Data hiding is sometimes called encapsulation because the data
    and its code are put together in a package or ‘capsule.'”
  — [Smith, 1991]

"Encapsulation -- also known as information hiding --
prevents clients from seeing its inside view, were the behavioof the
abstraction is implemented."

        -- [Booch, 1991]

Source: http://www.tonymarston.co.uk/php-mysql/abstraction.txt

From the above definitions, we figured out that Abstraction is more collective thing the principle of Object oriented programming and denotes to show something in simpler way e.g. Building blocks of Building, such as color or shapes.
What is necessary to the User and essential feature of the object to the End User and ignoring the inessential details? While Encapsulation is a strategy used as part of abstraction to implement the abstraction, wrap data in a unit or capsule i.e. Class. It keeps the data safe from outside code. It refers to the object of the class. We access the class by instantiating the class object. Object properties and method can be hidden from the end user.

Note* Here end user is perspective to the User who references your DLL File. Now we use reference of object then it will show only necessary methods and properties and hide methods which are not necessary which we have hided from the external User.

For Example I have Meeting Management System App

Where when I create the MOM Minutes of the Meeting and insert the Meetings details to the DB for e.g. using Function

  • CreateMOM()

When this function executes it first it adds the Meeting Name, Segments (Retails, Audit, Operational etc.) creates the MeetingId for the MOM and then based on the Meeting Id after that based upon the MeetingId it internally calls other methods that creates the child of Meeting as shown below:

  • Participants in the Meeting (Employees who participated in the Meeting)
  • Agenda Covered in the Meeting
  • File uploaded (any excel file)
  • Format Function Covered
  • Sending mail to all the participants related what action was taken in the meeting.

So we can imagine that there is couple of step it does.

  • Now imagine we have another User or Client who is consuming our class rather than telling him all the things we just tell him the method name CreateMOM(), as Abstraction we are hiding the user only the necessary function and encapsulation is hiding the other complex things from it. (Adding participants to other sub tables, agenda covered in the meeting etc.) We implement the encapsulation using Access Modifiers (Private).

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data.SqlClient;

//Abstraction Namespace

namespace Abstraction

{


///
<summary>


/// Meeting Class contains list of Properties and functions for the Meeting Creation


///
</summary>


class
Meeting

{


private
int _Id;


private
string _Name;


public
string Name

{


get

{


return _Name;

}


set

{

_Name = value;

}

}


private
string _Format;


public
string Format {


get

{


return _Format;

}


set

{

_Format=value;

}

}


private
string _Location;


public
string Location {


get

{


return _Location;

}


set

{

_Location=value;

}

}


private
string _FileUploaded;


public
string FileUploaded

{


get

{


return _FileUploaded;

}


set

{

_FileUploaded = value;

}

}


public
bool InsertToDb()

{


try

{


this._Id = AddandGetMeetingId(_Name, _Format, _Location);


bool Status = AdduploadedFiles(this._Id,_FileUploaded);


if(Status ==true)

{


return
true;

}


else

{


return
false;

}

}


catch(Exception ex)

{


throw
new
Exception(ex.Message.ToString());

}

}


private
int AddandGetMeetingId(string MeetingName,string FormatName,string MeetingLocation)

{


try

{


DAL objDal=new
DAL();


int Id= objDal.CreateMom(MeetingName,FormatName,MeetingLocation);


return Id;

}


catch (Exception ex)

{


throw
new
Exception(ex.Message.ToString());

}

}


private
bool AdduploadedFiles(int id,string FileName)

{


try

{


DAL objDal = new
DAL();


bool status = objDal.CreateMom(id,FileName);


return
true;

}


catch (Exception ex)

{


throw
new
Exception(ex.Message.ToString());

}

}

}

}

Now here I have set the Class properties _id private and function AddandGetMeetingId and AdduploadedFiles as private because I don’t want end user to see these methods and Properties. Now when I am consuming this class I am only able to see methods and properties which I want User to see hiding the complexities from him and only showing the necessary information that is necessary.


We can see from above diagram that only required information is shown the User. User access the class method as shown below

try

{


Console.WriteLine(“Enter the Meeting Name”);

objMeeting.Name = Console.ReadLine();


Console.WriteLine(“Enter the Format Name”);

objMeeting.Format = Console.ReadLine();


Console.WriteLine(“Enter the Meeting Location”);

objMeeting.Location = Console.ReadLine();


Console.WriteLine(“Enter the file Uploaded”);

objMeeting.FileUploaded = Console.ReadLine();


bool status = objMeeting.InsertToDb();


if(status==true)

{


Console.WriteLine(“Minutes of Meeting Created Successfully”);

}


else

{


Console.WriteLine(“Failed to Created Minutes of the Meeting”);

}


Console.ReadLine();

}


catch (Exception ex)

{


Console.WriteLine(ex.Message.ToString() +” “+ex.StackTrace.ToString());

}

And DAL Layer which will insert data to the Database

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data.SqlClient;

using System.Configuration;

namespace Abstraction

{


public
class
DAL

{


public
static
SqlConnection con;


static DAL()

{

con=new
SqlConnection( ConfigurationManager.ConnectionStrings[“DBclass”].ConnectionString);

}


public
int CreateMom(string MtngName,string FrmtName,string Location)

{


try

{


string query = “insert into tblMeetingMaster values (‘” + MtngName + “‘,'” + FrmtName + “‘,'” + Location + “‘)”;


SqlCommand cmd = new
SqlCommand(query, DAL.con);

con.Open();

cmd.ExecuteScalar();


string query2 = “select IDENT_CURRENT(‘tblMeetingMaster’)”;


SqlCommand cmd1 = new
SqlCommand(query2,DAL.con);


decimal value = (decimal)cmd1.ExecuteScalar();


return
Convert.ToInt32(value);

}


catch (Exception ex)

{


throw
new
Exception(ex.Message.ToString());

}


finally

{

con.Close();

}

}


public
bool CreateMom(int MeetingId, string FileName)

{


try

{


string query = “insert into tblFileUploaded values (‘” + MeetingId + “‘,'” + FileName + “‘)”;


SqlCommand cmd = new
SqlCommand(query, DAL.con);

con.Open();

cmd.ExecuteNonQuery();


return
true;

}


catch (Exception ex)

{


return
false;


throw
new
Exception(ex.Message);

}


finally

{

con.Close();

con.Dispose();

}

}

}

}


End User Inputs the Details


Values inserted in DB


Inheritance


From the above Diagram you can predict there are two Classes Football Player, Cricket player both of them are having lots of common properties and methods. So in order to duplicate code in both the classes we move all duplicate code into base class called Player.


Inheritance is a parent child relationship in an object oriented programming where we can create a Parent class and extend the functionality of parent class by creating child class.

While talking about above classes the specific code that left respective two them is football is in football we have Goals Scored, while in Cricket we Run Scored etc. Let go ahead and implement the same as written below.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace Inheritance

{


public
class
Player

{


public
string PlayerName;


public
string ClubName;


public
string PlayGround;


public
void PlayerDetails()

{


Console.WriteLine(“THE Player details are PlayerName {0}, Registered to{1} playing in {2}”,PlayerName,ClubName,PlayGround);


Console.ReadLine();

}

}


public
class
CricketPlayer:Player

{


public
int Runscored;

}


public
class
FootballPlayer : Player

{


public
int GoalScored;

}

}

  • In this above example derived class inherits from the Parent class
  • C# Supports only single class inheritance.
  • Child class is a specialization of base class.
  • Parent classes are automatically instantiated before derived class.
  • Parent class constructs executes before child class.

In interview sometimes Interviewer asks us which constructor will be called first when we call a child class constructor?

Parent classes are automatically instantiated before derived class, as soon as we create an object of child class the parent class Constructor is called as shown below.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace ParentClassConstructor

{


public
class
Parent

{


public Parent()

{


Console.WriteLine(“Parent class constructor called”);


Console.ReadLine();

}

}


public
class
Child : Parent

{


public Child()

{


Console.WriteLine(“Child class constructor called”);


Console.ReadLine();

}

}


class
Program

{


static
void Main(string[] args)

{


Child objChild = new
Child();

}

}

}


Properties

Property is a member that provides the mechanism to what gets assigned and what gets returned from your fields/classes variables. In simple terms we can say that properties help us to inject the logic inside the fields /classes variables. By using the Access modifiers we can make it possible to what gets assigned and what gets returned from your fields. Properties implements Encapsulation because it hides the complexity from the User.

Why Properties:

  • As discussed earlier if we make our class fields public we will not have any control on what gets assigned and what gets returned from your fields.
  • Below are the scenarios which we face when our fields are public

Below is a class called Employee Class with public fields ID.

  • Our Business rule say that ID should not be less than 1
  • Name cannot be set to NULL
  • Marks Should be Read only

But as we can see below we are able to change the values and assigned the same to our fields.

public class Employee

{

public int ID;

}

public class Program

{

static void Main(string[] args)

{

Employee obj = new Employee ();

obj.ID = -100; // ID should not be less than 1

obj.NAME = null; // name should not be empty

obj.MARKS = 20; //Marks should be a read only property but here we are able to change it

Console.WriteLine(“the values of the new object are ID {0} ,NAME {1}, MARKS {2} “, obj.ID, obj.NAME,obj.MARKS);

Console.ReadLine();

}

}

When I execute the application we will see that all are Business rules has been violated and there is no message to the user for the same.


So in order to validate the Business rule we use get and set methods properties in C#. The programming language that before properties they use Getter and Setter(for better understanding) method as shown below.

Firstly we create our property to be private so that they won’t be accessible to the external world.

Now we write to methods for set and get i.e. SetId, GetId to control what’s get into the fields and what set into the fields of class.


Now I initialize the object of Employee class we find that we are not able get access to our fields rather we are able to access SetId and GetId methods. Now I set the value to the _id field as shown below


Now I as I set the value of id as -100 and than call the obj.Getid we see how the flow of program goes.

STEP1. Once the object is created we try to set the value of _id=-100


STEP2. As the id value passed is less than 1 it enters the exception and throw a new exception for the same.


STEP 3. It enters the catch block and will print the exception message as shown below.



Now we input a correct value which is greater than 1 and print the value using getId function as shown below.



Now we are following the business rules so the output will be


So now we can see that these methods are allowing us to control over what gets assigned and returned.

Now we will see how we in C# we implement Properties in order to hide complexity from the User (Encapsulation).

In order to use a property you have to write


Or we can use code snippet shortcut prop and press tab for the same.



And then create your get and set accessor as above.

Now before as we created the object of Employee class and we called the function getID and setId but now we will not get any method beside no will get ID Property as shown below.


Now how will it be checked once the value of property ID is passed to the set accessor it has keyword called (value)


Once the value of is assigned to property ID it calls the set accessor as shown below:


And for getting the values of the id we have to just write object name than property name, it invokes the id provide and it invokes the get accessor of this property and returns a value which is present in private.



As we can see get and set are Read Write properties, user can write a value to the fields and same he can read the value from the fields.

So what if want an only read only field like pi in math’s that is 3.14 or other ready only fields as per business logics.

For this we need to make our property with only get accessor

Eg. Here I am taking an example of bonus which is same for all employees in order to make it a read property will assign only get accessor to it as shown below.


Now if I try and change the property to something else


It shows that it value cannot be assigned as it’s a read only property i.e. we can only read the value, not assign them.



And if in case you want to make a property you want to write only than you have to use only set accessor.

Auto Implemented Properties: As earlier discussed short cut for property prop, actually
Microsoft introduces auto implemented properties in C# 3.0 for scenarios where we don’t
want to inject any logic Eg. City etc. In Auto Implemented we don’t have to create private fields because compiler will automatically create for us private field and the public property will be used to read and write to that property

Eg. Of Auto Implemented Property

public
int MyProperty { get; set; }

//Just one line

Note: In the end I want to end this article saying that the advantage of using properties is that we don’t have to create function for what gets and set in the fields. In properties the compiler on its own generates getter and setter methods as we did earlier before properties when it parses the C# property syntax as per the algorithm defined.

Access Modifiers

All Class members’ behavior, data and class have their accessibility level, which specify that whether they can accessed from other code in your Assembly.

There are 5 types of different Access Modifiers in C#:

  • Private
  • Protected
  • Friend
  • Protected Friend(Protected Internal)
  • Public


Image Source:
http://dotnethints.com/blogs/public-vs-protected-vs-private

In Private
Access modifier only the members of class can access to the function or variable or it accessibility level is within the containing class.

For ex.

We have a Customer Class with two properties

CustomerID and CustomerName as shown below

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace AccessModifier

{


public
class
Customer

{


private
int _id;//declared with private


private
string _CustomerName;


public
int id //public property with get and set accessor

{


get

{


return id;

}


set

{

id = value;

}

}


public
string CustomerName

{


get

{


return _CustomerName;

}


set

{

CustomerName = value;

}

}

}

}

Now once I try to access the private property outside the class I wouldn’t be able to access them due to its protection level.


We can only access the Public Properties not private fields.


If I try to access the public members there are no errors and we can easily access them.

namespace AccessModifier

{


class
Program

{


static
void Main(string[] args)

{


Customer c1 = new
Customer();

c1.id = 1;

c1.CustomerName = “saillesh”;

}

}

}

So here we also learnt about Public access modifier all members are accessible in all classes and projects. I.e. Any where

Protected: In Protected access modifiers all the members of the class are accessible to the class member and to the derived classes.

Now I create two classes one Customer and GoldCustomer. Gold Customer inherits from Customer class as shown below

using System;

namespace AccessModifier

{


public
class
Customer

{


protected
int _id;


protected
string _CustomerName;


public
int id

{


get

{


return id;

}


set

{

id = value;

}

}


public
string CustomerName

{


get

{


return _CustomerName;

}


set

{

CustomerName = value;

}

}

}


public
class
GoldCustomer : Customer

{


private
float _Discount;


public
float Discount

{


get

{


return _Discount;

}


set

{

_Discount = value;

}

}


public
void PrintDetails()

{


GoldCustomer cust = new
GoldCustomer();//creating object of child class

cust._id = 1; ///we can access the protected value of parent class

cust._CustomerName = “Nishant”; //same goes here

}

}

}

Calling Program.cs


If I try to access it from outside the class we can see that it is inaccessible due to its protection level. So if you want to access the protected class member you can access it from child class using child class object inside a class or using Base
keyword.


Friend (Internal): The members with internal access modifiers are available anywhere in the containing project or assembly.

To demonstrate the same we need to add one more project as mentioned below.

Add Class library and Name it Project1

using System;

namespace Project1

{


public
class
SilverCustomer

{


internal
string CustomerName = “saillesh”; //internal

}


public
class
BronzeCustomer

{


public
void details()

{


SilverCustomer obj = new
SilverCustomer(); //we create an instance of Silver Customer


Console.WriteLine(obj.CustomerName);//we can easily access the member of SilverCustomer

}

}

}

Now once I try it from other main project let see what happens so for the same I have added Project1 class to main project to use the same

As shown below


using System;

using Project1;//using the Namespace

namespace InternalExample

{


class
Program

{


static
void Main(string[] args)

{


SilverCustomer objSilver = new
SilverCustomer();

}

}

}

Now as soon as I try to access the Internal member we would not be able to access the member and if try to input the member name we will face the compile time error.


Now Protected Internal it’s the combination of both Internal and Protection. All members in current project and all members in derived class in any other project /assembly can access the members. Don’t get confused it means that the class of different project who is inheriting this class will be able to access the member. You can see that as shown below.

Main class of Project1 named as SilverCustomer

using System;

namespace Project1

{


public
class
SilverCustomer

{


protected
internal
string CustomerName = “saillesh”;

}

}

Now in other project I create Platinum Customer Class

using System;

using Project1;//using the project1 namespace

namespace InternalExample

{


public
class
PlatinumCustomer: SilverCustomer //inheriting the SilverCustomer Class

{


public
void details()

{


PlatinumCustomer obj = new
PlatinumCustomer();

obj.CustomerName = “Ankit Negi”; we can see that SilverCustomer protected Intenal member is accessible to other project

}

}

}

Conclusion

Here we complete our day 2. In Day 3 we will take our C# class to next Contents. If any confusion you can comment for the same. Add on information is heartily welcomed.

Learn C# in 7 Days – Day 1

Introduction: As the title specifies “Learn C# in 7 days” I will be writing article for the same, the intended focus is towards the beginners so that they can easily grasp the C# Language concepts and become C# developer after reading the articles. So let’s get started with Day 1. In case you feel I have left the very important topic feel free to comment and any corrective measures and implementation are most welcomed.


Day1 Contents

  1. Introduction
  2. Agenda
  3. Creating Your First Program
  4. Console Programs
  5. Escape Sequencing
  6. Exceptional Handling
  7. Arrays
  8. For Each Loop
  9. Parallel.ForeachLoop

Introduction

C# is object oriented programming language which allows us to build large variety applications. It was developed by Microsoft within .NET framework. C# is one of the many languages that are supported by .NET framework. Its development team is led by Anders Hejlsberg. The Current version is C# 6.0, which was released on July 20, 2015.

what’s the agenda?

As the name specifies learn C#, so we will be going to learn C# in 7 days. For the first day we will cover small but important topics that will keep our foundation of C#.

Creating Your First Program

As you are reading about C# so I hope that you will familiar with Visual Studio IDE. A rich, integrated development environment for creating stunning applications for Windows, Android, and iOS, as well as modern web applications and cloud services. Source : https://www.visualstudio.com/ . I will be helping you creating your first C# Program step by step.

Step1. Open Visual Studio

In case if you don’t have Visual Studio you can download from the below link https://www.visualstudio.com/.

Step2. Create New Project, Click on File=> New=> Project


Step3: Select Visual C#

You can see the following set of options available with Visual C# using these components we can develop our respective applications.


Right now we will select Console Application and name our Project as CSharpStepByStep. We will implement the C # concepts using Console Application because it simplifies the learning process of C#. Once the loading of classes and required component need for visual studio project get loaded we will have our Program.cs file as shown below:

using System;
 using System.Collections.Generic;
 using System.Linq;
 using System.Text;
 using System.Threading.Tasks;
 namespace CSharpStepByStep
 {
 class Program
{
 static void Main(string[] args)
{
}
}
}
 

I will be getting rid of little code as it’s our first warm up topic I don’t want you to mess up and confuse. In short I will only keep the code that is necessary.

using System;
class Program
{
static void Main()
{

 }
}

Now you can see I have removed some code that will talk in our later section. So now I want to print Welcome to Learn C# Step By Step in Console window. By console window I mean to say is Command Prompt. So in order to do that I will just write Console.WriteLine(“message”). . Here Console is a class. WriteLine is used to write the message or data to the screen or the User. Console.Readline is used to take input from the User, until and unless user presses the input from the keyboard the screen waits for the input.

using System;
class Program
{
static void Main()
{
Console.WriteLine("Learn C# Step by Step");
}
}

So once I run my application in debug mode the command window just pops up and gets closed, in order to keep the window open we use one more method of Console called Readkey which will not close the console window until and unless the user presses a key or you can run the program using ctrl+f5 run without debugging.

using System;
class Program
{
static void Main()
{
Console.WriteLine("Learn C# Step by Step");
Console.ReadKey();
}
}


Let’s focus little in depth for the above code to understand it better.

Using System:

This line signifies that in our program we are going to make use of System Namespace. So you might be thinking what the hell Namespace is. Namespace is a collection of classes, when we use our Console.WriteLine or Console.ReadLine you may have seen the following


That specify the Console class is present in the System Namespace, if we remove the Using System Namespace we would get compile error


That means compiler don’t know where do Console exist. We can add the fully conventional keyword System.Console class name before using its function as shown below


class Program
{
static void Main()
{
System.Console.WriteLine("Learn C# Step by Step");
System.Console.ReadLine();
}
}
 

But doing this we have to use System wherever we want to use the Console Class in order to use System class, so in order to avoid this we use them at top in Using Section.

Every lines of Code should be inside a Class, we will discussing about class once we go ahead.

class Program
{
static void Main()
{
System.Console.WriteLine("Learn C# Step by Step");
System.Console.ReadLine();
}
}
 

Static Void Main()

Is an entry point function which tells the compiler from where the execution is going to start.


Once the program get executed it’s enter the entry point function and execute the sets of instructions.

*Tip- When you want to see the flow of program you can insert a breakpoint using F9 and see your program flow line by line.

Console Programs

In this topic we will be enhancing our first program by taking input from the User and displaying the desired results to the User.

As we have learnt to write to Console we write Console.WriteLine, so when we want to read the User Input we use Consol.ReadLine(); This function reads the next lines of characters from the standard input stream as shown above in figure. In order to read the User Input we have to read the User Input in a variable *note variable is a location in memory which is used to store data used in Application.    Once the value is stored in variable we will display the User entered value concatenated with to Learn C #Step by Step as shown below:

using System;
 class Program
{
static void Main()
{
Console.WriteLine("Learn C# Step by Step");//printing to the Console
 Console.WriteLine("Enter your Name");//printing to the Console
 string UserName = Console.ReadLine();//Reading User Input
 Console.WriteLine("Welcome "+UserName+ " to Learn C# Step by Step");//printing user entered concatenated with other string.
 Console.ReadLine();
}
}
 

Let go through code


As we can see that Console.WriteLine has been printed to the console Window, now yellow shaded lines read the User Input stores it into variable called UserName and in next line we write the variable value along with string as shown below.


  • C# is a case sensitive language i.e.

The word Case sensitive means the text is sensitive to capitalization of letters or words. ex.

Class and class are two different word because of C of first being Caps. You can see the below figure for more elaboration.


//added

Variable

Is the small memory data which are allocated to our program where actual data will be stored.

In below example we are using a String variable to take the input from the User and display the same to the screen.

using System;
 namespace readline
 {
  class Program
{
 static void Main(string[] args)
{
 Console.WriteLine("Enter your Name");
 string userName = Console.ReadLine();
 Console.WriteLine("Your Name is {0}",userName);
 Console.ReadKey();
 }}
}


Console.WriteLine(“Your Name is {0}”,userName);

Here we are concatenate “Your Name is” and userName value

Int

Let take up that we have another requirement of Age, as we all know that age is an integer type. So we have to take integer as a variable. When we try to read the input from the User as shown below the error appears

As the error specifies cannot convert string to a integer, whenever we take input from the user it’s is always treat as a string, so we need to convert it to our required datatype.

using System;
 namespace readline
 {
 class Program
{
static void Main(string[] args)
{
Console.WriteLine("Enter your Name");
string userName = Console.ReadLine();
Console.WriteLine("Enter your age");
int userAge =Convert.ToInt16( Console.ReadLine());
Console.WriteLine("Your Name is {0} and your age is {1}",userName,userAge);
Console.ReadKey();
}}}
 

The output of the program if users inputs correct integer value.

Now if I try inserting the non-integer value I will face an exception/error, as shown below “Input string was not in a correct format”. So now to learn about Exceptional Handling we will learn ahead in this article.

//added

Escape Sequencing

Sometimes we need to add Character combinations consisting of a backslash (\) followed by a letter in our programs eg. We want our output to have been like this “Hello Readers” Along with double quotes. In order to do that we make use of Escape Sequencing in C#, as defined here the escape sequence character in c# is (\ ). This backslash says to compiler whatever precedes me treat them as a regular character.

using System;
class Program
{
 static void Main()
 {
 Console.WriteLine("\"Hello Readers\"");
 Console.WriteLine("\\heelo reader\\");
 Console.ReadLine();
 }}
 

Output:


You can read the chart of Escape sequence in below mentioned link

https://msdn.microsoft.com/en-us/library/h21280bw.aspx

Exception Handling

Exceptional handling the way of passing control from one part to another i.e. if there is any exception in our program we can catch the same pass the control to another. As you may be aware of Exception Handling I want sum up the definition of Exception as Exception is the error that occurs when a program is running. So here we will learn how to handle the Exception and how we can respond it to the User in a user friendly manner.

Eg: Divide by Zero Exception


So in order to do Exceptional Handling we need two things.

Try, Catch and finally, we can see in above figure from where our Exception was raised or program crashed. Showing the actual unhandled exception will annoy the user as they are cryptic and do not make sense to the end user. We can tell the compiler that I am excepting to get error in this line and in case you get exception can you just throw the Exception into the Catch as shown below.

using System;
 
class Program
  {
 static void Main()
 {
 int a = 10;
 int b = 0;
 int result;
 try {
 result =a/b;
 Console.WriteLine("\n Result after division is ",result);
 Console.ReadLine();
 }
 catch(Exception ex)
 { 
 Console.WriteLine("Divisor should be greater than 0");
 Console.ReadLine();
 }
 }
}
 

Exception is actually a class that derives from System.Exception class. With the help of this we can show the User Friendly message to the Users rather than Compiler message shown by the compiler.


An exception Class has several useful properties (we will learn more about properties as we go ahead) that provide valuable information about the Exception.

  • Message: Describes the current exception details
  • StackTrace: Provide the call stack to the line number in the method where the exception occurred.


What if rather than using Exception class we use DivideByZeroException
which is a child class of Exception. System.Exception is base class of Exception

  • DivideByZeroException Inherits from ArithmeticException
  • ArithmeticException Inherits from SystemException
  • SystemException Inherits from Exception

Now using DivideByZeroException


 catch (DivideByZeroException ex)
 {
 Console.WriteLine("Divisor should be greater then 0");
 Console.WriteLine(ex.Data.ToString());
 Console.WriteLine(ex.Message.ToString());
 Console.WriteLine(ex.StackTrace.ToString());
 Console.ReadLine();
 }


But this catch block will only handle DivideByZeroException so in order to catch other Exception we can include another catch block and use base exception type as shown below.

Now I am trying to read the content of the file and if any exception occurs while reading the same exception will be handled via Exception class else if while dividing we get divide by zero Exception that will be handled by DivideByZero Exception class.

using System;
using System.IO;
 
 class Program
  {
 static void Main()
 {
 int a = 10;
 int b = 0;
 int result;
 try
 {
 StreamReader obj = new StreamReader(@"C:\Users\Developer\Documents\Article\.net Framework\Product Ke");
 Console.WriteLine(obj.ReadToEnd());
 obj.Close();
 result = a / b;
 }
 catch (DivideByZeroException ex)
 {
 Console.WriteLine("Divisor should be greater than 0");
 Console.ReadLine();
 }
 catch (Exception ex)
 {
 Console.WriteLine(ex.Message.ToString());
 Console.ReadLine();
 }
 }
}
 


Finally- here the set of statements are written that you want to execute irrespective of Exception occurs or not. Eg. Disposing ADO.NET objects, Close IO objects etc(*Note we will be discussing these topics ahead). Finally block is not mandatory.

In above code as soon as the Exception occurs the handle is transferred to the Exception class neglecting all other statement after that that would result in object occupying the memory. So in order to close the same we will do it in finally block as shown below.

using System;
using System.IO;
 
 class Program
 {
 static void Main()
 {
 int a = 10;
 int b = 0;
 int result;
 StreamReader obj = null;
 try
{
  obj = new StreamReader(@"C:\Users\Developer\Documents\Article\.net Framework\Product Ke");
 Console.WriteLine(obj.ReadToEnd());
 result = a / b;
 }
 catch (DivideByZeroException ex)
 {
 Console.WriteLine("Divisor should be greater then 0");
 }
 catch (Exception ex)
 {
 Console.WriteLine(ex.Message.ToString());
 }
 finally
 {
 if(obj!=null)
 {
 obj.Close();
 }
 Console.WriteLine("I am finally block");
 Console.ReadLine();
 }
 }
}
 


Inner Exceptions

Once while executing our Program we face some exception so we handle the exception in catch block, what if we want to do some operation inside catch block for eg. Writing exception to a text file etc. In order to do these operations we may face some other Inner Exception inside a catch block so let’s see how we handle the Inner Exceptions. Now as I have mentioned I want to log the Exception to a text file with exception message, trace and data and time.

using System;
using System.IO;
 
class Program
 {
 static void Main()
 {
 int a = 10;
int b = 0;
 int result;
 try
 {
 result = a / b;
 }
 catch (Exception ex)
 {
 string Filepath = @"C:\Users\Developer\Documents\Article\.net Framework\Exception.txt";
if (File.Exists(Filepath))
 {
  StreamWriter objException = new StreamWriter(Filepath);
 objException.Write(DateTime.Now+" "+ex.Message+" "+ex.StackTrace);
 }
 else
 {
 throw
 new
 FileNotFoundException("File not found "+ Filepath,ex.Message);
 }
 Console.WriteLine(ex.Message.ToString());
 }
 }
}
 

You can see in above code that once the Divide by Zero Exception comes, I am writing the exception to text file as shown below


What if while writing the Exception I face another exception, so in order to handle that we use Throw Exception as shown above.


Arrays

An array is a group of similar data types, Eg. I we want to store 10 integers. As this article is focused on C# I will not be going in depth of Arrays. I will help you how to declare and use Arrays in c#.

Syntax of Declaring an Array
 int[] intArr = new
 int[3]; //size to specified
 (name of array)
 


Eg:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 namespace Arrays
 {
 class Program
 {
 static void Main(string[] args)
 {
 int[] intArr = new int[10];
 for (int i = 0; i < 10; i++) //looping threw I and assigning I value to array
 {
 intArr[i] = i;
 }
 Console.WriteLine("The Arrays consist of following values");
 for(int i=0;i<10;i++)
 {
 Console.WriteLine(intArr[i]);
 }
 Console.ReadLine();
 }
 }
}
 

Advantages-

  • Arrays are strongly typed; they don’t allow storing other data type apart from declared data type.
  • 2D arrays are used to represent matrices.


Fig 1: Arrays are strongly typed i.e. it can only accept the value of declared datatype.

Disadvantages-

  • Once we declare the array size it cannot be changed.
  • Since the array size is fixed so if insert less value the memory is wasted and more than System.IndexOutOfRangeException will happen.

Foreach Loop

For each loop is used to iterate threw the collection, doesn’t includes initialization, termination and increment/decrement characteristics as in other loops.

Advantages:

  • No need to know the size of the Collection.
  • Loops are easier to write, it least error prone loop.

*Note Code Snippet for foreach Loop is foreach and then press tab.

Ex:

Syntax: foreach(datatype same a collection Datatype name in Collection)

{

//statements

}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Arrays
{
 class Program
 {
 static void Main(string[] args)
 {
 int[] intArr = new int[10];
 //creating a collection of int array
 for (int i = 0; i < 10; i++)
 {
 intArr[i] = i;
 }
 Console.WriteLine("The Array consist of following values");
 //intArr is a collection 
foreach (int Value in intArr)
  {
Console.WriteLine(Value);
 }
  Console.ReadLine();
 }
}
 }
 


Fig: Demonstrating how Actually For Each Loop Flows

As we move ahead we learn more benefits of using for each loop.

Parallel ForeachLoop

In .net Microsoft® .NET Framework 4 Microsoft provide Parallel Computing features which specifies new programming working model for writing multi-threaded and asynchronous code . I won’t be going in depth of that and will focus on Parallel ForeachLoop given in Parallel Computing.

As we have learn above about Foreach loop we also have Parallel foreach loop. In sequential foreach loop, the loop is run from a single thread, while in Parallel Foreach loop it uses multiple threads. The implementation of Parallel Foreach loop should be done when we have our independent set of statements i.e. not depending upon each other. The parallel loop runs faster when the computer has available cores.

Conclusion

If you want to watch and learn C# you can  start with C# the below video Learn C# in 60 days.

Here we have successfully completed our day 1. In day 2 we will talk about Namespaces, Classes, Inheritance, Access Modifiers, Properties, Interface and Abstract Class. So keep reading, keep learning.
If there is any confusion you can comment us for the same. Any feedbacks and added information is heartily welcomed.

 

The Great SQL LOADER UTILITY IN ORACLE

Abstract: This article focus on inserting millions of records in a single go in a single or multiple table.

Introduction

Relational DBMS provide us with many features which we generally are not aware of in real life, if you are lucky you will be informed by colleagues, learnt threw watching tutorial, books or else when you are stuck in day to day escalation from support team that the utility you gave for inserting is running slow.

Agenda:
Inserting a bulk upload from CSV file to the database using SQL Loader utility.

 

Here in the article I will inserting 2 lakh records in the table, which I was finding an overhead in my day to day development life.

Let’s get started, I have a csv files with 200999 records. In Order to insert the same.

I will follow below steps:

  1. Go to database and search for table( ex TblEmployee)
  2. Right click on table and select import data.


Figure1.0 Demonstrating Import data feature in sql developer.

 

 

 

 

  1. Search for the file you want to insert and click on ok.


Figure2.0 selecting your csv file.

  1. Following below window will open, you can define the delimiter as per your need, csv file has by default ‘,’ as a delimiter


Figure3.0 Checking the records from csv file.

 

Cross verify your records, if you find any redundant data that you don’t want to insert, than please correct the same in the file.

 

Once checked than click on Next button


Figure4.0 Select the import method.

  1. Select Import method as SQL loader utility as shown below:


Figure5.0 SQL Loader Utility

 

To remove the row limit clear the 100 value from the textbox.

Click on next:

Now in Column definition you need to map your csv headers with table column


Figure6.0 mapping csv headers with table column.

Click Next


Figure7.0 setting up configuration path for the log file and ctl file.

 

 

Now set the path for the log file, bad file and batch file, as shown below:


Figure8.0 setting up configuration path for the log file and ctl file.

 

Now once you are done with path configuration click on next.


Figure9.0 verifying the configuration done in the SQL loader.

Click on verify to verify the configuration, if you have mapped a two csv headers with same column, this window will give you an error.


Figure10.0 verified successfully.

 

Once everything is verified. Click on Finish

We can see the files are created to our configured folder:

 


 

Figure11.0 Batch File generated.

Now before executing I want to you to note down one more requirement. What if one of my column is using oracle sequences for the column value, than how should we proceed to do the same.

In ctl file generated we need to add a simple one line to achieve the same, as shown below:

load data

infile ‘C:\Users\Saillesh.pawar\Desktop\RRP_B_E_01082016new.csv’ “str ‘\r\n'”

append

into table tableName

fields terminated by ‘,’

OPTIONALLY ENCLOSED BY ‘”‘ AND ‘”‘

trailing nullcols

( EMPLOYEE_ID CHAR(4000),

FIRST_NAME CHAR(4000),

EMAIL_ID CHAR(4000),

MOBILE_NO CHAR(4000),

CITY CHAR(4000),

STATE CHAR(4000),

DEVICE_TYPE CHAR(4000),

FLAG CHAR(4000),

UD CHAR(4000),

COUPON_CODE CHAR(4000),

ADDED_DATE DATE “dd-mm-yy”

)

After adding Sequence command:

load data

infile ‘C:\Users\Saillesh.pawar\Desktop\RRP_B_E_01082016new.csv’ “str ‘\r\n'”

append

into table REFERRAL_TRANS_DATA

fields terminated by ‘,’

OPTIONALLY ENCLOSED BY ‘”‘ AND ‘”‘

trailing nullcols

( EMPLOYEE_ID CHAR(4000),

FIRST_NAME CHAR(4000),

EMAIL_ID CHAR(4000),

MOBILE_NO CHAR(4000),

CITY CHAR(4000),

STATE CHAR(4000),

DEVICE_TYPE CHAR(4000),

FLAG CHAR(4000),

UD CHAR(4000),

COUPON_CODE CHAR(4000),

ADDED_DATE DATE “dd-mm-yy”,

     SR_NO “REFERRAL_TV_SR_NO.nextval”

)

 

Once we have resolved this problem

Let look into our one more problem what if I don’t have access to the server where db is installed and I don’t have tnsnames.ora files for the tns of database

For the same I will just pass the full tns to the batch file, I will edit the batch file and insert following commands after sqldr.

userid=’RPOS_PRODUCTION/rpos@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.01)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=tns)))’

And after all the command write pause for pausing cmd.

sqlldr userid=’RPOS_PRODUCTION/rpos@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.01)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=tns)))’

CONTROL=RRP_B_E_01082016new.ctl LOG=D:\bulkUploadSqlDeveloper\RRP_B_E_01082016new.log BAD=D:\bulkUploadSqlDeveloper\RRP_B_E_01082016new.bad skip=1

Pause

 

Once we are done with this we are ready to execute our batch file.


Figure12.0 Running Batch File.

 

After running the batch file, you will get the following output:


Figure13.0 Records are getting inserted

 

You can see your table records for the status of job. The SQL loader Utility helps you upload bulk data from the csv or other types of file in an easy and efficient manner.


Figure14.0 SQL loader gets completed.

 

Once the all the records are inserted we can check the same in our db.


Figure15.0 count reconciled with database.

 

Now what if we want to insert the same values into another table, then we need to add some more configuration in our cdl file below the first table as shown below:

 

into table tableName

fields terminated by ‘,’

OPTIONALLY ENCLOSED BY ‘”‘ AND ‘”‘

trailing nullcols

(

“Empl ID” FILLER POSITION(1) CHAR, –start from the first location of csv header

” Name” FILLER, –by pass this header

“Email ID” filler, –by pass

mobileno “Substr(:mobileno,1,10)”, first header

STATUS CONSTANT ‘Email invitation to be sent’, –inserting hardcoded values to the column

City filler, –by pass

State filler,–by pass

Type filler,–by pass

flag filler,–by pass

ud filler,–by pass

pmsg_Val ,

email_id “:pmsg_Val”, –using above value for the other columns.

employee_id “:pmsg_Val”,

pmsg_status “1”,

item_count “1”,

pmsg_id “41”,

create_dt “SYSDATE+1”,

product_id “1000461”,

expiry_date “SYSDATE+14”

)

I hope this article was useful for uploading bulk upload from csv file using SQL loader.

 

References