public void Transaction()
{
IDbContext dbContext = null;
try
{
dbContext = DbContextFactory.GetDbContext();
dbContext.Open(true);
dbContext.From <Student>().Insert(new Student()
{
Name = "stduent1"
});
//throw new Exception("rollback");
dbContext.From <School>().Insert(new School()
{
Name = "school1"
});
dbContext.Commit();
}
catch (Exception)
{
dbContext?.Rollback();
throw;
}
finally
{
dbContext?.Close();
}
}
public static List <String> GetSysColumnsPKList(string tableName)
{
List <String> result = new List <String>();
IDbContext ctx = DbFactory.Configure();
try
{
DbHelper helper = new DbHelper(typeof(SysColumns));
ctx.CommandText = string.Format("SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name = '{0}'", tableName);
using (IDataReader reader = DaoBase.GetDataReader(ctx))
while (reader.Read())
{
result.Add(reader[0].ToString());
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
ctx.Close();
}
return(result);
}
public static List <ReportParameterLabel> GetReportParameterLabelList(string filterExpression)
{
List <ReportParameterLabel> result = new List <ReportParameterLabel>();
IDbContext ctx = DbFactory.Configure();
try
{
DbHelper helper = new DbHelper(typeof(ReportParameterLabel));
ctx.CommandText = helper.Select(filterExpression);
using (IDataReader reader = DaoBase.GetDataReader(ctx))
while (reader.Read())
{
result.Add((ReportParameterLabel)helper.IDataReaderToObject(reader, new ReportParameterLabel()));
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
ctx.Close();
}
return(result);
}
public static List <spSensusRIPerBulanPerKelas> GetspSensusRIPerBulanPerKelasList(String[] parameterField, String[] param)
{
List <spSensusRIPerBulanPerKelas> result = new List <spSensusRIPerBulanPerKelas>();
IDbContext ctx = DbFactory.Configure();
try
{
DbHelper helper = new DbHelper(typeof(spSensusRIPerBulanPerKelas));
ctx.CommandText = "sprirpt_SensusRI_perBulan_perKelas";
ctx.CommandType = System.Data.CommandType.StoredProcedure;
//Add Parameter
int count = parameterField.Length;
for (int i = 0; i < count; i++)
{
ctx.Add(parameterField[i], param[i]);
}
using (IDataReader reader = DaoBase.GetDataReader(ctx))
while (reader.Read())
{
result.Add((spSensusRIPerBulanPerKelas)helper.IDataReaderToObject(reader, new spSensusRIPerBulanPerKelas()));
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
ctx.Close();
}
return(result);
}
public static List <spPharmacyStockCard> GetspPharmacyStockCardList(String[] parameterField, String[] param)
{
List <spPharmacyStockCard> result = new List <spPharmacyStockCard>();
IDbContext ctx = DbFactory.Configure();
try
{
DbHelper helper = new DbHelper(typeof(spPharmacyStockCard));
ctx.CommandText = "spfmrpt_KartuPersediaanFarmasi";
ctx.CommandType = System.Data.CommandType.StoredProcedure;
//Add Parameter
int count = parameterField.Length;
for (int i = 0; i < count; i++)
{
ctx.Add(parameterField[i], param[i]);
}
using (IDataReader reader = DaoBase.GetDataReader(ctx))
while (reader.Read())
{
result.Add((spPharmacyStockCard)helper.IDataReaderToObject(reader, new spPharmacyStockCard()));
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
ctx.Close();
}
return(result);
}
public static DataTable GetDataReport(string procedureName, string[] parameterField, string[] param)
{
DataTable result;
IDbContext ctx = DbFactory.Configure();
try
{
ctx.CommandText = procedureName;
ctx.CommandType = CommandType.StoredProcedure;
ctx.Clear();
//Add Parameter
int count = parameterField.Length;
for (int i = 0; i < count; i++)
{
ctx.Add(parameterField[i], param[i]);
}
//Get DataReader
result = DaoBase.GetDataTable(ctx);
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
ctx.Close();
}
return(result);
}
public static int ExecuteNonQuery(IDbContext ctx)
{
bool isNullTransaction=false;
// Selalu pakai transaction untuk mencegah record ditable lain di update bila ada proses yg gagal
if (ctx.Transaction == null)
{
isNullTransaction = true;
ctx.Command.Transaction = ctx.Command.Connection.BeginTransaction();
}
int result;
// Untuk keperluan error handling & loging
try
{
//Error bila untuk app windows, jadi diabaikan saja
if (HttpContext.Current != null)
{
HttpContext.Current.Session["_LastSqlException"] = null;
HttpContext.Current.Session["_LastSqlCommand"] = ctx.Command;
}
}
catch (Exception) { }
try
{
result = ctx.Command.ExecuteNonQuery();
if (isNullTransaction)
ctx.Command.Transaction.Commit();
}
catch (SqlException ex)
{
try
{
//Error bila untuk app windows, jadi diabaikan saja
if (HttpContext.Current != null)
{
HttpContext.Current.Session["_LastSqlException"] = ex;
}
}
catch (Exception) { }
if (isNullTransaction)
ctx.Command.Transaction.Rollback();
throw new Exception(ex.Message, ex);
}
catch (Exception ex)
{
if (isNullTransaction)
ctx.Command.Transaction.Rollback();
throw new Exception(ex.Message, ex);
}
finally
{
if (isNullTransaction)
ctx.Command.Transaction = null;
ctx.Close();
}
return result;
}
public void Insert()
{
IDbContext context = null;
try
{
context = DbContextFactory.GetDbContext();
var row = context.From <Student>().InsertReturnId(s => new Student()
{
Age = 90,
Name = "zshh",
IsDelete = false
});
//because set "id[isIdentity=true]"£¬so not set "id" value
var row1 = context.From <Student>().Insert(new Student()
{
Grade = Grade.A,
CreateTime = DateTime.Now,
Name = "jack",
});
//batch added
var row2 = context.From <Student>().Insert(new List <Student>()
{
new Student()
{
Grade = Grade.C,
CreateTime = DateTime.Now,
Name = "tom",
},
new Student()
{
Grade = Grade.F,
CreateTime = DateTime.Now,
Name = "jar",
},
});
}
catch (Exception e)
{
//debug sql logger
Console.WriteLine(context.Loggers);
}
finally
{
context.Close();
}
}
public static DataSet GetDataSet(IDbContext ctx)
{
DataSet ds = new DataSet();
IDataAdapter da = ctx.DataAdapter;
// Untuk keperluan error handling & loging
try
{
//Error bila untuk app windows, jadi diabaikan saja
HttpContext.Current.Session["_LastSqlException"] = null;
HttpContext.Current.Session["_LastSqlCommand"] = ctx.Command;
}
catch (Exception) { }
try
{
da.Fill(ds);
}
catch (SqlException ex)
{
try
{
//Error bila untuk app windows, jadi diabaikan saja
HttpContext.Current.Session["_LastSqlException"] = ex;
}
catch (Exception) { }
throw new Exception(ex.Message, ex);
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
ctx.Close();
}
return(ds);
}
public static object ExecuteScalar(IDbContext ctx)
{
// Untuk keperluan error handling & loging
try
{
//Error bila untuk app windows, jadi diabaikan saja
HttpContext.Current.Session["_LastSqlException"] = null;
HttpContext.Current.Session["_LastSqlCommand"] = ctx.Command;
}
catch (Exception) { }
object result;
try
{
result = ctx.Command.ExecuteScalar();
}
catch (SqlException ex)
{
try
{
//Error bila untuk app windows, jadi diabaikan saja
HttpContext.Current.Session["_LastSqlException"] = ex;
}
catch (Exception) { }
throw new Exception(ex.Message, ex);
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
ctx.Close();
}
return(result);
}
public static object ExecuteScalar(IDbContext ctx)
{
// Untuk keperluan error handling & loging
try
{
//Error bila untuk app windows, jadi diabaikan saja
HttpContext.Current.Session["_LastSqlException"] = null;
HttpContext.Current.Session["_LastSqlCommand"] = ctx.Command;
}
catch (Exception) { }
object result;
try
{
result = ctx.Command.ExecuteScalar();
}
catch (SqlException ex)
{
try
{
//Error bila untuk app windows, jadi diabaikan saja
HttpContext.Current.Session["_LastSqlException"] = ex;
}
catch (Exception) { }
throw new Exception(ex.Message, ex);
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
ctx.Close();
}
return result;
}
public static DataSet GetDataSet(IDbContext ctx)
{
DataSet ds = new DataSet();
IDataAdapter da = ctx.DataAdapter;
// Untuk keperluan error handling & loging
try
{
//Error bila untuk app windows, jadi diabaikan saja
HttpContext.Current.Session["_LastSqlException"] = null;
HttpContext.Current.Session["_LastSqlCommand"] = ctx.Command;
}
catch (Exception) { }
try
{
da.Fill(ds);
}
catch (SqlException ex)
{
try
{
//Error bila untuk app windows, jadi diabaikan saja
HttpContext.Current.Session["_LastSqlException"] = ex;
}
catch (Exception) { }
throw new Exception(ex.Message, ex);
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
ctx.Close();
}
return ds;
}
public static int ExecuteNonQuery(IDbContext ctx)
{
bool isNullTransaction = false;
// Selalu pakai transaction untuk mencegah record ditable lain di update bila ada proses yg gagal
if (ctx.Transaction == null)
{
isNullTransaction = true;
ctx.Command.Transaction = ctx.Command.Connection.BeginTransaction();
}
int result;
// Untuk keperluan error handling & loging
try
{
//Error bila untuk app windows, jadi diabaikan saja
if (HttpContext.Current != null)
{
HttpContext.Current.Session["_LastSqlException"] = null;
HttpContext.Current.Session["_LastSqlCommand"] = ctx.Command;
}
}
catch (Exception) { }
try
{
result = ctx.Command.ExecuteNonQuery();
if (isNullTransaction)
{
ctx.Command.Transaction.Commit();
}
}
catch (SqlException ex)
{
try
{
//Error bila untuk app windows, jadi diabaikan saja
if (HttpContext.Current != null)
{
HttpContext.Current.Session["_LastSqlException"] = ex;
}
}
catch (Exception) { }
if (isNullTransaction)
{
ctx.Command.Transaction.Rollback();
}
throw new Exception(ex.Message, ex);
}
catch (Exception ex)
{
if (isNullTransaction)
{
ctx.Command.Transaction.Rollback();
}
throw new Exception(ex.Message, ex);
}
finally
{
if (isNullTransaction)
{
ctx.Command.Transaction = null;
}
ctx.Close();
}
return(result);
}
public bool Save()
{
return(context.Close());
}