Notes

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace Ado_dot_Net
{
public class SqlCommandDemo
{
SqlConnection conn;
public SqlCommandDemo()
{
conn = new SqlConnection("Data Source=DESKTOP-9USJKLA\SQLEXPRESS;Initial Catalog=adopractise;Integrated Security=SSPI");
}
public static void Main(String[] args)
{
SqlCommandDemo scd = new SqlCommandDemo();

Console.WriteLine();
Console.WriteLine("Categories Before Insert");
Console.WriteLine("------------------------");

scd.ReadData();
scd.ReadData1();

scd.ReadData2();

scd.ReadData3();



scd.InsertData();
Console.WriteLine();
Console.WriteLine("Categories After Insert");
Console.WriteLine("-----------------------");

scd.ReadData();

Console.WriteLine("Categories After Insert in account");
Console.WriteLine("-----------------------");

scd.ReadData1();

Console.WriteLine("Categories After Insert in loan");
Console.WriteLine("-----------------------");

scd.ReadData2();

Console.WriteLine("Categories After Insert in Order1");
Console.WriteLine("-----------------------");

scd.ReadData3();

scd.UpdateData();
Console.WriteLine();
Console.WriteLine("Categories After Update");
Console.WriteLine("-----------------------");

scd.ReadData();

scd.DeleteData();
Console.WriteLine();
Console.WriteLine("Categories After Delete");
Console.WriteLine("-----------------------");

scd.ReadData();

int numberOfRecords = scd.GetNumberOfRecords();

int numberOfRecords1 = scd.GetNumberOfRecords1();

int numberOfRecords2 = scd.GetNumberOfRecords2();

int numberOfRecords3 = scd.GetNumberOfRecords3();

Console.WriteLine();
Console.WriteLine("Number of Records : {0},{1},{2},{3}", numberOfRecords, numberOfRecords1, numberOfRecords2, numberOfRecords3);
}

public int GetNumberOfRecords()
{
int count = 0;
using (SqlCommand NoOfRows = new SqlCommand("Select Count(*) From District ", conn))
{
conn.Open();
count = (int)NoOfRows.ExecuteScalar();
return count;
}
}

public int GetNumberOfRecords1()
{
int count = 0;
using (SqlCommand NoOfRows = new SqlCommand("Select Count(*) From account ", conn))
{
conn.Open();
count = (int)NoOfRows.ExecuteScalar();
return count;
}
}

public int GetNumberOfRecords2()
{
int count = 0;
using (SqlCommand NoOfRows = new SqlCommand("Select Count(*) From loan ", conn))
{
conn.Open();
count = (int)NoOfRows.ExecuteScalar();
return count;
}
}

public int GetNumberOfRecords3()
{
int count = 0;
using (SqlCommand NoOfRows = new SqlCommand("Select Count(*) From Order1 ", conn))
{
conn.Open();
count = (int)NoOfRows.ExecuteScalar();
return count;
}
}
public void ReadData()
{
SqlDataReader rdr = null;


try
{
conn.Open();
SqlCommand cmd = new SqlCommand("Select district_id,region From District", conn);

rdr = cmd.ExecuteReader();

while (rdr.Read())
{
Console.WriteLine("{0},{1}", rdr[0], rdr[1]);
}
}
finally
{
if (rdr != null)
rdr.Close();
if (conn != null)
conn.Close();
}
}
public void ReadData1()
{
SqlDataReader rdr = null;


try
{
conn.Open();
SqlCommand cmd = new SqlCommand("Select * From account", conn);

rdr = cmd.ExecuteReader();

while (rdr.Read())
{
Console.WriteLine("{0},{1},{2},{3}", rdr[0], rdr[1], rdr[2], rdr[3]);
}
}
finally
{
if (rdr != null)
rdr.Close();
if (conn != null)
conn.Close();
}
}

public void ReadData2()
{
SqlDataReader rdr = null;


try
{
conn.Open();
SqlCommand cmd = new SqlCommand("Select * From loan", conn);

rdr = cmd.ExecuteReader();

while (rdr.Read())
{
Console.WriteLine("{0},{1},{2},{3},{4},{5}", rdr[0], rdr[1], rdr[2], rdr[3], rdr[4], rdr[5]);
}
}
finally
{
if (rdr != null)
rdr.Close();
if (conn != null)
conn.Close();
}
}
public void ReadData3()
{
SqlDataReader rdr = null;


try
{
conn.Open();
SqlCommand cmd = new SqlCommand("Select * From Order1", conn);

rdr = cmd.ExecuteReader();

while (rdr.Read())
{
Console.WriteLine("{0},{1},{2},{3},{4}", rdr[0], rdr[1], rdr[2], rdr[3], rdr[4]);
}
}
finally
{
if (rdr != null)
rdr.Close();
if (conn != null)
conn.Close();
}
}
public void InsertData()
{
try
{
conn.Open();

string insertString = @"insert into District(district_id,region) values ('1','faridabad'),('2','alwar'),('3','ahmedabad'),('4','faridabad'),('5','alwar'),('6', 'delhi'),('7','kolkata')";
string insertString1 = @"insert into account(account_id,district_id,frequency,date1) values ('1', '2','50','2000-2-20'),('2','1','100','2001-4-10'),('3','2','50','2002-5-12')";
string insertString2 = @"insert into loan(loan_id,account_id,date1,amount,duration,payment) values ('1', '2','2000-2-20','500','20','2000'),('2','1','2001-4-10','300','30','1000'),('3','1','2002-5-12','300','15','1000')";
string insertString3 = @"insert into Order1(order_id,account_id,bank_to,account_to,amount) values ('1', '2','shivam','ram','20000'),('2','1','alex','fernandes','300'),('3','1','shravan','rohit','150')";


SqlCommand cmd = new SqlCommand(insertString, conn);
SqlCommand cmd1 = new SqlCommand(insertString1, conn);

SqlCommand cmd2 = new SqlCommand(insertString2, conn);

SqlCommand cmd3 = new SqlCommand(insertString3, conn);


cmd.ExecuteNonQuery();
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
cmd3.ExecuteNonQuery();
}
finally
{
if (conn != null)
conn.Close();
}
}

public void UpdateData()
{
try
{
conn.Open();

string updateString = @"Update District Set Region = 'Neemrana' Where Region = 'faridabad'";

SqlCommand cmd = new SqlCommand(updateString, conn);

cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
conn.Close();
}
}
public void DeleteData()
{
try
{
conn.Open();
string deleteString = @"delete from District where region = 'alwar'";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = deleteString;
cmd.Connection = conn;
cmd.ExecuteNonQuery();

}
finally
{
if (conn != null)
conn.Close();
}
}
}
}