public static bool SaveEmployee(EmployeeEntity employee)
{
bool lSuccess = false;
bool lExist = false;
using (MyDataContext db = new MyDataContext(connectionString))
{
var query = db.Employees.Where(p => (p.Id == employee.Id));
foreach (var rec in query)
{
// Update all property
PropertyInfo[] properties = typeof(EmployeeEntity).GetProperties();
foreach (PropertyInfo property in properties)
{
property.SetValue(rec, property.GetValue(employee));
}
lExist = true;
}
if (!lExist)
{
// Insert record
db.Employees.InsertOnSubmit(employee);
}
try
{
db.SubmitChanges();
lSuccess = true;
}
catch { }
}
return(lSuccess);
}
public static bool SaveEmployee(EmployeeEntity employee)
{
bool lSuccess = false;
using (var context = new MyDataContext("DefaultConnectionString"))
{
context.Database.CreateIfNotExists();
using (var transaction = context.Database.BeginTransaction())
{
try
{
var query = context.Employees.Where(p => (p.Id == employee.Id));
foreach (var rec in query)
{
// Delete record
context.Employees.Remove(rec);
}
// Insert record
context.Employees.Add(employee);
context.SaveChanges();
transaction.Commit();
lSuccess = true;
}
catch (Exception exc)
{
transaction.Rollback();
throw exc;
}
}
}
return(lSuccess);
}
static EmployeeProvider()
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString))
{
// Create database if not exists
using (var contextDB = new MyDataContext(connection, false))
{
contextDB.Database.CreateIfNotExists();
}
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
try
{
using (var context = new MyDataContext(connection, false))
{
// Passing an existing transaction to the context
context.Database.UseTransaction(transaction);
if (context.Employees.Count() == 0)
{
context.Employees.Add(
new EmployeeEntity()
{
Id = 1,
Name = "Mark",
Gender = "Male",
DateOfBirth = DateTime.Parse("10/10/1980")
});
context.Employees.Add(
new EmployeeEntity()
{
Id = 2,
Name = "Mary",
Gender = "Female",
DateOfBirth = DateTime.Parse("11/10/1981")
});
context.Employees.Add(
new EmployeeEntity()
{
Id = 3,
Name = "John",
Gender = "Male",
DateOfBirth = DateTime.Parse("8/10/1979")
});
context.SaveChanges();
}
}
transaction.Commit();
}
catch (Exception exc)
{
transaction.Rollback();
throw exc;
}
}
}
static EmployeeProvider()
{
// Use LocalDB, and create a Database.mdf file in the
// aplication folder if it does not exist.
server = "(LocalDB)\\MSSQLLocalDB";
directory = BaseDir;
dbName = "Database";
connectionString = "Data Source=" + server + ";AttachDbFilename=\"" + directory + dbName + ".mdf\";Initial Catalog = " + MyDataContext.Generate_Identifier_Name(directory, dbName) + ";Integrated Security=True;";
// If MDF file does not exist, create database
if (!File.Exists(directory + dbName + ".mdf"))
{
using (MyDataContext db = new MyDataContext("Data Source=" + server + ";"))
{
if (!db.CreateDatabase(directory, dbName))
{
throw new ApplicationException("Database creation error");
}
}
}
// If Datatable does not exist, create and initialize it.
using (MyDataContext db = new MyDataContext(connectionString))
{
if (!db.DatabaseExists())
{
throw new ApplicationException("Database connection error. ConnectionString: " + connectionString);
}
if (!db.TableExists <EmployeeEntity>())
{
db.CreateTable <EmployeeEntity>();
db.Employees.InsertOnSubmit(
new EmployeeEntity()
{
Id = 1,
Name = "Mark",
Gender = "Male",
DateOfBirth = DateTime.Parse("10/10/1980")
});
db.Employees.InsertOnSubmit(
new EmployeeEntity()
{
Id = 2,
Name = "Mary",
Gender = "Female",
DateOfBirth = DateTime.Parse("11/10/1981")
});
db.Employees.InsertOnSubmit(
new EmployeeEntity()
{
Id = 3,
Name = "John",
Gender = "Male",
DateOfBirth = DateTime.Parse("8/10/1979")
});
db.SubmitChanges();
}
}
}
public static bool Test()
{
bool lTest;
using (MyDataContext context = new MyDataContext(ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString))
{
lTest = context.Database.Exists();
}
return(lTest);
}
public static EmployeeEntity GetEmployee(int id)
{
EmployeeEntity employee;
using (MyDataContext context = new MyDataContext(ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString))
{
employee = context.Employees.Where(p => p.Id == id).FirstOrDefault();
}
return(employee);
}
public static bool Test()
{
bool lTest;
using (MyDataContext db = new MyDataContext(connectionString))
{
lTest = db.DatabaseExists();
}
return(lTest);
}
//public static bool AddEmployee(EmployeeEntity employee)
//{
// bool lSuccess = false;
// using (MyDataContext db = new MyDataContext(connectionString))
// {
// db.Employees.InsertOnSubmit(employee);
// try
// {
// db.SubmitChanges();
// lSuccess = true;
// }
// catch { }
// }
// return lSuccess;
//}
public static EmployeeEntity GetEmployee(int id)
{
EmployeeEntity employee;
using (MyDataContext db = new MyDataContext(connectionString))
{
employee = db.Employees.Where(p => p.Id == id).FirstOrDefault();
}
return(employee);
}
public static bool Test()
{
bool lTest;
using (var context = new MyDataContext("DefaultConnectionString"))
{
lTest = context.Database.Exists();
}
return(lTest);
}
public static EmployeeEntity GetEmployee(int id)
{
EmployeeEntity employee = null;
using (var context = new MyDataContext("DefaultConnectionString"))
{
foreach (var rec in context.Employees.Where(p => p.Id == id))
{
employee = rec;
}
}
return(employee);
}
public static EmployeeEntity GetEmployee(int id)
{
EmployeeEntity employee = null;
using (MyDataContext context = new MyDataContext(ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString))
{
foreach (var rec in context.Employees.Where(p => p.Id == id))
{
employee = rec;
}
}
return(employee);
}
public static bool SaveEmployee(EmployeeEntity employee)
{
bool lSuccess = false;
bool lExist = false;
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString))
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
try
{
using (var context = new MyDataContext(connection, false))
{
// Passing an existing transaction to the context
context.Database.UseTransaction(transaction);
var query = context.Employees.Where(p => (p.Id == employee.Id));
foreach (var rec in query)
{
// Update all property
PropertyInfo[] properties = typeof(EmployeeEntity).GetProperties();
foreach (PropertyInfo property in properties)
{
property.SetValue(rec, property.GetValue(employee));
}
lExist = true;
}
if (!lExist)
{
// Insert record
context.Employees.Add(employee);
}
context.SaveChanges();
}
transaction.Commit();
lSuccess = true;
}
catch (Exception exc)
{
transaction.Rollback();
// throw exc;
}
}
return(lSuccess);
}
public static bool SaveEmployee(EmployeeEntity employee)
{
bool lSuccess = false;
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString))
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
try
{
using (var context = new MyDataContext(connection, false))
{
// Passing an existing transaction to the context
context.Database.UseTransaction(transaction);
var query = context.Employees.Where(p => (p.Id == employee.Id));
foreach (var rec in query)
{
// Delete record
context.Employees.Remove(rec);
}
// Insert record
context.Employees.Add(employee);
context.SaveChanges();
}
transaction.Commit();
lSuccess = true;
}
catch (Exception exc)
{
transaction.Rollback();
// throw exc;
}
}
return(lSuccess);
}
static EmployeeProvider()
{
using (var context = new MyDataContext("DefaultConnectionString"))
{
if (!context.Database.Exists())
{
context.Database.CreateIfNotExists();
}
}
Database.SetInitializer(new MigrateDatabaseToLatestVersion <MyDataContext, Migrations.Configuration>(true));
using (var context = new MyDataContext("DefaultConnectionString"))
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
if (context.Employees.Count() == 0)
{
context.Employees.Add(
new FullTimeEmployeeEntity()
{
Id = 1,
Name = "Mark",
Gender = "Male",
DateOfBirth = DateTime.Parse("10/10/1980"),
AnnualSalary = 50000
});
context.Employees.Add(
new PartTimeEmployeeEntity()
{
Id = 2,
Name = "Mary",
Gender = "Female",
DateOfBirth = DateTime.Parse("11/10/1981"),
HoursWorked = 20,
HourlyPay = 10
});
context.Employees.Add(
new FullTimeEmployeeEntity()
{
Id = 3,
Name = "John",
Gender = "Male",
DateOfBirth = DateTime.Parse("8/10/1979"),
AnnualSalary = 20000
});
context.SaveChanges();
}
transaction.Commit();
}
catch (Exception exc)
{
transaction.Rollback();
throw exc;
}
}
}
}