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> /// 执行SQL语句或者存储过程 ,返回IEnumerable<T> /// </summary> /// <typeparam name="T">返回类似</typeparam> /// <param name="connKey">连接字符串Key</param> /// <param name="commandText">SQL语句或者存储过程名</param> /// <param name="commandParameters">SQL语句或者存储过程参数</param> /// <param name="commandType">SQL语句类型</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> /// 执行SQL语句或者存储过程 ,返回IEnumerable<T> /// </summary> /// <typeparam name="T">返回类似</typeparam> /// <param name="trans">语句所在的事务</param> /// <param name="commandText">SQL语句或者存储过程名</param> /// <param name="commandParameters">SQL语句或者存储过程参数</param> /// <param name="commandType">SQL语句类型</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)); }