public static async Task UpdateRows()
{
string connectionString = ConfigurationManager.ConnectionStrings["ProgrammingCSharpConnection"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("UPDATE People SET FirstName='John'", connection);
await connection.OpenAsync();
int numberOfUpdatedRows = await command.ExecuteNonQueryAsync();
Console.WriteLine("Updated {0} rows", numberOfUpdatedRows);
}
}
private async Task AddStatistic(SqlConnection con, PackageResult package)
{
const string sql = @"MERGE dbo.PackageStatistics AS t
USING
(
SELECT @name as 'Name', @latestSupportType as 'LatestSupportType'
) AS s
ON t.Name = s.Name
WHEN MATCHED THEN
UPDATE SET LatestSupportType = s.LatestSupportType, [Count] = [Count] + 1
WHEN NOT MATCHED THEN
INSERT (Name, LatestSupportType, Count)
VALUES (s.Name, s.LatestSupportType, 1);";
try
{
using (var cmd = new SqlCommand(sql, con))
{
cmd.Parameters.AddWithValue("@name", package.PackageName);
cmd.Parameters.AddWithValue("@latestSupportType", package.SupportType.ToString());
await cmd.ExecuteNonQueryAsync();
}
}
catch (Exception ex)
{
Log.Logger.Error(ex, "Exception writing statistic {stat}", package.PackageName);
}
}
private async Task RunScriptAsync(string script, CancellationToken cancellationToken = default(CancellationToken))
{
var cxnString = cfg.Properties["connection.connection_string"] + "; Pooling=No";
// Disable connection pooling so this won't be hindered by
// problems encountered during the actual test
string sql;
using (var reader = new StreamReader(GetType().Assembly.GetManifestResourceStream(GetType().Namespace + "." + script)))
{
sql = await(reader.ReadToEndAsync());
}
using (var cxn = new SqlConnection(cxnString))
{
await(cxn.OpenAsync(cancellationToken));
foreach (var batch in Regex.Split(sql, @"^go\s*$", RegexOptions.IgnoreCase | RegexOptions.Multiline)
.Where(b => !string.IsNullOrEmpty(b)))
{
using (var cmd = new System.Data.SqlClient.SqlCommand(batch, cxn))
{
await(cmd.ExecuteNonQueryAsync(cancellationToken));
}
}
}
}
/// <summary>
/// This method is called whenever the module is sent a message from the EdgeHub.
/// It just pipe the messages without any change.
/// It prints all the incoming messages.
/// </summary>
static async Task <MessageResponse> ProcessInputAsa(Message message, object userContext)
{
int counterValue = Interlocked.Increment(ref counterAsaMessages);
byte[] messageBytes = message.GetBytes();
string messageString = Encoding.UTF8.GetString(messageBytes);
_logger.Log($"Received ASA message: {counterValue}");
if (!string.IsNullOrEmpty(messageString))
{
var eventList = JsonConvert.DeserializeObject <List <MessageBodyAsaInput> >(messageString);
string insertRowStatement = "";
foreach (MessageBodyAsaInput item in eventList)
{
insertRowStatement += $"INSERT INTO MeasurementsDB.dbo.AggreatedMeasurements (deviceId, timestamp, avgHumidity, avgTemperature) VALUES ({item.deviceId}', CONVERT(DATETIME2,'{item.WindowEndTime}', 127), {item.avgHumidity}, {item.avgTemperature});\n";
}
//Store the data in SQL db
using (Sql.SqlConnection conn = new Sql.SqlConnection(_sqlConnString))
{
conn.Open();
using (Sql.SqlCommand cmd = new Sql.SqlCommand(insertRowStatement, conn))
{
//Execute the command and log the # rows affected.
var rows = await cmd.ExecuteNonQueryAsync();
_logger.Log($"{rows} rows were inserted");
}
}
}
return(MessageResponse.Completed);
}
public async Task<ActionResult> FileUpload(IEnumerable<HttpPostedFileBase> files)
{
var result = new List<FileUpload>();
foreach (var file in files)
{
byte[] data = new byte[file.ContentLength];
await file.InputStream.ReadAsync(data, 0, file.ContentLength);
string fileName = Path.GetFileName(file.FileName);
int Id;
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SamplesDb"].ConnectionString))
{
using (var comm = new SqlCommand(
"INSERT INTO Files (FileName, Data, ContentType)" +
" VALUES(@FileName, @Data, @ContentType);" +
" SELECT @Id=SCOPE_IDENTITY()", conn))
{
comm.Parameters.AddWithValue("@FileName", fileName);
comm.Parameters.AddWithValue("@Data", data);
comm.Parameters.AddWithValue("@ContentType", file.ContentType);
comm.Parameters.Add(new SqlParameter("@Id", System.Data.SqlDbType.Int)).Direction = System.Data.ParameterDirection.Output;
await conn.OpenAsync();
await comm.ExecuteNonQueryAsync();
Id = (int)comm.Parameters["@Id"].Value;
result.Add(new FileUpload { Id = Id, FileName = fileName,ContentType=file.ContentType });
}
}
}
return View(result);
}
public static async Task InsertPurchaseOrderHeaderAsync(string cnStr)
{
const string queryString =
"INSERT INTO Purchasing.PurchaseOrderHeader " +
"(RevisionNumber, Status, EmployeeID, VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight, ModifiedDate) " +
"VALUES " +
"(@RevisionNumber, @Status, @EmployeeID, @VendorID, @ShipMethodID, @OrderDate, @ShipDate, @SubTotal, @TaxAmt, @Freight, @ModifiedDate)";
var dt = DateTime.UtcNow;
using (var cn = new SqlConnection(cnStr))
{
using (var cmd = new SqlCommand(queryString, cn))
{
cmd.Parameters.AddWithValue("@RevisionNumber", 1);
cmd.Parameters.AddWithValue("@Status", 4);
cmd.Parameters.AddWithValue("@EmployeeID", 258);
cmd.Parameters.AddWithValue("@VendorID", 1580);
cmd.Parameters.AddWithValue("@ShipMethodID", 3);
cmd.Parameters.AddWithValue("@OrderDate", dt);
cmd.Parameters.AddWithValue("@ShipDate", dt);
cmd.Parameters.AddWithValue("@SubTotal", 123.40M);
cmd.Parameters.AddWithValue("@TaxAmt", 12.34M);
cmd.Parameters.AddWithValue("@Freight", 5.76M);
cmd.Parameters.AddWithValue("@ModifiedDate", dt);
await cn.OpenAsync().ConfigureAwait(false);
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
}
public Task Send(Message[] messages)
{
if (messages == null || messages.Length == 0)
{
return TaskAsyncHelper.Empty;
}
SqlConnection connection = null;
try
{
connection = new SqlConnection(_connectionString);
connection.Open();
using (var cmd = new SqlCommand(_insertSql, connection))
{
cmd.Parameters.AddWithValue("Payload", JsonConvert.SerializeObject(messages));
return cmd.ExecuteNonQueryAsync()
.Then(() => connection.Close()) // close the connection if successful
.Catch(ex => connection.Close()); // close the connection if it explodes
}
}
catch (SqlException)
{
if (connection != null && connection.State != ConnectionState.Closed)
{
connection.Close();
}
throw;
}
}
/// <summary>
/// Performs a sequence of non blocking database operations.
/// </summary>
private async static Task NonBlockingDatabaseOperations()
{
// Build the database connection.
using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleHttpApplication"].ConnectionString))
{
// Open the database connection.
await sqlConnection.OpenAsync();
// Insert the database row.
SqlCommand insertSqlCommand = new SqlCommand("INSERT INTO [Session] VALUES('" + Guid.NewGuid() + "', 'Session Alpha', '2012-06-27 10:05:45'); SELECT CAST(SCOPE_IDENTITY() AS INT);", sqlConnection);
int sessionID = (int)await insertSqlCommand.ExecuteScalarAsync();
// Select the database row.
SqlCommand selectSqlCommand = new SqlCommand("SELECT * FROM [Session] WHERE [SessionID] = " + sessionID, sqlConnection);
SqlDataReader sqlDataReader = await selectSqlCommand.ExecuteReaderAsync();
await sqlDataReader.ReadAsync();
sqlDataReader.Close();
// Update the database row.
SqlCommand updateSqlCommand = new SqlCommand("UPDATE [Session] SET [SessionCode] = '" + Guid.NewGuid() + "', [Name] = 'Session Beta', [StartDate] = '2013-07-28 11:06:46' WHERE [SessionID] = " + sessionID, sqlConnection);
await updateSqlCommand.ExecuteNonQueryAsync();
// Delete the database row.
SqlCommand deleteSqlCommand = new SqlCommand("DELETE FROM [Session] WHERE [SessionID] = " + sessionID, sqlConnection);
await deleteSqlCommand.ExecuteNonQueryAsync();
}
}
public async Task InsertHttpRequestLogAsync(HttpRequestLog request)
{
using (var conn = new SqlConnection(_connectionString))
{
await conn.OpenAsync();
var cmd = new SqlCommand("uspInsertRequestLog", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", request.Id);
cmd.Parameters.AddWithValue("@UserName", request.UserName);
cmd.Parameters.AddWithValue("@UserIpAddress", request.UserIpAddress);
cmd.Parameters.AddWithValue("@HttpAction", request.HttpAction);
cmd.Parameters.AddWithValue("@RequestUrl", request.RequestUrl);
cmd.Parameters.AddWithValue("@RequestHeader", request.RequestHeader);
cmd.Parameters.AddWithValue("@RequestBody", request.RequestBody);
cmd.Parameters.AddWithValue("@UserAgent", request.UserAgent);
cmd.Parameters.AddWithValue("@DeviceInfo", request.DeviceInfo);
cmd.Parameters.AddWithValue("@BrowserInfo", request.BrowserInfo);
cmd.Parameters.AddWithValue("@IsAnonymous", request.IsAnonymous);
cmd.Parameters.AddWithValue("@IsAuthenticated", request.IsAuthenticated);
cmd.Parameters.AddWithValue("@IsGuest", request.IsGuest);
cmd.Parameters.AddWithValue("@IsSystem", request.IsSystem);
cmd.Parameters.AddWithValue("@RequestTimeStamp", request.RequestTimeStamp);
await cmd.ExecuteNonQueryAsync();
conn.Close();
}
}
public static Task<int> ExecuteNonQueryAsyncWithRetry(
SqlCommand command,
CancellationToken cancellationToken,
IRetryPolicy retryPolicy)
{
return retryPolicy.ExecuteAsyncWithRetry(() => command.ExecuteNonQueryAsync(cancellationToken));
}
public static async Task TruncateTransactionalOutboxTableAsync(this SystemData.SqlConnection sqlConnection)
{
//CLEAR the Table for Integration Tests to validate:
await using var sqlCmd = new SystemData.SqlCommand(
SqlCommands.TruncateTransactionalOutbox,
sqlConnection
);
await sqlCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
public async Task Delete()
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
var command = new SqlCommand("DROP TABLE Customers", connection);
await command.ExecuteNonQueryAsync();
}
}
public async Task ExecuteAsync(ScriptFile script, BuildItem.BuildActionType action)
{
if(action == BuildItem.BuildActionType.Drop || action == BuildItem.BuildActionType.DropAndCreate)
{
string cmdText = string.Format("DROP {0} [{1}].[{2}]", script.ScriptObject.ObjectType.ToString(), script.ScriptObject.SchemaName, script.ScriptObject.ObjectName);
var cmd = new SqlCommand(cmdText, Connection);
await cmd.ExecuteNonQueryAsync();
}
}
private async Task CreateDatabase()
{
using(var connection = _localDbInstance.CreateConnection())
{
await connection.OpenAsync();
using(var command = new SqlCommand($"CREATE DATABASE {_databaseName}", connection))
{
await command.ExecuteNonQueryAsync();
}
}
}
private async Task<int> ExecuteQuery(string connectionString, string query)
{
using (var conn = new SqlConnection(connectionString))
{
await conn.OpenAsync();
using (var cmd = new SqlCommand(query, conn))
{
return await cmd.ExecuteNonQueryAsync();
}
}
}
public static async Task Update( this Tag TagToUpdate, SqlConnection Connection, SqlTransaction Transaction = null ) {
using( SqlCommand Command = new SqlCommand() ) {
Command.Connection = Connection;
if( null != Transaction ) Command.Transaction = Transaction;
Command.CommandText = "UPDATE Tags SET Text = @Text WHERE Id = @Id;";
Command.Parameters.Add( "Text", System.Data.SqlDbType.VarChar ).Value = TagToUpdate.Text;
Command.Parameters.Add( "Id", System.Data.SqlDbType.SmallInt ).Value = TagToUpdate.Id;
await Command.ExecuteNonQueryAsync();
}
}
public async Task DeleteCacheItemAsync(string key)
{
using (var connection = new SqlConnection(ConnectionString))
{
var command = new SqlCommand(SqlQueries.DeleteCacheItem, connection);
command.Parameters.AddCacheItemId(key);
await connection.OpenAsync();
await command.ExecuteNonQueryAsync();
}
}
public static async Task FilterMessageAndSendMessage(
[EdgeHubTrigger("input1")] Message messageReceived,
[EdgeHub(OutputName = "output1")] IAsyncCollector <Message> output,
ILogger logger)
{
const int temperatureThreshold = 20;
byte[] messageBytes = messageReceived.GetBytes();
var messageString = System.Text.Encoding.UTF8.GetString(messageBytes);
if (!string.IsNullOrEmpty(messageString))
{
logger.LogInformation("Info: Received one non-empty message");
// Get the body of the message and deserialize it.
var messageBody = JsonConvert.DeserializeObject <MessageBody>(messageString);
//Store the data in SQL db
const string str = "Data Source=tcp:sql,1433;Initial Catalog=MeasurementsDB;User Id=SA;Password=Strong!Passw0rd;TrustServerCertificate=False;Connection Timeout=30;";
using (Sql.SqlConnection conn = new Sql.SqlConnection(str))
{
conn.Open();
var insertMachineTemperature = "INSERT INTO MeasurementsDB.dbo.TemperatureMeasurements VALUES (CONVERT(DATETIME2,'" + messageBody.timeCreated + "', 127), 'machine', " + messageBody.machine.temperature + ");";
var insertAmbientTemperature = "INSERT INTO MeasurementsDB.dbo.TemperatureMeasurements VALUES (CONVERT(DATETIME2,'" + messageBody.timeCreated + "', 127), 'ambient', " + messageBody.ambient.temperature + ");";
using (Sql.SqlCommand cmd = new Sql.SqlCommand(insertMachineTemperature + "\n" + insertAmbientTemperature, conn))
{
//Execute the command and log the # rows affected.
var rows = await cmd.ExecuteNonQueryAsync();
logger.LogInformation($"{rows} rows were updated");
}
}
if (messageBody != null && messageBody.machine.temperature > temperatureThreshold)
{
// Send the message to the output as the temperature value is greater than the threashold.
using (var filteredMessage = new Message(messageBytes))
{
// Copy the properties of the original message into the new Message object.
foreach (KeyValuePair <string, string> prop in messageReceived.Properties)
{
filteredMessage.Properties.Add(prop.Key, prop.Value);
}
// Add a new property to the message to indicate it is an alert.
filteredMessage.Properties.Add("MessageType", "Alert");
// Send the message.
await output.AddAsync(filteredMessage);
logger.LogInformation("Info: Received and transferred a message with temperature above the threshold");
}
}
}
}
public async Task CreateMessageTypeAsync(MessageType messageType)
{
var messageTypes = new List<string>();
var validation = "NONE";
if (messageType.Validation.Equals(MessageTypeValidation.Empty))
{
validation = "EMPTY";
}
else if (messageType.Validation.Equals(MessageTypeValidation.WellFormedXml))
{
validation = "WELL_FORMED_XML";
if (messageType.Validation.XmlSchemaCollectionName != null)
{
var collection = messageType.Validation.XmlSchemaCollectionName.Escaped();
validation = $"VALID_XML WITH SCHEMA COLLECTION {collection}";
}
}
var sql = $@"
IF NOT EXISTS (SELECT 1 FROM sys.service_message_types m WHERE m.name = @name)
BEGIN
CREATE MESSAGE TYPE {messageType.Name.Escaped()}
VALIDATION = {validation}
END
ELSE
BEGIN
ALTER MESSAGE TYPE {messageType.Name.Escaped()}
VALIDATION = {validation}
END
";
var command = new SqlCommand(sql, connection)
{
Parameters =
{
new SqlParameter
{
ParameterName = "@name",
Value = messageType.Name.Value,
SqlDbType = SqlDbType.NVarChar,
Size = 128,
},
}
};
await command.ExecuteNonQueryAsync();
}
public async Task<IEnumerable<string>> Post()
{
var result = new List<string>();
var watch = new Stopwatch();
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
var command = new SqlCommand("CREATE TABLE Customers (Id uniqueidentifier, Forename nvarchar(200), Surname nvarchar(200), PRIMARY KEY (Id))", connection);
await command.ExecuteNonQueryAsync();
command.CommandText = "INSERT INTO Customers (Id, Forename, Surname) VALUES(@Id, @Forename, @Surname)";
for (int i = 1; i < 11; i++)
{
AddInsertParameters(command, i);
watch.Restart();
await command.ExecuteNonQueryAsync();
watch.Stop();
result.Add($"SQL INSERT {i} try: {watch.ElapsedMilliseconds}");
}
}
return result;
}
public async Task RecordVisitAsync(string ipAddress)
{
const string insertStatement = "INSERT INTO Visits(IpAddress, CreatedOn) VALUES(@IpAddress, @CreatedOn)";
using (var conn = new SqlConnection(_connectionString))
{
using (var cmd = new SqlCommand(insertStatement, conn))
{
cmd.Parameters.Add("IpAddress", SqlDbType.NVarChar).Value = ipAddress;
cmd.Parameters.Add("CreatedOn", SqlDbType.DateTimeOffset).Value = DateTimeOffset.Now;
await conn.OpenAsync();
await cmd.ExecuteNonQueryAsync();
}
}
}
/// <summary>
/// Writes the logevent to the database.
/// </summary>
/// <param name="args">The log event arguments.</param>
/// <exception cref="System.Data.SqlException">Thrown if an error occured while saving to the database.</exception>
/// <returns>A Task object that can be used to listen for completion of the work.</returns>
/// <remarks>
/// The SQL database the provider ConnectionString connects to, should contain an ArachneLog table.
/// The table should have 5 columns: INT id NOT NULL, VARCHAR(10) level NOT NULL, DATETIME timstamp NOT NULL, VARCHAR(MAX) message NOT NULL, VARCHAR(MAX) exception
/// </remarks>
public async Task OnLogEventAsync(LogArgs args)
{
string connectionString = ConfigurationManager.ConnectionStrings[_connectionStringName].ConnectionString;
using (SqlConnection con = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO ArachneLog (level, timestamp, message, exception) VALUES (@lvl, @time, @msg, @ex)", con))
{
cmd.Parameters.AddWithValue("@lvl", Enum.GetName(typeof(LogLevel), args.Level));
cmd.Parameters.AddWithValue("@time", args.Timestamp);
cmd.Parameters.AddWithValue("@msg", args.Message);
cmd.Parameters.AddWithValue("@ex", args.Exception);
await con.OpenAsync();
await cmd.ExecuteNonQueryAsync();
}
}
}
public static async Task InsertRowWithParameterizedQuery()
{
string connectionString = ConfigurationManager.ConnectionStrings["ProgrammingCSharpConnection"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("INSERT INTO People([FirstName], [LastName], [MiddleName]) VALUES(@firstName, @lastName, @middleName)", connection);
await connection.OpenAsync();
command.Parameters.AddWithValue("@firstName", "John");
command.Parameters.AddWithValue("@lastName", "Doe");
command.Parameters.AddWithValue("@middleName", "Little");
int numberOfInsertedRows = await command.ExecuteNonQueryAsync();
Console.WriteLine("Inserted {0} rows", numberOfInsertedRows);
}
}
public async Task<int> DownloadCarparkData()
{
DataTable dt = await DownloadByUri("https://api.at.govt.nz/v2/public/display/parkinglocations?", DownloadType.Carpark).ConfigureAwait(false);
using (SqlConnection con = new SqlConnection(Helper.GetCurrentDbConnectionString()))
{
using (SqlCommand cmd = new SqlCommand("Update_Table_Carpark", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("tblCarpark", dt);
con.Open();
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
return dt.Rows.Count;
}
public async Task FinalizeRequestLogAsync(HttpRequestLog request)
{
using (var conn = new SqlConnection(_connectionString))
{
await conn.OpenAsync();
var cmd = new SqlCommand("uspFinalizeRequestLog", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", request.Id);
cmd.Parameters.AddWithValue("@Response", request.Response);
cmd.Parameters.AddWithValue("@ResponseBody", request.ResponseBody);
cmd.Parameters.AddWithValue("@ResponseTimeStamp", request.ResposneTimeStamp);
cmd.Parameters.AddWithValue("@RequestTotalTime", request.RequestTotalTime);
await cmd.ExecuteNonQueryAsync();
conn.Close();
}
}
protected async override Task<HttpResponseMessage> SendAsync( HttpRequestMessage Request, CancellationToken CancelToken ) {
Guid SessionToken = GetSessionToken( Request );
if( !SessionToken.Equals( Guid.Empty ) ) {
using( SqlConnection Connection = new SqlConnection( RoleEnvironment.GetConfigurationSettingValue( "DatabaseConnectionString" ) ) ) {
Connection.Open();
using( SqlCommand Command = new SqlCommand() ) {
Command.Connection = Connection;
Command.CommandText = "SELECT EmailAddress FROM Speakers WHERE SessionToken = @SessionToken AND SessionExpires > @CurrentTime";
Command.Parameters.Add( "SessionToken", System.Data.SqlDbType.UniqueIdentifier ).Value = SessionToken;
Command.Parameters.Add( "CurrentTime", System.Data.SqlDbType.DateTime ).Value = DateTime.UtcNow;
using( SqlDataReader DataReader = await Command.ExecuteReaderAsync() ) {
if( DataReader.Read() ) {
GenericIdentity UserIdentity = new GenericIdentity( DataReader.GetString( 0 ) );
GenericPrincipal UserPrincipal = new GenericPrincipal( UserIdentity, null );
Thread.CurrentPrincipal = UserPrincipal;
Request.GetRequestContext().Principal = UserPrincipal;
} else {
// return 401
HttpResponseMessage Response401 = new HttpResponseMessage( System.Net.HttpStatusCode.Unauthorized );
Response401.Headers.Add( "Access-Control-Allow-Origin", Request.Headers.GetValues( "Origin" ).First() );
Response401.Headers.Add( "Access-Control-Allow-Credentials", "true" );
return Response401;
}
}
}
HttpResponseMessage Response = await base.SendAsync( Request, CancelToken );
using( SqlCommand Command = new SqlCommand() ) {
Command.Connection = Connection;
Command.CommandText = "UPDATE Speakers SET SessionExpires = @SessionExpires WHERE SessionToken = @SessionToken";
Command.Parameters.Add( "SessionToken", System.Data.SqlDbType.UniqueIdentifier ).Value = SessionToken;
Command.Parameters.Add( "SessionExpires", System.Data.SqlDbType.DateTime ).Value = DateTime.UtcNow.AddMinutes( 20 );
await Command.ExecuteNonQueryAsync();
}
return Response;
}
}
return await base.SendAsync( Request, CancelToken );
}
public async Task Revoke(string type)
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
using (var command = new SqlCommand(@"
DELETE FROM [Data] WHERE [Owner] = @Owner AND [Type] = @Type
", connection, transaction))
{
command.Parameters.AddWithValue("Owner", owner).DbType = DbType.AnsiString;
command.Parameters.AddWithValue("Type", type).DbType = DbType.AnsiString;
await command.ExecuteNonQueryAsync();
}
transaction.Commit();
}
}
}
private static async Task DeadlockParticipantAsync(SqlConnection connection, bool isVictim, SemaphoreSlim myLock, SemaphoreSlim partnerLock, CancellationToken cancellationToken = default(CancellationToken))
{
try
{
//
// CLID = 1 has only 10 records, CLID = 3 has 100. This guarantees
// which process will be chosen as the victim (the one which will have
// less work to rollback)
//
var clid = isVictim ? 1 : 3;
using (var cmd = new System.Data.SqlClient.SqlCommand("UPDATE DeadlockHelper SET Data = newid() WHERE CLId = @CLID", connection))
{
//
// Exclusive lock on some records in the table
//
cmd.Parameters.AddWithValue("@CLID", clid);
await(cmd.ExecuteNonQueryAsync(cancellationToken));
}
}
finally
{
//
// Notify partner that I have finished my work
//
myLock.Release();
}
//
// Wait for partner to finish its work
//
if (!await(partnerLock.WaitAsync(120000, cancellationToken)))
{
throw new InvalidOperationException("Wait for partner has taken more than two minutes");
}
using (var cmd = new System.Data.SqlClient.SqlCommand("SELECT TOP 1 Data FROM DeadlockHelper ORDER BY Data", connection))
{
//
// Requires shared lock on table, should be blocked by
// partner's exclusive lock
//
await(cmd.ExecuteNonQueryAsync(cancellationToken));
}
}
public static async Task LogAsync(string cnStr, string logTableName)
{
string queryString = "INSERT INTO dbo." + logTableName + " (LogId, Message, LogTime) VALUES (@LogId, @Message, @LogTime)";
var logMessage = new LogMessage();
using (var cn = new SqlConnection(cnStr))
{
using (var cmd = new SqlCommand(queryString, cn))
{
cmd.Parameters.AddWithValue("@LogId", logMessage.LogId);
cmd.Parameters.AddWithValue("@Message", logMessage.Message);
cmd.Parameters.AddWithValue("@LogTime", logMessage.LogTime);
await cn.OpenAsync().ConfigureAwait(false);
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
}
public async Task RunCheck(Source source, string connectionString)
{
var playlists = await GetSongs(source.Url);
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(mergeSql, conn))
{
foreach (var playlist in playlists.OrderBy(t => t.Key))
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("Station", playlist.Value.Station);
cmd.Parameters.AddWithValue("Title", playlist.Value.Title);
cmd.Parameters.AddWithValue("Artist", playlist.Value.Artist);
await cmd.ExecuteNonQueryAsync();
}
}
}
}
public async Task DoAnotherCoolThing()
{
var sqlConnection = new SqlConnection();
var sqlCommand = new SqlCommand();
try
{
await sqlConnection.OpenAsync();
sqlCommand.Connection = sqlConnection;
await sqlCommand.ExecuteNonQueryAsync();
}
catch (Exception ex)
{
await _logger.LogAsync(ex);
}
finally
{
if (sqlConnection.State != ConnectionState.Closed)
await sqlConnection.CloseAsync();
}
}
public async Task ExecuteAsync(ScriptFile script, BuildItem.BuildActionType action)
{
string cmdText;
switch (action)
{
case BuildItem.BuildActionType.Drop:
return;
case BuildItem.BuildActionType.Alter:
cmdText = script.Sql.GetAlterScript();
break;
case BuildItem.BuildActionType.Create:
case BuildItem.BuildActionType.DropAndCreate:
cmdText = script.Sql.GetCreateScript();
break;
default:
throw new NotSupportedException(string.Format("Unable to execute a script for build action type {0}.", action));
}
var cmd = new SqlCommand(cmdText, Connection);
await cmd.ExecuteNonQueryAsync();
}
private async Task<bool> DeleteNonOwner(Guid device_id, Guid user_id)
{
bool success = false;
Exception err = null;
string sql = "DELETE FROM dbo.DeviceOwners WHERE device_id = @device_id AND user_id != @user_id";
if (db.State != ConnectionState.Open)
await db.OpenAsync();
SqlTransaction trans = db.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand(sql, db, trans);
cmd.Parameters.AddWithValue("@device_id", device_id);
cmd.Parameters.AddWithValue("@user_id", user_id);
int count = await cmd.ExecuteNonQueryAsync();
if (count > 0)
{
success = true;
}
trans.Commit();
}
catch (Exception e)
{
err = e;
trans.Rollback();
}
finally
{
db.Close();
}
if (err != null)
{
throw err;
}
return success;
}
public dynamic CommandExecute(CommandExecuteType execType)
{
try
{
dynamic result = 0;
switch (execType)
{
case CommandExecuteType.NonQuery:
result = command.ExecuteNonQuery();
break;
case CommandExecuteType.NonQueryAsync:
result = command.ExecuteNonQueryAsync();
break;
case CommandExecuteType.Reader:
result = command.ExecuteReader();
break;
case CommandExecuteType.ReaderAsync:
result = command.ExecuteReaderAsync();
break;
case CommandExecuteType.Scalar:
result = command.ExecuteScalar();
break;
case CommandExecuteType.ScalarAsync:
result = command.ExecuteScalarAsync();
break;
}
ClearParametersCommand();
return(result);
}
catch (Exception ex)
{
throw new ConnectionManagerException(string.Format("Unable to execute command caused by {0}", ex.Message), ex.InnerException);
}
}
public async Task <int> PersistDynoCard(DynoCard card)
{
int cardID = -1;
if (card.SurfaceCard == null || card.SurfaceCard.NumberOfPoints == 0)
{
//If there's no surface card, return without inserting.
return(-1);
}
try
{
// //Store the data in SQL DB
using (Sql.SqlConnection conn = new Sql.SqlConnection(ConnectionString))
{
conn.Open();
var insertDynoCard = new StringBuilder("INSERT INTO [ACTIVE].[DYNO_CARD] ([PU_ID], [DC_UPDATE_DATE], [DC_UPDATE_BY]) ")
.Append("OUTPUT INSERTED.DC_ID ")
.Append($"VALUES (1, '{DateTime.Now}', 'edgeModule') ");
var insertSurfaceCard = new StringBuilder("INSERT INTO [ACTIVE].[CARD_HEADER] ")
.Append("([DC_ID], [CH_EPOC_DATE], [CH_SCALED_MAX_LOAD], [CH_SCALED_MIN_LOAD], ")
.Append("[CH_NUMBER_OF_POINTS], [CH_STROKE_LENGTH], ")
.Append("[CH_STROKE_PERIOD], [CH_CARD_TYPE], [CH_UPDATE_DATE], [CH_UPDATE_BY]) ")
.Append("OUTPUT INSERTED.CH_ID ")
.Append("VALUES ({0}, ").Append($"CONVERT(int, DATEDIFF(ss, '01-01-1970 00:00:00', '{card.SurfaceCard.Timestamp}')), {card.SurfaceCard.ScaledMaxLoad}, {card.SurfaceCard.ScaledMinLoad}, ")
.Append($"{card.SurfaceCard.NumberOfPoints}, {card.SurfaceCard.StrokeLength}, {card.SurfaceCard.StrokePeriod}, 'S', '{DateTime.Now}', 'edgeModule');");
var insertPumpCard = new StringBuilder("INSERT INTO [ACTIVE].[CARD_HEADER] ")
.Append("([DC_ID], [CH_EPOC_DATE], [CH_SCALED_MAX_LOAD], [CH_SCALED_MIN_LOAD], ")
.Append("[CH_NUMBER_OF_POINTS], [CH_GROSS_STROKE], [CH_NET_STROKE], [CH_PUMP_FILLAGE], ")
.Append("[CH_FLUID_LOAD], [CH_CARD_TYPE], [CH_UPDATE_DATE], [CH_UPDATE_BY]) ")
.Append("OUTPUT INSERTED.CH_ID ")
.Append("VALUES ({0}, ").Append($"CONVERT(int, DATEDIFF(ss, '01-01-1970 00:00:00', '{card.PumpCard.Timestamp}')), {card.PumpCard.ScaledMaxLoad}, {card.PumpCard.ScaledMinLoad}, ")
.Append($"{card.PumpCard.NumberOfPoints}, {card.PumpCard.GrossStroke}, {card.PumpCard.NetStroke}, {card.PumpCard.PumpFillage}, {card.PumpCard.FluidLoad}, ")
.Append($"'P', '{DateTime.Now}', 'edgeModule'); ");
var insertDetail = "INSERT INTO [ACTIVE].[CARD_DETAIL] ([CH_ID],[CD_POSITION],[CD_LOAD],[CD_UPDATE_DATE],[CD_UPDATE_BY]) VALUES ({0}, {1}, {2}, '{3}', 'edgeModule');";
using (Sql.SqlCommand dynoCardCommand = new Sql.SqlCommand())
{
//Insert the DynoCard record
dynoCardCommand.Connection = conn;
string dynoCardInsertStatement = insertDynoCard.ToString();
//Console.WriteLine($"Dynocard insert: {dynoCardInsertStatement}");
dynoCardCommand.CommandText = dynoCardInsertStatement;
var dynoCardID = await dynoCardCommand.ExecuteScalarAsync();
cardID = (int)dynoCardID;
//Insert the Surface card header record
string surfaceCardInsertStatement = string.Format(insertSurfaceCard.ToString(), dynoCardID);
// Console.WriteLine($"Surface card insert: {surfaceCardInsertStatement}");
dynoCardCommand.CommandText = surfaceCardInsertStatement;
var headerID = await dynoCardCommand.ExecuteScalarAsync();
//Insert the Surface card detail records
foreach (var point in card.SurfaceCard.CardCoordinates)
{
string detailStatement = string.Format(insertDetail, headerID, point.Position, point.Load, DateTime.Now);
//Console.WriteLine($"Surface Detail Statement: {detailStatement}");
dynoCardCommand.CommandText = detailStatement;
await dynoCardCommand.ExecuteNonQueryAsync();
}
if (card.PumpCard != null && card.PumpCard.NumberOfPoints != 0)
{
//Insert the Pump card header record
string pumpCardInsertStatement = string.Format(insertPumpCard.ToString(), dynoCardID);
//Console.WriteLine($"Pump card insert: {pumpCardInsertStatement}");
dynoCardCommand.CommandText = pumpCardInsertStatement;
headerID = await dynoCardCommand.ExecuteScalarAsync();
//Insert the Pump card detail records
foreach (var point in card.PumpCard.CardCoordinates)
{
string detailStatement = string.Format(insertDetail, headerID, point.Position, point.Load, DateTime.Now);
//Console.WriteLine($"Pump Detail Statement: {detailStatement}");
dynoCardCommand.CommandText = detailStatement;
await dynoCardCommand.ExecuteNonQueryAsync();
}
}
}
}
}
catch (Exception ex)
{
System.Console.WriteLine($"Error trying to insert dyno card data: {ex.Message}");
System.Console.WriteLine(ex.StackTrace);
await Task.FromResult(false);
}
return(await Task.FromResult(cardID));
}
public async Task <IActionResult> ExecuteCommand([FromBody] Command command)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
string connectionString = await GetConnectionString(command);
var result = new CommandResult();
using (SqlClient.SqlConnection sqlConnection = new SqlClient.SqlConnection(connectionString))
{
await sqlConnection.OpenAsync();
sqlConnection.InfoMessage += (sender, args) =>
{
result.Messages.Add(args.Message);
};
for (int batchIndex = 0; batchIndex < command.Sql.Count; batchIndex++)
{
string sql = command.Sql[batchIndex];
Log.LogInformation("Executing T-SQL batch {BatchNumber} of {BatchCount}...",
batchIndex + 1,
command.Sql.Count
);
using (var sqlCommand = new SqlClient.SqlCommand(sql, sqlConnection))
{
sqlCommand.CommandType = CommandType.Text;
foreach (Parameter parameter in command.Parameters)
{
sqlCommand.Parameters.Add(
parameter.ToSqlParameter()
);
}
try
{
result.ResultCode = await sqlCommand.ExecuteNonQueryAsync();
}
catch (SqlClient.SqlException sqlException)
{
Log.LogError(sqlException, "Error while executing T-SQL: {ErrorMessage}", sqlException.Message);
result.ResultCode = -1;
result.Errors.AddRange(
sqlException.Errors.Cast <SqlClient.SqlError>().Select(
error => new SqlError
{
Kind = SqlErrorKind.TSql,
Message = error.Message,
Class = error.Class,
Number = error.Number,
State = error.State,
Procedure = error.Procedure,
Source = error.Source,
LineNumber = error.LineNumber
}
)
);
if (command.StopOnError)
{
Log.LogInformation("Terminating command processing because the request was configured to stop on the first error encountered.");
break;
}
}
catch (Exception unexpectedException)
{
Log.LogError(unexpectedException, "Unexpected error while executing T-SQL: {ErrorMessage}", unexpectedException.Message);
result.ResultCode = -1;
result.Errors.Add(new SqlError
{
Kind = SqlErrorKind.Infrastructure,
Message = $"Unexpected error while executing T-SQL: {unexpectedException.Message}"
});
if (command.StopOnError)
{
Log.LogInformation("Terminating command processing because the request was configured to stop on the first error encountered.");
break;
}
}
}
Log.LogInformation("Executed T-SQL batch {BatchNumber} of {BatchCount}.",
batchIndex + 1,
command.Sql.Count
);
}
}
return(Ok(result));
}
public async Task <InvokeResult> AddItemAsync(DataStreamRecord item)
{
var fields = String.Empty;
var values = String.Empty;
foreach (var fld in _stream.Fields)
{
/* validation should happen long before this point, however if someone manipulated the value, it could be very, very bad
* with a SQL injection attack, so error on the side of caution and never let it get through.
*/
if (!Validator.Validate(fld).Successful)
{
throw new Exception($"Invalid field name {fld.FieldName}");
}
fields += String.IsNullOrEmpty(fields) ? $"{fld.FieldName}" : $",{fld.FieldName}";
values += String.IsNullOrEmpty(values) ? $"@{fld.FieldName}" : $",@{fld.FieldName}";
}
fields += $",{_stream.DeviceIdFieldName},{_stream.TimeStampFieldName}";
values += $",@{_stream.DeviceIdFieldName},@{_stream.TimeStampFieldName}";
var sql = $"insert into [{_stream.DBTableName}] ({fields}) values ({values})";
using (var cn = new System.Data.SqlClient.SqlConnection(_connectionString))
using (var cmd = new System.Data.SqlClient.SqlCommand(sql, cn))
{
cmd.CommandType = System.Data.CommandType.Text;
foreach (var field in _stream.Fields)
{
object value = System.DBNull.Value;
if (item.Data.ContainsKey(field.FieldName))
{
value = item.Data[field.FieldName];
if (value == null)
{
value = System.DBNull.Value;
}
}
if (value != System.DBNull.Value && field.FieldType.Value == DeviceAdmin.Models.ParameterTypes.GeoLocation)
{
var geoParts = value.ToString().Split(',');
if (geoParts.Count() != 2)
{
return(InvokeResult.FromError($"Attmept to insert invalid geo code {value}"));
}
// Note geo codes ares stored HH.MMMMMM,HH.MMMMMM where lat comes first, SQL expects those to come lon then lat
var parameter = new SqlParameter($"@{field.FieldName}", $"POINT({geoParts[1]} {geoParts[0]})")
{
Direction = ParameterDirection.Input,
};
cmd.Parameters.Add(parameter);
}
else
{
cmd.Parameters.AddWithValue($"@{field.FieldName}", value);
}
}
if (String.IsNullOrEmpty(item.Timestamp))
{
item.Timestamp = DateTime.UtcNow.ToJSONString();
}
cmd.Parameters.AddWithValue($"@{_stream.TimeStampFieldName}", item.Timestamp.ToDateTime());
cmd.Parameters.AddWithValue($"@{_stream.DeviceIdFieldName}", item.DeviceId);
await cn.OpenAsync();
var insertResult = await cmd.ExecuteNonQueryAsync();
}
return(InvokeResult.Success);
}
private static async Task WriteLinksAsync(IDictionary<string, int> zones, ILookup<string, string> aliases)
{
var cs = ConfigurationManager.ConnectionStrings["tzdb"].ConnectionString;
using (var connection = new SqlConnection(cs))
{
var command = new SqlCommand("[Tzdb].[AddLink]", connection) { CommandType = CommandType.StoredProcedure };
command.Parameters.Add("@LinkZoneId", SqlDbType.Int);
command.Parameters.Add("@CanonicalZoneId", SqlDbType.Int);
await connection.OpenAsync();
foreach (var alias in aliases)
{
var canonicalId = zones[alias.Key];
foreach (var link in alias)
{
command.Parameters[0].Value = zones[link];
command.Parameters[1].Value = canonicalId;
await command.ExecuteNonQueryAsync();
}
}
connection.Close();
}
}
public async Task Save(Type valueType, string user, string name, object value)
{
const string sqlInsert = "INSERT [dbo].[Settings] VALUES(@Type, @User, @Name, @Value, @Version)";
const string sqlUpdate =
"UPDATE [dbo].[Settings] " +
"SET [Value] = @Value, " +
" [Version] = @Version " +
"WHERE [Type] = @Type " +
" AND (@User IS NULL OR [User] = @User) " +
" AND (@Name IS NULL OR [Name] = @Name)";
using (var connection = CreateConnection())
{
var command = new SqlCommand(sqlUpdate, connection);
command.Parameters.AddWithValue("@Value", value);
command.Parameters.AddWithValue("@Type", valueType.FullName);
command.Parameters.AddWithValue("@User", (object)user ?? DBNull.Value);
command.Parameters.AddWithValue("@Name", (object)name ?? DBNull.Value);
command.Parameters.AddWithValue("@Version", valueType.Assembly.GetName().Version.ToString());
var updatedRows = command.ExecuteNonQuery();
if (updatedRows == 0)
{
command.CommandText = sqlInsert;
await command.ExecuteNonQueryAsync();
}
}
}
