static void SimpleQuary()
{
try
{
//insert
new HappyDbAccess.DbAccess(GlobleConfig.ConnectionString).Update(@"INSERT INTO Persons
(Name, Address, Email, PhoneNumber, RegDate) VALUES('Chamith','Sri Lanka','*****@*****.**','0718920205','" + DateTime.Today.ToShortDateString() + "');");
Console.WriteLine("insert is success");
}
catch (Exception ex)
{
Console.WriteLine("EXCEPTION = {0}", ex.Message);
}
// select
DataTable dt = new DataTable();
try
{
dt = new HappyDbAccess.DbAccess(GlobleConfig.ConnectionString).SelectTable(@"SELECT Id, Name, Address, Email, PhoneNumber, RegDate FROM Persons");
foreach (DataRow item in dt.Rows)
{
Console.WriteLine(string.Format("{0} \t {1} \t {2} \t {3} \t {4} \t {5}", item[0].ToString(), item[1].ToString(), item[2].ToString(), item[3].ToString(),
item[4].ToString(), item[5].ToString()));
}
}
catch (Exception ex)
{
Console.WriteLine("EXCEPTION = {0}", ex.Message);
}
Console.Read();
//update
try
{
new HappyDbAccess.DbAccess(GlobleConfig.ConnectionString).Update(@"UPDATE Persons SET Name = 'Ruwan', Email = '*****@*****.**' WHERE (Id = '1')");
Console.WriteLine("update is success");
}
catch (Exception ex)
{
Console.WriteLine("EXCEPTION = {0}", ex.Message);
}
// select
try
{
dt = new HappyDbAccess.DbAccess(GlobleConfig.ConnectionString).SelectTable(@"SELECT Id, Name, Address, Email, PhoneNumber, RegDate FROM Persons");
foreach (DataRow item in dt.Rows)
{
Console.WriteLine(string.Format("{0} \t {1} \t {2} \t {3} \t {4} \t {5}", item[0].ToString(), item[1].ToString(), item[2].ToString(), item[3].ToString(),
item[4].ToString(), item[5].ToString()));
}
}
catch (Exception ex)
{
Console.WriteLine("EXCEPTION = {0}", ex.Message);
}
Console.Read();
//delete
try
{
new HappyDbAccess.DbAccess(GlobleConfig.ConnectionString).Update(@"DELETE FROM Persons WHERE (Id = 1)");
Console.WriteLine("delete is success");
}
catch (Exception ex)
{
Console.WriteLine("EXCEPTION = {0}", ex.Message);
}
// select
try
{
dt = new HappyDbAccess.DbAccess(GlobleConfig.ConnectionString).SelectTable(@"SELECT Id, Name, Address, Email, PhoneNumber, RegDate FROM Persons");
foreach (DataRow item in dt.Rows)
{
Console.WriteLine(string.Format("{0} \t {1} \t {2} \t {3} \t {4} \t {5}", item[0].ToString(), item[1].ToString(), item[2].ToString(), item[3].ToString(),
item[4].ToString(), item[5].ToString()));
Console.Read();
}
}
catch (Exception ex)
{
Console.WriteLine("EXCEPTION = {0}", ex.Message);
}
}
static void QueryWithParameter()
{
try
{
//insert
new HappyDbAccess.DbAccess(GlobleConfig.ConnectionString).Update(@"INSERT INTO Persons
(Name, Address, Email, PhoneNumber, RegDate) VALUES(@Name,@Address,@Email,@PhoneNumber,@RegDate);",
new List <SqlParameter>()
{
new SqlParameter("Name", "Chamith"),
new SqlParameter("Address", "Sri Lanka"),
new SqlParameter("Email", string.Format("{0}@gmail.com", Guid.NewGuid().ToString())),
new SqlParameter("PhoneNumber", "12345678984"),
new SqlParameter("RegDate", DateTime.Today.ToShortDateString())
});
Console.WriteLine("insert is success");
}
catch (Exception ex)
{
Console.WriteLine("EXCEPTION = {0}", ex.Message);
}
// select
DataTable dt = new DataTable();
try
{
dt = new HappyDbAccess.DbAccess(GlobleConfig.ConnectionString).SelectTable(@"SELECT Id, Name, Address, Email, PhoneNumber, RegDate FROM Persons WHERE Address = @Address",
new List <SqlParameter>()
{
new SqlParameter("@Address", "Sri Lanka")
}
);
foreach (DataRow item in dt.Rows)
{
Console.WriteLine(string.Format("{0} \t {1} \t {2} \t {3} \t {4} \t {5}", item[0].ToString(), item[1].ToString(), item[2].ToString(), item[3].ToString(),
item[4].ToString(), item[5].ToString()));
}
}
catch (Exception ex)
{
Console.WriteLine("EXCEPTION = {0}", ex.Message);
}
Console.Read();
//update
try
{
new HappyDbAccess.DbAccess(GlobleConfig.ConnectionString).Update(@"UPDATE Persons SET Name = @Name, Email = @Email WHERE (Id = @Id)",
new List <SqlParameter>()
{
new SqlParameter("@Name", "Gayan"),
new SqlParameter("Email", $"{Guid.NewGuid().ToString()}@gmail.com"),
new SqlParameter("Id", "1")
}
);
Console.WriteLine("update is success");
}
catch (Exception ex)
{
Console.WriteLine("EXCEPTION = {0}", ex.Message);
}
// select
try
{
dt = new HappyDbAccess.DbAccess(GlobleConfig.ConnectionString).SelectTable(@"SELECT Id, Name, Address, Email, PhoneNumber, RegDate FROM Persons");
foreach (DataRow item in dt.Rows)
{
Console.WriteLine(string.Format("{0} \t {1} \t {2} \t {3} \t {4} \t {5}", item[0].ToString(), item[1].ToString(), item[2].ToString(), item[3].ToString(),
item[4].ToString(), item[5].ToString()));
}
}
catch (Exception ex)
{
Console.WriteLine("EXCEPTION = {0}", ex.Message);
}
Console.Read();
//delete
try
{
new HappyDbAccess.DbAccess(GlobleConfig.ConnectionString).Update(@"DELETE FROM Persons WHERE (Id = @Id)",
new List <SqlParameter>()
{
new SqlParameter("Id", "2")
});
Console.WriteLine("delete is success");
}
catch (Exception ex)
{
Console.WriteLine("EXCEPTION = {0}", ex.Message);
}
// select
try
{
dt = new HappyDbAccess.DbAccess(GlobleConfig.ConnectionString).SelectTable(@"SELECT Id, Name, Address, Email, PhoneNumber, RegDate FROM Persons");
foreach (DataRow item in dt.Rows)
{
Console.WriteLine(string.Format("{0} \t {1} \t {2} \t {3} \t {4} \t {5}", item[0].ToString(), item[1].ToString(), item[2].ToString(), item[3].ToString(),
item[4].ToString(), item[5].ToString()));
Console.Read();
}
}
catch (Exception ex)
{
Console.WriteLine("EXCEPTION = {0}", ex.Message);
}
}
