/// <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();
            }
        }
Beispiel #2
1
 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);
     }
 }
 public static Task<int> ExecuteNonQueryAsyncWithRetry(
                                                       SqlCommand command,
                                                       CancellationToken cancellationToken,
                                                       IRetryPolicy retryPolicy)
 {
     return retryPolicy.ExecuteAsyncWithRetry(() => command.ExecuteNonQueryAsync(cancellationToken));
 }
 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);
                }
            }
        }
Beispiel #6
0
        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;
            }
        }
Beispiel #7
0
        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();
            }
        }
        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);
            }
        }
Beispiel #9
0
	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();
         }
     }
 }
Beispiel #12
0
 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();
         }
     }
 }
Beispiel #13
0
		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();
			}
		}
Beispiel #14
0
        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();
            }
        }
Beispiel #15
0
        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();
        }
Beispiel #16
0
		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;
		}
 /// <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 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();
                }
            }
        }
Beispiel #19
0
        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);
            }
        }
Beispiel #20
0
        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();
            }
        }
Beispiel #21
0
        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;
        }
		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();
                }
            }
        }
        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);
                }
            }
        }
Beispiel #25
0
        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 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();
 }
Beispiel #27
0
 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();
     }
 }
Beispiel #28
0
        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 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();
                }
            }
        }
        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();
            }
        }