/// <summary>
/// Creates the output parameters for a SQL command.
/// </summary>
/// <param name="command">The SQL command.</param>
/// <param name="outputParameters">The output parameter collection.</param>
private static void CreateOutputParameters(SqlCommand command, IEnumerable <KeyValuePair <string, object> > outputParameters)
{
SqlParameter param;
foreach (var parameter in outputParameters)
{
object databaseParameter = SqlTypeHelper.ConvertToDatabaseValue(parameter.Value);
if (parameter.Value is decimal)
{
param = new SqlParameter(parameter.Key, SqlDbType.Decimal)
{
Precision = 32, Scale = 16
};
param.Value = parameter.Value;
}
else
{
param = new SqlParameter(parameter.Key, databaseParameter);
}
param.Direction = ParameterDirection.InputOutput;
command.Parameters.Add(param);
}
}
/// <summary>
/// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
/// </summary>
public void Dispose()
{
// statements are cached and disposed by the owner connection
// reset statement so any kept result is discarted
try
{
this.Statement.InUse = false;
this.Statement.SqliteStatement.Reset();
// dispose all dependencies that have been held during statement execution
if (this.dependencyContextCollection != null)
{
foreach (IDisposable dependency in this.dependencyContextCollection)
{
dependency.Dispose();
}
this.dependencyContextCollection = null;
}
}
catch (SQLiteException exception)
{
SqlTypeHelper.ThrowDatabaseException(exception);
}
}
/// <summary>
/// Creates the input parameters for a SQL command.
/// </summary>
/// <param name="command">The SQL command.</param>
/// <param name="inputParameters">The input parameter collection.</param>
private static void CreateInputParameters(SqlCommand command, IEnumerable <KeyValuePair <string, object> > inputParameters)
{
foreach (var parameter in inputParameters)
{
var parameterValue = parameter.Value;
Microsoft.Dynamics.Commerce.Runtime.Data.Types.DataTable table;
if (SqlTypeHelper.TryGetTable(parameterValue, out table))
{
if (string.IsNullOrWhiteSpace(table.TableName))
{
throw new DatabaseException(DatabaseErrorCode.ParameterNotValid, "The table name of the data table must be provided.");
}
DataTable systemDataTable = SqlTypeHelper.ConvertToSystemDataTable(command.Connection, table);
var sqlParameter = command.Parameters.AddWithValue(parameter.Key, systemDataTable);
sqlParameter.TypeName = string.Format("[{0}].{1}", CrtDatabaseSchemaName, systemDataTable.TableName);
continue;
}
object databaseValue = SqlTypeHelper.ConvertToDatabaseValue(parameterValue);
command.Parameters.AddWithValue(parameter.Key, databaseValue);
}
}
/// <summary>
/// Reads the next result set row.
/// </summary>
/// <returns>Whether new result set row exists or not.</returns>
/// <remarks>To read the first row in the result set, as well as all subsequent rows, a call to this method is necessary for each row to be read.</remarks>
public bool Read()
{
SQLiteResult sqlResult = SQLiteResult.OK;
try
{
sqlResult = this.Statement.SqliteStatement.Step();
}
catch (SQLiteException exception)
{
SqlTypeHelper.ThrowDatabaseException(exception);
}
switch (sqlResult)
{
case SQLiteResult.ROW:
return(true);
case SQLiteResult.OK:
case SQLiteResult.DONE:
return(false);
case SQLiteResult.BUSY:
this.HandleDeadlock();
return(false);
default:
string message = string.Format(
"Database operation could not be completed. Sqlite result: {0}.",
sqlResult.ToString());
throw new DatabaseException(DatabaseErrorCode.Unknown, message);
}
}
/// <summary>
/// Reads the output parameters from a SQL command.
/// </summary>
/// <param name="command">The SQL command.</param>
/// <param name="outputParameters">The output parameter dictionary.</param>
private static void ReadOutputParameters(SqlCommand command, IDictionary <string, object> outputParameters)
{
foreach (SqlParameter parameter in command.Parameters)
{
if (parameter.Direction == ParameterDirection.InputOutput)
{
outputParameters[parameter.ParameterName] = SqlTypeHelper.ConvertToManagedValue(parameter.Value);
}
}
}
private static SqlFieldConfiguration CreateFieldConfiguration(PropertyField field, FieldGraphPath <PropertyField> graphPath)
{
var fieldConfig = SqlFieldConfiguration.Create(field)
.ColumnName(string.Join("_", graphPath.Path))
.DataType(SqlTypeHelper.GetDataType(field.FieldType.Type))
.IsNullable(SqlTypeHelper.TypeIsNullable(field.FieldType.Type));
fieldConfig.PropertyPath = graphPath;
return(fieldConfig);
}
/// <summary>
/// Creates the table.
/// </summary>
/// <param name="entityMap">The entity map.</param>
/// <returns></returns>
/// <exception cref="ArgumentNullException">entityMap</exception>
public static DataTable CreateTable(this EntityMap entityMap)
{
if (entityMap == null)
{
throw new ArgumentNullException(nameof(entityMap));
}
var table = new DataTable(entityMap.TableName);
if (entityMap.PrimaryKey != null)
{
foreach (Property key in entityMap.PrimaryKey.Keys)
{
var column = new DataColumn(key.ColumnName, SqlTypeHelper.GetClrType(key.DbType, false))
{
AllowDBNull = key.IsNullable
};
table.Columns.Add(column);
}
}
foreach (var prop in entityMap.Properties)
{
if (table.Columns.Contains(prop.ColumnName) || prop.IsMappingComplexType())
{
continue;
}
var column = new DataColumn(prop.ColumnName, SqlTypeHelper.GetClrType(prop.DbType, false))
{
AllowDBNull = prop.IsNullable
};
table.Columns.Add(column);
}
foreach (var rel in entityMap.Relations)
{
if (rel.RelationType > RelationshipType.ManyToOne)
{
continue;
}
if (table.Columns.Contains(rel.ColumnName))
{
continue; //column has already been added
}
DataColumn column = new DataColumn(rel.ColumnName, SqlTypeHelper.GetClrType(rel.DbType, false))
{
AllowDBNull = rel.IsNullable
};
table.Columns.Add(column);
}
return(table);
}
protected virtual IEnumerable <IntersectedFields <ViewIntersectionModel, ViewIntersectionField, EntityModel, EntityField> > GetInvalidFields(
IntersectAnalysis intersectAnalysis
)
{
foreach (var intersectedFields in intersectAnalysis.IntersectedFields)
{
if (intersectedFields.LeftField.FieldDataType != intersectedFields.RightField.FieldDataType &&
SqlTypeHelper.GetDataType(intersectedFields.RightField.FieldDataType) == null)
{
yield return(intersectedFields);
}
}
}
public void Apply(BuildContext context, BuildStep buildStep, SqlEntityConfiguration sqlEntityConfiguration,
SqlDataModelBuilder sqlDataModelBuilder)
{
if (buildStep != BuildStep.DefineRelationships)
{
return;
}
var typeModel = TypeModel.GetModelOf(sqlEntityConfiguration.EntityType);
ModelFields(typeModel.Fields, new string[0]);
void ModelFields(IReadOnlyList <PropertyField> fields, string[] parentPath)
{
foreach (var field in fields)
{
var fieldGraphPath = new FieldGraphPath <PropertyField>(
parentPath.Concat(new[] { field.FieldName }).ToArray(),
field
);
if (SqlTypeHelper.GetDataType(field.FieldType.Type) == null)
{
if (!IsRegisteredEntity(context, field.FieldType.Type))
{
ModelFields(field.FieldModel.Fields, fieldGraphPath.Path);
}
else
{
// build a fk relationship
var model = context.SqlBuilders.First(q => q.EntityType == field.FieldType.Type);
var foreignKeyField = model.ModelledFields.FirstOrDefault(q => q.Options.IsPrimaryKey);
if (foreignKeyField == null)
{
throw new Exception("Referenced entity missing primary key field.");
}
var fieldConfig = SqlFieldConfiguration.Create(field)
.ColumnName($"{string.Join("_", fieldGraphPath.Path)}_{foreignKeyField.Options.ColumnName}")
.DataType(foreignKeyField.Options.SqlDataType)
.IsNullable(true)
.ForeignKey(foreignKeyField.PropertyPath);
fieldConfig.PropertyPath = fieldGraphPath;
sqlDataModelBuilder.ModelledFields.Add(fieldConfig);
}
}
}
}
}
public SqlStatement PrepareStatement(string commandString)
{
if (this.isDisposed)
{
throw new ObjectDisposedException(typeof(DatabaseConnection).FullName, "Cannot call PrepareStatement() on a disposed connection.");
}
if (this.SqlConnection == null)
{
throw new DatabaseException(DatabaseErrorCode.OperationNotValid, "Cannot call PrepareStatement() on an unopened connection.");
}
SqlStatement statement;
// tries to get statement from cache
if (!this.statementCache.TryGetValue(commandString, out statement))
{
// if the statement is not cached yet, create it and add it to the cache
try
{
ISQLiteStatement sqliteStatement = this.SqlConnection.Prepare(commandString);
statement = new SqlStatement(sqliteStatement, this);
this.CacheStatement(commandString, statement);
}
catch (SQLiteException exception)
{
SqlTypeHelper.ThrowDatabaseException(exception);
}
}
// mark this statement in use so we cannot remove it from the statement cache
statement.InUse = true;
// cached statements needs to be reset to start the execution from the begining
// it is expected that once a result has been consumed the DatabaseResult is disposed reseting the statement
// reset here handles the case of a bogus application code that did not dispose its database result
try
{
statement.SqliteStatement.Reset();
}
catch (SQLiteException exception)
{
SqlTypeHelper.ThrowDatabaseException(exception);
}
return(statement);
}
public DataTable StokOrtalamaAlisSatisFiyatlariRaporu(string subeKodu, string GCKod, string stokKodu, DateTime? startDate, DateTime? finishDate) {
IDbConnection con = Session.Connection;
IDbCommand cmd = con.CreateCommand();
StringBuilder query = new StringBuilder();
query.AppendFormat(@" SELECT st.STOK_KODU StokKodu,st.STOK_ADI StokIsmi,Miktar=sum(sh.GCMIK),OrtalamaFiyat=(
sum(sh.BIRIM_FIYAT*sh.GCMIK)/sum(sh.GCMIK)),
ToplamFiyat=sum(sh.GCMIK)*(
sum(sh.BIRIM_FIYAT*sh.GCMIK)/sum(sh.GCMIK))
FROM StokHareket sh INNER JOIN
Stok st ON sh.STOK_KODU = st.STOK_KODU
where (st.SUBE_KODU={0} or st.SubelerdeOrtak=1) and sh.GCKOD='{1}'
group by st.STOK_KODU,st.STOK_ADI ", subeKodu, GCKod);
if(startDate.HasValue && finishDate.HasValue)
query.AppendFormat(" {0} between '{1}' and '{2}' ", SqlTypeHelper.GetDate("sh.TARIH"), startDate.Value.JustDate().ToString("yyyy-MM-dd"), finishDate.Value.JustDate().ToString("yyyy-MM-dd"));
if (!string.IsNullOrEmpty(stokKodu))
query.AppendFormat(" and st.STOK_KODU='{0}'",stokKodu);
cmd.CommandText = query.ToString();
IDataReader dread = null;
DataTable dt = new DataTable();
try {
dread = cmd.ExecuteReader();
dt.Columns.AddRange(
new DataColumn[]
{
new DataColumn("StokKodu",typeof(string)),
new DataColumn("StokIsmi",typeof(string)),
new DataColumn("Miktar",typeof(double)),
new DataColumn("OrtalamaFiyat",typeof(double)),
new DataColumn("ToplamFiyat",typeof(double))
}
);
while (dread.Read()) {
DataRow dr = dt.NewRow();
dr[0] = dread[0].ToStringOrEmpty();
dr[1] = dread[1].ToStringOrEmpty();
dr[2] = dread[2].ToStringOrEmpty("0");
dr[3] = dread[3].ToStringOrEmpty("0");
dr[4] = dread[4].ToStringOrEmpty("0");
dt.Rows.Add(dr);
}
} catch (Exception exc) { throw exc; } finally {
if (dread != null && !dread.IsClosed)
dread.Close();
}
return dt;
}
/// <summary>
/// Opens the connection on the database.
/// </summary>
/// <remarks>This operation must be performed on the connection before any other action can be executed using the connection.
/// The connection should be disposed calling <see cref="Dispose"/> on this object.</remarks>
public void Open()
{
Guid correlationId = Guid.NewGuid();
RetailLogger.Log.CrtDataOpenConnectionStart(correlationId, this.connectionStringForTracing);
try
{
this.SqlConnection.Open();
RetailLogger.Log.CrtDataOpenConnectionEnd(correlationId);
}
catch (SqlException sqlException)
{
RetailLogger.Log.CrtDataOpenConnectionFailure(correlationId, sqlException);
SqlTypeHelper.HandleException(sqlException);
}
}
public IResultReader <TValue> AddField <TValue>(QueryExpression queryExpression)
{
if (SqlTypeHelper.GetDataType(typeof(TValue)) == null)
{
throw new InvalidOperationException("Complex projection types aren't currently supported.");
}
var alias = $"__AutoAlias_{_projectionExpressions.Count}";
_projectionExpressions.Add(
alias,
new AliasExpression(
queryExpression,
alias)
);
return(new ValueReader <TValue>(alias));
}
/// <summary>
/// Disposes the connection.
/// </summary>
public void Dispose()
{
if (this.SqlConnection == null)
{
return;
}
try
{
this.SqlConnection.Close();
this.SqlConnection.Dispose();
this.SqlConnection = null;
}
catch (SqlException sqlException)
{
SqlTypeHelper.HandleException(sqlException);
}
}
protected ViewIntersectionField(
string fieldName, bool canRead, bool canWrite, Type fieldDataType, TypeModel declaringTypeModel,
PropertyInfoField originPropertyField
)
{
FieldName = fieldName;
CanRead = canRead;
CanWrite = canWrite;
FieldDataType = fieldDataType;
DeclaringTypeModel = declaringTypeModel;
OriginPropertyField = originPropertyField;
if (SqlTypeHelper.GetDataType(fieldDataType) == null)
{
SelfModel = ViewIntersectionModel.FromTypeModel(
TypeModel.GetModelOf(fieldDataType)
);
}
}
public void Apply(BuildContext context, BuildStep buildStep, SqlEntityConfiguration sqlEntityConfiguration,
SqlDataModelBuilder sqlDataModelBuilder)
{
if (buildStep != BuildStep.DefineFields)
{
return;
}
var typeModel = TypeModel.GetModelOf(sqlEntityConfiguration.EntityType);
ModelFields(typeModel.Fields, new string[0]);
void ModelFields(IReadOnlyList <PropertyField> fields, string[] parentPath)
{
foreach (var field in fields)
{
var fieldGraphPath = new FieldGraphPath <PropertyField>(
parentPath.Concat(new[] { field.FieldName }).ToArray(),
field
);
if (FieldAlreadyModelled(sqlDataModelBuilder.ModelledFields, fieldGraphPath))
{
continue;
}
if (SqlTypeHelper.GetDataType(field.FieldType.Type) == null)
{
// not an SQL primitive type, if it's not defined in builder auto-config sub properties
if (IsRegisteredEntity(context, field.FieldType.Type))
{
continue;
}
ModelFields(field.FieldModel.Fields, fieldGraphPath.Path);
}
else
{
sqlDataModelBuilder.ModelledFields.Add(CreateFieldConfiguration(field, fieldGraphPath));
}
}
}
}
public IModelNode VisitModel(IDataModel model)
{
var entityTypeModel = model as TypeModel;
if (entityTypeModel == null)
{
return(null);
}
var fields = new List <SqlStorageField <TEntity> >();
foreach (var field in model.Fields)
{
_currentPath.Add(field.FieldName);
var stateNode = (ModelStateNode)Visit(field);
var sqlEntityField = stateNode.TransformedNode as SqlStorageField <TEntity>;
if (sqlEntityField != null)
{
fields.Add(sqlEntityField);
}
if (stateNode.EntityModelState != null)
{
_modelStateStack.Push(stateNode.EntityModelState);
}
if (SqlTypeHelper.GetDataType(field.FieldType.Type) == null)
{
var subModel = Visit(field.FieldModel) as SqlStorageModel <TEntity>;
fields.AddRange(subModel.Fields);
}
if (stateNode.EntityModelState != null)
{
_modelStateStack.Pop();
}
_currentPath.RemoveAt(_currentPath.Count - 1);
}
return(new SqlStorageModel <TEntity>(fields, _sqlEntityConfiguration.DefaultTableName));
}
public IResultReader <TProperty> AddField <TProperty>(Expression <Func <T, TProperty> > projection)
{
if (SqlTypeHelper.GetDataType(typeof(TProperty)) == null)
{
throw new InvalidOperationException("Complex projection types aren't currently supported.");
}
var expressionResult = ExpressionConverter.Convert(projection);
var alias = $"__AutoAlias_{_projectionExpressions.Count}";
_projectionExpressions.Add(
alias,
new AliasExpression(
expressionResult.QueryExpression,
alias)
);
_requiredJoins.AddJoins(expressionResult.RequiredJoins);
return(new ValueReader <TProperty>(alias));
}
/// <summary>
/// Bind parameters to the statement.
/// </summary>
/// <param name="statement">The statement to bind the parameters to.</param>
private void BindParameters(ISQLiteStatement statement)
{
string currentParameterKey = string.Empty;
statement.ClearBindings();
try
{
foreach (var parameter in this.Parameters)
{
currentParameterKey = parameter.Key;
object sqlParameterValue = SqlTypeHelper.ConvertManagedValueToSqlValue(parameter.Value);
statement.Bind(currentParameterKey, sqlParameterValue);
}
}
catch (SQLiteException ex)
{
string message = string.Format("An error occurred when binding parameter '{0}'. Check if parameter is present in the query text. See inner exception for details.", currentParameterKey);
throw new SQLiteException(message, ex);
}
}
/// <summary>
/// Opens the connection on the database.
/// </summary>
/// <remarks>This operation must be performed on the connection before any other action can be executed using the connection.
/// The connection should be disposed calling <see cref="Dispose()"/> on this object.</remarks>
public void Open()
{
if (this.isDisposed)
{
throw new ObjectDisposedException(typeof(DatabaseConnection).FullName, "Cannot call Open() on a disposed connection.");
}
if (this.SqlConnection == null)
{
SqliteConnectionStringBuilder connectionString = new SqliteConnectionStringBuilder(this.ConnectionString);
try
{
this.SqlConnection = new SQLiteConnection(connectionString.MainDatabase);
// attach any other databases
this.AttachDatabase(connectionString.AttachedDatabases);
// store temp tables in memory
this.ExecuteSqlCommand("pragma temp_store=2;");
this.ExecuteSqlCommand(string.Format("pragma busy_timeout={0};", this.busyTimeout));
}
catch (DatabaseException)
{
this.DiposeSqliteConnection();
throw;
}
catch (SQLiteException exception)
{
this.DiposeSqliteConnection();
SqlTypeHelper.ThrowDatabaseException(exception);
}
catch (Exception exception)
{
this.DiposeSqliteConnection();
throw new DatabaseException("Error when opening the connection.", exception);
}
}
}
private async static Task <string> ExecuteTypeSqlCommand(
DbContext context,
string tableName,
IDictionary <string, Type> idFields,
IDictionary <string, Type> fieldsToUpdate)
{
using (var command = context.Database.Connection.CreateCommand())
{
var typeName = GetUniqueTypeName(tableName, fieldsToUpdate);
var idFieldsString =
string.Join(",", idFields.Select(f => $"{f.Key} {SqlTypeHelper.GetDbType(f.Value).ToString()}"));
var fieldsToUpdateBuilder = new StringBuilder();
foreach (var fieldToUpdate in fieldsToUpdate)
{
var sqlType = SqlTypeHelper.GetDbType(fieldToUpdate.Value);
var sqlTypeString = sqlType.ToString();
if (sqlType == SqlDbType.NVarChar)
{
sqlTypeString += "(MAX)";
}
fieldsToUpdateBuilder.Append($"{fieldToUpdate.Key} {sqlTypeString},");
}
command.CommandType = CommandType.Text;
command.CommandText = string.Format(
GenericTypeCreateSQL,
typeName,
idFieldsString,
fieldsToUpdateBuilder.ToString(0, fieldsToUpdateBuilder.Length - 1));
await command.ExecuteNonQueryAsync();
return(typeName);
}
}
/// <summary>
/// Gets system data table contains schema definition only.
/// </summary>
/// <param name="connection">The SQL connection object.</param>
/// <param name="dataTable">The object as a <see cref="Data.Types.DataTable"/>.</param>
/// <returns>The system data table contains schema definition.</returns>
private static System.Data.DataTable GetConvertedSystemDataTable(SqlConnection connection, DataTable dataTable)
{
if (cachedEmptySystemTables.ContainsKey(dataTable.TableName))
{
return(cachedEmptySystemTables[dataTable.TableName].Clone());
}
System.Data.DataTable systemDataTable;
using (var command = connection.CreateCommand())
{
systemDataTable = new System.Data.DataTable(dataTable.TableName);
systemDataTable.Locale = CultureInfo.InvariantCulture;
command.Parameters.AddWithValue("@schemaName", SqlServerDatabaseProvider.CrtDatabaseSchemaName);
command.Parameters.AddWithValue("@tableName", systemDataTable.TableName);
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM [crt].[GetUdttColumns](@schemaName, @tableName)";
using (var dataReader = command.ExecuteReader())
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
var type = SqlTypeHelper.GetClrType((string)dataReader["TABLE_TYPE_COL_DATATYPE"]);
var name = (string)dataReader["TABLE_TYPE_COL_NAME"];
var dataColumn = new System.Data.DataColumn(name, type);
systemDataTable.Columns.Add(dataColumn);
}
}
}
}
cachedEmptySystemTables[dataTable.TableName] = systemDataTable;
return(systemDataTable.Clone());
}
/// <summary>
/// Gets table schema info for a table in SQLite databases.
/// </summary>
/// <param name="connection">Connection to SQLite databases.</param>
/// <param name="tableName">The SQLite table name.</param>
/// <returns><c>SqliteTableSchema</c> object.</returns>
public SqliteTableSchema GetTableSchemaInfo(IDatabaseConnection connection, string tableName)
{
this.ValidateTableExistence(connection, tableName);
const int IndexOfName = 1, IndexOfType = 2;
Dictionary <string, SqliteTableColumn> tableColumns = new Dictionary <string, SqliteTableColumn>();
string queryString = string.Format("PRAGMA table_info({0});", tableName);
using (var result = this.ExecuteQuery(connection, new SqlQuery(queryString)))
{
while (result.Read())
{
string columnName = result.GetValue <string>(IndexOfName).ToUpperInvariant();
SqlType sqlType = SqlTypeHelper.ConvertStringToSqlType(result.GetValue <string>(IndexOfType));
Type managedType = SqlTypeHelper.ConvertSqlTypeToManagedType(sqlType);
tableColumns.Add(columnName, new SqliteTableColumn(columnName, sqlType, managedType));
}
}
return(new SqliteTableSchema(tableName, tableColumns));
}
public IDatabaseResult ExecuteQuery(IDatabaseConnection connection, IDatabaseQuery query)
{
ThrowIf.Null(query, "query");
SqlDataReader sqlDataReader = null;
Guid dataAccessInstrumentationId = Guid.NewGuid();
using (ChannelDbQueryBoundaryPerfContext perfContext = new ChannelDbQueryBoundaryPerfContext())
{
string methodName = GetCallerName();
string queryString = query.BuildQuery(this.GetDatabaseQueryBuilder()) ?? string.Empty;
string parameterList = string.Join(", ", query.Parameters.Select(p => string.Format("{0}={1}", p.Key, p.Value)));
// get the string between the "FROM" and next whitespace
Match match = FromClauseRegex.Match(queryString);
// when match is not found, take the first FromClauseNonMatchLengthLimit characters or whatever is available
string fromClause = match.Success
? string.Format(
"[{0}].[{1}]",
match.Groups[SchemaGroupName].Success ? match.Groups[SchemaGroupName].Value : DefaultSchemaName,
match.Groups[ObjectGroupName].Success ? match.Groups[ObjectGroupName].Value : string.Empty)
: queryString.Substring(0, Math.Min(FromClauseNonMatchLengthLimit, queryString.Length));
RetailLogger.Log.CrtDataAccessExecuteQueryStarted(fromClause, dataAccessInstrumentationId);
DatabaseConnection databaseConnection = GetDatabaseConnection(connection);
SqlConnection sqlConnection = databaseConnection.SqlConnection;
using (SqlCommand command = new SqlCommand(queryString, sqlConnection))
{
command.CommandTimeout = 0; // infinite wait timeout for command execution
CreateInputParameters(command, query.Parameters);
try
{
RetailLogger.Log.CrtDataAccessSqlServerSelectStarted(methodName, dataAccessInstrumentationId, queryString, parameterList, fromClause);
sqlDataReader = command.ExecuteReader();
perfContext.CallWasSuccessful();
RetailLogger.Log.CrtDataAccessSqlServerSelectFinished(
methodName,
callWasSuccessful: true,
correlationId: dataAccessInstrumentationId,
sqlQuery: queryString,
parameterList: parameterList,
fromClause: fromClause);
}
catch (SqlException sqlException)
{
RetailLogger.Log.CrtDataAccessSqlServerSelectFinished(
methodName,
callWasSuccessful: false,
correlationId: dataAccessInstrumentationId,
sqlQuery: queryString,
parameterList: parameterList,
fromClause: fromClause);
RetailLogger.Log.CrtDataAccessExecuteQueryFinished(numberOfResults: 0, wasSuccessful: false, correlationId: dataAccessInstrumentationId);
SqlTypeHelper.HandleException(sqlException);
}
}
}
DatabaseResult databaseResult = new DatabaseResult(sqlDataReader);
databaseResult.ConfigureMonitoringEvent(dataAccessInstrumentationId);
return(databaseResult);
}
public DataTable FaturaRapor(string subeKodu, FaturaIrsaliyeDurumlari fatDurum, FaturaTipDurum fatTipDurum, string cariKodu
, DateTime?dtTarBas, DateTime?dtTarBit, DateTime?dtVadeBas, DateTime?dtVadeBit)
{
StringBuilder query = new StringBuilder(
@"select f.FATIRSNO FaturaIrsaliyeNo,f.FTIRSIP FaturaIrsaliyeTuru,f.FAT_TIPI FaturaIrsaliyeTipi
,f.CARI_KODU CariKodu,f.KdvTutar,f.BrutTutar,f.SatirIsk Iskanto
,f.GenelToplam Tutar,f.TARIH Tarih,f.VadeTarih
from FatIrsUst f");
string and = "and";
string and2 = "and";
string or = "";
// public enum FTIRSIP : byte
//{
// SatisFat = 1, AlisFat, SatisIrs, AlisIrs, MusSip, SaticiSip,DirektSatis
//}
//public enum FatTipi : byte
//{
// KapaliFat = 1, AcikFat, MuhtelifFat, IadeFat,KrediKarti, ZayiIadeFat
//}
string beginParentez = "(";
query.AppendFormat(" where f.SUBE_KODU='{0}' ", subeKodu);
query.ConditionAppend(!string.IsNullOrEmpty(cariKodu), "{0} f.CARI_KODU='{1}' ".With(and, cariKodu),
() => { and = "and"; });
query.ConditionAppend(fatDurum.AlisFaturasi, " {0} {1} f.FTIRSIP=2 ".With(and2, beginParentez),
() => { or = "or"; beginParentez = ""; and2 = ""; });
query.ConditionAppend(fatDurum.SatisFaturasi, " {0} {1} {2} f.FTIRSIP=1 or f.FTIRSIP=7 ".With(and2, beginParentez, or),
() => { beginParentez = ""; });
query.ConditionAppend(fatDurum.AlisIrsaliyesi, " {0} {1} f.FTIRSIP=4 ".With(and2, beginParentez),
() => { or = "or"; beginParentez = ""; and2 = ""; });
query.ConditionAppend(fatDurum.SatisIrsaliyesi, " {0} {1} {2} f.FTIRSIP=3 ".With(and2, beginParentez, or),
() => { beginParentez = ""; });
or = "";
and2 = "and";
query.ConditionAppend((beginParentez == ""), " ) ");
beginParentez = "(";
query.ConditionAppend(fatTipDurum.Iade, " {0} {1} f.FAT_TIPI=4 ".With(and2, beginParentez),
() => { or = "or"; beginParentez = ""; and2 = ""; });
query.ConditionAppend(fatTipDurum.KrediKarti, " {0} {1} {2} f.FAT_TIPI=5 ".With(and2, beginParentez, or),
() => { or = "or"; beginParentez = ""; and2 = ""; });
query.ConditionAppend(fatTipDurum.Pesin, " {0} {1} {2} f.FAT_TIPI=1 ".With(and2, beginParentez, or),
() => { or = "or"; beginParentez = ""; and2 = ""; });
query.ConditionAppend(fatTipDurum.Vadeli, " {0} {1} {2} f.FAT_TIPI=2 ".With(and2, beginParentez, or),
() => { or = "or"; beginParentez = ""; and2 = ""; });
query.ConditionAppend((beginParentez == ""), " ) ");
if (dtTarBas.HasValue && dtTarBit.HasValue)
{
query.Append(" {0} {1} between '{2}' and '{3}' ".With(and, SqlTypeHelper.GetDate("f.TARIH"), dtTarBas.Value.JustDate().ToString("yyyy-MM-dd"), dtTarBit.Value.JustDate().ToString("yyyy-MM-dd")));
and = "and";
}
if (dtVadeBas.HasValue && dtVadeBit.HasValue)
{
query.Append(" {0} {1} between '{2}' and '{3}' ".With(and, SqlTypeHelper.GetDate("f.VadeTarih"), dtVadeBas.Value.JustDate().ToString("yyyy-MM-dd"), dtVadeBit.Value.JustDate().ToString("yyyy-MM-dd")));
and = "and";
}
//query.ConditionAppend((dtTarBas.HasValue && dtTarBit.HasValue),
// " {0} f.TARIH=between '{1}' and '{2}' ".With(and, dtTarBas.Value.ToString("yyyy-MM-dd"), dtTarBit.Value.ToString("yyyy-MM-dd"))
// , () => {and = "and"; });
//query.ConditionAppend((dtVadeBas!=null && dtVadeBit!=null) && (dtTarBas.HasValue && dtTarBit.HasValue),
// " {0} f.TARIH=between '{1}' and '{2}' ".With(and, dtTarBas.Value.ToString("yyyy-MM-dd"), dtTarBit.Value.ToString("yyyy-MM-dd")));
IDbConnection con = Session.Connection;
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = query.ToString();
IDataReader dr = null;
DataTable dt = new DataTable();
dt.Columns.AddRange(
new DataColumn[]
{
new DataColumn("FaturaIrsaliyeNo"),
new DataColumn("FaturaIrsaliyeTuru"),
new DataColumn("FaturaIrsaliyeTipi"),
new DataColumn("CariKodu", typeof(string)),
new DataColumn("KdvTutar", typeof(double)),
new DataColumn("BrutTutar", typeof(double)),
new DataColumn("Iskanto", typeof(double)),
new DataColumn("Tutar", typeof(double)),
new DataColumn("Tarih", typeof(DateTime)),
new DataColumn("VadeTarih")
});
try {
dr = cmd.ExecuteReader();
while (dr.Read())
{
DataRow dar = dt.NewRow();
dar["FaturaIrsaliyeNo"] = dr["FaturaIrsaliyeNo"].ToStringOrEmpty();
dar["FaturaIrsaliyeTuru"] = dr["FaturaIrsaliyeTuru"].ToStringOrEmpty();
dar["FaturaIrsaliyeTipi"] = dr["FaturaIrsaliyeTipi"].ToStringOrEmpty();
dar["CariKodu"] = dr["CariKodu"].ToStringOrEmpty();
dar["KdvTutar"] = dr["KdvTutar"].ToDouble();
dar["BrutTutar"] = dr["BrutTutar"].ToDouble();
dar["Iskanto"] = dr["Iskanto"].ToDouble();
dar["Tutar"] = dr["Tutar"].ToDouble();
dar["Tarih"] = dr["Tarih"].ToStringOrEmpty("");
dar["VadeTarih"] = dr["VadeTarih"].ToDate();
dt.Rows.Add(dar);
}
} catch (Exception exc) { throw exc; } finally {
if (dr != null && !dr.IsClosed)
{
dr.Close();
}
}
return(dt);
}
/// <summary>
/// Gets the value for a specific field in the current result set row.
/// </summary>
/// <param name="index">The index of the field to be read.</param>
/// <param name="valueType">The expected type of the field being read.</param>
/// <returns>The field value read.</returns>
public object GetValue(int index, System.Type valueType)
{
object sqlValue = this.Statement.SqliteStatement[index];
return(SqlTypeHelper.ConvertSqlValueToManagedValue(sqlValue, valueType));
}
void ImportData(MapConfig mapConfig, SqlConnection conn, SqlTransaction transaction, AppOptionInfo opts, string filePath)
{
var exportedData = ExportedDataModel.LoadFromFile(filePath);
var ent = mapConfig.GetEntityMap(exportedData.EntityName);
Logger.Log(LogLevel.Debug, $"[{ent.FullName}] - table: {ent.TableName} - rows: {exportedData.DataRows.Count}");
string destinationTableName = $"[{ent.SchemaName}].[{ent.TableName}]";
try
{
DataTable table = ent.CreateTable();
RazorInterpreter interpreter = new RazorInterpreter();
int count = 0;
if (opts.Merge)
{
//create table
var tableCreate = interpreter.CompileTemplate(Templates.CreateTempTable, ent);
destinationTableName = $"[{ent.SchemaName}].[#{ent.TableName}]";
ExecuteCommand(tableCreate, conn, transaction);
}
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))
{
foreach (DataColumn tableColumn in table.Columns)
{
sqlBulkCopy.ColumnMappings.Add(tableColumn.ColumnName, tableColumn.ColumnName);
}
foreach (var exportedDataDataRow in exportedData.DataRows)
{
var dataRow = table.NewRow();
foreach (var field in exportedDataDataRow)
{
var prop = ent.FindPropertyByColumnName(field.Key);
var clrType = SqlTypeHelper.GetClrType(prop.DbType, prop.IsNullable);
var column = table.Columns[field.Key];
if (field.Value == null || string.IsNullOrWhiteSpace(field.Value.ToString()))
{
dataRow[field.Key] = DBNull.Value;
}
else
{
var convertMethod = converter.GetConverterFactoryMethod(clrType);
var value = convertMethod(field.Value.ToString().Trim());
dataRow[field.Key] = value ?? DBNull.Value;
}
}
count++;
Logger.Log(LogLevel.Debug, "Adding row " + count);
table.Rows.Add(dataRow);
dataRow.AcceptChanges();
}
sqlBulkCopy.DestinationTableName = destinationTableName;
Logger.Log(LogLevel.Debug, $"Data table [{sqlBulkCopy.DestinationTableName}] loaded. Will now write to the database.");
sqlBulkCopy.WriteToServer(table);
if (opts.Merge)
{
var mergeScript = interpreter.CompileTemplate(Templates.Merge, ent);
ExecuteCommand(mergeScript, conn, transaction);
ExecuteCommand($"DROP TABLE {destinationTableName}", conn, transaction);
}
table.Clear();
}
}
catch (Exception ex)
{
Logger.Log(LogLevel.Error, $"Error trying to import data to {ent.TableName}...");
throw;
}
}
public void ExecuteStoredProcedure(
IDatabaseConnection connection,
string procedureName,
IEnumerable <KeyValuePair <string, object> > parameters,
IDictionary <string, object> outputParameters,
Action <IDatabaseResult> resultCallback,
out int?storeProcedureResultValue)
{
if (resultCallback == null)
{
throw new ArgumentNullException("resultCallback");
}
if (string.IsNullOrWhiteSpace(procedureName))
{
throw new ArgumentNullException("procedureName");
}
Guid dataAccessInstrumentationId = Guid.NewGuid();
using (ChannelDbStoredProcBoundaryPerfContext perfContext = new ChannelDbStoredProcBoundaryPerfContext())
{
string parameterList = string.Join(", ", parameters.Select(p => string.Format("{0}={1}", p.Key, p.Value)));
DatabaseConnection databaseConnection = GetDatabaseConnection(connection);
SqlConnection sqlConnection = databaseConnection.SqlConnection;
SqlDataReader reader = null;
// in case of stored procedure name doesn't contain schema prefix, we add one
if (procedureName.IndexOf(".", StringComparison.OrdinalIgnoreCase) == -1)
{
procedureName = string.Format("[{0}].{1}", CrtDatabaseSchemaName, procedureName);
}
RetailLogger.Log.CrtDataAccessExecuteStoredProcedureStarted(procedureName, dataAccessInstrumentationId);
using (SqlCommand command = new SqlCommand(procedureName, sqlConnection))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = 0; // infinite wait timeout for command execution
if (parameters != null)
{
CreateInputParameters(command, parameters);
}
if (outputParameters != null)
{
CreateOutputParameters(command, outputParameters);
}
SqlParameter returnValueParameter = AddReturnValueParameter(command);
long numberOfReads = 0;
bool success = false;
try
{
RetailLogger.Log.CrtDataAccessSqlServerStoredProcedureStarted(procedureName, parameterList, dataAccessInstrumentationId);
using (reader = command.ExecuteReader())
{
perfContext.CallWasSuccessful();
RetailLogger.Log.CrtDataAccessSqlServerStoredProcedureFinished(procedureName, parameterList, true, dataAccessInstrumentationId);
using (DatabaseResult result = new DatabaseResult(reader))
{
resultCallback(result);
numberOfReads = result.MonitoringNumberOfReads;
}
}
success = true;
}
catch (SqlException sqlException)
{
RetailLogger.Log.CrtDataAccessSqlServerStoredProcedureFinished(procedureName, parameterList, false, dataAccessInstrumentationId);
SqlTypeHelper.HandleException(sqlException);
}
finally
{
RetailLogger.Log.CrtDataAccessExecuteStoredProcedureFinished(numberOfReads, success, dataAccessInstrumentationId);
}
if (outputParameters != null)
{
ReadOutputParameters(command, outputParameters);
}
storeProcedureResultValue = (int?)returnValueParameter.Value;
}
}
}
/// <summary>
/// Gets the type of the CLR.
/// </summary>
/// <param name="dbType">Type of the db.</param>
/// <param name="isNullable">if set to <c>true</c> [is nullable].</param>
/// <returns></returns>
public virtual Type GetClrType(DbType dbType, bool isNullable)
{
return(SqlTypeHelper.GetClrType(dbType, isNullable));
}
/// <summary>
/// Formats the column for the create table statement.
/// </summary>
/// <param name="column">The column to be formatted into a string.</param>
/// <returns>The formatted string.</returns>
private static string FormatColumn(DataColumn column)
{
return(string.Format("{0} {1}", column.ColumnName, SqlTypeHelper.GetSqliteTypeNameFromManagedType(column.DataType)));
}
