public static string getOptionsTinh(this Models.SQLServerDataContext db, string value = "", string where = "", bool all = true, string defValueSelect = "", bool dangKyTaiKhoan = false)
{
var Server = HttpContext.Current.Server;
var op = new List <string>();
if (all)
{
op.Add($"<option value=\"{defValueSelect}\"> -- Chọn -- </option>");
}
string tsql = dangKyTaiKhoan ? $"select id,ten from dmtinh where id in (select idtinh from nguoidung group by idtinh) {(where == "" ? "" : $" and {where}")} order by ten" : $"select id,ten from dmtinh {(where == "" ? "" : $" where {where}")} order by ten";
public static void saveTaiKhoan(this Models.SQLServerDataContext db, Dictionary <string, string> data, string iduser = "")
{
if (data.ContainsKey("sdt"))
{
if (data["sdt"].ToLower() == "administrator")
{
throw new Exception("Tài khoản 'administrator' không thể thao tác");
}
}
if (data.ContainsKey("tendangnhap"))
{
if (data["tendangnhap"].ToLower() == "administrator")
{
throw new Exception("Tài khoản 'administrator' không thể thao tác");
}
if (Regex.IsMatch(data["tendangnhap"], "[/\\:*?\"<>|]+"))
{
throw new Exception("Tên đăng nhập không hợp lệ (Không chứa các ký tự /\\:*?\"<>|);");
}
}
if (iduser.ToLower() == "administrator")
{
throw new Exception("Tài khoản 'administrator' không thể thao tác");
}
var val1 = new List <string>();
var tsql = "";
if (string.IsNullOrEmpty(iduser))
{
/* Add New */
foreach (var v in data)
{
val1.Add(v.Value.getValueField());
}
tsql = $"insert into nguoidung ({string.Join(",", data.Keys)}) values (N'{string.Join("',N'", val1)}')";
}
else
{
/* Edit */
foreach (var v in data)
{
val1.Add($"{v.Key}=N'{v.Value.getValueField()}'");
}
tsql = $"update nguoidung set {string.Join(",", val1)} where tendangnhap=N'{iduser.getValueField()}'";
}
db.Execute(tsql);
}
public static void createThongBao(this Models.SQLServerDataContext db, string fromIdUser, string toIdUser, string noidung, string chitiet, DateTime?hansudung = null)
{
try
{
var item = new Models.thongbao();
item.chitiet = chitiet;
item.dadoc = 0;
item.den = toIdUser;
item.hansudung = hansudung.HasValue ? hansudung.Value : DateTime.Now;
item.noidung = noidung;
item.times = DateTime.Now;
item.tu = fromIdUser;
db.thongbaos.InsertOnSubmit(item);
db.SubmitChanges();
}
catch (Exception ex) { ex.save(); }
}
private static void bulkCopyTableFromAccess(this Models.SQLServerDataContext db, string fileName, string tableName, itemScheduler args, ref int countRow, ref List <string> msgError, string where = "", string fromTable = "")
{
var cols = new List <string>();
var dt = db.getDataSet($"select top 1 * from {tableName}").Tables[0];
foreach (DataColumn c in dt.Columns)
{
cols.Add(c.ColumnName);
}
string col = $"[{string.Join("],[", cols)}]";
var f = new FileInfo(fileName);
var acc = new access(f.FullName, "");
local.taskList.Modify(args.action, $"{f.Name}-{tableName}: {countRow:#,0} processed ..");
if (string.IsNullOrEmpty(fromTable))
{
fromTable = tableName;
}
string tsql = $"select {col} from {fromTable}{(string.IsNullOrEmpty(where) ? "" : " where " + where)};";
var r = acc.reader(tsql);
try
{
if (r.HasRows == false)
{
r.Close(); acc.close(); return;
}
dt = new DataTable(tableName);
foreach (var item in cols)
{
dt.Columns.Add(item);
}
local.taskList.Modify(args.action, $"{f.Name}-{tableName}: {countRow:#,0} processed ..");
while (r.Read())
{
if (dt.Rows.Count >= args.packetSize)
{
dt.bulkCopy(local.connectionstring, tableName);
countRow += dt.Rows.Count;
dt = new DataTable(tableName);
foreach (var item in cols)
{
dt.Columns.Add(item);
}
local.taskList.Modify(args.action, $"{f.Name}-{tableName}: {countRow:#,0} processed ..");
}
DataRow row = dt.NewRow();
foreach (var item in cols)
{
row[item] = $"{r[item]}";
}
dt.Rows.Add(row);
}
if (dt.Rows.Count > 0)
{
dt.bulkCopy(local.connectionstring, tableName);
countRow += dt.Rows.Count;
dt = new DataTable(tableName);
local.taskList.Modify(args.action, $"{f.Name}-{tableName}: {countRow:#,0} processed ..");
}
r.Close();
acc.close();
}
catch (Exception ex)
{
try { r.Close(); } catch { }
acc.close();
msgError.Add($"{DateTime.Now:HH:mm:ss}-{f.Name}-{tableName}: " + ex.saveMessage());
local.taskList.Modify(args.action, $"{msgError[msgError.Count - 1]}");
}
}
public static object getConnection(this Models.SQLServerDataContext db) => db.Connection;
public static int databaseSize(this Models.SQLServerDataContext db) => db.DatabaseSize();
