static void Main(string[] args) { try { DapperConfiguration.Initialize(); ConnectionProvider provider = new ConnectionProvider(); UserAccountDapper dapper = new UserAccountDapper(); // UserAccountBroker userDataBroker = new UserAccountBroker(provider); //UserAccount user = userDataBroker.GetUserAccount("florian", "pwd"); UserAccount user = null; IEnumerable <UserAccount> accounts = dapper.GetAllContact(provider.GetConnection(), 1); user = dapper.GetUserAccount(provider.GetConnection(), "florian", "pwd"); if (user != null) { Console.WriteLine(user.Firstname); Console.WriteLine(user.Lastname); } else { Console.WriteLine("Not found"); } } catch (Exception e) { Console.WriteLine(e.Message.ToString()); } Console.Read(); }
public void FirstConnectionNoyOpenUsesSecondAndWorksTwice() { var d1 = new DummyConnectionFactoryWrapper(false); var d2 = new DummyConnectionFactoryWrapper(true); var cp = new ConnectionProvider(d2, d1); var connection = cp.GetConnection(); Assert.NotNull(connection); var connection2 = cp.GetConnection(); Assert.NotNull(connection2); Assert.Equal(1, d1.NumberOfTimesCalled); }
public void FirstThrowsSocketUsesSecondAndWorksTwice() { var d1 = new DummyConnectionFactoryWrapper(new SocketException(123)); var d2 = new DummyConnectionFactoryWrapper(true); var cp = new ConnectionProvider(d2, d1); var connection = cp.GetConnection(); Assert.NotNull(connection); var connection2 = cp.GetConnection(); Assert.NotNull(connection2); }
public override void Open() { if (_connectionString == null || _connectionString.Length == 0) { throw ExceptionHelper.ConnectionStringNotInitialized(); } IsConnecting = true; try { if (JdbcConnection != null && !JdbcConnection.isClosed()) { throw ExceptionHelper.ConnectionAlreadyOpen(_internalState); } JdbcConnection = ConnectionProvider.GetConnection(ConnectionStringBuilder); IsOpened = true; OnStateChange(new StateChangeEventArgs(ConnectionState.Closed, ConnectionState.Open)); } catch (SQLWarning warning) { OnSqlWarning(warning); } catch (SQLException exp) { OnSqlException(exp); } finally { IsConnecting = false; } }
public int CreateStock(Stock stockModel) { using (SqlConnection connGetDistrict = ConnectionProvider.GetConnection()) { try { SqlCommand cmdDistrict = new SqlCommand("SP_Stock", connGetDistrict); cmdDistrict.CommandType = CommandType.StoredProcedure; cmdDistrict.CommandTimeout = 250; cmdDistrict.Parameters.Add("@flag", SqlDbType.Char).Value = "CreateStock"; cmdDistrict.Parameters.Add("@LOCATION_ID", SqlDbType.Int).Value = stockModel.Location_Id; cmdDistrict.Parameters.Add("@ITEM_ID", SqlDbType.Int).Value = stockModel.Item_Id; cmdDistrict.Parameters.Add("@UOM_ID", SqlDbType.Int).Value = stockModel.Base_Uom_Id; cmdDistrict.Parameters.Add("@QUANTITY", SqlDbType.Decimal).Value = stockModel.Stock_Quantity; int result = cmdDistrict.ExecuteNonQuery(); return(result); } catch (Exception ex) { return(0); } finally { if (connGetDistrict.State == ConnectionState.Open) { connGetDistrict.Close(); } } } }
public List <HelpMasterEntity> Select(HelpMasterEntity t) { using (var con = ConnectionProvider.GetConnection()) { var result = from record in con.Table <HelpMasterEntity>() select record; if (t.GameId != -1) { result = result.Where(data => data.GameId == t.GameId); } if (t.HelpIndex != -1) { result = result.Where(data => data.HelpIndex == t.HelpIndex); } if (t.Explain != null) { result = result.Where(data => data.Explain.Equals(t.Explain)); } if (t.Image != null) { result = result.Where(data => data.Image.Equals(t.Image)); } var helpMasterEntities = result as IList <HelpMasterEntity> ?? result.ToList(); return(helpMasterEntities.Count() != 0 ? helpMasterEntities.ToList() : null); } }
public List <DetectiveGameMasterEntity> Select(DetectiveGameMasterEntity t) { using (var con = ConnectionProvider.GetConnection()) { var result = from record in con.Table <DetectiveGameMasterEntity>() select record; if (t.Level != -1) { result = result.Where(data => data.Level == t.Level); } if (t.Point != -1) { result = result.Where(data => data.Point == t.Point); } if (t.Tile != -1) { result = result.Where(data => data.Tile == t.Tile); } if (t.CollectTile != -1) { result = result.Where(data => data.CollectTile == t.CollectTile); } if (t.FakeFlg != null) { result = result.Where(data => data.FakeFlg == t.FakeFlg); } if (t.FakeTile != -1) { result = result.Where(data => data.FakeTile == t.FakeTile); } var detectiveGameMasterEntities = result as IList <DetectiveGameMasterEntity> ?? result.ToList(); return(detectiveGameMasterEntities.Count() != 0 ? detectiveGameMasterEntities.ToList() : null); } }
public string CreateLocation(Location location) { using (SqlConnection connGetDistrict = ConnectionProvider.GetConnection()) { try { SqlCommand cmdDistrict = new SqlCommand("SP_Location", connGetDistrict); cmdDistrict.CommandType = CommandType.StoredProcedure; cmdDistrict.CommandTimeout = 250; cmdDistrict.Parameters.Add("@flag", SqlDbType.Char).Value = "CreateLocation"; cmdDistrict.Parameters.Add("@LOCATION_DESC", SqlDbType.Char).Value = location.Location_Description; cmdDistrict.Parameters.Add("@DEPARTMENT_ID", SqlDbType.Int).Value = location.Department_id; cmdDistrict.Parameters.Add("@ISACTIVE", SqlDbType.Char).Value = location.Is_Active ? "1" : "0"; SqlDataAdapter da = new SqlDataAdapter(cmdDistrict); DataTable dt = new DataTable(); da.Fill(dt); string result = dt.Rows[0]["Result"].ToString(); return(result); } catch (Exception ex) { return(null); } finally { if (connGetDistrict.State == ConnectionState.Open) { connGetDistrict.Close(); } } } }
public int BuscarCount(string intervalo) { if (String.IsNullOrWhiteSpace(intervalo)) { intervalo = String.Empty; } var SQL = new StringBuilder(); SQL.Append("SELECT COUNT(id) AS COUNT "); SQL.Append("FROM genders "); SQL.Append("WHERE UPPER(description) LIKE '%'||@intervalo||'%' "); using (var cnx = ConnectionProvider.GetConnection()) { var cmd = cnx.CreateCommand(); cmd.CommandText = SQL.ToString(); cmd.Parameters.AddWithValue("@intervalo", intervalo.ToUpper()); using (var reader = cmd.ExecuteReader()) { if (reader.Read()) { return(reader.GetInt("COUNT")); } } return(0); } }
public int CreateItem(Items itemsModel) { using (SqlConnection connGetDistrict = ConnectionProvider.GetConnection()) { try { SqlCommand cmdDistrict = new SqlCommand("SP_Item", connGetDistrict); cmdDistrict.CommandType = CommandType.StoredProcedure; cmdDistrict.CommandTimeout = 250; cmdDistrict.Parameters.Add("@flag", SqlDbType.Char).Value = "CreateItem"; cmdDistrict.Parameters.Add("@ITEM_DESC", SqlDbType.NVarChar).Value = itemsModel.Item_Description; cmdDistrict.Parameters.Add("@UOM_ID", SqlDbType.Int).Value = itemsModel.Uom_Id; cmdDistrict.Parameters.Add("@QUANTITY", SqlDbType.Decimal).Value = itemsModel.Quantity; cmdDistrict.Parameters.Add("@OutID", SqlDbType.Int).Direction = ParameterDirection.Output; cmdDistrict.ExecuteNonQuery(); int ID = Convert.ToInt32(cmdDistrict.Parameters["@OutID"].Value); return(ID); } catch (Exception ex) { return(0); } finally { if (connGetDistrict.State == ConnectionState.Open) { connGetDistrict.Close(); } } } }
public static void InitDataBase() { var dbPath = GetLocalFilePath("brainchallenge.db3"); ConnectionProvider.DbPath = dbPath; using (var con = ConnectionProvider.GetConnection()) { try { //テーブルを削除 con.DropTable <GameTypeMasterEntity>(); con.DropTable <GameMasterEntity>(); con.DropTable <HelpMasterEntity>(); con.DropTable <ScoreEntity>(); con.DropTable <SampleEntity>(); } catch (Exception e) { Console.WriteLine(e); } //テーブルを作成 con.CreateTable <GameTypeMasterEntity>(); con.CreateTable <GameMasterEntity>(); con.CreateTable <HelpMasterEntity>(); con.CreateTable <ScoreEntity>(); con.CreateTable <SampleEntity>(); } LoadingData(); }
public DataTable GetAllRequestDetailById(int RequestId) { using (SqlConnection connGetDistrict = ConnectionProvider.GetConnection()) { try { SqlCommand cmdDistrict = new SqlCommand("SP_Request", connGetDistrict); cmdDistrict.CommandType = CommandType.StoredProcedure; cmdDistrict.CommandTimeout = 250; cmdDistrict.Parameters.Add("@flag", SqlDbType.Char).Value = "GetReqDtlById"; cmdDistrict.Parameters.Add("@REQUEST_HDR_ID", SqlDbType.Int).Value = RequestId; cmdDistrict.Parameters.Add("@OutID", SqlDbType.Int).Direction = ParameterDirection.Output; SqlDataAdapter da = new SqlDataAdapter(cmdDistrict); DataTable dt = new DataTable(); da.Fill(dt); return(dt); } catch (Exception ex) { return(null); } finally { if (connGetDistrict.State == ConnectionState.Open) { connGetDistrict.Close(); } } } }
public static DataTable insert(string tCLAXX, string tCLCOD) { using (SqlConnection connGetDistrict = ConnectionProvider.GetConnection()) { try { SqlCommand cmdDistrict = new SqlCommand("InsertProductClassMastercustom", connGetDistrict); cmdDistrict.CommandType = CommandType.StoredProcedure; cmdDistrict.CommandTimeout = 250; //cmdDistrict.Parameters.Add("@flag", SqlDbType.Char).Value = "UpdateData"; cmdDistrict.Parameters.Add("@Code", SqlDbType.NVarChar).Value = tCLCOD; cmdDistrict.Parameters.Add("@Description", SqlDbType.NVarChar).Value = tCLAXX; SqlDataAdapter da = new SqlDataAdapter(cmdDistrict); DataTable dt = new DataTable(); da.Fill(dt); return(dt); } catch (Exception ex) { return(null); } finally { if (connGetDistrict.State == ConnectionState.Open) { connGetDistrict.Close(); } } } }
public int CreateUsers(Users users) { using (SqlConnection connGetDistrict = ConnectionProvider.GetConnection()) { try { SqlCommand cmdDistrict = new SqlCommand("SP_Users", connGetDistrict); cmdDistrict.CommandType = CommandType.StoredProcedure; cmdDistrict.CommandTimeout = 250; cmdDistrict.Parameters.Add("@flag", SqlDbType.Char).Value = "CreateUsers"; cmdDistrict.Parameters.Add("@COMPANY_ID", SqlDbType.Int).Value = users.Company_Id; cmdDistrict.Parameters.Add("@USER_NAME", SqlDbType.NVarChar).Value = users.User_Name; cmdDistrict.Parameters.Add("@USER_PASSWORD", SqlDbType.NVarChar).Value = users.User_Password; cmdDistrict.Parameters.Add("@NAME", SqlDbType.NVarChar).Value = users.Name; cmdDistrict.Parameters.Add("@DEPARTMENT_ID", SqlDbType.Int).Value = users.Department_Id; cmdDistrict.Parameters.Add("@ROLE_ID", SqlDbType.Int).Value = users.Role_Id; cmdDistrict.Parameters.Add("@ISACTIVE", SqlDbType.Char).Value = users.Is_Active ? "1" : "0"; int result = cmdDistrict.ExecuteNonQuery(); return(result); } catch (Exception ex) { return(0); } finally { if (connGetDistrict.State == ConnectionState.Open) { connGetDistrict.Close(); } } } }
internal async Task <Dictionary <string, BadgeDefinition> > GetBadges() { Dictionary <string, BadgeDefinition> badges = new Dictionary <string, BadgeDefinition>(); using (IAsyncDbClient client = ConnectionProvider.GetConnection()) { using (IQuery query = client.CreateQuery("SELECT * FROM `badge_definitions`;")) { using (var reader = await query.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { string code = reader.GetData <string>("code").ToUpper(); if (!badges.ContainsKey(code)) { badges.Add(code, new BadgeDefinition(code, reader.GetData <string>("required_right"))); } } } } } return(badges); }
public IEnumerable BuscarAll(string intervalo, int pg) { if (String.IsNullOrEmpty(intervalo)) { intervalo = string.Empty; } var SQL = new StringBuilder(); SQL.Append("SELECT id, description "); SQL.Append("FROM genders "); SQL.Append("WHERE UPPER(description) LIKE '%'||@intervalo||'%' "); if (pg > -1) { SQL.Append($"OFFSET {10 * (pg -1)} "); SQL.Append($"LIMIT {10}"); } using (var cnx = ConnectionProvider.GetConnection()) { var cmd = cnx.CreateCommand(); cmd.CommandText = SQL.ToString(); cmd.Parameters.AddWithValue("@intervalo", intervalo.ToUpper()); var retorno = new List <Gender>(); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { var item = BuscarModel(reader); retorno.Add(item); } } return(retorno); } }
/// <inheritdoc /> protected override async Task <IEnumerable <QueuedMessage> > GetPendingMessages(CancellationToken cancellationToken = default(CancellationToken)) { var queuedMessages = new List <QueuedMessage>(); var connection = ConnectionProvider.GetConnection(); try { var commandBuilder = CommandBuilders.NewSelectPendingMessagesCommandBuilder(); commandBuilder.QueueName = QueueName; using (var scope = new TransactionScope(TransactionScopeOption.Required, TransactionScopeAsyncFlowOption.Enabled)) { using (var command = commandBuilder.BuildDbCommand(connection)) { using (var reader = await command.ExecuteReaderAsync(cancellationToken)) { while (await reader.ReadAsync(cancellationToken)) { try { var record = commandBuilder.BuildQueuedMessageRecord(reader); var messageContent = record.Content; var headers = DeserializeHeaders(record.Headers); var message = new Message(headers, messageContent); if (message.IsEncrypted() && MessageEncryptionService != null) { message = await MessageEncryptionService.Decrypt(message); } #pragma warning disable 612 var principal = await ResolvePrincipal(headers, record.SenderPrincipal); #pragma warning restore 612 message = message.WithoutSecurityToken(); var attempts = record.Attempts; var queuedMessage = new QueuedMessage(message, principal, attempts); queuedMessages.Add(queuedMessage); } catch (Exception ex) { DiagnosticService.Emit(new SQLEventBuilder(this, SQLEventType.SQLMessageRecordFormatError) { Detail = "Error reading previously queued message record; skipping", Exception = ex }.Build()); } } } } scope.Complete(); } } finally { ConnectionProvider.ReleaseConnection(connection); } // SQL calls are not async to avoid the need for TransactionAsyncFlowOption // and dependency on .NET 4.5.1 and later return(queuedMessages.AsEnumerable()); }
/// <summary> /// Updates a queued message in the database i.e. in response to an acknowledgement failure /// </summary> /// <param name="queuedMessage">The queued message to delete</param> /// <param name="abandoned">The date/time the message was abandoned (if applicable)</param> /// <param name="cancellationToken">(Optional) A cancellation token through which the /// caller can request cancellation of the update operation</param> /// <returns>Returns a task that completes when the update operation completes</returns> protected virtual async Task UpdateQueuedMessage(QueuedMessage queuedMessage, DateTime?abandoned, CancellationToken cancellationToken = default(CancellationToken)) { var connection = ConnectionProvider.GetConnection(); try { var message = queuedMessage.Message; var headers = message.Headers; var commandBuilder = CommandBuilders.NewUpdateQueuedMessageCommandBuilder(); commandBuilder.MessageId = headers.MessageId; commandBuilder.QueueName = QueueName; commandBuilder.Abandoned = abandoned; commandBuilder.Attempts = queuedMessage.Attempts; using (var scope = new TransactionScope(TransactionScopeOption.Required, TransactionScopeAsyncFlowOption.Enabled)) { using (var command = commandBuilder.BuildDbCommand(connection)) { await command.ExecuteNonQueryAsync(cancellationToken); } scope.Complete(); } } finally { ConnectionProvider.ReleaseConnection(connection); } }
public int DeleteLocation(int location_id) { using (SqlConnection connGetDistrict = ConnectionProvider.GetConnection()) { try { SqlCommand cmdDistrict = new SqlCommand("SP_Location", connGetDistrict); cmdDistrict.CommandType = CommandType.StoredProcedure; cmdDistrict.CommandTimeout = 250; cmdDistrict.Parameters.Add("@flag", SqlDbType.Char).Value = "DeleteLocation"; cmdDistrict.Parameters.Add("@LOCATION_ID", SqlDbType.Char).Value = location_id; int result = cmdDistrict.ExecuteNonQuery(); return(result); } catch (Exception ex) { return(0); } finally { if (connGetDistrict.State == ConnectionState.Open) { connGetDistrict.Close(); } } } }
/// <summary> /// Обновляет кэш отсутвующих /// </summary> /// <param name="query"></param> /// <param name="options"></param> /// <param name="connection"></param> protected long[] UpdateCache(string query, ObjectDataCacheHints options = null, IDbConnection connection = null) { lock (_nativeCache){ var allids = new List <long>(); bool cascade = true; if (null == connection) { if (string.IsNullOrWhiteSpace(ConnectionString)) { UpdateSingleQuery(query, options, null, allids, cascade); } else { //no self created using (var c = ConnectionProvider.GetConnection(ConnectionString)){ if (string.IsNullOrWhiteSpace(c.ConnectionString)) { throw new Exception("bad connection!"); } UpdateSingleQuery(query, options, c, allids, cascade); c.Close(); } } } else { UpdateSingleQuery(query, options, connection, allids, cascade); } return(allids.ToArray()); } }
private static void DbInitialize() { using (var con = ConnectionProvider.GetConnection()) { try { //テーブルを削除 con.DropTable <GameTypeMasterEntity>(); con.DropTable <GameMasterEntity>(); con.DropTable <HelpMasterEntity>(); con.DropTable <ScoreEntity>(); con.DropTable <DetectiveGameMasterEntity>(); con.DropTable <SampleEntity>(); } catch (Exception) { // ignored } //テーブルを作成 con.CreateTable <GameTypeMasterEntity>(); con.CreateTable <GameMasterEntity>(); con.CreateTable <HelpMasterEntity>(); con.CreateTable <ScoreEntity>(); con.CreateTable <DetectiveGameMasterEntity>(); con.CreateTable <SampleEntity>(); } LoadingData(); }
public int CreateRequest(MaterialRequest materialRequest) { using (SqlConnection connGetDistrict = ConnectionProvider.GetConnection()) { int Result = 0; try { SqlCommand cmdDistrict = new SqlCommand("SP_Request", connGetDistrict); cmdDistrict.CommandType = CommandType.StoredProcedure; cmdDistrict.CommandTimeout = 250; cmdDistrict.Parameters.Add("@flag", SqlDbType.Char).Value = "CreateRequestHdr"; cmdDistrict.Parameters.Add("@DATE", SqlDbType.Date).Value = materialRequest.Date; cmdDistrict.Parameters.Add("@TIME", SqlDbType.NVarChar).Value = materialRequest.Time; cmdDistrict.Parameters.Add("@REQUESTER_NAME", SqlDbType.NVarChar).Value = materialRequest.Requester_Name; cmdDistrict.Parameters.Add("@DEPARTMENT_ID", SqlDbType.Int).Value = materialRequest.Department_Id; cmdDistrict.Parameters.Add("@DESCRIPTION", SqlDbType.NVarChar).Value = materialRequest.Description; cmdDistrict.Parameters.Add("@COMMENTS", SqlDbType.NVarChar).Value = materialRequest.Comment; cmdDistrict.Parameters.Add("@OutID", SqlDbType.Int).Direction = ParameterDirection.Output; cmdDistrict.ExecuteNonQuery(); int ID = Convert.ToInt32(cmdDistrict.Parameters["@OutID"].Value); string ReqNumber = "MRI" + ID; cmdDistrict.Parameters.Clear(); cmdDistrict.Parameters.Add("@flag", SqlDbType.Char).Value = "UpdateReqNo"; cmdDistrict.Parameters.Add("@REQUEST_NO", SqlDbType.NVarChar).Value = ReqNumber; cmdDistrict.Parameters.Add("@REQUEST_HDR_ID", SqlDbType.Int).Value = ID; cmdDistrict.Parameters.Add("@OutID", SqlDbType.Int).Direction = ParameterDirection.Output; Result = cmdDistrict.ExecuteNonQuery(); if (Result > 0) { foreach (var Items in materialRequest.RequestItemsList) { cmdDistrict.Parameters.Clear(); cmdDistrict.Parameters.Add("@flag", SqlDbType.Char).Value = "CreateRequestDtl"; cmdDistrict.Parameters.Add("@REQUEST_HDR_ID", SqlDbType.Int).Value = ID; cmdDistrict.Parameters.Add("@ITEM_ID", SqlDbType.Int).Value = Items.Item_Id; cmdDistrict.Parameters.Add("@UOM_ID", SqlDbType.Int).Value = Items.Uom_Id; cmdDistrict.Parameters.Add("@TO_LOCATION", SqlDbType.Int).Value = Items.To_Location; cmdDistrict.Parameters.Add("@REQUEST_QUANTITY", SqlDbType.Decimal).Value = Items.Request_Quantity; cmdDistrict.Parameters.Add("@COMMENTS", SqlDbType.NVarChar).Value = Items.Comment; cmdDistrict.Parameters.Add("@OutID", SqlDbType.Int).Direction = ParameterDirection.Output; Result = cmdDistrict.ExecuteNonQuery(); } } return(Result); } catch (Exception ex) { return(0); } finally { if (connGetDistrict.State == ConnectionState.Open) { connGetDistrict.Close(); } } } }
public bool Insert(ScoreEntity t) { using (var con = ConnectionProvider.GetConnection()) { var result = con.Insert(t); return(result == 1); } }
public async Task <int?> InsertAsync(CustomerEntity customer) { using (var connection = ConnectionProvider.GetConnection(_configuration)) { var id = await connection.InsertAsync(customer); return(id); } }
public async Task <CustomerEntity> GetAsync(int id) { using (var connection = ConnectionProvider.GetConnection(_configuration)) { var customer = await connection.GetAsync <CustomerEntity>(id); return(customer); } }
public List <ScoreEntity> Select() { using (var con = ConnectionProvider.GetConnection()) { var result = from record in con.Table <ScoreEntity>() select record; var scoreEntities = result as IList <ScoreEntity> ?? result.ToList(); return(scoreEntities.Count() != 0 ? scoreEntities.ToList() : null); } }
public async Task <int> CountAsync(string conditions = "", object parameters = null) { using (var connection = ConnectionProvider.GetConnection(_configuration)) { var count = await connection.RecordCountAsync <CustomerEntity>(conditions, parameters); return(count); } }
public async Task <IEnumerable <CustomerEntity> > GetListAsync(int pageNumber, int rowsPerPage, string orderBy, string conditions = "", object parameters = null) { using (var connection = ConnectionProvider.GetConnection(_configuration)) { var customers = await connection.GetListPagedAsync <CustomerEntity>(pageNumber, rowsPerPage, conditions, orderBy, parameters); return(customers); } }
public List <DetectiveGameMasterEntity> Select() { using (var con = ConnectionProvider.GetConnection()) { var result = from record in con.Table <DetectiveGameMasterEntity>() select record; var detectiveGameMasterEntities = result as IList <DetectiveGameMasterEntity> ?? result.ToList(); return(detectiveGameMasterEntities.Count() != 0 ? detectiveGameMasterEntities.ToList() : null); } }
internal async Task ProgressAchievement(int userId, string group, int newLevel, int newProgress) { using (IAsyncDbClient client = ConnectionProvider.GetConnection()) { using (IQuery query = client.CreateQuery("REPLACE INTO `user_achievements` VALUES ('@0', @1, '@2', '@3')", userId, group, newLevel, newProgress)) { await query.ExecuteNonQueryAsync(); } } }