public Trans(string connectionString)
{
this.db = new DBHelper();
this.conn = this.db.CreateConnection(connectionString);
this.conn.Open();
this.dbTrans = this.conn.BeginTransaction();
}
/// <summary>
/// Method for generator override
/// </summary>
/// <param name="connection">Available database connection</param>
/// <param name="transaction">Available database transaction</param>
/// <param name="connectionString">Avaliable connection string</param>
/// <returns></returns>
protected void Save(System.Data.Common.DbConnection connection, System.Data.Common.DbTransaction transaction, string connectionString)
{
//MySqlContextExecutionWrapper wrapper = new MySqlContextExecutionWrapper();
//wrapper.Initialize(ref connection, ref transaction, connectionString);
//SaveImplementation(connection, transaction);
//wrapper.Cleanup(ref connection, ref transaction, connectionString);
}
/// <summary>
/// Ham nay de mo ket noi den database
/// </summary>
/// <param name="ServerName"></param>
/// <param name="DatabaseName"></param>
/// <param name="UserId"></param>
/// <param name="Password"></param>
/// <returns></returns>
public static bool DB_OpenConnection(string ServerName, string DatabaseName, string UserId, string Password)
{
DbProviderFactory = System.Data.SqlClient.SqlClientFactory.Instance;
DbConnectionStringBuilder DbConnectionStringBuilder = DbProviderFactory.CreateConnectionStringBuilder();
DbConnectionStringBuilder.Add("Data Source", ServerName);
DbConnectionStringBuilder.Add("User ID", UserId);
DbConnectionStringBuilder.Add("Password", Password);
DbConnectionStringBuilder.Add("Initial Catalog", DatabaseName);
bool IsConnected;
try
{
if (DbConnection == null)
{
DbConnection = DbProviderFactory.CreateConnection();
}
if (DbConnection.State != ConnectionState.Open)
{
DbConnection.ConnectionString = DbConnectionStringBuilder.ConnectionString;
DbConnection.Open();
}
IsConnected = true;
}
catch
{
IsConnected = false;
}
return(IsConnected);
}
public IDbConnection GetConnection(string connectionString, string provider)
{
System.Data.Common.DbProviderFactory factory = System.Data.Common.DbProviderFactories.GetFactory(provider);
System.Data.Common.DbConnection conn = factory.CreateConnection();
conn.ConnectionString = connectionString;
return(conn);
}
protected void Load(System.Data.Common.DbConnection connection, System.Data.Common.DbTransaction transaction, Guid identifier, string connectionString)
{
//MySqlContextExecutionWrapper wrapper = new MySqlContextExecutionWrapper();
//wrapper.Initialize(ref connection, ref transaction, connectionString);
//LoadImplementation(connection, transaction, identifier);
//wrapper.Cleanup(ref connection, ref transaction, connectionString);
}
private void SetDbParams(System.Data.Common.DbConnection conn)
{
if (conn.GetType().Name.ToLower().IndexOf("oracle") > -1 || conn.GetType().Name.ToLower().IndexOf("pgsql") > -1)
{
dbParamChar = ':';
}
}
/// <summary>
/// Wrapper method to determine if a connection
/// is opened to a read only database.
/// </summary>
/// <param name="connection">The open connection</param>
/// <returns>Bool indicating if the database is read only</returns>
public static bool IsReadOnly(this System.Data.Common.DbConnection connection)
{
bool ret = false;
IsDatabaseReadOnly(connection).ContinueWith(t => ret = t.Result);
return(ret);
}
public override void CloseCon(System.Data.Common.DbConnection myConnection)
{
if (myConnection.State == ConnectionState.Open)
{
return;
}
}
}//Execute(string sql)
/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="Conn">数据库连接对象</param>
/// <param name="param">参数</param>
/// <returns>返回受影响行数</returns>
static public int Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param)
{
if (Conn == null)
{
DBClassHelper.ErrLog("DBClassHelper.Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param):连接对象为空!");
return(0);
}
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandTimeout = 180;
cmd.CommandText = sql;
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
try
{
return(cmd.ExecuteNonQuery());
}
catch (Exception ex)
{
DBClassHelper.ErrLog("DBClassHelper.Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param):" + ex.Message + "/nsql=" + sql);
return(0);
}
}//Execute(string sql,System.Data.IDataParameter[] param)
}//ExecuteQueryToDataTable(string sql)
/// <summary>
/// 执行查询返回DataReader
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="Conn">连接对象</param>
/// <returns>成功时返回Reader对象，失败时返回null</returns>
static public System.Data.IDataReader ExecuteQuery(string sql, System.Data.Common.DbConnection Conn)
{
System.Data.IDataReader reader = null;
if (Conn == null)
{
return(null);
}
try
{
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandTimeout = 180;
reader = cmd.ExecuteReader();
return(reader);
}
catch (Exception ex)
{
if (ex.Message.Contains("死锁"))
{
WriteLog(ex.Message + " 再做列锁循环！ExecuteQuery");
System.Threading.Thread.Sleep(200);
return(ExecuteQuery(sql, Conn));
}
else
{
DBClassHelper.ErrLog("DBClassHelper.ExecuteQuery(string sql, System.Data.Common.DbConnection Conn):" + ex.Message + "; \n sql =" + sql);
}
return(null);
}
}//ExecuteQuery(string sql)
protected override void BatchInsertRows(System.Data.Common.DbConnection conn, IEnumerable <ReceiveLogEntity> rows)
{
DbCommand command = conn.CreateCommand();
command.CommandText = @"INSERT INTO ReceiveLog(ReceiveDate,ReceiveFile,MonitorIP,MonitorAlias,ReceiveState) VALUES(@ReceiveDate,@ReceiveFile,@MonitorIP,@MonitorAlias,@ReceiveState)";
DbParameter dateParam = new SQLiteParameter("@ReceiveDate", DbType.DateTime);
DbParameter fileParam = new SQLiteParameter("@ReceiveFile", DbType.String);
DbParameter ipParam = new SQLiteParameter("@MonitorIP", DbType.String, 30);
DbParameter dictParam = new SQLiteParameter("@MonitorAlias", DbType.String);
DbParameter stateParam = new SQLiteParameter("@ReceiveState", DbType.String, 10);
command.Parameters.Add(dateParam);
command.Parameters.Add(fileParam);
command.Parameters.Add(ipParam);
command.Parameters.Add(dictParam);
command.Parameters.Add(stateParam);
foreach (var r in rows)
{
dateParam.Value = r.ReceiveDate;
fileParam.Value = r.ReceiveFile;
ipParam.Value = r.MonitorIP;
dictParam.Value = r.MonitorAlias;
stateParam.Value = r.ReceiveState;
command.ExecuteNonQuery();
}
}
public SQLCODEC(string sql, int inputSize, int idealSize, string connectString)
{
while (true)
{
this._x7e648b416c264559 = inputSize;
this._x08b9e0820ab2b457 = idealSize;
do
{
this._x7316af229433e69e = new OleDbConnection(connectString);
this._x7316af229433e69e.Open();
this._xd1d7cdb50796b29b = this._x7316af229433e69e.CreateCommand();
if ((((uint) inputSize) - ((uint) inputSize)) <= uint.MaxValue)
{
this._xd1d7cdb50796b29b.CommandText = sql;
this._xd1d7cdb50796b29b.Prepare();
}
this._xd1d7cdb50796b29b.Connection = this._x7316af229433e69e;
if ((((uint) idealSize) - ((uint) idealSize)) >= 0)
{
return;
}
}
while ((((uint) idealSize) + ((uint) inputSize)) > uint.MaxValue);
}
}
/// <summary>
/// Returns a new <see cref="ProfilableDbConnection"/> that wraps <paramref name="connection"/>,
/// providing query execution profiling. If profiler is null, no profiling will occur.
/// </summary>
/// <param name="connection">Your provider-specific flavor of connection, e.g. SqlConnection, OracleConnection</param>
protected ProfilableDbConnection(DbConnection connection)
{
if (connection == null) throw new ArgumentNullException("connection");
_conn = connection;
_conn.StateChange += StateChangeHandler;
}
protected internal virtual IDbCommandWrap GetCommandWrap(IStatementSetting statement)
{
DbConnection connection;
var closeConnectionOnCommandDisposed = true;
if (this.IsInTransaction) {
if (this.transaction == null) {
lock (this.lockTransaction) {
if (this.transaction == null) {
connection = this.GetConnection(statement);
connection.Open();
if(this.DataService.Debug) {
LogService.WriteLog(this, LogLevel.DEBUG, "Connection open with transaction:" + connection.GetHashCode());
}
this.transaction = connection.BeginTransaction();
}
}
}
connection = this.Transaction.Connection;
closeConnectionOnCommandDisposed = false;
} else {
connection = this.GetConnection(statement);
connection.Open();
if(this.DataService.Debug) {
LogService.WriteLog(this, LogLevel.DEBUG, "Connection open without transaction:" + connection.GetHashCode());
}
}
var command = connection.CreateCommand();
command.Transaction = this.Transaction;
return new DbCommandWrap(command, closeConnectionOnCommandDisposed);
}
public DbConnection()
{
DbData Data = new DbData()
{
Host = "200.14.248.248,1440", //"10.0.0.12",
Engine = DbEngine.MsSQL,
//Name = "OssosiFtp_Wrk",
Name = "OssosiFtp",
User = "******",
Password = "******" //"abcDEF123"
};
DbName = Data.Host + "_" + Data.Name;
SaveDerivedParameters = false;
ReadPropertiesFromParameters = true;
switch (Data.Engine)
{
case DbEngine.MsSQL:
con = new SqlConnection();
conStr = $"Data Source={Data.Host};Initial Catalog={Data.Name};Persist Security Info=True;User ID={Data.User};Password={Data.Password}";
devPrm = (Command) => SqlCommandBuilder.DeriveParameters(Command as SqlCommand);
prpNamFrmPrm = (Parameter) => Parameter.ParameterName.Substring(1);
break;
}
OpenConnection();
}
public void Dispose()
{
this.Close();
if (this._DbConnection != null)
{
this._DbConnection.Dispose();
this._DbConnection = null;
}
if (this._DbCommand != null)
{
this._DbCommand.Dispose();
this._DbCommand = null;
}
if (this._DbAdapter != null)
{
this._DbAdapter.Dispose();
this._DbAdapter = null;
}
if (this._DbBuilder != null)
{
this._DbBuilder.Dispose();
this._DbBuilder = null;
}
base.Dispose();
}
/// <summary>
/// Initializes a new instance of the <see cref="TransactionManager"/> class.
/// </summary>
/// <param name="connectionString">The connection string to the database.</param>
public TransactionManager(string connectionString)
{
this.connectionString = connectionString;
this.database = new SqlDatabase(connectionString);
//this.database = DatabaseFactory.CreateDatabase(this.instanceName);
this.connection = this.database.CreateConnection();
}
public bool DeleteProducts(int actId, string ProductIds)
{
bool result = false;
using (System.Data.Common.DbConnection dbConnection = this.database.CreateConnection())
{
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(string.Format("Delete from Hishop_PointExChange_Products WHERE exChangeId ={0} and ProductId in ( {1} )", actId, ProductIds.ReplaceSingleQuoteMark()));
System.Data.Common.DbCommand sqlStringCommand2 = this.database.GetSqlStringCommand("update Hishop_PointExChange_PointExChanges set ProductNumber=ProductNumber-1 where Id=@Id");
this.database.AddInParameter(sqlStringCommand2, "Id", System.Data.DbType.Int32, actId);
try
{
this.database.ExecuteNonQuery(sqlStringCommand, dbTransaction);
this.database.ExecuteNonQuery(sqlStringCommand2, dbTransaction);
dbTransaction.Commit();
result = true;
}
catch
{
dbTransaction.Rollback();
result = false;
}
finally
{
if (dbTransaction.Connection != null)
{
dbConnection.Close();
}
}
}
return(result);
}
private static async Task <int> ExecuteImplAsync(IDbConnection cnn, CommandDefinition command, object param)
{
var identity = new Identity(command.CommandText, command.CommandType, cnn, null, param?.GetType(), null);
var info = GetCacheInfo(identity, param, command.AddToCache);
bool wasClosed = cnn.State == ConnectionState.Closed;
using (var cmd = (DbCommand)command.SetupCommand(cnn, info.ParamReader))
{
try
{
if (wasClosed)
{
await((DbConnection)cnn).OpenAsync(command.CancellationToken).ConfigureAwait(false);
}
var result = await cmd.ExecuteNonQueryAsync(command.CancellationToken).ConfigureAwait(false);
command.OnCompleted();
return(result);
}
finally
{
if (wasClosed)
{
cnn.Close();
}
}
}
}
public bool DeleteProduct(int exchangeId, int productId)
{
bool result = false;
using (System.Data.Common.DbConnection dbConnection = this.database.CreateConnection())
{
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("delete from Hishop_PointExChange_Products where [exChangeId]= @exChangeId and [ProductId] = @ProductId ");
this.database.AddInParameter(sqlStringCommand, "exChangeId", System.Data.DbType.Int32, exchangeId);
this.database.AddInParameter(sqlStringCommand, "ProductId", System.Data.DbType.Int32, productId);
System.Data.Common.DbCommand sqlStringCommand2 = this.database.GetSqlStringCommand("update Hishop_PointExChange_PointExChanges set ProductNumber=ProductNumber-1 where Id=@Id");
this.database.AddInParameter(sqlStringCommand2, "Id", System.Data.DbType.Int32, exchangeId);
try
{
this.database.ExecuteNonQuery(sqlStringCommand, dbTransaction);
this.database.ExecuteNonQuery(sqlStringCommand2, dbTransaction);
dbTransaction.Commit();
result = true;
}
catch
{
dbTransaction.Rollback();
result = false;
}
finally
{
if (dbTransaction.Connection != null)
{
dbConnection.Close();
}
}
}
return(result);
}
private static async Task <IDataReader> ExecuteReaderImplAsync(IDbConnection cnn, CommandDefinition command)
{
Action <IDbCommand, object> paramReader = GetParameterReader(cnn, ref command);
DbCommand cmd = null;
bool wasClosed = cnn.State == ConnectionState.Closed;
try
{
cmd = (DbCommand)command.SetupCommand(cnn, paramReader);
if (wasClosed)
{
await((DbConnection)cnn).OpenAsync(command.CancellationToken).ConfigureAwait(false);
}
var reader = await cmd.ExecuteReaderAsync(wasClosed?CommandBehavior.CloseConnection | CommandBehavior.SequentialAccess : CommandBehavior.SequentialAccess, command.CancellationToken).ConfigureAwait(false);
wasClosed = false;
return(reader);
}
finally
{
if (wasClosed)
{
cnn.Close();
}
cmd?.Dispose();
}
}
protected static void Exec(DbConnection conn, string sql)
{
using(var cmd = conn.CreateCommand()) {
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
public RestaurantType Get(int id)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
return _connection.Get<RestaurantType>(id);
}
}
public override DbCommand CreateCommand(DbConnection conn)
{
var cmd = conn.CreateCommand();
var pi = cmd.GetType().GetProperty("BindByName");
if (pi != null) pi.SetValue(cmd, true, null);
return cmd;
}
public string[] ForSpecific(DbConnection connection,
string collectionName,
string value,
string restrictionName)
{
return GetSchemaRestrictions(connection, collectionName, value, restrictionName);
}
/// <summary>
///
/// </summary>
/// <param name="criteria"></param>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <returns></returns>
public MembershipEntity Find(MembershipsCriteria criteria, DbConnection connection, DbTransaction transaction)
{
var password = criteria.Password;
if (password == null) { this.DoNothing(); }
else
{
if (criteria.Password is SecureString) { password = ((SecureString)criteria.Password).GetString(); }
var hash = KandaHashAlgorithm.ComputeHash( typeof(SHA512Managed).FullName, (string)password, Encoding.Unicode);
password = hash;
}
criteria.Password = password;
var reader = default(KandaDbDataReader);
try
{
reader = MembershipsGateway.Select(criteria, connection, transaction);
var found = (reader.Read() ? KandaDbDataMapper.MapToObject<MembershipEntity>(reader) : MembershipEntity.Empty);
return found;
}
finally
{
if (reader != null) { reader.Close(); }
}
}
/// <summary>
/// 构造函数--重载方法二
/// </summary>
/// <param name="connectionstring">数据库链接</param>
/// <param name="databasetype">数据库的类型</param>
public DBHelper(string connectionstring, string databasetype)
{
MyFactory = DbProviderFactories.GetFactory(databasetype);
MyConnection = MyFactory.CreateConnection();
MyConnection.ConnectionString = (databasetype.ToString() == "System.Data.OleDb") ? ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + GetDataPath(connectionstring) + ";") : (connectionstring);
MyCommand = MyConnection.CreateCommand();
}
public bool Fun_OpenDataBase()
{
String sConnect = "";
try
{
if (this.DBConn != null)
{
if (this.DBConn.State == System.Data.ConnectionState.Open)
{
this.DBConn.Close();
}
}
//sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBPath;
sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DBPath;
DBConn = new OleDbConnection();
DBCommand = new OleDbCommand();
DBConn.ConnectionString = sConnect;
DBConn.Open();
return(true);
}
catch (Exception)
{
return(false);
}
}
private static async Task <IEnumerable <TReturn> > MultiMapAsync <TReturn>(this IDbConnection cnn, CommandDefinition command, Type[] types, Func <object[], TReturn> map, string splitOn)
{
if (types.Length < 1)
{
throw new ArgumentException("you must provide at least one type to deserialize");
}
object param = command.Parameters;
var identity = new Identity(command.CommandText, command.CommandType, cnn, types[0], param?.GetType(), types);
var info = GetCacheInfo(identity, param, command.AddToCache);
bool wasClosed = cnn.State == ConnectionState.Closed;
try {
if (wasClosed)
{
await((DbConnection)cnn).OpenAsync().ConfigureAwait(false);
}
using (var cmd = (DbCommand)command.SetupCommand(cnn, info.ParamReader))
using (var reader = await cmd.ExecuteReaderAsync(command.CancellationToken).ConfigureAwait(false)) {
var results = MultiMapImpl <TReturn>(null, default(CommandDefinition), types, map, splitOn, reader, identity, true);
return(command.Buffered ? results.ToList() : results);
}
}
finally {
if (wasClosed)
{
cnn.Close();
}
}
}
public IEnumerable<RestaurantType> GetAll()
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
return _connection.GetList<RestaurantType>(new {});
}
}
public void Setup()
{
var factory = new DatabaseProviderFactory(TestConfigurationSource.CreateConfigurationSource());
var db = factory.CreateDefault();
connection = db.CreateConnection();
connection.Open();
}
static void Import(string simAreasPath, DbConnection connection)
{
var simarea = new SimAreasContext(connection, false, new DropCreateDatabaseAlways<SimAreasContext>());
var engine = new FileHelperEngine(typeof(SimAreas));
var entries = (SimAreas[])engine.ReadFile(simAreasPath);
foreach (var entry in entries)
{
var area = (from s in simarea.SimAreas
where s.SimAreaName == entry.SimAreaName
select s).FirstOrDefault();
if (area == null)
{
area = new SimArea
{
SimAreaName = entry.SimAreaName,
Latitude = entry.Latitude,
Longitude = entry.Longitude,
Height = entry.Height,
GeoidSeparation = entry.GeoidSeparation,
OpsLimitFile = entry.OpsLimitFile,
SimLimitFile = entry.SimLimitFile,
};
simarea.SimAreas.Add(area);
simarea.SaveChanges();
}
}
}
public void Insert(DbConnection conn, string tableName, DataTable sourceData, Dictionary<string, string> columnMappings = null, DataRowState state = DataRowState.Added)
{
SqlConnection sqlConn = conn as SqlConnection;
Check(conn);
using (SqlBulkCopyWrapper sqlBC = new SqlBulkCopyWrapper(sqlConn))
{
sqlBC.BatchSize = 10000;
sqlBC.BulkCopyTimeout = 600;
sqlBC.DestinationTableName = tableName;
if (columnMappings != null && columnMappings.Count > 0)
{
foreach (var item in columnMappings)
{
sqlBC.ColumnMappings.Add(item.Value, item.Key);
}
}
else
{
foreach (var item in sourceData.Columns.Cast<DataColumn>())
{
sqlBC.ColumnMappings.Add(item.ColumnName, item.ColumnName);
}
}
sqlBC.WriteToServer(sourceData, state);
}
}
internal Table(string tableName, DbConnection connection, TableDesignerDoc owner)
{
_owner = owner;
_oldname = tableName;
_connection = connection;
_name = tableName;
_owner.Name = _name;
_catalog = _connection.Database; // main
ReloadDefinition();
if (_key == null) _key = new PrimaryKey(_connection, this, null);
if (_exists)
{
using (DataTable tbl = connection.GetSchema("ForeignKeys", new string[] { Catalog, null, Name }))
{
foreach (DataRow row in tbl.Rows)
{
_fkeys.Add(new ForeignKey(connection, this, row));
_oldfkeys.Add(new ForeignKey(connection, this, row));
}
}
}
using (DataTable tbl = connection.GetSchema("Columns", new string[] { Catalog, null, Name }))
{
foreach (DataRow row in tbl.Rows)
{
_columns.Add(new Column(row, this));
}
}
}
void InsertNewSummoner(Summoner summoner, DbConnection connection)
{
//We are dealing with a new summoner
string query = string.Format("insert into summoner ({0}) values ({1})", GetGroupString(NewSummonerFields), GetPlaceholderString(NewSummonerFields));
using (var newSummoner = Command(query, connection))
{
newSummoner.SetFieldNames(NewSummonerFields);
newSummoner.Set(Profile.Identifier);
newSummoner.Set(summoner.AccountId);
newSummoner.Set(summoner.SummonerId);
newSummoner.Set(summoner.SummonerName);
newSummoner.Set(summoner.InternalName);
newSummoner.Set(summoner.SummonerLevel);
newSummoner.Set(summoner.ProfileIcon);
newSummoner.Set(summoner.HasBeenUpdated);
newSummoner.Set(summoner.UpdateAutomatically);
long timestamp = Time.UnixTime();
newSummoner.Set(timestamp);
newSummoner.Set(timestamp);
newSummoner.Execute();
summoner.Id = GetInsertId(connection);
}
}
public void OnClosed(DbConnection connection)
{
_connectionEventTimeline.WriteTimelineMessage();
_connectionLifetimeTimeline.WriteTimelineMessage();
_connectionEventTimeline = null;
_connectionLifetimeTimeline = null;
}
public IUnitOfWork GetUnitOfWork()
{
if (_UnitOfWork != null)
throw new Exception("A unit of work is already in use");
else
{
if(! KeepConnectionAlive)
{
_Context = new WADbContext(DatabaseName);
if(_FirstRequest)
{
_Context.Database.Initialize(force: false);
_FirstRequest = false;
}
}
else
{
if(_DbConnection == null)
{
string connectionString = null;
if(DisableSqlCe)
{
_DbConnection = new SqlConnection();
connectionString = @" Server=.\SQLEXPRESS; Database=" +
DatabaseName +
"; Trusted_Connection=true";
}
else
{
// using SqlCe
_DbConnection = new SqlCeConnection();
connectionString = "Data Source=" + DatabaseName;
}
// create a context and initialize the db on startup
if(_FirstRequest)
{
using (var tempContext = new WADbContext(connectionString))
{
tempContext.Database.Initialize(force: false);
}
_FirstRequest = false;
}
_DbConnection.ConnectionString = connectionString;
_DbConnection.Open();
}
_Context = new WADbContext(_DbConnection, false);
}
_UnitOfWork = new UnitOfWork(_Context);
return _UnitOfWork;
}
}
public static int Roles_DeleteRole (DbConnection connection, string applicationName, string rolename, bool deleteOnlyIfRoleIsEmpty)
{
string appId = DerbyApplicationsHelper.GetApplicationId (connection, applicationName);
if (appId == null)
return 1;
string roleId = GetRoleId (connection, appId, rolename);
if (roleId == null)
return 2;
if (deleteOnlyIfRoleIsEmpty) {
string querySelect = "SELECT RoleId FROM aspnet_UsersInRoles WHERE RoleId = ?";
OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);
AddParameter (cmdSelect, "RoleId", roleId);
using (OleDbDataReader reader = cmdSelect.ExecuteReader ()) {
if (reader.Read ())
// role is not empty
return 3;
}
}
string queryDelUsers = "DELETE FROM aspnet_UsersInRoles WHERE RoleId = ?";
OleDbCommand cmdDelUsers = new OleDbCommand (queryDelUsers, (OleDbConnection) connection);
AddParameter (cmdDelUsers, "RoleId", roleId);
cmdDelUsers.ExecuteNonQuery ();
string queryDelRole = "DELETE FROM aspnet_Roles WHERE ApplicationId = ? AND RoleId = ? ";
OleDbCommand cmdDelRole = new OleDbCommand (queryDelRole, (OleDbConnection) connection);
AddParameter (cmdDelRole, "ApplicationId", appId);
AddParameter (cmdDelRole, "RoleId", roleId);
cmdDelRole.ExecuteNonQuery ();
return 0;
}
/// <summary>
/// Instantiate an opened connection enlisted to the Transaction
/// if promotable is false, the Transaction wraps a local
/// transaction inside and can never be promoted
/// </summary>
/// <param name="dbResourceAllocator"></param>
/// <param name="transaction"></param>
/// <param name="wantPromotable"></param>
internal SharedConnectionInfo(
DbResourceAllocator dbResourceAllocator,
Transaction transaction,
bool wantPromotable,
ManualResetEvent handle)
{
Debug.Assert((transaction != null), "Null Transaction!");
if (null == handle)
throw new ArgumentNullException("handle");
this.handle = handle;
if (wantPromotable)
{
// Enlist a newly opened connection to this regular Transaction
this.connection = dbResourceAllocator.OpenNewConnection();
this.connection.EnlistTransaction(transaction);
}
else
{
// Make this transaction no longer promotable by attaching our
// IPromotableSinglePhaseNotification implementation (LocalTranscaction)
// and track the DbConnection and DbTransaction associated with the LocalTranscaction
LocalTransaction localTransaction = new LocalTransaction(dbResourceAllocator, handle);
transaction.EnlistPromotableSinglePhase(localTransaction);
this.connection = localTransaction.Connection;
this.localTransaction = localTransaction.Transaction;
}
}
/// <summary>
/// Resolve at run time the appropriate set of DbParameters for a stored procedure
/// </summary>
/// <param name="transaction">A valid SqlTransaction object</param>
/// <param name="connection">A valid SqlConnection object</param>
/// <param name="storedProcedureName">The name of the stored procedure</param>
/// <param name="includeReturnValueParameter">Whether or not to include their return value parameter</param>
/// <returns>The parameter array discovered.</returns>
private static IList<SqlParameter> DiscoverSpParameterSet(DbTransaction transaction, DbConnection connection, string storedProcedureName, bool includeReturnValueParameter)
{
using (SqlCommand cmd = new SqlCommand(storedProcedureName, connection as SqlConnection))
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
cmd.CommandType = CommandType.StoredProcedure;
if (transaction != null)
{
cmd.Transaction = transaction as SqlTransaction;
}
SqlCommandBuilder.DeriveParameters(cmd);
if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}
SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(discoveredParameters, 0);
return discoveredParameters;
}
}
public int ExecuteSqlNonQuery(StringBuilder sbSql, string providerName, string connectionString, out string strError)
{
int intEffected = -1; // return number rows effected
strError = "";
try
{
conn = CreateDbConnection(providerName, connectionString, out strError);
conn.Open();
}
catch (Exception ex)
{
strError = ex.Message;
return(-1);
}
try
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = sbSql.ToString();
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
intEffected = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
strError = ex.Message;
return(-1);
}
return(intEffected);
}
// Given a provider name and connection string,
// create the DbProviderFactory and DbConnection.
// Returns a DbConnection on success; null on failure.
private DbConnection CreateDbConnection(string providerName, string connectionString, out string strError)
{
// Assume failure.
conn = null;
strError = "";
// Create the DbProviderFactory and DbConnection.
if (connectionString != null)
{
try
{
DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
conn = factory.CreateConnection();
conn.ConnectionString = connectionString;
}
catch (Exception ex)
{
// Set the connection to null if it was created.
if (conn != null)
{
conn = null;
}
strError = ex.Message;
}
}
// Return the connection.
return(conn);
}
internal static DbCommand CreateCommand(DbConnection conn = null)
{
var factory = GetFactory();
var result = factory.CreateCommand();
result.Connection = conn;
return result;
}
private static void CreateTable(DbConnection connection)
{
using (var command = connection.CreateCommand())
{
command.CommandText = @"
create table Person (
PersonId integer,
Name nvarchar(100),
EMail nvarchar(100),
ZipCode nvarchar(100),
City nvarchar(100),
primary key(PersonId)
);
create table History (
PersonId integer,
HistoryNo integer,
Content nvarchar(100),
primary key(PersonId, HistoryNo)
);
create table IdManege (
TableName nvarchar(100),
Id integer,
Guid nvarchar(100),
primary key (TableName, Id)
);
";
var ret = command.ExecuteNonQuery();
}
}
public NuoDbCommand(string query, DbConnection conn)
{
if (!(conn is NuoDbConnection))
throw new ArgumentException("Connection is not a NuoDB connection", "conn");
sqlText = query;
connection = (NuoDbConnection)conn;
}
public static int Roles_CreateRole (DbConnection connection, string applicationName, string rolename)
{
string appId = (string) DerbyApplicationsHelper.Applications_CreateApplication (connection, applicationName);
if (appId == null)
return 1;
string querySelect = "SELECT RoleName FROM aspnet_Roles WHERE ApplicationId = ? AND LoweredRoleName = ?";
OleDbCommand cmdSelect = new OleDbCommand (querySelect, (OleDbConnection) connection);
AddParameter (cmdSelect, "ApplicationId", appId);
AddParameter (cmdSelect, "LoweredRoleName", rolename.ToLowerInvariant ());
using (OleDbDataReader reader = cmdSelect.ExecuteReader ()) {
if (reader.Read ())
return 2; // role already exists
}
string queryInsert = "INSERT INTO aspnet_Roles (ApplicationId, RoleId, RoleName, LoweredRoleName) VALUES (?, ?, ?, ?)";
OleDbCommand cmdInsert = new OleDbCommand (queryInsert, (OleDbConnection) connection);
AddParameter (cmdInsert, "ApplicationId", appId);
AddParameter (cmdInsert, "RoleId", Guid.NewGuid ().ToString ());
AddParameter (cmdInsert, "RoleName", rolename);
AddParameter (cmdInsert, "LoweredRoleName", rolename.ToLowerInvariant ());
cmdInsert.ExecuteNonQuery ();
return 0;
}
protected override string GetDbProviderManifestToken(DbConnection connection)
{
var glimpseConnection = connection as GlimpseDbConnection;
DbConnection rawConnection = glimpseConnection == null ? connection : glimpseConnection.InnerConnection;
return(InnerProviderServices.GetProviderManifestToken(rawConnection));
}
public Manager(String fileName)
{
localization = new System.ComponentModel.ComponentResourceManager(typeof(WorkerTimeStrings));
this.fileName = fileName;
if (File.Exists(fileName)) {
this.sqlConnection = new SQLiteConnection("Data Source=" + fileName );
this.sqlCommand = this.sqlConnection.CreateCommand();
sqlConnection.Open();
} else {
this.sqlConnection = new SQLiteConnection("Data Source=" + fileName);
this.sqlCommand = this.sqlConnection.CreateCommand();
sqlConnection.Open();
ExecuteNonQuery("CREATE TABLE Tracks ([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [ProjectID] INTEGER, [ActivityID] INTEGER, [Start] DATETIME, [End] DATETIME, [Duration] INTEGER)");
ExecuteNonQuery("CREATE TABLE Projects ([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [Name] TEXT)");
ExecuteNonQuery("CREATE TABLE Activity ([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [Name] TEXT, [ProjectID] INTEGER)");
if (MessageBox.Show(localization.GetString("String10"), localization.GetString("String3"), MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes )
{
ExecuteNonQuery("INSERT INTO Projects (Name) VALUES ('" + localization.GetString("String12") + " 1')");
ExecuteNonQuery("INSERT INTO Projects (Name) VALUES ('" + localization.GetString("String12") + " 2')");
ExecuteNonQuery("INSERT INTO Projects (Name) VALUES ('" + localization.GetString("String12") + " 3')");
ExecuteNonQuery("INSERT INTO Activity (Name,ProjectID) VALUES ('" + localization.GetString("String13") + " 1',1)");
ExecuteNonQuery("INSERT INTO Activity (Name,ProjectID) VALUES ('" + localization.GetString("String13") + " 2',1)");
ExecuteNonQuery("INSERT INTO Activity (Name,ProjectID) VALUES ('" + localization.GetString("String13") + " 3',1)");
ExecuteNonQuery("INSERT INTO Activity (Name,ProjectID) VALUES ('" + localization.GetString("String13") + " 1',2)");
ExecuteNonQuery("INSERT INTO Activity (Name,ProjectID) VALUES ('" + localization.GetString("String13") + " 2',2)");
ExecuteNonQuery("INSERT INTO Activity (Name,ProjectID) VALUES ('" + localization.GetString("String13") + " 3',2)");
}
}
Update1();
}
public void OnCommitted(DbConnection connection)
{
_transactionEventTimeline.WriteTimelineMessage();
_transactionLifetimeTimeline.WriteTimelineMessage(true);
_transactionEventTimeline = null;
_transactionLifetimeTimeline = null;
}
/// <summary>Gets the db types for the SQL CE provider.</summary>
/// <param name="connection">The connection (not required).</param>
/// <returns></returns>
public override Dictionary<string, DbModelType> GetDbTypes(DbConnection connection)
{
Dictionary<string, DbModelType> dbTypes = new Dictionary<string, DbModelType>();
string dataTypesSql = "SELECT * FROM information_schema.provider_types";
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = dataTypesSql;
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string typeName = (string)reader["TYPE_NAME"];
int columnSize = Convert.ToInt32(reader["COLUMN_SIZE"]);
DbModelType dbType = new DbModelType(typeName, columnSize);
dbType.CreateParameters = Convert.ToString(reader["CREATE_PARAMS"]);
dbType.LiteralPrefix = Convert.ToString(reader["LITERAL_PREFIX"]);
dbType.LiteralSuffix = Convert.ToString(reader["LITERAL_SUFFIX"]);
dbType.ProviderDbType = Convert.ToString(reader["DATA_TYPE"]);
FixCreateFormat(dbType);
FixMaxLengths(dbType);
AssignSystemTypes(dbType);
dbTypes.Add(typeName, dbType);
}
}
}
return dbTypes;
}
/// <summary>
/// Initializes a new instance of the <see cref="TransactionManager"/> class.
/// </summary>
/// <param name="connectionString">The connection string to the database.</param>
/// <param name="providerInvariantName">Name of the provider invariant.</param>
public TransactionManager( string connectionString, string providerInvariantName )
{
this._connectionString = connectionString;
this._invariantProviderName = providerInvariantName;
this._database = new GenericDatabase(_connectionString, DbProviderFactories.GetFactory( this._invariantProviderName ) );
this._connection = this._database.CreateConnection();
}
public static bool UpdateVote(VoteInfo vote)
{
Database database = DatabaseFactory.CreateDatabase();
bool result;
using (System.Data.Common.DbConnection dbConnection = database.CreateConnection())
{
VoteDao voteDao = new VoteDao();
dbConnection.Open();
System.Data.Common.DbTransaction dbTransaction = dbConnection.BeginTransaction();
try
{
if (!voteDao.UpdateVote(vote, dbTransaction))
{
dbTransaction.Rollback();
result = false;
}
else
{
if (!voteDao.DeleteVoteItem(vote.VoteId, dbTransaction))
{
dbTransaction.Rollback();
result = false;
}
else
{
int num = 0;
if (vote.VoteItems != null)
{
foreach (VoteItemInfo current in vote.VoteItems)
{
current.VoteId = vote.VoteId;
current.ItemCount = 0;
num += voteDao.CreateVoteItem(current, dbTransaction);
}
if (num < vote.VoteItems.Count)
{
dbTransaction.Rollback();
result = false;
return(result);
}
}
dbTransaction.Commit();
result = true;
}
}
}
catch
{
dbTransaction.Rollback();
result = false;
}
finally
{
dbConnection.Close();
}
}
return(result);
}
/// <summary>
/// Execute parameterized SQL that selects a single value
/// </summary>
/// <returns>The first cell selected</returns>
public static Task <T> ExecuteScalarAsync <T>(
this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int?commandTimeout = null, CommandType?commandType = null
)
{
var command = new CommandDefinition(sql, param, transaction, commandTimeout, commandType, CommandFlags.Buffered);
return(ExecuteScalarImplAsync <T>(cnn, command));
}
private void processQuery(System.Data.Common.DbConnection conn, IQueryable <ServiceTicket> query)
{
if (new Random().Next(10) > 8)
{
_connections.Add(conn);
throw new Exception("Invalid query");
}
}
public IHelper(string connString)
{
// this 是某个实现了 IHelper 的类的对象,访问的是那个类中的 CreateConnection(..),
conn = this.CreateConnection(connString); //创建连接对象
cmd = this.CreateCommand(); //创建操作对象
cmd.Connection = conn;
cmd.CommandType = System.Data.CommandType.Text; //默认执行的是Sql语句
}
/// <summary>
/// Execute a single-row query asynchronously using .NET 4.5 Task.
/// </summary>
public static Task <object> QueryFirstOrDefaultAsync(this IDbConnection cnn, Type type, string sql, object param = null, IDbTransaction transaction = null, int?commandTimeout = null, CommandType?commandType = null)
{
if (type == null)
{
throw new ArgumentNullException(nameof(type));
}
return(QueryFirstOrDefaultAsync <object>(cnn, type, new CommandDefinition(sql, param, transaction, commandTimeout, commandType, CommandFlags.None, default(CancellationToken))));
}
/// <summary>
/// Execute a query asynchronously using .NET 4.5 Task.
/// </summary>
public static Task <IEnumerable <object> > QueryAsync(this IDbConnection cnn, Type type, string sql, dynamic param = null, IDbTransaction transaction = null, int?commandTimeout = null, CommandType?commandType = null)
{
if (type == null)
{
throw new ArgumentNullException(nameof(type));
}
return(QueryAsync <object>(cnn, type, new CommandDefinition(sql, (object)param, transaction, commandTimeout, commandType, CommandFlags.Buffered, default(CancellationToken))));
}
protected DbAccessCommand()
{
this._DbConnection = null;
this._DbCommand = null;
this._DbAdapter = null;
this._DbBuilder = null;
this._AutoOpenClose = true;
}
/// <summary>
/// Execute a command that returns multiple result sets, and access each in turn
/// </summary>
public static Task <GridReader> QueryMultipleAsync(
this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int?commandTimeout = null, CommandType?commandType = null
)
{
var command = new CommandDefinition(sql, param, transaction, commandTimeout, commandType, CommandFlags.Buffered);
return(QueryMultipleAsync(cnn, command));
}
private static void PrepareCommand(
DbCommand command,
DbConnection connection,
DbTransaction transaction,
CommandType commandType,
string commandText,
DbParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
command.Connection = connection;
command.CommandText = commandText;
command.CommandType = commandType;
if (transaction != null)
{
if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}