public List <Alert> GetAlerts(DateTime date, int userId)
{
const string query = @"
select
id,
kind,
status,
date,
late_days LateDays,
amount,
contract_code ContractCode,
client_name ClientName,
loan_officer LoanOfficer,
city,
address,
phone,
branch_name BranchName
from
dbo.Alerts(@date, @userId)
";
using (var connection = _connectionProvider.GetConnection())
{
return(connection.Query <Alert>(query, new { date, userId }).ToList());
}
}
public DataItem SingleQueryForSingleRecord(string connectionString)
{
using (var connection = _connectionProvider.GetConnection(connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText =
"SELECT [Id], [Name], [Value] FROM [Benchmark_Simple_Table] WHERE [Id] = 1";
using (var reader = command.ExecuteReader(CommandBehavior.SingleRow))
{
var idOrdinal = reader.GetOrdinal("Id");
var nameOrdinal = reader.GetOrdinal("Name");
var valueOrdinal = reader.GetOrdinal("Value");
while (reader.Read())
{
var id = reader.GetInt32(idOrdinal);
var name = reader.GetString(nameOrdinal);
var value = reader.GetInt32(valueOrdinal);
return(new DataItem {
Id = id, Name = name, Value = value
});
}
}
}
}
return(null);
}
public async Task <int> AddFollowAsync(Follow follow, bool removeBan)
{
var rowaffected = 0;
using (var conn = _connProvider.GetConnection())
{
var tran = conn.BeginTransaction();
try
{
rowaffected = await conn.ExecuteAsync("insert into Follows(SUID,TUID,IsMutual,FollowTime) values (@SUID,@TUID,@IsMutual,@FollowTime)", follow, tran);
if (follow.IsMutual)
{
await conn.ExecuteAsync("update Follows set IsMutual=1 where SUID=@SUID and TUID= @TUID",
new { SUID = follow.TUID, TUID = follow.SUID }, tran);
}
if (removeBan)
{
await conn.ExecuteAsync("delete from Bans where SUID=@SUID and TUID=@TUID",
new { SUID = follow.SUID, TUID = follow.TUID }, tran);
}
await conn.ExecuteAsync("update UserStatistics set Follows=Follows+1, Bans=Bans-@Ban where UID=@UID",
new { Ban = removeBan ? 1 : 0, UID = follow.SUID }, tran);
await conn.ExecuteAsync("update UserStatistics set Followers=Followers+1 where UID=@uid",
new { UID = follow.TUID }, tran);
await conn.ExecuteAsync(@"insert into Notices(UID,Title,Content,Type,State,TargetID, TargetInfo,CreateTime)
values(@UID,@Title, @Content, @Type, @State,@TargetId,@TargetInfo,@CreateTime)" ,
new
{
UID = follow.TUID,
Title = "æœ‰äººå–œæ¬¢ä½ ",
Content = string.Format("{0}å°†ä½ æ·»åŠ ä¸ºä»–å–œæ¬¢çš„äºº", follow.SUname),
Type = 1,
State = 0,
TargetId = follow.SUID,
TargetInfo = follow.SUname,
CreateTime = DateTime.Now
}, tran);
tran.Commit();
}
catch
{
try
{
tran.Rollback();
}
catch { }
throw;
}
}
return(rowaffected);
}
public async Task <int> AddReportAsync(Entities.Report r)
{
using (var conn = _connProvider.GetConnection())
{
return(await conn.ExecuteAsync(@"insert into Reports (UID,Type,TargetID,Reason,Status,ReportTime)
values(@UID,@Type,@TargetID,@Reason,@Status,@ReportTime)", r));
}
}
public Event GetEvent(Guid id)
{
using (var db = _connectionProvider.GetConnection())
{
var sql = "SELECT ID as Id, Name, Starts, Ends, Status, Location from Event " +
"WHERE Id = @Id";
var @event = db.Query <Event>(sql, new { Id = id }).First();
return(@event);
}
}
public async Task <ApplicationRole> FindByIdAsync(string roleId, CancellationToken cancellationToken)
{
cancellationToken.ThrowIfCancellationRequested();
using (var connection = connectionProvider.GetConnection())
{
await connection.OpenAsync(cancellationToken);
return(await connection.QuerySingleOrDefaultAsync <ApplicationRole>($@"SELECT * FROM [ApplicationRole]
WHERE [Id] = @{nameof(ApplicationRole.RoleId)}", new { roleId }));
}
}
public void SetEventStatus(Guid id, string status)
{
Enum.TryParse <Event.EventStatus>(status, out var statusEnum);
if (statusEnum == default(Event.EventStatus))
{
throw new FormatException("Not a valid status");
}
using (var db = _connectionProvider.GetConnection())
{
var sql = "Update Event SET Status = @NewStatus WHERE ID=@Id";
db.Query(sql, new { NewStatus = (int)statusEnum, Id = id });
}
}
public static IDbConnection GetConnection(this ISession session)
{
IConnectionProvider provider = (session.SessionFactory as NHibernate.Engine.ISessionFactoryImplementor).ConnectionProvider;
IDbConnection connection = provider.GetConnection();
return(connection);
}
/// <summary>
/// Execute the specified stored procedure with the given parameters and then converts
/// the results using the supplied delegate.
/// </summary>
/// <typeparam name="T2">The collection type to return.</typeparam>
/// <param name="converter">The delegate which converts the raw results.</param>
/// <param name="sp_name">The name of the stored procedure.</param>
/// <param name="parameters">Parameters for the stored procedure.</param>
/// <returns></returns>
public ICollection <T2> ExecuteStoredProcedure <T2>(Converter <IDataReader, T2> converter, string sp_name,
params Parameter[] parameters)
{
IConnectionProvider connectionProvider = ((ISessionFactoryImplementor)SessionFactory).ConnectionProvider;
IDbConnection connection = connectionProvider.GetConnection();
try
{
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = sp_name;
command.CommandType = CommandType.StoredProcedure;
RepositoryHelper <T> .CreateDbDataParameters(command, parameters);
IDataReader reader = command.ExecuteReader();
ICollection <T2> results = new List <T2>();
while (reader.Read())
{
results.Add(converter(reader));
}
reader.Close();
return(results);
}
}
finally
{
connectionProvider.CloseConnection(connection);
}
}
public int ExecuteStatement(string sql)
{
using (IConnectionProvider prov = ConnectionProviderFactory.NewConnectionProvider(cfg.Properties))
{
IDbConnection conn = prov.GetConnection();
try
{
using (IDbTransaction tran = conn.BeginTransaction())
using (IDbCommand comm = conn.CreateCommand())
{
comm.CommandText = sql;
comm.Transaction = tran;
comm.CommandType = CommandType.Text;
int result = comm.ExecuteNonQuery();
tran.Commit();
return(result);
}
}
finally
{
prov.CloseConnection(conn);
}
}
}
public PublisherService(
IConnectionProvider connectionProvider,
string exchange,
string queue,
string routingKey)
{
_model = connectionProvider.GetConnection().CreateModel();
_exchange = exchange;
_model.ExchangeDeclare(
exchange: exchange,
type: "direct",
durable: true,
autoDelete: false);
_model.QueueDeclare(
queue: queue,
durable: true,
exclusive: false,
autoDelete: false);
_model.QueueBind(
queue: queue,
exchange: exchange,
routingKey: routingKey);
_key = routingKey;
Log.Information("Connected to RabbitMQ");
}
public List <SearchResult> Search(string keywords)
{
const string query = @"
select
v.id
, 'Village Bank' [Type]
, t.active Active
, v.name
, d.name District
, t.city City
from
dbo.Villages v
left join
dbo.Tiers t on v.id = t.id
left join
dbo.Districts d on d.id = t.district_id
where
v.name like @keywords
order by
v.name
";
using (var connection = _connectionProvider.GetConnection())
{
keywords = "%" + keywords + "%";
return(connection.Query <SearchResult>(query, new { keywords }).ToList());
}
}
public void TestConnectionProvider()
{
IConnectionProvider connectionProvider = LocalServiceLocator.GetService <IConnectionProvider>();
IDbConnection conn = connectionProvider.GetConnection("CustomerDbContext");
Console.WriteLine(conn.ConnectionString);
}
public int ExecuteStatement(string sql)
{
if (cfg == null)
{
cfg = TestConfigurationHelper.GetDefaultConfiguration();
}
using (IConnectionProvider prov = ConnectionProviderFactory.NewConnectionProvider(cfg.Properties))
{
var conn = prov.GetConnection();
try
{
using (var tran = conn.BeginTransaction())
using (var comm = conn.CreateCommand())
{
comm.CommandText = sql;
comm.Transaction = tran;
comm.CommandType = CommandType.Text;
int result = comm.ExecuteNonQuery();
tran.Commit();
return(result);
}
}
finally
{
prov.CloseConnection(conn);
}
}
}
public async Task <string> logup(string username, string password)
{
var USER_AUTHORIZED = false;
User user = null;
//fetch clientinfo
Client client = new Client(username, password);
//open connection
IDbConnection db = con.GetConnection();
//retrieve userinfo
user = await upcr.GetUser(client);
if (user == null)
{
USER_AUTHORIZED = true;
if (await upcr.Create(client) == -1)
{
return("user created");
}
}
else
{
USER_AUTHORIZED = false;
}
if (USER_AUTHORIZED == true)
{
Console.WriteLine("token sent to user");
return(await token.createToken(user));
}
else
{
Console.WriteLine("SORRY!!!token cant be sent");
return("wrong token");
}
}
public IEnumerable <Answer> GetAnswersForPeriod(DateTime start, DateTime eSnd)
{
using (var connection = _connectionProvider.GetConnection())
{
return(connection.Query <Answer>("Select * FROM Answer"));
}
}
public async Task <List <Notice> > GetNoticesAsync(string userId, int pIndex, int pSize)
{
using (var conn = _connProvider.GetConnection())
{
var tmp = await conn.QueryAsync <Notice>(@"select ID,Title,Content,Type,CreateTime as SendTime,State,TargetID,TargetInfo
from Notices where UID=@UID Order by ID desc limit @Skip, @Take",
new
{
UID = userId,
Skip = (pIndex - 1) * pSize,
Take = pSize
});
return(tmp.AsList());
}
}
public static FluentConfiguration ExposeDbCommand(this FluentConfiguration fluentConfiguration, Action <IDbCommand, global::NHibernate.Cfg.Configuration> action)
{
fluentConfiguration.ExposeConfiguration(cfg =>
{
DbConnection connection = null;
IConnectionProvider provider = null;
IDbCommand command = null;
try
{
provider = GetConnectionProvider(cfg);
connection = provider.GetConnection();
command = connection.CreateCommand();
command.CommandType = CommandType.Text;
action(command, cfg);
}
finally
{
if (command != null)
{
command.Dispose();
}
if (connection != null)
{
provider.CloseConnection(connection);
provider.Dispose();
}
}
});
return(fluentConfiguration);
}
public ReplyToConsumer(
IConnectionProvider connectionProvider,
string exchange,
string queue,
string routingKey,
string exchangeType,
int timeToLive = 30000,
ushort prefetchSize = 10)
{
_connectionProvider = connectionProvider;
_exchange = exchange;
_queue = queue;
_model = _connectionProvider.GetConnection().CreateModel();
var ttl = new Dictionary <string, object>
{
{ "x-message-ttl", timeToLive }
};
_model.ExchangeDeclare(_exchange, exchangeType, arguments: ttl);
_model.QueueDeclare(_queue,
durable: true,
exclusive: false,
autoDelete: false,
arguments: null);
_model.QueueBind(_queue, _exchange, routingKey);
_model.BasicQos(0, prefetchSize, false);
}
private void InitConnectionAndExecute(Action <string> scriptAction, bool execute, bool justDrop, DbConnection connection, TextWriter exportOutput)
{
Initialize();
TextWriter fileOutput = exportOutput;
IConnectionProvider connectionProvider = null;
try
{
if (fileOutput == null && outputFile != null)
{
fileOutput = new StreamWriter(outputFile);
}
if (execute && connection == null)
{
if (_requireTenantConnection)
{
throw new ArgumentException("When Database multi-tenancy is enabled you need to provide explicit connection. Please use overload with connection parameter.");
}
var props = new Dictionary <string, string>();
foreach (var de in dialect.DefaultProperties)
{
props[de.Key] = de.Value;
}
if (configProperties != null)
{
foreach (var de in configProperties)
{
props[de.Key] = de.Value;
}
}
connectionProvider = ConnectionProviderFactory.NewConnectionProvider(props);
connection = connectionProvider.GetConnection();
}
Execute(scriptAction, execute, justDrop, connection, fileOutput);
}
catch (HibernateException)
{
// So that we don't wrap HibernateExceptions in HibernateExceptions
throw;
}
catch (Exception e)
{
log.Error(e, e.Message);
throw new HibernateException(e.Message, e);
}
finally
{
if (connectionProvider != null)
{
connectionProvider.CloseConnection(connection);
connectionProvider.Dispose();
}
}
}
public DbConnection GetConnection()
{
var connection = _connectionProvider.GetConnection();
return(_externalStorageConnectionProvider == null
? connection
: new HybridStorageConnection(connection, _externalStorageConnectionProvider.GetProvider()));
}
public async Task <bool> CheckOtp(string otp, string username)
{
Client c = new Client(username, "none");
IDbConnection db = con.GetConnection();
User u = new User();
string otpstring = await upcr.GetOtp(u);
if (otpstring == otp)
{
Console.WriteLine("otp confirmed");
return(true);
}
else
{
return(false);
}
}
public List <StatisticsViaDay> GetDayStatistics()
{
using (var conn = _connProvider.GetConnection())
{
var tmp = conn.Query <StatisticsViaDay>("select ID as Day,Mottos,TotalScore from T_DayStatistics");
return(tmp.AsList());
}
}
public IDisposable BindConnectBroadcasts()
{
return(_broadcastService.Listen <ConnectionBroadcast>(
b => b.Kind == ConnectionBroadcastKind.Connect)
.SelectMany(b => _connectionProvider.GetConnection(b.Id))
.ObserveOn(RxApp.MainThreadScheduler)
.SubscribeWithLog(connectionInfo =>
{
// add node
_connectionNodeBuilder.HandleAction(
new ConnectionNodeAction.Add(connectionInfo.Id, connectionInfo));
// activate node
_model.SelectedItem = _model.Nodes.Items
.OfType <ConnectionNodeModel>()
.FirstOrDefault(node => node.ConnectionInfo == connectionInfo);
}));
}
public void Execute(Action <string> scriptAction, bool export, bool justDrop)
{
Initialize();
IDbConnection connection = null;
StreamWriter fileOutput = null;
IConnectionProvider connectionProvider = null;
var props = new Dictionary <string, string>();
foreach (var de in dialect.DefaultProperties)
{
props[de.Key] = de.Value;
}
if (configProperties != null)
{
foreach (var de in configProperties)
{
props[de.Key] = de.Value;
}
}
try
{
if (outputFile != null)
{
fileOutput = new StreamWriter(outputFile);
}
if (export)
{
connectionProvider = ConnectionProviderFactory.NewConnectionProvider(props);
connection = connectionProvider.GetConnection();
}
Execute(scriptAction, export, justDrop, connection, fileOutput);
}
catch (HibernateException)
{
// So that we don't wrap HibernateExceptions in HibernateExceptions
throw;
}
catch (Exception e)
{
log.Error(e.Message, e);
throw new HibernateException(e.Message, e);
}
finally
{
if (connection != null)
{
connectionProvider.CloseConnection(connection);
connectionProvider.Dispose();
}
}
}
public CommandActionDbTransactionContext(IConnectionProvider provider, bool readOnly)
{
if (provider == null)
{
throw new ArgumentNullException("provider");
}
IsReadOnly = readOnly;
lazyConnection = new Lazy <IDbConnection>(() => provider.GetConnection());
lazyTransaction = new Lazy <IDbTransaction>(() => Connection.BeginTransaction());
}
public async Task <IEnumerable <TopicDto> > GetAsync(string userId)
{
using (IDbConnection connection = _connectionProvider.GetConnection())
{
int?topicId = null;
IEnumerable <TopicDto> topics = await connection.QueryAsync <TopicDto>(GET_TOPICS, new { topicId });
IEnumerable <AttemptDto> attempts = await connection.QueryAsync <AttemptDto>(GET_ATTEMPTS_FOR_USER, new { userId, topicId });
ILookup <int, AttemptDto> attemptLookup = attempts.ToLookup(x => x.TopicId);
foreach (TopicDto topic in topics)
{
topic.Attempts = attemptLookup[topic.Id];
}
return(topics);
}
}
private Dictionary <int, string> GetDictionary()
{
var connection = _connectionProvider.GetConnection();
var dbContext = new DBConnector(connection);
return(dbContext.GetRealData(Query)
.AsEnumerable()
.Select(row => Converter.ToModelFromDataRow <StatusModel>(row))
.ToDictionary(s => s.StatusId, s => s.StatusName));
}
public T GetById(object id)
{
using (var connection = connectionProvider.GetConnection())
{
connection.Open();
return(connection.Get <T>(id));
}
}
public CustomerCoreCacheItemWatcher(InvalidationMode mode,
TimeSpan pollPeriod,
IContentInvalidator invalidator,
IConnectionProvider connectionProvider,
ILogger logger,
int dueTime = 0,
bool useTimer = true,
DatabaseType databaseType = DatabaseType.SqlServer)
: base(mode, pollPeriod, invalidator, connectionProvider?.GetConnection(), logger, dueTime, useTimer, null, databaseType.ToString())
{
}
public void Prepare()
{
connectionProvider = ConnectionProviderFactory.NewConnectionProvider(cfgProperties);
connection = connectionProvider.GetConnection();
}
