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=lastpractise;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 users");
Console.WriteLine("-----------------------");

scd.ReadData1();

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

scd.ReadData2();

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

scd.ReadData3();

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

scd.ReadData();

int numberOfRecords = scd.GetNumberOfRecords();
Console.WriteLine();
Console.WriteLine("Number of Records : {0}", numberOfRecords);
}

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


try
{
conn.Open();
SqlCommand cmd = new SqlCommand("Select code,name From continet_name from countries", conn);

rdr = cmd.ExecuteReader();

while (rdr.Read())
{
Console.WriteLine("{0},{1},{2}", rdr[0], rdr[1], rdr[2]);
}
}
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 users", conn);

rdr = cmd.ExecuteReader();

while (rdr.Read())
{
Console.WriteLine("{0},{1},{2},{3},{4},{5},{6}", rdr[0], rdr[1], rdr[2], rdr[3], rdr[4], rdr[5], rdr[6]);
}
}
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 merchants", 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 ReadData3()
{
SqlDataReader rdr = null;


try
{
conn.Open();
SqlCommand cmd = new SqlCommand("Select * From products", 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 InsertData()
{
try
{
conn.Open();

string insertString = @"insert into countries(code,name,continent_name) values ('6', 'Alaska','America'),('7','kolkata','India')";
string insertString1 = @"insert into users(id,full_name,email,gender,date_of_birth,country_code,created_at) values ('6', 'Shravan Kumar','[email protected]','Male','2001-4-10','3','2009-03-12'),('7','Raja Ali',[email protected]','Male',,'2001-4-10','2',2010-5-3)";
string insertString2 = @"insert into merchants(id,merchant_name,admin_id,country_code,created_at) values ('6', 'dharamsena','6','2','2000-2-20'),('7','Alex Hales','7','2009-03-12')";
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')";

string insertString4 = @"insert into products(id,merchant_id,name,price,status,created_at) values ('6', '3','monitor','20000','available','dec 2021'),('7','1','keyboard','300','unavailable','jan 2022')";
string insertString5 = @"insert into orders(id,user_id,status,created_at) values ('6', '3','shipped','2000-08-2'),('7','1','confirmed','2002-02-12')";
string insertString6 = @"insert into order_item(order_id,product_id,quantity) values ('5', '1','3'),('6','1','5')";



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

SqlCommand cmd2 = new SqlCommand(insertString2, conn);

SqlCommand cmd3 = new SqlCommand(insertString3, conn);


SqlCommand cmd4 = new SqlCommand(insertString4, conn);

SqlCommand cmd5 = new SqlCommand(insertString5, conn);

SqlCommand cmd6 = new SqlCommand(insertString6, conn);


cmd.ExecuteNonQuery();
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
cmd3.ExecuteNonQuery();

cmd4.ExecuteNonQuery();

cmd5.ExecuteNonQuery();

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

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

string updateString = @"Update countries Set Name = 'Neemrana' Where Region = 'India'";

SqlCommand cmd = new SqlCommand(updateString, conn);

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

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