First of all, we will create a database and table followed by insertion of data in table. We will be using the following queries.

  1. create database EmployeeDatabase
  2. use EmployeeDatabase
  4. Create Table EmployeeDetails
  5. (
  6. Name varchar(50),
  7. Email varchar(50),
  8. Salary varchar(30),
  9. Department varchar (50)
  10. )
  11. select * from EmployeeDetails
  13. INSERT INTO EmployeeDetails(Name, Email, Salary, Department)
  14. VALUES ('ram', '[email protected]', '35000', 'IT');

Secondly, we will create a new project and choose console application in C# and give a name to the project as SqlConnectionDemo, and write the following code in our program.cs

  1. using System;
  2. using System.Data.SqlClient;
  4. namespace SqlConnectionDemo
  5. {
  6. /// <summary>
  7. ///ADO.NET is an object oriented libraries that allows us to interact with database and we weill learn how to connect with sqldatabase using ADO.NET
  8. /// </summary>
  9. class Program
  10. {
  11. public void SelectData()
  12. {
  13. // Create object(instance) of sql connection which will provide a connection to the data source(database) to read and write database
  14. SqlConnection cn = new SqlConnection(@"data source=DESKTOP-6DF0CN3\SQLEXPRESS; Database=EmployeeDatabase; integrated security = true");
  15. // Sql command allow data adpeters to read, add, update and delete the records in a data source.
  16. // Data adapters support 4 properties that give us access to these objects - select, insert, update adn delete for creating queries to database.
  17. // Sqlcommand perform a query on the EmployeeDetails
  18. SqlCommand cmd = new SqlCommand("select * from EmployeeDetails", cn);
  19. // Open the connection by calling open() method of sqlconnection object cn
  20. cn.Open();
  21. //holds a read only, forward only set of data from a database.
  22. //using a sql Data Reader can increase speed because only one row of data is in memory at a time.
  23. // result set is returned as SqlDataReader
  24. SqlDataReader rdr = cmd.ExecuteReader();
  25. // While Loop Read the first column from each row of result set which is employee name
  26. while (rdr.Read())
  27. {
  28. // Print employee details
  29. Console.WriteLine(rdr[0] + " " + rdr[1] + " " + rdr[2]+ " " + rdr[3]);
  30. }
  31. // Close the reader by calling CLose() method
  32. rdr.Close();
  33. // Close the connection by calling close() method
  34. cn.Close();
  35. }
  36. static void Main(string[] args)
  37. {
  38. // Create the object of Program class P
  39. Program p = new Program();
  40. // invoke the SelectData () Method
  41. p.SelectData();
  42. Console.ReadLine();
  43. }
  44. }
  45. }

Output

ram [email protected] 35000 IT