protected override ChangeResult executeInternal(SQLDatabaseConnection conn)
{
if (_conditions == null || !_conditions.Any())
{
return new ChangeResult()
{
Exception = new Exception("Command without conditions.")
}
}
;
var tableName = typeof(T).Name;
StringBuilder commandBuilder = new StringBuilder($"DELETE FROM [{tableName}] WHERE [{_pkProperty.Name}] IN (SELECT [{_pkProperty.Name}] FROM [{tableName}] {base.getWhereClause()}) ");
var sqlParameters = getWhereParameters();
int commandResult = conn.ExecuteCommand(commandBuilder.ToString(), getTimeout(), sqlParameters);
return(new ChangeResult()
{
DeletedCount = commandResult, AffectedCount = commandResult
});
}
}
internal TemporaryTableCommand(string customConnectionString, SQLDatabaseConnection existentConnection, Factory factory) : base(customConnectionString, existentConnection, factory)
{
_properties = TypeDescriptor.GetProperties(typeof(T));
if (_properties == null || _properties.Count <= 0)
{
throw new Exception("Invalid class type.");
}
}
public AdoCommand(string customConnectionString, SQLDatabaseConnection existentConnection, Factory factory)
{
_customConnectionString = customConnectionString;
_connection = existentConnection;
_factory = factory;
_hadTransaction = existentConnection != null && existentConnection.Connection.State == ConnectionState.Open && existentConnection.HasTransaction();
}
protected SQLDatabaseConnection getConnection()
{
if (_connection == null)
{
_connection = _factory.GetNewConnection(_customConnectionString);
}
return(_connection);
}
protected void disposeConnection()
{
if (_connection != null && !_hadTransaction)
{
_connection.Dispose();
}
_connection = null;
}
internal RawSqlCommand(string sqlCommand, string customConnectionString, SQLDatabaseConnection existentConnection, Factory factory) : base(customConnectionString, existentConnection, factory)
{
if (string.IsNullOrWhiteSpace(sqlCommand))
{
throw new ArgumentNullException(nameof(sqlCommand));
}
_sqlCommand = sqlCommand;
}
internal BulkCommand(string customConnectionString, SQLDatabaseConnection existentConnection, Factory factory) : base(customConnectionString, existentConnection, factory)
{
_properties = TypeDescriptor.GetProperties(typeof(T));
if (_properties == null || _properties.Count <= 0)
{
throw new Exception("Invalid class type.");
}
_pkProperty = _properties.Cast <PropertyDescriptor>().FirstOrDefault(p => (p.Attributes?.Cast <Attribute>()?.Any(a => a.GetType().Equals(typeof(System.ComponentModel.DataAnnotations.KeyAttribute))) ?? false));
}
static void CacheServer()
{
SQLDatabaseConnection cnn = new SQLDatabaseConnection();
cnn.Server = "192.168.0.10";
cnn.Port = 5000;
cnn.Username = "******";
cnn.Password = "******";
cnn.Open();
if (cnn.State == ConnectionState.Open)
{
SQLDatabaseCacheServer cs = new SQLDatabaseCacheServer();
cs.Connection = cnn;
// In Cache server collections are automatically created if one does not exist.
//Add remove raw bytes with Cache Id of 101 and collection name System.String
//if trying to exchange strings or data with other programing languages use raw
cs.AddRaw("System.String", Encoding.UTF8.GetBytes("Example Text for Cache Server"), "101");
string c101 = Encoding.UTF8.GetString((byte[])cs.Get("System.String", "101")).ToString();
cs.Remove("System.String", "101");
cs.Add <string>("Example Text for Cache Server", "101");
c101 = cs.Get <string>("101");
cs.Remove <string>("101");
ApplicationUser u = new ApplicationUser();
u.Id = 1;
u.Name = "SQLUser";
u.Job = "SQL Developer";
string id = cs.Add <ApplicationUser>(u);
ApplicationUser user = cs.Get <ApplicationUser>(id);
Console.WriteLine("Id \t {0} ", user.Id);
Console.WriteLine("Name \t {0} ", user.Name);
Console.WriteLine("Job \t {0} ", user.Job);
List <string> collectionList = cs.CollectionList();
foreach (string collectionName in collectionList)
{
Console.WriteLine("Collection : {0}", collectionName);
}
cs.DropCollection("System.String");
cs.DropCollection <ApplicationUser>();
}
cnn.Close();
cnn.Dispose();
Console.WriteLine("CacheServer() Completed");
}
private GenericResult <T> executeInternal <T>(Func <SQLDatabaseConnection, T> action, bool keepConnectionOpen = false)
{
try
{
if (_parameters != null)
{
for (int i = 0; i < _parameters.Count; i++)
{
if (_parameters[i].SqlDbType.In(System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NChar, System.Data.SqlDbType.NText, System.Data.SqlDbType.Text))
{
_parameters[i].SqlDbType = System.Data.SqlDbType.Char;
}
}
}
SQLDatabaseConnection connection = base.getConnection();
var originalConnectionState = connection.Connection.State;
var hadTransaction = connection.HasTransaction();
T result;
try
{
if (originalConnectionState != System.Data.ConnectionState.Open)
{
connection.Connection.Open();
}
result = action(connection);
}
finally
{
if ((!hadTransaction) && (!keepConnectionOpen || originalConnectionState != System.Data.ConnectionState.Open))
{
connection.Dispose();
}
}
return(new GenericResult <T>()
{
Result = result
});
}
catch (Exception ex)
{
var fullException = new Exception("An error has occurred while executing the command.", new Exception($"Error in the execution of the command: {(_sqlCommand ?? string.Empty)}", ex));
_factory._logger.LogError(fullException);
return(new GenericResult <T>()
{
Exception = fullException
});
}
}
protected override SelectResult <TResult> executeInternal(SQLDatabaseConnection conn)
{
var tableName = typeof(T).Name;
bool isPaging = _pageRequest != null && _pageRequest.OrderByFields.Length > 0 && _pageRequest.Take > 0;
StringBuilder commandBuilder = new StringBuilder($" SELECT");
if (isPaging)
{
commandBuilder.Append($" ROW_NUMBER() OVER ( ORDER BY { String.Join(", ", _pageRequest.OrderByFields)} ) AS [RowNum], ");
}
else if (_topCount.HasValue && _topCount.Value >= 0)
{
commandBuilder.Append($" TOP {_topCount.Value.ToString()}");
}
commandBuilder.Append($" {getSelectFieldsCommand()} ");
if (!isPaging && !String.IsNullOrWhiteSpace(_temporaryTableName))
{
commandBuilder.Append($" INTO {_temporaryTableName} ");
}
commandBuilder.Append($" FROM {tableName} {base.getWhereClause()} ");
if (!isPaging && _topCount.HasValue && _topCount.Value > 0 && _orderByFields != null && _orderByFields.Length > 0)
{
commandBuilder.Append(" ORDER BY ");
for (int i = 0; i < _orderByFields.Length; i++)
{
string fieldName = ((_orderByFields[i].Body as MemberExpression) ?? ((_orderByFields[i].Body as UnaryExpression)?.Operand as MemberExpression))?.Member.Name;
if (String.IsNullOrWhiteSpace(fieldName))
{
break;
}
if (i > 0)
{
commandBuilder.Append(", ");
}
commandBuilder.Append(fieldName);
}
}
string commandText = commandBuilder.ToString();
if (isPaging)
{
commandText = $" SELECT * {(String.IsNullOrWhiteSpace(_temporaryTableName) ? string.Empty : $"INTO {_temporaryTableName}")} FROM ( {commandText} ) OT1 WHERE OT1.[RowNum] > {_pageRequest.Skip} AND OT1.[RowNum] <= {_pageRequest.Take + _pageRequest.Skip} ORDER BY OT1.[RowNum] ";
protected override ChangeResult executeInternal(SQLDatabaseConnection conn)
{
if (_conditions == null || !_conditions.Any())
{
return new ChangeResult()
{
Exception = new Exception("Command without conditions.")
}
}
;
if (_specificFields == null || _specificFields.Count <= 0)
{
return new ChangeResult()
{
Exception = new Exception("Command without set's.")
}
}
;
var tableName = typeof(T).Name;
StringBuilder builder = new StringBuilder();
var parameters = SqlCommandBuilder.BuildUpdateSetStatement <T>(ref builder, (from f in _specificFields select f.Value).ToArray(), _pkProperty, _properties);
builder.Append($" WHERE [{_pkProperty.Name}] IN (SELECT [{_pkProperty.Name}] FROM [{tableName}] {base.getWhereClause()}) ");
var sqlParameters = getWhereParameters() ?? new Microsoft.Data.SqlClient.SqlParameter[0];
var additionalParameters = parameters.Select(p => new Microsoft.Data.SqlClient.SqlParameter(p.Item1, SqlCommandBuilder.GetSqlRawValue(p.Item3, p.Item2))
{
SqlDbType = MicroORM.Core.SqlCommandBuilder.GetSqlFieldType(p.Item3)
}).ToList();
if (additionalParameters.Count > 0)
{
sqlParameters = sqlParameters.Concat(additionalParameters).ToArray();
}
int commandResult = conn.ExecuteCommand(builder.ToString(), getTimeout(), sqlParameters);
ResetChanges();
return(new ChangeResult()
{
UpdatedCount = commandResult, AffectedCount = commandResult
});
}
}
}
static void OpenCloseConnection()
{
SQLDatabaseConnection cnn = new SQLDatabaseConnection();
cnn.Server = "192.168.0.10";
cnn.Port = 5000;
cnn.Username = "******";
cnn.Password = "******";
cnn.Open();
Console.WriteLine(cnn.State);
cnn.Close();
cnn.Dispose();
Console.WriteLine("OpenCloseConnection() Completed");
}
protected override ExistsResult executeInternal(SQLDatabaseConnection conn)
{
var tableName = typeof(T).Name;
StringBuilder commandBuilder = new StringBuilder($"SELECT TOP 1 1 FROM {tableName} {base.getWhereClause()} ");
var sqlParameters = getWhereParameters();
object commandResult = null;
commandResult = conn.ExecuteScalarCommand(commandBuilder.ToString(), getTimeout(), sqlParameters);
return(new ExistsResult()
{
Result = commandResult != null && commandResult.Equals(1)
});
}
private static void InsertUserIntoCTNReportDatabase(DMIUser user)
{
SqlConnection myConnection = SQLDatabaseConnection.ConnectTo("QA-AZUKS-DMI2", "CTN_Report");
myConnection.Open();
//generate SQL insert statement parts
string insertInto = "INSERT INTO [User]";
string columnNames = "(UserId,FirstName,Surname,ClientId,Username,Email,GlobalAdminType,UserMustChangePasswordOnLogin,Deleted,Disabled)";
string columnValues = $"VALUES ('{user.userId}','{user.firstname}', '{user.lastname}', '2349','{user.username}','{user.email}',0,0,0,0)";
//construct sql statement
string command = insertInto + columnNames + columnValues;
SqlCommand myCommand = new SqlCommand(command, myConnection);
myCommand.ExecuteNonQuery();
}
public static void InsertRightsFor(ProductType product, UserClaimAbility userClaimAbility, DMIUser user)
{
string rightId = FindRightIdFor(product, userClaimAbility);
string userId = user.userId;
string insertInto = "INSERT INTO[CTN_Report].[dbo].[UserRight] ";
string columnNames = "(UserId, RightId) ";
string columnValues = $"VALUES ('{userId}', '{rightId}')";
string command = insertInto + columnNames + columnValues;
SqlConnection myConnection = SQLDatabaseConnection.ConnectTo("QA-AZUKS-DMI2", "CTN_Report");
myConnection.Open();
SqlCommand sqlCommand = new SqlCommand(command, myConnection);
sqlCommand.ExecuteNonQuery();
}
protected override ChangeResult executeInternal(SQLDatabaseConnection conn)
{
int insertedCount = _insertDataTable.Rows.Count;
var sqlConn = conn.Connection as SqlConnection;
var sqlTran = conn.GetTransaction() as SqlTransaction;
int batchSize = _properties.Count > 64 ? 5000 : 10000;
var bulkCopy = sqlTran != null ? new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.Default, sqlTran) : new Microsoft.Data.SqlClient.SqlBulkCopy(sqlConn);
for (int i = 0; i < _properties.Count; i++)
{
var prop = _properties[i];
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(prop.Name, prop.Name));
}
bulkCopy.DestinationTableName = getTableName();
bulkCopy.BulkCopyTimeout = 120;
int currentRow = 0;
while (currentRow < insertedCount)
{
var batchRows = _insertDataTable.Rows.Cast <DataRow>().Skip(currentRow).Take(batchSize).ToArray();
bulkCopy.WriteToServer(batchRows);
_cancelRetry = true;
currentRow += batchSize;
}
bulkCopy.Close();
_insertDataTable.Clear();
return(new ChangeResult()
{
InsertedCount = insertedCount, AffectedCount = insertedCount
});
}
public void SetAgeOfPassword(int days)
{
var dateInPast = DateTime.Today.AddDays(-days);
SqlConnection myConnection = SQLDatabaseConnection.ConnectTo(Config.Environment.SqlServer, "CAL_IdentityUser");
myConnection.Open();
//generate SQL insert statement parts
string updateTableName = "UPDATE [CAL_IdentityUser].[dbo].[AspNetUsers]";
string setPasswordResetDate = $"SET LastPasswordReset = '{dateInPast}'";
string forUser = $"WHERE Id = '{this.userId}'";
//construct sql statement
string command = updateTableName + setPasswordResetDate + forUser;
SqlCommand myCommand = new SqlCommand(command, myConnection);
myCommand.ExecuteNonQuery();
}
private bool disposedValue = false; // To detect redundant calls
protected virtual void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
// TODO: dispose managed state (managed objects).
if (_connection != null && !_hadTransaction)
{
_connection.Dispose();
}
}
// TODO: free unmanaged resources (unmanaged objects) and override a finalizer below.
// TODO: set large fields to null.
_connection = null;
disposedValue = true;
}
}
public static string FindRightIdFor(ProductType product, UserClaimAbility userClaimAbility)
{
// Connect to sql
SqlConnection myConnection = SQLDatabaseConnection.ConnectTo("QA-AZUKS-DMI2", "CTN_Report");
myConnection.Open();
//string id = "";
string select = "SELECT [CTN_Report].[dbo].[Right].[RightId] ";
string from = "FROM [CTN_Report].[dbo].[Module] ";
string innerJoin = "INNER JOIN[CTN_Report].[dbo].[Right] ON [CTN_Report].[dbo].[Module].[ModuleId] = [CTN_Report].[dbo].[Right].[ModuleId] ";
string whereProductIs = $"WHERE[CTN_Report].[dbo].[Module].[Name] = '{product.ToString()}' ";
string whereAbilityIs = $"AND [CTN_Report].[dbo].[Right].[Name] = '{userClaimAbility.ToString()}'";
string command = select + from + innerJoin + whereProductIs + whereAbilityIs;
SqlCommand myCommand = new SqlCommand(command, myConnection);
return(SQLRead.FromDatabase(command, "RightId", myConnection));
}
internal ChangeCommand(string customConnectionString, SQLDatabaseConnection existentConnection, Factory factory) : base(customConnectionString, existentConnection, factory)
{
_properties = TypeDescriptor.GetProperties(typeof(T));
if (_properties == null || _properties.Count <= 0)
{
throw new Exception("Invalid class type.");
}
_pkProperty = _properties.Cast <PropertyDescriptor>().FirstOrDefault(p => (p.Attributes?.Cast <Attribute>()?.Any(a => a.GetType().Equals(typeof(System.ComponentModel.DataAnnotations.KeyAttribute))) ?? false));
if (_pkProperty == null)
{
throw new Exception("It is not possible to update a table without a defined primary key.");
}
_isPkIdentiy = _pkProperty.Attributes.OfType <System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedAttribute>()?.FirstOrDefault()?.DatabaseGeneratedOption == System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity;
if (_isPkIdentiy)
{
_insertProperties = new PropertyDescriptorCollection(_properties.Cast <PropertyDescriptor>().Except(new[] { _pkProperty }).ToArray());
}
else
{
_insertProperties = _properties;
}
_saveLog = _factory._logger.IsModelSavingLog(typeof(T));
string logFieldName = _factory._logger.GetModelLogField(typeof(T));
if (string.IsNullOrWhiteSpace(logFieldName))
{
logFieldName = _pkProperty.Name;
}
_logField = _properties.Cast <PropertyDescriptor>().FirstOrDefault(p => p.Name.ToUpper() == logFieldName.ToUpper());
if (_logField == null)
{
_logField = _pkProperty;
}
}
static void CreateDropDatabase()
{
SQLDatabaseConnection cnn = new SQLDatabaseConnection();
cnn.Server = "192.168.0.10";
cnn.Port = 5000;
cnn.Username = "******";
cnn.Password = "******";
cnn.Open();
if (cnn.State == ConnectionState.Open)
{
SQLDatabaseCommand cmd = new SQLDatabaseCommand(cnn);
SQLDatabaseUtility u = new SQLDatabaseUtility();
u.Command = cmd;
u.CreateDatabase("TestDatabase");
u.DropDatabase("TestDatabase");
}
cnn.Close();
cnn.Dispose();
Console.WriteLine("CreateDropDatabase() Completed");
}
protected override CountResult executeInternal(SQLDatabaseConnection conn)
{
var tableName = typeof(T).Name;
StringBuilder commandBuilder = new StringBuilder($"SELECT COUNT(*) FROM {tableName} {base.getWhereClause()} ");
var sqlParameters = getWhereParameters();
object commandResult = null;
commandResult = conn.ExecuteScalarCommand(commandBuilder.ToString(), getTimeout(), sqlParameters);
long count;
if (!long.TryParse(commandResult?.ToString() ?? "0", out count))
{
count = 0;
}
return(new CountResult()
{
Result = count
});
}
public int ImportTable(string FilePathAndName, bool IsFirstLineHeader)
{
int _row_count = 0;
List <string> _HeaderColumns = new List <string>();
using (CsvReader = new CsvFileReader(FilePathAndName, Encoding.UTF8))
{
CsvReader.OnEmptyLine = BlankLine.SkipEntireLine;
CsvReader.MaximumLines = 1; //Just read one line to get the header info and/or number of columns.
while (CsvReader.ReadLine())
{
int ColumnCount = 0;
foreach (string Field in CsvReader.Fields)
{
ColumnCount++;
if (IsFirstLineHeader)
{
_HeaderColumns.Add(Field);
}
else
{
_HeaderColumns.Add("CsvColumn" + ColumnCount);
}
}
break;
}
}
if (_HeaderColumns.Count == 0)
{
throw new Exception("Columns are required, check the function parameters.");
}
if (SQLDatabaseConnection.State != ConnectionState.Open)
{
throw new Exception("A valid and open connection is required.");
}
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(SQLDatabaseConnection))
{
if (SQLDatabaseTransaction != null)
{
cmd.Transaction = SQLDatabaseTransaction;
}
//cmd.CommandText = string.Format("DROP TABLE IF EXISTS [{0}].[{1}]", SchemaName, TableName);
//cmd.ExecuteNonQuery();
System.Data.DataTable dt = SQLDatabaseConnection.GetSchema("Columns", new string[] { string.Format("[{0}].[{1}]", SchemaName, TableName) });
if (dt.Rows.Count != 6) //Table does not exists other wise if 6 rows then table have definition
{
cmd.CommandText = string.Format("CREATE TABLE IF NOT EXISTS [{0}].[{1}] (", SchemaName, TableName);
foreach (string ColumnName in _HeaderColumns)
{
cmd.CommandText += ColumnName + " None,"; //The DataType none is used since we do not know if all rows have same datatype
}
cmd.CommandText = cmd.CommandText.Substring(0, cmd.CommandText.Length - 1); //Remove the last comma
cmd.CommandText += ");";
cmd.ExecuteNonQuery(); // Create table
dt = SQLDatabaseConnection.GetSchema("Columns", new string[] { string.Format("[{0}].[{1}]", SchemaName, TableName) });
if (dt.Rows.Count != 6)
{
throw new Exception("Unable to create or find table.");
}
}
// Sanity check if number of columns in CSV and table are equal
if (dt.Rows.Count != _HeaderColumns.Count)
{
throw new Exception("Number of columns in CSV should be same as number of columns in the table");
}
// Start of code block to generate INSERT statement.
cmd.CommandText = string.Format("INSERT INTO {0}.[{1}] VALUES (", SchemaName, TableName);
int ParamCount = 0;
foreach (string ColumnName in _HeaderColumns)
{
ParamCount++;
cmd.CommandText += string.Format("@param{0},", ParamCount); //The DataType none is used since we do not know if all rows have same datatype
}
cmd.CommandText = cmd.CommandText.Substring(0, cmd.CommandText.Length - 1); //Remove the last comma
cmd.CommandText += ");";
// Add parameters
ParamCount = 0;
foreach (string ColumnName in _HeaderColumns)
{
ParamCount++;
cmd.Parameters.Add(string.Format("@param{0}", ParamCount)); //The DataType none is used since we do not know if all rows have same datatype
}
// End of code block to generate INSERT statement.
//Read CSV once insert statement has been created.
using (CsvReader = new CsvFileReader(FilePathAndName, Encoding.UTF8))
{
CsvReader.OnEmptyLine = BlankLine.SkipEntireLine;
//Skip the header line.
if (IsFirstLineHeader)
{
CsvReader.SkipLines = 1;
}
while (CsvReader.ReadLine())
{
int CsvColumnCount = 0;
foreach (string FieldValue in CsvReader.Fields)
{
CsvColumnCount++;
cmd.Parameters["@param" + CsvColumnCount].Value = FieldValue; //Assign File Column to parameter
}
cmd.ExecuteNonQuery();
_row_count++; // Count inserted rows.
}
}
}
return(_row_count);
}
public long ImportTable(string filePathAndName, RootPathObject rootPath, bool downloadToLocal = false, long limit = long.MaxValue)
{
var rowCount = 0;
List <string> headerColumns = new List <string>();
using (DelimitedReader = new DelimitedFileReader(filePathAndName, rootPath, false))
{
DelimitedReader.Delimiter = Delimiter;
DelimitedReader.OnEmptyLine = BlankLine.SkipEntireLine;
DelimitedReader.MaximumLines = 1; //Just read one line to get the header info and/or number of columns.
while (DelimitedReader.ReadLine())
{
int columnCount = 0;
foreach (string field in DelimitedReader.Fields)
{
columnCount++;
if (rootPath.ModeSettings.DelimitedSettings.HasHeader)
{
if (headerColumns.Contains(field))
{
headerColumns.Add($"{field}_DUPLICATE_{columnCount}");
}
else
{
headerColumns.Add(field);
}
}
else
{
headerColumns.Add("Column" + columnCount);
}
}
break;
}
}
if (headerColumns.Count == 0)
{
throw new Exception("Columns are required, check the function parameters.");
}
Logger.Debug($"Headers: {JsonConvert.SerializeObject(headerColumns, Formatting.Indented)}");
if (SQLDatabaseConnection.State != ConnectionState.Open)
{
throw new Exception("A valid and open connection is required.");
}
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(SQLDatabaseConnection))
{
if (SQLDatabaseTransaction != null)
{
cmd.Transaction = SQLDatabaseTransaction;
}
// cmd.CommandText = $"DROP TABLE IF EXISTS [{SchemaName}].[{TableName}]";
// cmd.ExecuteNonQuery();
cmd.CommandText = $"CREATE TABLE IF NOT EXISTS [{SchemaName}].[{TableName}] (";
foreach (var columnName in headerColumns)
{
cmd.CommandText +=
$"[{columnName}]" +
$" VARCHAR({int.MaxValue}),"; //The DataType none is used since we do not know if all rows have same datatype
}
cmd.CommandText = cmd.CommandText.Substring(0, cmd.CommandText.Length - 1); //Remove the last comma
cmd.CommandText += ");";
Logger.Debug($"Create table SQL: {cmd.CommandText}");
cmd.ExecuteNonQuery(); // Create table
var dt = SQLDatabaseConnection.GetSchema("Columns", new string[] { $"[{SchemaName}].[{TableName}]" });
// Sanity check if number of columns in CSV and table are equal
if (dt.Rows.Count != headerColumns.Count)
{
throw new Exception("Number of columns in CSV should be same as number of columns in the table");
}
// Start of code block to generate INSERT statement.
cmd.CommandText = $"INSERT INTO {SchemaName}.[{TableName}] VALUES (";
int paramCount = 0;
foreach (string columnName in headerColumns)
{
paramCount++;
cmd.CommandText +=
$"@param{paramCount},"; //The DataType none is used since we do not know if all rows have same datatype
}
cmd.CommandText = cmd.CommandText.Substring(0, cmd.CommandText.Length - 1); //Remove the last comma
cmd.CommandText += ");";
Logger.Debug($"Insert Row SQL: {cmd.CommandText}");
// Add parameters
paramCount = 0;
foreach (string columnName in headerColumns)
{
paramCount++;
cmd.Parameters.Add(
$"@param{paramCount}"); //The DataType none is used since we do not know if all rows have same datatype
}
// End of code block to generate INSERT statement.
Logger.Debug($"Reading delimited file {filePathAndName}");
//Read CSV once insert statement has been created.
using (DelimitedReader = new DelimitedFileReader(filePathAndName, rootPath, downloadToLocal))
{
DelimitedReader.Delimiter = Delimiter;
DelimitedReader.OnEmptyLine = BlankLine.SkipEntireLine;
DelimitedReader.SkipLines = rootPath.SkipLines;
//Skip the header line.
if (rootPath.ModeSettings.DelimitedSettings.HasHeader)
{
DelimitedReader.SkipLines += 1;
}
var trans = SQLDatabaseConnection.BeginTransaction();
try
{
while (DelimitedReader.ReadLine() && rowCount < limit)
{
int csvColumnCount = 0;
foreach (string fieldValue in DelimitedReader.Fields)
{
csvColumnCount++;
if (cmd.Parameters.IndexOf("@param" + csvColumnCount) != -1)
{
cmd.Parameters["@param" + csvColumnCount].Value =
fieldValue; //Assign File Column to parameter
}
}
cmd.ExecuteNonQuery();
rowCount++; // Count inserted rows.
// commit every 1000 rows
if (rowCount % 1000 == 0)
{
trans.Commit();
trans = SQLDatabaseConnection.BeginTransaction();
}
}
// commit any pending inserts
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
Logger.Error(e, e.Message);
throw;
}
}
}
return(rowCount);
}
public long ExportTable(string filePathAndName, bool appendToFile = false)
{
SQLDatabaseConnection.Open();
long rowCount = 0;
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(SQLDatabaseConnection))
{
if (SQLDatabaseTransaction != null)
{
cmd.Transaction = SQLDatabaseTransaction;
}
cmd.CommandText = $@"SELECT * FROM [{SchemaName}].[{TableName}]";
using (DelimitedWriter = new DelimitedFileWriter(filePathAndName, appendToFile, Encoding.UTF8))
{
// set variables
DelimitedWriter.Delimiter = Delimiter;
DelimitedWriter.QuoteWrap = ReplicationFormData.QuoteWrap;
DelimitedWriter.NullValue = ReplicationFormData.NullValue;
// write custom header to file if not empty
if (!string.IsNullOrWhiteSpace(ReplicationFormData.CustomHeader))
{
DelimitedWriter.WriteLineToFile(ReplicationFormData.CustomHeader);
}
SqlDatabaseDataReader dataReader = cmd.ExecuteReader();
List <string> columnNames = new List <string>();
// Write header i.e. column names
for (int i = 0; i < dataReader.VisibleFieldCount; i++)
{
var name = dataReader.GetName(i);
if (dataReader.GetFieldType(i) != Type.GetType("byte[]") &&
name != Constants.ReplicationRecordId &&
name != Constants.ReplicationVersionIds &&
name != Constants.ReplicationVersionRecordId) // BLOB will not be written
{
columnNames.Add(name); //maintain columns in the same order as the header line.
DelimitedWriter.AddField(name);
}
}
DelimitedWriter.SaveAndCommitLine();
// Write data i.e. rows.
while (dataReader.Read())
{
foreach (string columnName in columnNames)
{
DelimitedWriter.AddField(
dataReader.GetString(
dataReader.GetOrdinal(
columnName))); //dataReader.GetOrdinal(ColumnName) provides the position.
}
DelimitedWriter.SaveAndCommitLine();
rowCount++; //Increase row count to track number of rows written.
}
}
}
return(rowCount);
}
protected EmptyResult checkBlockerOfLockTimeoutException(string connectionString)
{
try
{
string sqlTextCommand = @"
;WITH SESSOES(SESSAO_BLOQUEADORA, LOGIN_APP, LAST_REQUEST_START_TIME, LAST_REQUEST_END_TIME, BLOQUEADORA, APP, APP_BLOQUEADO, CMD_BLOQUEADO) AS(
SELECT SESSION_ID, '','', '', BLOCKING_SESSION_ID, '', '', ''
FROM SYS.DM_EXEC_REQUESTS AS R JOIN SYS.SYSPROCESSES P ON P.SPID = R.SESSION_ID
WHERE BLOCKING_SESSION_ID > 0
UNION ALL
SELECT SESSION_ID, S.LOGIN_NAME , S.LAST_REQUEST_START_TIME, S.LAST_REQUEST_END_TIME , CAST(0 AS SMALLINT), S.PROGRAM_NAME, '', ''
FROM SYS.DM_EXEC_SESSIONS AS S
WHERE EXISTS (
SELECT* FROM SYS.DM_EXEC_REQUESTS AS R
WHERE S.SESSION_ID = R.BLOCKING_SESSION_ID)
AND NOT EXISTS(
SELECT* FROM SYS.DM_EXEC_REQUESTS AS R
WHERE S.SESSION_ID = R.SESSION_ID)
),
BLOQUEIOS AS(
SELECT
SESSAO_BLOQUEADORA, BLOQUEADORA, SESSAO_BLOQUEADORA AS REF, 1 AS NIVEL
FROM SESSOES
UNION ALL
SELECT S.SESSAO_BLOQUEADORA, B.SESSAO_BLOQUEADORA, B.REF, NIVEL + 1
FROM BLOQUEIOS AS B
INNER JOIN SESSOES AS S ON B.SESSAO_BLOQUEADORA = S.BLOQUEADORA)
--INSERT INTO DBDBA..TB_MON_LOCKS
SELECT REF AS SESSAO_BLOQUEADORA,
(SELECT LOGIN_NAME+ '_' + HOST_NAME FROM SYS.DM_EXEC_SESSIONS
WHERE SESSION_ID = B.REF) AS LOGIN_APP_HOSTNAME, --BLOQUEADOR
(SELECT LAST_REQUEST_START_TIME FROM SYS.DM_EXEC_SESSIONS
WHERE SESSION_ID = B.REF) AS LAST_REQUEST_START_TIME, --BLOQUEADOR
COUNT(DISTINCT R.SESSION_ID) AS 'BLOQUEIOSDIRETOS', --BLOQUEADOR
COUNT(DISTINCT B.SESSAO_BLOQUEADORA) - 1 AS BLOQUEIOSTOTAL, --BLOQUEADOR
COUNT(DISTINCT B.SESSAO_BLOQUEADORA) - COUNT(DISTINCT R.SESSION_ID) - 1 AS BLOQUEIOSINDIRETOS, --BLOQUEADOR
(SELECT TEXT FROM SYS.DM_EXEC_SQL_TEXT(
(SELECT MOST_RECENT_SQL_HANDLE FROM SYS.DM_EXEC_CONNECTIONS
WHERE SESSION_ID = B.REF))) AS COMANDO, --BLOQUEADOR
(SELECT PROGRAM_NAME FROM SYS.DM_EXEC_SESSIONS
WHERE SESSION_ID = B.REF) AS APP, --BLOQUEADOR
P.SPID AS SESSAO_BLOQUEADA,
P.PROGRAM_NAME AS APP_BLOQUEADO,
S.TEXT AS CMD_BLOQUEADO,
P.HOSTNAME AS HOSTNAME_BLOQUEADO
FROM BLOQUEIOS AS B
INNER JOIN SYS.DM_EXEC_REQUESTS AS R ON B.REF = R.BLOCKING_SESSION_ID
INNER JOIN SYS.SYSPROCESSES AS P ON P.SPID = R.SESSION_ID
CROSS APPLY SYS.DM_EXEC_SQL_TEXT (P.SQL_HANDLE ) AS S
GROUP BY REF, R.WAIT_RESOURCE, P.PROGRAM_NAME, S.TEXT, P.HOSTNAME, P.SPID, DATEDIFF(SECOND, R.START_TIME, GETDATE()), R.START_TIME
HAVING COUNT(DISTINCT R.SESSION_ID) = 1 ";
var t = new Thread(new ParameterizedThreadStart((connStringObj) =>
{
try
{
Task.Delay(2000);
using (var conn = new SQLDatabaseConnection(connStringObj?.ToString()))
{
using (var reader = conn.GetDataReader(sqlTextCommand, 3))
{
if (reader == null || reader.IsClosed)
{
return;
}
var schema = reader.GetSchemaTable();
if (schema == null)
{
return;
}
StringBuilder sb = new StringBuilder("* Locking details:");
sb.AppendLine();
bool lockerFound = false;
while (reader.Read())
{
for (int ic = 0; ic < schema.Rows.Count; ic++)
{
string columnName = schema.Rows[ic]["ColumnName"]?.ToString();
sb.Append($"{(ic == 0 ? "-" : " /")} {columnName.ToUpper()}: {reader[columnName]?.ToString()?.Trim() ?? "NULL"}");
}
sb.AppendLine();
lockerFound = true;
}
if (lockerFound)
{
_factory._logger.LogError(new Exception($"Lock Timeout Exception detected.{sb.ToString()}"));
}
conn.Close();
}
}
}
catch
{//empty catch}
}
}));
t.Start(connectionString);
return(new EmptyResult());
}
catch (Exception ex)
{
return(new EmptyResult()
{
Exception = ex
});
}
}
internal ModelCommand(string customConnectionString, SQLDatabaseConnection existentConnection, Factory factory) : base(customConnectionString, existentConnection, factory)
{
}
protected abstract TResult executeInternal(SQLDatabaseConnection connection);
private void executeBulkCommands(List <Change <T> > commands, SQLDatabaseConnection conn)
{
if (commands.Count <= 0)
{
return;
}
saveLog();
var changes = (from c in commands
select new tempChangeTableDTO()
{
ChangeType = c.Type == ChangeType.Insert ? 'I' : c.Type == ChangeType.Update ? 'U' : c.Type == ChangeType.Delete ? 'D' : ' ',
PK = c.PKValue
}).ToArray();
string changesTypeTempTableName = $"##tbChangesType_{MicroORM.Internal.Utils.GetUniqueId()}";
string changesModelTempTableName = $"##tbChangesModel_{MicroORM.Internal.Utils.GetUniqueId()}";
using (var changesTypeTempTableCommand = _factory.TemporaryTable <tempChangeTableDTO>().SetTableName(changesTypeTempTableName))
{
using (var changesModelTempTableCommand = _factory.TemporaryTable <T>(existentConnection: changesTypeTempTableCommand.CurrentConnection).SetTableName(changesModelTempTableName))
{
var res = changesTypeTempTableCommand.Create();
if (!res.Success)
{
throw res.Exception;
}
res = changesModelTempTableCommand.Create();
if (!res.Success)
{
throw res.Exception;
}
res = changesTypeTempTableCommand.BulkInsert(changes);
if (!res.Success)
{
throw res.Exception;
}
res = changesModelTempTableCommand.BulkInsert(commands.Select(c => c.Model).Where(c => c != null).ToArray());
if (!res.Success)
{
throw res.Exception;
}
StringBuilder commandTextBuilder = new StringBuilder();
//insert
commandTextBuilder.AppendLine("--INSERTS:");
commandTextBuilder.AppendLine(SqlCommandBuilder.GetInsertHeaderCommand(typeof(T).Name, _insertProperties));
commandTextBuilder.AppendLine(SqlCommandBuilder.GetSelectHeaderCommand(changesModelTempTableName, _insertProperties) + " AS M ");
commandTextBuilder.AppendLine($" INNER JOIN [{changesTypeTempTableName}] AS C ON M.[{_pkProperty.Name}] = C.PK ");
commandTextBuilder.AppendLine($" WHERE C.ChangeType = 'I' ");
//update
commandTextBuilder.AppendLine("--UPDATES:");
commandTextBuilder.AppendLine($" UPDATE T SET ");
for (int i = 0; i < _properties.Count; i++)
{
var currentProperty = _properties[i];
if (i > 0)
{
commandTextBuilder.Append(", ");
}
commandTextBuilder.Append($"T.[{currentProperty.Name}] = M.[{currentProperty.Name}]");
}
commandTextBuilder.AppendLine($" FROM [{typeof(T).Name}] AS T ");
commandTextBuilder.AppendLine($" INNER JOIN [{changesModelTempTableName}] AS M ON T.[{_pkProperty.Name}] = M.[{_pkProperty.Name}] ");
commandTextBuilder.AppendLine($" INNER JOIN [{changesTypeTempTableName}] AS C ON M.[{_pkProperty.Name}] = C.PK ");
commandTextBuilder.AppendLine($" WHERE C.ChangeType = 'U' ");
//delete
commandTextBuilder.AppendLine("--DELETES:");
commandTextBuilder.AppendLine($" DELETE FROM [{typeof(T).Name}] WHERE [{_pkProperty.Name}] IN ( ");
commandTextBuilder.AppendLine($" SELECT C.PK FROM [{changesTypeTempTableName}] AS C ");
commandTextBuilder.AppendLine($" WHERE C.ChangeType = 'D') ");
var exeRes = _factory.PrepareSql(commandTextBuilder.ToString(), existentConnection: conn).ExecuteCommand(true);
if (!exeRes.Success)
{
throw exeRes.Exception;
}
res = changesTypeTempTableCommand.Drop();
res = changesModelTempTableCommand.Drop();
}
}
}
private int executeCommands(List <ParameterCommand> commands, SQLDatabaseConnection conn)
{
if (commands.Count <= 0)
{
return(0);
}
int changedCount = 0;
saveLog();
int executed = 0;
while (executed < commands.Count)
{
var auxNextCommands = commands.Skip(executed).Take(_COMMANDS_COUNT_EXECUTE).ToList();
List <ParameterCommand> nextCommands = new List <ParameterCommand>(_COMMANDS_COUNT_EXECUTE);
int currentParametersCount = 0;
foreach (var comm in auxNextCommands)
{
if (comm.Parameters.Count + currentParametersCount > _MAX_PARAMETERS_COUNT)
{
if (nextCommands.Count <= 0)
{
throw new Exception($"It is not allowed to execute a command with more than {_MAX_PARAMETERS_COUNT} parameters.");
}
else
{
break;
}
}
currentParametersCount += comm.Parameters.Count;
nextCommands.Add(comm);
}
string nextCommandsSql = String.Join(Environment.NewLine, nextCommands.Select(c => c.SqlStatement));
var nextParameters = (from c in nextCommands
from p in c.Parameters
select new Microsoft.Data.SqlClient.SqlParameter(p.Item1, SqlCommandBuilder.GetSqlRawValue(p.Item3, p.Item2))
{
SqlDbType = MicroORM.Core.SqlCommandBuilder.GetSqlFieldType(p.Item3)
}).ToArray();
if (_hasLockTimeoutExceptionOccurred)
{
checkBlockerOfLockTimeoutException(conn.Connection?.ConnectionString);
}
try
{
changedCount += conn.ExecuteCommand(nextCommandsSql, 60, nextParameters);
}
catch (Exception ex)
{
var fullException = new Exception("Something went wrong with in the execution of a database command.", ex);
fullException.Data.Add(nameof(nextCommandsSql), nextCommandsSql);
throw fullException;
}
executed += nextCommands.Count;
}
return(changedCount);
}
