public Tuple <int, string> GetConnectionString(string smallFileName, string targetConnectionString)
{
IDataParameter[] parameters =
{
GetParameter("@file_name", smallFileName)
};
using (IDbConnection conn = GetConnection())
{
conn.Open();
using (var dr = GetReader(conn, "sc_get_user_dsn", parameters, CommandType.StoredProcedure))
{
if (!dr.Read())
{
throw new Exception("target database not found for this file");
}
var databaseId = DataReaderExtensions.GetValue <int>(dr, "db_database_id");
var catalog = DataReaderExtensions.GetValue <string>(dr, "database_name");
var server = DataReaderExtensions.GetValue <string>(dr, "server_name");
var connStr = catalog + ";" + server;
var items = server.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
var dataSource = items.FirstOrDefault(x => x.Contains("Data Source="));
if (!string.IsNullOrEmpty(dataSource))
{
connStr = string.Format(targetConnectionString, dataSource, catalog);
}
return(new Tuple <int, string>(databaseId, connStr));
}
}
}
public string GetSetting(string settingName)
{
string settingValue = string.Empty;
try
{
IDataParameter[] parameters =
{
GetParameter("@setting_name", settingName)
};
using (IDbConnection conn = GetConnection())
{
conn.Open();
using (var rdr = GetReader(conn, "sc_get_db_setting", parameters, CommandType.StoredProcedure))
{
if (rdr.Read())
{
settingValue = DataReaderExtensions.GetValue <string>(rdr, "db_setting_value");
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(settingValue);
}
public IEnumerable <T> ExecuteIEnumerable <T>(IDbConnection conn, string commandText, CommandType commandType, params IDataParameter[] parameterValues) where T : class, new()
{
using (IDataReader dr = MySqlHelper.ExecuteReader((MySqlConnection)conn, commandType, commandText, parameterValues))
{
return(DataReaderExtensions.DataReaderToList <T>(dr));
}
}
public IEnumerable <T> ExecuteIEnumerable <T>(IDbTransaction trans, string commandText, CommandType commandType) where T : class, new()
{
using (IDataReader dr = MySqlHelper.ExecuteReader(trans, commandType, commandText))
{
return(DataReaderExtensions.DataReaderToList <T>(dr));
}
}
public IEnumerable <T> ExecuteIEnumerable <T>(IDbTransaction trans, string commandText, CommandType commandType, params IDataParameter[] parameterValues) where T : class, new()
{
using (IDataReader dr = SQLHelper.ExecuteReader(trans, commandType, commandText, parameterValues))
{
return(DataReaderExtensions.DataReaderToList <T>(dr));
}
}
public IEnumerable <T> ExecuteIEnumerable <T>(IDbConnection conn, string commandText, CommandType commandType) where T : class, new()
{
using (IDataReader dr = SQLHelper.ExecuteReader((SqlConnection)conn, commandType, commandText))
{
return(DataReaderExtensions.DataReaderToList <T>(dr));
}
}
/// <summary>
/// Get location by id.
/// </summary>
/// <param name="id">Used to identity the data to get.</param>
/// <returns>A <see cref="Location"/> if exists.</returns>
public async Task <Location> GetById(int id)
{
// Initalize temporary obj.
Location tempLocationObj = new Location();
// Initialize command obj.
using SqlCommand cmd = _databaseAccess.GetCommand("GetLocationById", CommandType.StoredProcedure);
// Define input parameters.
cmd.Parameters.AddWithValue("@id", id);
// Initialize data reader.
using SqlDataReader reader = await _databaseAccess.GetSqlDataReader();
// Check if reader has any data.
if (reader.HasRows)
{
// Read the data.
while (await reader.ReadAsync())
{
tempLocationObj.Identifier = reader.GetInt32("Id");
tempLocationObj.Name = reader.GetString("Name");
tempLocationObj.Description = !DataReaderExtensions.IsDBNull(reader, "@description") ? reader.GetString("Description") : string.Empty;
}
}
// Return data obj.
return(tempLocationObj);
}
public List <KeHoachCT> GetKeHoachCongTac(JQueryDataTableParamModel param)
{
const string storeName = "st_GetKetHoachCongTac";
string query = @"SELECT DATENAME(dw,TuNgay) as Ngay, [IdKeHoach]
,khct.TenKeHoach
,nd.HoTen
,nd.ThuTu
,khct.[UserName]
,TuNgay AS ThoiGian
,[KeHoachCongTac]
,[TuGio] +[TuNgay] AS TuNgayModel
,[DenGio]+[TuNgay] AS DenNgayModel
,[DiaDiem]
,TuGio
,DenGio
,DenNgay AS DenNgayView
,cv.VietTat
,cv.TenChucVu as ChucVu
,CASE MucDo
WHEN 1 THEN N'Rất quan trọng'
WHEN 2 THEN N'Quan trọng'
WHEN 3 THEN N'Bình thường'
END AS MucDoView
FROM [KeHoachCongTac] khct LEFT JOIN [dbo].[NguoiDung] nd
on khct.UserName=nd.UserName
LEFT JOIN ChucVu cv
on cv.IdChucVu=nd.ChucVu
" ;
@query = @query + " WHERE khct.IsDeleted=0 AND ((TuNgay BETWEEN '" + param.StartDate + "' AND '" + param.EndDate + "') OR "
+ "(DenNgay BETWEEN '" + param.StartDate + "' AND '" + param.EndDate + "')) ";
if (!string.IsNullOrEmpty(param.CanBo))
{
@query = @query + " AND khct.UserName IN (" + param.CanBo + ")";
}
if (!string.IsNullOrEmpty(param.sSearch))
{
param.sSearch = param.sSearch.Trim();
@query = @query + " AND ( khct.TenKeHoach LIKE N'%" + param.sSearch +
"%' OR khct.KeHoachCongTac LIKE N'%" + param.sSearch +
"%' OR khct.DiaDiem LIKE N'%" + param.sSearch
+ "%' OR nd.HoTen LIKE N'%" + param.sSearch
+ "%')";
}
@query = @query + " ORDER BY nd.ThuTu ASC ";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@Query", @query),
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <KeHoachCT>(reader));
}
}
/// <summary>
/// 执行sql语句,ExecuteReader 返回IDataReader
/// </summary>
/// <param name="connectionStringKey">数据库连接字符串的Key</param>
/// <param name="commandText">sql语句</param>
public IEnumerable <T> ExecuteIEnumerable <T>(string connKey, string commandText, CommandType commandType) where T : class, new()
{
using (MySqlConnection con = GetConnByKey(_ConnectionStringKey))
{
using (IDataReader dr = MySqlHelper.ExecuteReader(con, commandType, commandText))
{
return(DataReaderExtensions.DataReaderToList <T>(dr));
}
}
}
/// <summary>
/// 执行sql语句,ExecuteReader 返回IDataReader
/// </summary>
/// <param name="connectionStringKey">数据库连接字符串的Key</param>
/// <param name="commandText">sql语句</param>
/// <param name="parameterValues">参数</param>
public IEnumerable <T> ExecuteIEnumerable <T>(string connKey, string commandText, CommandType commandType, params IDataParameter[] parameterValues) where T : class, new()
{
using (SqlConnection con = GetConnByKey(_ConnectionStringKey))
{
using (IDataReader dr = SQLHelper.ExecuteReader(con, commandType, commandText, parameterValues))
{
return(DataReaderExtensions.DataReaderToList <T>(dr));
}
}
}
/// <summary>
/// 执行ＳＱＬ语句或者存储过程 ,返回IEnumerable<T>
/// </summary>
/// <typeparam name="T">返回类似</typeparam>
/// <param name="connKey">连接字符串Key</param>
/// <param name="commandText">ＳＱＬ语句或者存储过程名</param>
/// <param name="commandParameters">ＳＱＬ语句或者存储过程参数</param>
/// <param name="commandType">ＳＱＬ语句类型</param>
/// <param name="commandTimeout">超时时间</param>
/// <returns>IEnumerable</returns>
public IEnumerable <T> ExecuteIEnumerable <T>(string connKey, string commandText, List <IDataParameter> commandParameters = null, CommandType commandType = CommandType.Text, int?commandTimeout = null) where T : class, new()
{
using (MySqlConnection conn = GetConnByKey(connKey))
{
using (IDataReader dr = SQLHelper.ExecuteReader(conn, commandText, commandParameters, commandType, commandTimeout))
{
return(DataReaderExtensions.DataReaderToList <T>(dr));
}
}
}
public List <PhongBan> GetNguoiDungPhongBan()
{
const string storeName = "st_getNguoiDungPhongBan";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <PhongBan>(reader));
}
}
public List <KeHoachCT> GetLichCongTac()
{
const string storeName = "st_getKeHoach";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <KeHoachCT>(reader));
}
}
public void TestBasicIntMap()
{
var dt = new DataTable();
dt.Columns.Add(new DataColumn("TheInt", typeof(int)));
dt.Rows.Add(42);
var result = DataReaderExtensions.MapDataToEntities <TheTestClass>(new DataTableReader(dt));
Assert.AreEqual(result.Count, 1, "Should have one result");
Assert.AreEqual(result[0].TheInt, 42, "Should match the int value from the datatable");
}
public List <Phong> GetPhongByUser(string user)
{
const string storeName = "st_GetDatPhongByUser";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@User", user)
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <Phong>(reader));
}
}
public List <ThamDuModel> GetThamDuByIdKeHoach(int id)
{
const string storeName = "st_getThamDuByIdKeHoach";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@IdKeHoach", id),
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <ThamDuModel>(reader));
}
}
public List <ChucVuModel> GetChucVuTheoPhongBan(int idPhongBan)
{
const string storeName = "st_getChucVuTheoPhongBan";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@IdPhongBan", idPhongBan)
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <ChucVuModel>(reader));
}
}
public void TestBasicStringMap()
{
var dt = new DataTable();
dt.Columns.Add(new DataColumn("TheString", typeof(string)));
dt.Rows.Add("string value");
var result = DataReaderExtensions.MapDataToEntities <TheTestClass>(new DataTableReader(dt));
Assert.AreEqual(result.Count, 1, "Should have one result");
Assert.AreEqual(result[0].TheString, "string value", "Should match the string value from the datatable");
}
public List <Phong> GetPhongHopTheoNgay(DateTime ngay, int id)
{
const string storeName = "st_getPhongTheoNgay";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@Ngay", ngay),
new SqlParameter("@Id", id),
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <Phong>(reader));
}
}
public List <User> CheckLogin(User user)
{
const string storeName = "st_CheckLogin";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@UserName", user.UserName),
new SqlParameter("@Password", user.Password)
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <User>(reader));
}
}
public List <KeHoachCT> GetThamDuAndChuTri(string userName, DateTime startDate, DateTime endDate)
{
const string storeName = "st_getThamDuAndChuTri";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@UserName", userName),
new SqlParameter("@StartDate", startDate),
new SqlParameter("@EndDate", endDate),
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <KeHoachCT>(reader));
}
}
public async Task <IEnumerable <User> > GetAll()
{
try
{
// Initialize command obj.
using SqlCommand c = _databaseAccess.GetCommand("GetAllUsers", CommandType.StoredProcedure);
// Initialzie data reader.
using SqlDataReader r = await _databaseAccess.GetSqlDataReader();
// Temporary list.
List <User> tempUsers = new List <User>();
// Check for any data.
if (r.HasRows)
{
// Read data.
while (await r.ReadAsync())
{
tempUsers.Add(
new User
{
Identifier = r.GetInt32("Id"),
FirstName = r.GetString("FirstName"),
LastName = r.GetString("LastName"),
Email = r.GetString("Email"),
ConsultantArea = new ConsultantArea()
{
Name = r.GetString("ConsultantAreaName")
},
Location = new Location()
{
Name = r.GetString("LocationName"),
Description = !DataReaderExtensions.IsDBNull(r, "LocationDesc") ? r.GetString("LocationDesc") : string.Empty
}
});
}
}
// Return data list.
return(tempUsers);
}
finally
{
_databaseAccess.CloseConnection();
}
}
/// <summary>
/// 执行sql语句,ExecuteReader 返回IDataReader
/// </summary>
/// <param name="commandText">sql语句</param>
/// <param name="parameterValues">参数</param>
public IEnumerable <T> ExecuteIEnumerable <T>(string commandText, CommandType commandType, params IDataParameter[] parameterValues) where T : class, new()
{
if (null != PangXieKX.Plathform.DB.Transactions.Transaction.Current)
{
return(ExecuteIEnumerable <T>(Transaction.Current.DbTransactionWrapper.DbTransaction, commandText, commandType, parameterValues));
}
else
{
using (MySqlConnection con = GetConnByKey(_ConnectionStringKey))
{
using (IDataReader dr = MySqlHelper.ExecuteReader(con, commandType, commandText, parameterValues))
{
return(DataReaderExtensions.DataReaderToList <T>(dr));
}
}
}
}
public List <User> GetListNguoiDung(JQueryDataTableParamModel param)
{
const string query = @"st_getNguoiDung";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@DisplayLength", param.iDisplayLength),
new SqlParameter("@DisplayStart", param.iDisplayStart),
new SqlParameter("@SortCol", param.ISortCol_0),
new SqlParameter("@SortDir", param.SSortDir_0),
new SqlParameter("@Search", param.sSearch),
};
SqlDataReader reader = conn.ExecDataReaderProc(query, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <User>(reader));
}
}
public bool IsOrchestrationEnabled(Constants.EntityType type)
{
bool result = false;
IDataParameter[] parameters =
{
GetParameter("@entity_type_id", type)
};
using (IDbConnection conn = GetConnection())
{
conn.Open();
using (var dr = GetReader(conn, "sc_get_orchestration_lookup", parameters, CommandType.StoredProcedure))
{
while (dr.Read())
{
result = DataReaderExtensions.IsDBNull(dr, "is_publish") ? false : DataReaderExtensions.GetValue <bool>(dr, "is_publish");
}
}
}
return(result);
}
public List <KeHoachCT> GetKeHoachCaNhan(JQueryDataTableParamModel param)
{
const string storeName = "st_getKeHoachCaNhan";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@DisplayLength", param.iDisplayLength),
new SqlParameter("@DisplayStart", param.iDisplayStart),
new SqlParameter("@SortCol", param.ISortCol_0),
new SqlParameter("@SortDir", param.SSortDir_0),
new SqlParameter("@Search", param.sSearch),
new SqlParameter("@UserName", param.CanBo),
new SqlParameter("@StartDate", param.StartDate),
new SqlParameter("@EndDate", param.EndDate),
new SqlParameter("@Type", param.Type)
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <KeHoachCT>(reader));
}
}
/// <summary>
/// Get all locations.
/// </summary>
/// <returns>A list of <see cref="Location"/>.</returns>
public async Task <IEnumerable <Location> > GetAll()
{
try
{
// Initialize temporary list.
List <Location> tempLocations = new List <Location>();
// Prepare command obj.
using SqlCommand cmd = _databaseAccess.GetCommand("GetAllLocations", CommandType.StoredProcedure);
// Initialize data reader.
using SqlDataReader reader = await _databaseAccess.GetSqlDataReader();
// Check if any data.
if (reader.HasRows)
{
// Read the data.
while (await reader.ReadAsync())
{
tempLocations.Add(
new Location()
{
Identifier = reader.GetInt32("Id"),
Name = reader.GetString("Name"),
Description = !DataReaderExtensions.IsDBNull(reader, "Description") ? reader.GetString("Description") : string.Empty
});
}
}
// Return data.
return(tempLocations);
}
finally
{
_databaseAccess.CloseConnection();
}
}
public IEnumerable <UpdatedEntityItem> GetUpdatedEntity(Tuple <int?, string> ids)
{
List <UpdatedEntityItem> resultList = new List <UpdatedEntityItem>();
IDataParameter[] parameters =
{
GetParameter("@product_id", ids.Item1),
GetParameter("@supplier_code", ids.Item2),
};
using (IDbConnection conn = GetConnection())
{
conn.Open();
using (var dr = GetReader(conn, "sc_import_get_affected_products", parameters, CommandType.StoredProcedure))
// todo create store procedure
{
while (dr.Read())
{
var entityId = DataReaderExtensions.IsDBNull(dr, "product_id") ? 0 : DataReaderExtensions.GetValue <int>(dr, "product_id");
var entityTypeId = DataReaderExtensions.IsDBNull(dr, "product_type_id") ? 0 : DataReaderExtensions.GetValue <int>(dr, "product_type_id");
var item = new UpdatedEntityItem
{
EntityId = entityId,
EntityTypeId =
entityTypeId == (int)Constants.ProductType.Ingredient
? Constants.EntityType.Ingredient
: Constants.EntityType.Dish,
MessageActionType = Constants.MessageActionType.StarChefEventsUpdated
};
resultList.Add(item);
}
return(resultList);
}
}
}
public Config GetImportInformation(string connStr, string smallFileName, string fileName)
{
IDataParameter[] parameters =
{
GetParameter("@sys_file_name", smallFileName)
};
using (IDbConnection conn = GetConnection())
{
conn.Open();
using (IDataReader dr = GetReader(conn, "sc_get_ingredient_import", parameters, CommandType.StoredProcedure))
{
if (dr.Read())
{
int importId = DataReaderExtensions.GetValue <int>(dr, "ingredient_import_id");
int userId = dr.GetInt32(1);
int uGroupId = dr.GetInt32(2);
string displayFileName = dr.GetString(3);
return(new Config(connStr, importId, userId, uGroupId, fileName, displayFileName));
}
}
}
throw new Exception("import details not found for this file");
}
/// <summary>
/// 执行ＳＱＬ语句或者存储过程 ,返回IEnumerable<T>
/// </summary>
/// <typeparam name="T">返回类似</typeparam>
/// <param name="trans">语句所在的事务</param>
/// <param name="commandText">ＳＱＬ语句或者存储过程名</param>
/// <param name="commandParameters">ＳＱＬ语句或者存储过程参数</param>
/// <param name="commandType">ＳＱＬ语句类型</param>
/// <param name="commandTimeout">超时时间</param>
/// <returns>IEnumerable</returns>
public IEnumerable <T> ExecuteIEnumerable <T>(IDbTransaction trans, string commandText, List <IDataParameter> commandParameters = null, CommandType commandType = CommandType.Text, int?commandTimeout = null) where T : class, new()
{
IDataReader dr = SQLHelper.ExecuteReader(trans, commandText, commandParameters, commandType, commandTimeout);
return(DataReaderExtensions.DataReaderToList <T>(dr));
}
