Notes

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.Intrinsics.Arm;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp5
{
internal class SQLCommand
{
SqlConnection conn;
string connectionString = "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Practice4;Integrated Security=SSPI";
public SQLCommand()
{
conn = new SqlConnection(connectionString);
}
static void Main()
{
SQLCommand sQLCommand = new SQLCommand();
/* Console.WriteLine("======================Before Inserting=======================");
sQLCommand.Readdata();
Console.WriteLine("======================After Inserting=======================");
sQLCommand.InsertData();
sQLCommand.Readdata();
Console.WriteLine("======================After Updating======================");
sQLCommand.UpdateData();
sQLCommand.Readdata();

Console.WriteLine("======================After Deleting======================");
sQLCommand.DeleteData();
sQLCommand.Readdata();

Console.WriteLine("======================Get Count======================");
Console.WriteLine(sQLCommand.GetCount());*/
sQLCommand.ReaddataUsingSPParams();


}
public void Readdata()
{
SqlDataReader rdr = null;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("Select * from Users", conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr[1]);
}

}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
public void ReaddataUsingSP()
{
SqlDataReader rdr = null;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("SP", conn);
cmd.CommandType = CommandType.StoredProcedure;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr[1]);
}

}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
public void ReaddataUsingSPParams()
{
SqlDataReader rdr = null;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("SPNew", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param;
param= cmd.Parameters.Add("@id", SqlDbType.Int);
param.Value = 2;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr[1]);
}

}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
public void InsertData()
{
try
{

conn.Open();
string query = @"INSERT INTO Users(full_name,enabled,last_login) values('John Doe','f',GETDATE())";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.Close();
}

}
}
public void UpdateData()
{
try
{

conn.Open();
string query = @"UPDATE Users SET full_name='John Doe' WHERE _id=1";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.Close();
}

}
}
public void DeleteData()
{
try
{

conn.Open();
string query = @"DELETE FROM Users WHERE _id=1";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.Close();
}

}
}
public int GetCount()
{
int count = -1;
try
{

conn.Open();
string query = @"SELECT count(*) from Users";
SqlCommand cmd = new SqlCommand(query, conn);
count = Convert.ToInt32(cmd.ExecuteScalar());
}
finally
{
if (conn != null)
{
conn.Close();
}

}
return count;
}
}
}