public Boolean CRUD_data(string sql)
{
Boolean check = koneksidatabase();
if (check)
{
try
{
konek.Open();
cmd = new SqlCommand(sql, konek);
cmd.ExecuteNonQuery();
konek.Close();
return(true);
}
catch (SqlException ex)
{
konek.Close();
GetLog log = new GetLog();
log.Create(ex.Message, MethodBase.GetCurrentMethod().ReflectedType.Name, MethodBase.GetCurrentMethod().Name);
return(false);
}
}
else
{
return(false);
}
}
public String CRUD_scalar(string sql)
{
Boolean check = koneksidatabase();
if (check)
{
try
{
konek.Open();
cmd = new SqlCommand(sql, konek);
string kd = cmd.ExecuteScalar().ToString();
konek.Close();
return(kd);
}
catch (SqlException ex)
{
konek.Close();
GetLog log = new GetLog();
log.Create(ex.Message, MethodBase.GetCurrentMethod().ReflectedType.Name, MethodBase.GetCurrentMethod().Name);
return(null);
}
}
else
{
return(null);
}
}
public DataTable get_dtTable(string sql)
{
Boolean check = koneksidatabase();
if (check)
{
try
{
konek.Open();
cmd = new SqlCommand(sql, konek);
dtAdapter = new SqlDataAdapter(cmd);
dtTable = new DataTable();
dtAdapter.Fill(dtTable);
konek.Close();
}
catch (SqlException ex)
{
konek.Close();
GetLog log = new GetLog();
log.Create(ex.Message, MethodBase.GetCurrentMethod().ReflectedType.Name, MethodBase.GetCurrentMethod().Name);
}
}
return(dtTable);
}
public List <ModelMahasiswa> selectall()
{
sql = "SELECT * FROM tb_mahasiswa WHERE is_deleted=0";
dtTable = get_dtTable(sql); // mengirim sql query untuk di eksekusi
try
{
// mapping data hasil eksekusi query ke class object model (list)
list = dtTable.AsEnumerable()
.Select(row => new ModelMahasiswa()
{
id_mahasiswa = row.Field <decimal>("id_mahasiswa"),
nama = row.Field <string>("nama"),
alamat = row.Field <string>("alamat"),
created_by = row.Field <string>("created_by"),
created_date = row.Field <Nullable <DateTime> >("created_date"),
modified_by = row.Field <string>("modified_by"),
modified_date = row.Field <Nullable <DateTime> >("modified_date"),
is_deleted = row.Field <bool>("is_deleted")
}).ToList();
}
catch (Exception ex)
{
GetLog log = new GetLog();
log.Create(ex.Message, MethodBase.GetCurrentMethod().ReflectedType.Name, MethodBase.GetCurrentMethod().Name);
}
return(list);
}
public ModelMahasiswa selectbyid(int id)
{
ModelMahasiswa m = new ModelMahasiswa();
sql = String.Format("SELECT * FROM tb_mahasiswa WHERE id_mahasiswa='{0}'", id);
try
{
dtRow = get_dtTable(sql).Rows[0]; // mengirim sql query untuk di eksekusi
// mapping data hasil eksekusi query ke class object model (satuan)
m.id_mahasiswa = dtRow.Field <int>("id_mahasiswa");
m.nama = dtRow.Field <string>("nama");
m.alamat = dtRow.Field <string>("alamat");
m.created_by = dtRow.Field <string>("created_by");
m.created_date = dtRow.Field <Nullable <DateTime> >("created_date");
m.modified_by = dtRow.Field <string>("modified_by");
m.modified_date = dtRow.Field <Nullable <DateTime> >("modified_date");
m.is_deleted = dtRow.Field <bool>("is_deleted");
}
catch (Exception ex)
{
GetLog log = new GetLog();
log.Create(ex.Message, MethodBase.GetCurrentMethod().ReflectedType.Name, MethodBase.GetCurrentMethod().Name);
}
return(m);
}
public Boolean koneksidatabase()
{
try
{
connectionstring = ConfigurationManager.ConnectionStrings["koneksidb"].ConnectionString;
konek = new SqlConnection(connectionstring);
konek.Open();
konek.Close();
return(true);
}
catch (SqlException ex)
{
GetLog log = new GetLog();
log.Create(ex.Message, MethodBase.GetCurrentMethod().ReflectedType.Name, MethodBase.GetCurrentMethod().Name);
return(false);
}
}
public Boolean CRUD_batch(List <string> sql_batch)
{
Boolean check = koneksidatabase();
if (check)
{
konek.Open();
SqlTransaction transact = konek.BeginTransaction();
try
{
foreach (var tmp_sql in sql_batch)
{
cmd = new SqlCommand(tmp_sql, konek);
cmd.Transaction = transact;
cmd.ExecuteNonQuery();
}
transact.Commit();
konek.Close();
return(true);
}
catch (SqlException ex)
{
transact.Rollback();
konek.Close();
GetLog log = new GetLog();
log.Create(ex.Message, MethodBase.GetCurrentMethod().ReflectedType.Name, MethodBase.GetCurrentMethod().Name);
return(false);
}
}
else
{
return(false);
}
}
