protected HistoryOperation CreateInsertOperation(
string contextKey, string migrationId, XDocument model, string productVersion = null)
{
using (var connection = ProviderFactory.CreateConnection())
{
connection.ConnectionString = ConnectionString;
using (var historyContext = new HistoryContext(connection, "dbo"))
{
historyContext.History.Add(
new HistoryRow
{
MigrationId = migrationId,
ContextKey = contextKey,
Model = CompressModel(model),
ProductVersion
= productVersion
?? typeof(DbContext).Assembly()
.GetCustomAttributes <AssemblyInformationalVersionAttribute>()
.Single()
.InformationalVersion,
});
var cancellingLogger = new CommandTreeCancellingLogger(historyContext);
DbInterception.Add(cancellingLogger);
historyContext.SaveChanges();
return(new HistoryOperation(
cancellingLogger.Log.OfType <DbModificationCommandTree>().ToList()));
}
}
}
public DbConnection GetSqlServerDbConnection() // Temporarily public for migratory purposes
{
DbConnection connection = ProviderFactory.CreateConnection();
connection.ConnectionString = ConnectionString;
return(connection);
}
public override Order GetBy(int guid)
{
Order result = default;
using (var connection = ProviderFactory.CreateConnection())
{
connection.ConnectionString = ConnectionString;
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = GetByIdSql;
command.AddParameter(ParamIdName, DbType.Int32, guid);
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
result = FromReaderToObject(reader);
if (reader.NextResult())
{
result.OrderDetails = ParseOrderDetail(reader);
}
}
}
}
}
return(result);
}
/// <summary>
/// Build a Select query command
/// </summary>
/// <param name="pParams">List of record parameters</param>
protected void BuildSelectCommand(params RecordParameter[] pParams)
{
if (RecordConnection == null)
{
RecordConnection = ProviderFactory.CreateConnection();
RecordConnection.ConnectionString = ConnectionString;
//RecordConnection = CreateConnection(null, this.ConnectionString);
}
String Attrs = String.Empty;
String Where = String.Empty;
foreach (String filed in Current[typeof(TRECORD)].m_Members) //foreach (MemberInfo member in Current[this.GetType()].Attributes)
{
MemberInfo member = typeof(TRECORD).GetMember(filed)[0];
object field = member.GetCustomAttributes(typeof(Field), true)[0];
if (!String.IsNullOrEmpty(Attrs))
{
Attrs += ", ";
}
Attrs += String.Format("[{0}]", (field as Field).Name);
}
if (pParams != null && pParams.Length > 0)
{
Where = String.Format("WHERE ({0})", BuildWhere(pParams));
}
_SelectCommandText = String.Format("SELECT {0} FROM {1} {2}", Attrs, TableName, Where);
}
public int MoveProductToAnotherCategory(int fromCategoryId, int toCategoryId)
{
using (var connectinon = ProviderFactory.CreateConnection(ConnectionString))
{
using (var transaction = connectinon.BeginTransaction())
{
try
{
var rows = connectinon.Execute(
Sql_MoveProductToAnotherCategory,
new
{
@fromId = fromCategoryId,
@toId = toCategoryId
}, transaction: transaction);
transaction.Commit();
return(rows);
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
}
}
}
/// <summary>
/// Connection을 엽니다.
/// </summary>
public virtual void OpenSharedConnection()
{
if (_connectionDepth > 0)
{
_connectionDepth++;
return;
}
if (IsDebugEnabled)
{
log.Debug("Connection을 엽니다... ConnectionString=[{0}]", ConnectionString);
}
lock (_syncLock) {
Connection = ProviderFactory.CreateConnection();
Connection.ConnectionString = ConnectionString;
Connection.Open();
Connection = OnConnectionOpened(Connection);
if (KeepConnectionAlive)
{
_connectionDepth++;
}
}
}
/// <summary>
/// Build a Select query command
/// </summary>
/// <param name="condition">Condition</param>
/// <param name="listOfParameters">List of record parameters</param>
protected void BuildSelectCommand(DbTransaction transaction, Condition condition, List <RecordParameter> listOfParameters, params RecordOrderBy[] pOrderBy)
{
if (RecordConnection == null)
{
RecordConnection = ProviderFactory.CreateConnection();
RecordConnection.ConnectionString = ConnectionString;
//RecordConnection = CreateConnection(null, this.ConnectionString);
}
String SelectFormat = "SELECT {0} FROM {1} {2}";
String Attrs = String.Empty;
String Where = String.Empty;
foreach (String filed in Current[typeof(TRECORD)].m_Members) //foreach (MemberInfo member in Current[this.GetType()].Attributes)
{
MemberInfo member = typeof(TRECORD).GetMember(filed)[0];
object field = member.GetCustomAttributes(typeof(Field), true)[0];
if (!String.IsNullOrEmpty(Attrs))
{
Attrs += ", ";
}
Attrs += String.Format("[{0}]", (field as Field).Name);
if (field is Field & (field as Field).IsPrimaryKey)
{
if (!String.IsNullOrEmpty(Where))
{
Where += " AND ";
}
Where += String.Format("[{0}]={1}",
(field as Field).Name,
InternalConvert.ConvertQueryArgument(RecordConnection, (field as Field).Name)
);
}
}
if (condition != null)
{
Where = String.Format("WHERE {0}", condition.Build(listOfParameters)).Replace(" ", " ");
}
else
{
Where = String.Format("WHERE ({0})", Where);
}
if (pOrderBy != null && pOrderBy.Length > 0)
{
SelectFormat += " ORDER BY ";
foreach (RecordOrderBy orderBy in pOrderBy)
{
SelectFormat += orderBy.ToString() + ",";
}
SelectFormat = SelectFormat.Substring(0, SelectFormat.Length - 1);
}
_SelectCommandText = String.Format(SelectFormat, Attrs, TableName, Where);
}
public IList <EmplShipWorked> GetEmplsWithShips()
{
using (var connection = ProviderFactory.CreateConnection(ConnectionString))
{
var emplDict = new Dictionary <int, EmplShipWorked>();
connection.Query <Employee, Shipper, Shipper>(
Sql_GetEmplsWithShips,
(e, s) =>
{
if (!emplDict.TryGetValue(e.EmployeeId, out var empl))
{
emplDict[e.EmployeeId] = new EmplShipWorked()
{
Employee = e,
Ships = new List <Shipper>()
};
}
emplDict[e.EmployeeId].Ships.Add(s);
return(s);
},
splitOn: "EmployeeID,ShipperID");
return(emplDict.Values.ToList());
}
}
public override Order Insert(Order item)
{
using (var connection = ProviderFactory.CreateConnection())
{
connection.ConnectionString = ConnectionString;
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = InsertSql;
PrepareInsertUpdateCommand(item, command);
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
var orderId = reader.SafeCastInt32(0);
item.Id = (int)orderId;
}
else
{
throw new Exception("Id of last record has not returned");
}
}
}
}
return(item);
}
/// <summary>
/// Executes a stored procedure that doesn't return data
/// </summary>
/// <param name="commandText">The query or stored procedure to run</param>
/// <param name="commandType">The type of command, text, or stored proc to run</param>
/// <param name="parameters">Optional list of parameters to use in the query</param>
/// <returns>The number of rows affected</returns>
public virtual int ExecuteNonQuery(string commandText, CommandType commandType, IList <IDbDataParameter> parameters = null)
{
parameters = parameters ?? new List <IDbDataParameter>();
try
{
using (var connection = ProviderFactory.CreateConnection())
{
if (connection == null)
{
throw new IpDataAccessException("Could not connect to the database");
}
connection.ConnectionString = ConnectionString;
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandTimeout = QueryTimeout == 0 ? 120 : QueryTimeout;
command.CommandType = commandType;
command.CommandText = commandText;
command.Parameters.AddRange(parameters.ToArray());
return(command.ExecuteNonQuery());
}
}
}
catch (Exception ex)
{
throw new IpDataAccessException(string.Format("SQL {0}: {1} failed to execute, see inner exception for more details.",
commandType == CommandType.StoredProcedure ? "Stored Proc" : "Text", commandText), ex);
}
}
public void InsertWhole(IList <Product> products)
{
void insertSuppliersAndSetId(IList <(Product p, Supplier s)> tuples, DbConnection conn, DbTransaction trans)
{
foreach (var(p, s) in tuples)
{
var id = conn.ExecuteScalar <int?>(Sql_TrySupplierInsert, s, trans);
if (id.HasValue)
{
s.SupplierID = id.Value;
p.SupplierID = id.Value;
}
}
}
void insertCustomersAndSetId(IList <(Product p, Category c)> tuples, DbConnection conn, DbTransaction trans)
{
foreach (var(p, c) in tuples)
{
var id = conn.ExecuteScalar <int?>(Sql_TryCategoryInsert, c, trans);
if (id.HasValue)
{
c.CategoryID = id.Value;
p.CategoryID = id.Value;
}
}
}
using (var connection = ProviderFactory.CreateConnection(ConnectionString))
{
using (var transaction = connection.BeginTransaction())
{
try
{
var suppliers = products
.Where(x => x.Supplier != null)
.Select(x => (x, x.Supplier)).ToList();
insertSuppliersAndSetId(suppliers, connection, transaction);
var categories = products
.Where(x => x.Category != null)
.Select(x => (x, x.Category)).ToList();
insertCustomersAndSetId(categories, connection, transaction);
foreach (var prod in products)
{
connection.Insert(prod, transaction);
}
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
}
}
}
public DbConnection CreateConnection()
{
var connection = ProviderFactory.CreateConnection();
connection.ConnectionString = ConnectionString;
connection.Open();
return(connection);
}
public DataTable SelectTable(string connectionString, string tableName, int nrows = int.MaxValue)
{
using (var conn = ProviderFactory.CreateConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
var result = new DataTable();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = string.Format("select * from [{0}]", tableName);
using (var da = ProviderFactory.CreateDataAdapter())
{
da.SelectCommand = cmd;
da.FillSchema(result, SchemaType.Source);
}
int n = 0;
var items = Array.CreateInstance(typeof(object), result.Columns.Count) as object[];
try
{
using (var rdr = cmd.ExecuteReader())
{
while (n < nrows && rdr.Read())
{
rdr.GetValues(items);
result.Rows.Add(items);
n++;
}
}
}
catch (DbException ex)
{
Logger.Write(ex, System.Diagnostics.TraceEventType.Error,
new Dictionary <string, object>()
{
{ "Action", string.Format("Read Table [{0}] failed on data provider: {1}", tableName, Name) },
{ "sql", cmd.CommandText },
{ "connectionString", connectionString },
{ "tableName", tableName },
{ "nRows", nrows }
},
"Error selecting data from table {0}", tableName);
// BUG: this throw is not caught in (some) wizard steps. One cause of the exceptions has been fixed, so to test that
// this throw gets caught now, we need to temporarily hard-code divide-by-zero inside the try block above.
throw new DataProviderControllerException(
string.Format("Read Table [{0}] failed on data provider: {1}", tableName, Name)
, ex);
}
}
return(result);
}
}
/// <summary>
/// Create a new database connection.
/// </summary>
/// <returns>A new, configured <see cref="DbConnection">database connection</see>.</returns>
public virtual DbConnection CreateConnection()
{
Contract.Ensures(Contract.Result <DbConnection>() != null);
var connection = ProviderFactory.CreateConnection();
connection.ConnectionString = connectionString;
return(connection);
}
public virtual DbConnection CreateConnection()
{
DbConnection dc = ProviderFactory.CreateConnection();
if (dc != null && dc.State != ConnectionState.Open)
{
dc.ConnectionString = this.ConnectionString;
dc.Open();
}
return(dc);
}
protected void AssertHistoryContextEntryExists(string contextKey)
{
using (var connection = ProviderFactory.CreateConnection())
{
connection.ConnectionString = ConnectionString;
using (var historyContext = new HistoryContext(connection, "dbo"))
{
Assert.True(historyContext.History.Any(h => h.ContextKey == contextKey));
}
}
}
protected void AssertHistoryContextDoesNotExist()
{
using (var connection = ProviderFactory.CreateConnection())
{
connection.ConnectionString = ConnectionString;
using (var historyContext = new HistoryContext(connection, "dbo"))
{
Assert.Throws <EntityCommandExecutionException>(() => historyContext.History.Count());
}
}
}
protected DbConnection CreateConnection(string path)
{
var conn = ProviderFactory.CreateConnection();
conn.ConnectionString = BuildConnectionString(path);
#if SYSTEM_DATA_SQLITE
((System.Data.SQLite.SQLiteConnection)conn).Flags |= System.Data.SQLite.SQLiteConnectionFlags.NoVerifyTypeAffinity;
#endif
return(conn);
}
private void Initialize()
{
ParameterChecker.ArgumentNotNullOrEmptyString(ProviderName, "ProviderName");
ValidateProviderName(ProviderName);
ParameterChecker.ArgumentNotNullOrEmptyString(ConnectionString, "ConnectionString");
ProviderFactory = DbProviderFactories.GetFactory(ProviderName);
Connection = ProviderFactory.CreateConnection();
Connection.ConnectionString = ConnectionString;
}
protected virtual DbConnection GetDbConnection()
{
var connection = ProviderFactory.CreateConnection();
if (connection == null)
{
throw new ApplicationException("Unable to create conneciton. Please make sure all DLL libraries have been installed.");
}
connection.ConnectionString = ConnectionString;
connection.Open();
return(connection);
}
public DropTableOperation GetDropHistoryTableOperation()
{
using (var connection = ProviderFactory.CreateConnection())
{
connection.ConnectionString = ConnectionString;
return((DropTableOperation)
new EdmModelDiffer().Diff(
new HistoryContext(connection, defaultSchema: null).GetModel(),
new DbModelBuilder().Build(ProviderInfo).GetModel())
.Single());
}
}
private DbConnection GetConnection()
{
DbConnection connection = ProviderFactory.CreateConnection();
if (connection == null)
{
throw new ArgumentException("Could not create a connection for Database Provider " + ProviderFactory.GetType().Name);
}
connection.ConnectionString = ConnectionString;
connection.Open();
return(connection);
}
public CreateTableOperation GetCreateHistoryTableOperation(string defaultSchema = null)
{
using (var connection = ProviderFactory.CreateConnection())
{
connection.ConnectionString = ConnectionString;
return((CreateTableOperation)
new EdmModelDiffer().Diff(
new DbModelBuilder().Build(ProviderInfo).GetModel(),
new HistoryContext(connection, contextOwnsConnection: true, defaultSchema: defaultSchema).GetModel(),
includeSystemOperations: true)
.Single());
}
}
public void Create_can_create_database()
{
DropDatabase();
Assert.False(DatabaseExists());
using (var connection = ProviderFactory.CreateConnection())
{
connection.ConnectionString = ConnectionString;
new DatabaseCreator().Create(connection);
}
Assert.True(DatabaseExists());
}
public IList <StatByRegion> GetStatByRegions()
{
using (var connection = ProviderFactory.CreateConnection(ConnectionString))
{
var result = connection.Query <Region, int, StatByRegion>(
Sql_GetStatByRegions,
(r, c) => new StatByRegion()
{
Region = r, Count = c
},
splitOn: "RegionID,Count")
.ToList();
return(result);
}
}
public ConexaoDAL(bool OpenTransaction)
{
try
{
//inicializa a hashtable para os parametros
mobjHashTableParameters = new Hashtable();
//providerBD
menuProvider = (ProviderType)Convert.ToInt32(ConfigurationSettings.AppSettings["ProviderBD"]);
//string conexao
string StrConexao = ConfigurationSettings.AppSettings["Conexao"];
//seta os valores principais
SqlConn.StrConexao = ConfigurationSettings.AppSettings["Conexao"];
SqlConn.Servidor = ConfigurationSettings.AppSettings["DataSource"];
SqlConn.UserName = ConfigurationSettings.AppSettings["UserName"];
SqlConn.Password = ConfigurationSettings.AppSettings["PassWord"];
SqlConn.DataBase = ConfigurationSettings.AppSettings["DataBase"];
if (!String.IsNullOrEmpty(StrConexao))
{
//inicializa o provider
pf = new ProviderFactory(menuProvider);
//iniciliza a conexao
SqlConn.Conexao = pf.CreateConnection(StrConexao);
SqlConn.Conexao.Open();
//inicializa a transação
if (OpenTransaction)
{
if (SqlConn.Trans == null)
{
SqlConn.Trans = SqlConn.Conexao.BeginTransaction(IsolationLevel.ReadCommitted);
}
}
}
else
{
throw new Exception("String de Conexão está vazio ou inválido");
}
}
catch (Exception ex)
{
throw new Exception("ConexaoDAL Error: " + ex.Message);
}
}
public void Can_get_empty_model()
{
using (var connection = ProviderFactory.CreateConnection())
{
connection.ConnectionString = ConnectionString;
using (var emptyContext = new EmptyContext(connection))
{
var model = emptyContext.GetModel();
var csdlSchemaNode = model.Descendants(_csdlNamespace + "Schema").Single();
var entityContainer = csdlSchemaNode.Descendants(_csdlNamespace + "EntityContainer").Single();
Assert.Equal(0, entityContainer.Descendants().Count());
}
}
}
public void Getting_model_does_not_create_database()
{
using (var connection = ProviderFactory.CreateConnection())
{
connection.ConnectionString = ConnectionString;
DropDatabase();
using (var emptyContext = new EmptyContext(connection))
{
emptyContext.GetModel();
Assert.False(Database.Exists(connection));
}
}
}
private void SetupDateInAttribute(int id, DateTime dateTime, string sql)
{
using (var connection = ProviderFactory.CreateConnection())
{
connection.ConnectionString = ConnectionString;
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = sql;
command.AddParameter(ParamIdName, DbType.Int32, id);
command.AddParameter("@dateTime", DbType.DateTime, dateTime);
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// Create a new connection instance
/// </summary>
/// <param name="pTransaction">A specific transaction</param>
/// <returns></returns>
public DbConnection CreateConnection(DbTransaction pTransaction, String pConnectionString)
{
if (pConnectionString == null)
{
throw new RecordConnectionStringNullException("ConnectionString is null!");
}
;
if (pTransaction != null)
{
return(pTransaction.Connection);
}
DbConnection conn = ProviderFactory.CreateConnection();
conn.ConnectionString = pConnectionString;
return(conn);
}
