/// <summary> /// The execute. /// </summary> protected override void Execute() { WorkflowTrace.Verbose("DoDbOperationTask Executing"); using (var connection = new SqlConnection(this.ConnectionString)) { WorkflowTrace.Verbose("DoDbOperationTask Opening Database"); // Pass the this.CancellationToken to all async operations connection.OpenAsync(this.CancellationToken).Wait(); var cmd = new SqlCommand("SELECT * FROM Customers WHERE CUST_ID = @CustomerId", connection); // Access arguments setup in the BeforeExecute method cmd.Parameters.AddWithValue("@CustomerId", this.Inputs.CustomerId); WorkflowTrace.Verbose("DoDbOperationTask Executing Reader"); // Pass the this.CancellationToken to all async operations using (var reader = cmd.ExecuteReaderAsync(this.CancellationToken).Result) { // Pass the this.CancellationToken to all async operations while (reader.ReadAsync(this.CancellationToken).Result) { // Inside of a loop, check the cancellation this.CancellationToken this.CancellationToken.ThrowIfCancellationRequested(); // Pass the this.CancellationToken to other classes and methods DoSomethingWithCustomer(reader, this.CancellationToken); } } } }
protected async Task <Adapters.Oal.dbo_consignment_update> SearchConsignmentUpdateAsync(string consignmentNo) { var adapter = new Adapters.Oal.dbo_consignment_initialAdapter(); var query = $"SELECT [id],[weight_double] FROM [dbo].[consignment_update] WHERE [number] = '{consignmentNo}'"; Adapters.Oal.dbo_consignment_update consignmentUpdate = null; using (var conn = new System.Data.SqlClient.SqlConnection(adapter.ConnectionString)) using (var cmd = new System.Data.SqlClient.SqlCommand(query, conn)) { await conn.OpenAsync(); using (var reader = await cmd.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { consignmentUpdate = new Adapters.Oal.dbo_consignment_update { id = reader["id"].ReadNullableString(), weight_double = reader["weight_double"].ReadNullable <double>() }; } } } return(consignmentUpdate); }
protected async Task <Adapters.Oal.dbo_consignment_initial> SearchConsignmentInitialAsync(string consignmentNo) { var adapter = new Adapters.Oal.dbo_consignment_initialAdapter(); var query = $"SELECT [dt_created_date_field] FROM [dbo].[consignment_initial] WHERE [number] = '{consignmentNo}'"; Adapters.Oal.dbo_consignment_initial consignmentInitial = null; using (var conn = new System.Data.SqlClient.SqlConnection(adapter.ConnectionString)) using (var cmd = new System.Data.SqlClient.SqlCommand(query, conn)) { await conn.OpenAsync(); using (var reader = await cmd.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { consignmentInitial = new Adapters.Oal.dbo_consignment_initial { dt_created_date_field = reader["dt_created_date_field"].ReadNullable <DateTime>() }; } } } return(consignmentInitial); }
private async Task <bool> DoLookupAsync(Bespoke.PosEntt.AcceptanceDatas.Domain.AcceptanceData item, Bespoke.PosEntt.EnttAcceptances.Domain.EnttAcceptance destination) { var connectionString = ConfigurationManager.ConnectionStrings["brom"].ConnectionString; const string queryString = @"SELECT [BranchCode],[BranchName] FROM [dbo].[BROMBranchProfile] WHERE BranchCostCenter = @branchCode"; using (var connection = new System.Data.SqlClient.SqlConnection(connectionString)) { using (var command = new System.Data.SqlClient.SqlCommand(queryString, connection)) { command.Parameters.Add("@branchCode", SqlDbType.VarChar, 255).Value = item.BranchCode.ToDbNull(); await connection.OpenAsync(); using (var reader = await command.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { destination.LocationId = reader["BranchCode"].ReadNullableString(); destination.LocationName = reader["BranchName"].ReadNullableString(); } } } } return(true); }
public async Task Get() { using (var con = new SqlConnection(connectionString)) { var sproc = SimulatedSelectStatement.Create(); var cmd = sproc.Build(con); await con.OpenAsync(); var reader = await cmd.ExecuteReaderAsync(); Assert.IsTrue(reader.Read()); Assert.AreEqual(sproc.TestInt, reader.Get<int>("Identifier")); Assert.AreEqual(sproc.TestBigInt, reader.Get<long>("BigInt")); Assert.AreEqual(sproc.TestBit, reader.Get<bool>("Bit")); Assert.AreEqual(sproc.TestDate.Value.Date, reader.Get<DateTime>("Date").Date); Assert.AreEqual(sproc.TestDateTime.Value.Date, reader.Get<DateTime>("DateTime").Date); Assert.AreEqual(sproc.TestDateTime2.Value.Date, reader.Get<DateTime>("DateTime2").Date); Assert.AreEqual(sproc.TestDecimal, reader.Get<decimal>("Decimal")); Assert.AreEqual(sproc.TestFloat, reader.Get<float>("Float")); Assert.AreEqual(Math.Round((decimal)sproc.TestMoney, 4), reader.Get<decimal>("Money")); Assert.AreEqual(sproc.TestNChar, reader.Get<string>("NChar")); Assert.AreEqual(sproc.TestNText, reader.Get<string>("NText")); Assert.AreEqual(sproc.TestText, reader.Get<string>("Text")); Assert.AreEqual(sproc.TestGuid, reader.Get<Guid>("Unique")); CollectionAssert.AreEqual(sproc.TestBinary, reader.Get<byte[]>("Binary")); CollectionAssert.AreEqual(sproc.TestImage, reader.Get<byte[]>("Image")); } }
/// <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(); } }
private async void ReadPeopleData() { Console.WriteLine("Reading people data from database"); using (SqlConnection connection = new SqlConnection(this.connectionString)) { SqlCommand command = new SqlCommand("SELECT * FROM People", connection); await connection.OpenAsync(); SqlDataReader dataReader = await command.ExecuteReaderAsync(); while(await dataReader.ReadAsync()) { string formatStringwithMiddleName = "Person ({0}) is named {1} {2} {3}"; string formatStringWithoutMiddleName = "Person ({0}) is names {1} {2}"; if(dataReader["MiddleName"]==null) { Console.WriteLine(formatStringWithoutMiddleName, dataReader["id"], dataReader["FirstName"].ToString(), dataReader["LastName"].ToString()); } else { Console.WriteLine(formatStringwithMiddleName, dataReader["id"], dataReader["FirstName"].ToString(), dataReader["MiddleName"].ToString(), dataReader["LastName"].ToString()); } } dataReader.Close(); } }
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 async Task DataTableDictionary() { using (var con = new SqlConnection(connectionString)) { var sproc = SimulatedSelectStatement.Create(); var cmd = sproc.Build(con); var loader = new DynamicLoader(); await con.OpenAsync(); var adapter = new SqlDataAdapter(cmd); var ds = new DataSet(); adapter.Fill(ds); var table = ds.Tables[0]; var obj = loader.Dictionary(table); Assert.IsNotNull(obj); Assert.AreEqual(sproc.TestInt, obj["Identifier"]); Assert.AreEqual(sproc.TestBigInt, obj["BigInt"]); Assert.AreEqual(sproc.TestBit, obj["Bit"]); Assert.AreEqual(sproc.TestDate.Value.Date, ((DateTime)obj["Date"]).Date); Assert.AreEqual(sproc.TestDateTime.Value.Date, ((DateTime)obj["DateTime"]).Date); Assert.AreEqual(sproc.TestDateTime2.Value.Date, ((DateTime)obj["DateTime2"]).Date); Assert.AreEqual(sproc.TestDecimal, obj["Decimal"]); Assert.AreEqual(sproc.TestFloat, obj["Float"]); Assert.AreEqual(Math.Round((decimal)sproc.TestMoney, 4), obj["Money"]); Assert.AreEqual(sproc.TestNChar, obj["NChar"]); Assert.AreEqual(sproc.TestNText, obj["NText"]); Assert.AreEqual(sproc.TestText, obj["Text"]); CollectionAssert.AreEqual(sproc.TestBinary, obj["Binary"] as byte[]); CollectionAssert.AreEqual(sproc.TestImage, obj["Image"] as byte[]); Assert.AreEqual(sproc.TestGuid, obj["Unique"]); } }
public async Task DataTableDictionaries() { using (var con = new SqlConnection(connectionString)) { var sproc = new dboSelectMultipleStatement(); var cmd = sproc.Build(con); var loader = new DynamicLoader(); await con.OpenAsync(); var adapter = new SqlDataAdapter(cmd); var ds = new DataSet(); adapter.Fill(ds); var objs = loader.Dictionaries(ds.Tables[0]); Assert.IsNotNull(objs); var i = 0; foreach (var obj in objs) { Assert.AreEqual(i, obj["Identifier"]); i++; } } }
public static async Task EnsureTablesExist(string connectionString) { var asm = Assembly.GetExecutingAssembly(); using (var stream = asm.GetManifestResourceStream("iSynaptic.Core.Persistence.AggregateStore.sql")) using (var reader = new StreamReader(stream)) { string script = await reader.ReadToEndAsync(); var matches = _scriptRegex.Matches(script); using (var connection = new SqlConnection(connectionString)) { var commands = matches.OfType<Match>() .Select(x => x.Groups["script"].Value) .Select(sql => new SqlCommand { Connection = connection, CommandType = CommandType.Text, CommandText = sql }); await connection.OpenAsync(); foreach(var command in commands) await command.ExecuteNonQueryAsync(); } } }
public async Task<Person> GetPersonById(int id) { Person person = null; using (var connection = new SqlConnection(ConnectionString)) { using (var command = connection.CreateCommand()) { command.CommandText = "select p.id, p.name, p.description from People as p where p.id=@id"; await connection.OpenAsync(); command.Parameters.Add(new SqlParameter("@id", id)); var reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { person = new Person() { Id = reader.Get<int>("id"), Description = reader.ReadString("Description"), Name = reader.ReadString("Name") }; } reader.Close(); } connection.Close(); } return person; }
/// <summary> /// Creates a new user /// </summary> /// <param name="username">Username</param> /// <param name="password">Password</param> /// <returns><c>True</c> if successful</returns> public static async Task<bool> CreateUser(string username, string password) { const string query = "INSERT INTO Users (Username, Hash, Role) VALUES(@username, @hash, @role)"; string passwordHash = BCryptHelper.HashPassword(password, BCryptHelper.GenerateSalt()); try { SqlConnection connection = new SqlConnection(ConnectionString); SqlCommand sqlCommand = new SqlCommand(query, connection); SqlParameter usernameParameter = new SqlParameter("@username", SqlDbType.NVarChar); SqlParameter hashParameter = new SqlParameter("@hash", SqlDbType.NVarChar); SqlParameter roleParameter = new SqlParameter("@role", SqlDbType.NVarChar); sqlCommand.Parameters.Add(usernameParameter); sqlCommand.Parameters.Add(hashParameter); sqlCommand.Parameters.Add(roleParameter); usernameParameter.Value = username; hashParameter.Value = passwordHash; //TODO: make sure Admin can change the role depending on what he needs. roleParameter.Value = "Admin"; await connection.OpenAsync(); sqlCommand.ExecuteNonQuery(); connection.Close(); return true; } catch (Exception) { return false; } }
public async Task<IEnumerable<string>> Get() { // Note that the entire DB code is async using async/await using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SensorDB"].ConnectionString)) { await connection.OpenAsync(); using (var command = connection.CreateCommand()) { command.CommandText = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Sensors'"; if ((int)(await command.ExecuteScalarAsync()) == 0) { command.CommandText = "CREATE TABLE Sensors ( SensorName VARCHAR(50) PRIMARY KEY )"; await command.ExecuteNonQueryAsync(); command.CommandText = "INSERT INTO Sensors VALUES ( 'Temperature Sensor' )"; await command.ExecuteNonQueryAsync(); command.CommandText = "INSERT INTO Sensors VALUES ( 'Velocity Sensor' )"; await command.ExecuteNonQueryAsync(); } command.CommandText = "SELECT SensorName FROM Sensors"; using (var reader = await command.ExecuteReaderAsync()) { var result = new List<string>(); while (await reader.ReadAsync()) { result.Add(reader.GetString(0)); } return result; } } } }
public async Task<List<WindowsDtls>> GetWindows() { var constring = @"Data Source='c:\users\u3696174\documents\visual studio 2012\Projects\CLSBforNode\CLSBforNode\App_Data\Database1.sdf'"; List<WindowsDtls> lst_result = new List<WindowsDtls>(); using (SqlConnection con=new SqlConnection(constring)) { await con.OpenAsync(); using (SqlCommand cmd=new SqlCommand()) { cmd.Connection = con; cmd.CommandText = @"select * from Windows"; cmd.CommandType = CommandType.Text; SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); adapter.Fill(dt); adapter.Dispose(); con.Close(); WindowsDtls obj_single = null; foreach (DataRow dr in dt.Rows) { obj_single = new WindowsDtls(); obj_single.ID = Convert.ToInt32( dr[0].ToString()); obj_single.Name = dr[1].ToString(); obj_single.Quantity = dr[2].ToString(); obj_single.Price = dr[3].ToString(); obj_single.Image = dr[4].ToString(); lst_result.Add(obj_single); obj_single = null; } } } return lst_result; }
public async Task<HttpResponseMessage> Post(CancellationToken cancellationToken) { if (!Request.Content.IsMimeMultipartContent()) { throw new HttpResponseException(HttpStatusCode.UnsupportedMediaType); } using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DbContext"].ConnectionString)) { await conn.OpenAsync(cancellationToken); using (var multipartProvider = new MultipartBlobStreamProvider(conn)) { var blobProvider = await Request.Content.ReadAsMultipartAsync(multipartProvider, cancellationToken); var firstBlobKey = blobProvider.BlobData.First().BlobKey; if (Request.Headers.Accept.Any(a => a.MediaType == "application/json")) { return Request.CreateResponse(HttpStatusCode.OK, new { fileKey = firstBlobKey }); } else { // fix for ie9 not supporting json return Request.CreateResponse(HttpStatusCode.OK, new { fileKey = firstBlobKey }, "text/html"); } } } }
public static async Task ReadAsync(int productId) { var connection = new SqlConnection(GetConnectionString()); string sql = "SELECT Prod.ProductID, Prod.Name, Prod.StandardCost, Prod.ListPrice, CostHistory.StartDate, CostHistory.EndDate, CostHistory.StandardCost " + "FROM Production.ProductCostHistory AS CostHistory " + "INNER JOIN Production.Product AS Prod ON CostHistory.ProductId = Prod.ProductId " + "WHERE Prod.ProductId = @ProductId"; var command = new SqlCommand(sql, connection); var productIdParameter = new SqlParameter("ProductId", SqlDbType.Int); productIdParameter.Value = productId; command.Parameters.Add(productIdParameter); await connection.OpenAsync(); using (SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection)) { while (await reader.ReadAsync()) { int id = reader.GetInt32(0); string name = reader.GetString(1); DateTime from = reader.GetDateTime(4); DateTime? to = reader.IsDBNull(5) ? (DateTime?)null : reader.GetDateTime(5); decimal standardPrice = reader.GetDecimal(6); WriteLine($"{id} {name} from: {from:d} to: {to:d}; price: {standardPrice}"); } } }
public async void ShouldNotDeadlock() { // Not all library code used to have .ConfigureAwait(false); // This caused this code to run forever. Async test, opening async connection, running the sync db commands which use the async versions internally. // See these topics for more information about .ConfigureAwait(false); // http://stackoverflow.com/questions/13489065/best-practice-to-call-configureawait-for-all-server-side-code/13489639#13489639 // http://blog.stephencleary.com/2012/07/dont-block-on-async-code.html string connectionstring = "Server=.;Database=master;Integrated Security=true"; SqlConnection conn = new SqlConnection(connectionstring); await conn.OpenAsync(); string query = "select top 1 TABLE_NAME from information_schema.tables"; DatabaseHelper.ExecuteDataSet(connectionstring, CommandType.Text, query); DatabaseHelper.ExecuteNonQuery(connectionstring, CommandType.Text, query); DatabaseHelper.ExecuteScalar<string>(connectionstring, CommandType.Text, query); using (var dbReader = DatabaseHelper.ExecuteReader(connectionstring, CommandType.Text, query)) { if (dbReader.HasRows) { while (dbReader.Read()) { } } } }
/// <summary> /// Retrives an given user. /// </summary> /// <param name="username">Username of given user.</param> /// <returns><c>SqlDataReader</c> with user data.</returns> public static async Task<List<string>> RetrieveUser(string username) { SqlConnection connection = new SqlConnection(ConnectionString); //search for given username const string query = "SELECT * FROM Users WHERE Username=@username"; SqlCommand sqlCommand = new SqlCommand(query, connection); SqlParameter usernameParameter = new SqlParameter("@username", SqlDbType.NVarChar); sqlCommand.Parameters.Add(usernameParameter); usernameParameter.Value = username; await connection.OpenAsync(); var userData = new List<string>(); using (SqlDataReader sqlReader = await sqlCommand.ExecuteReaderAsync()) { while (await sqlReader.ReadAsync()) { userData.Add(sqlReader["Username"].ToString()); userData.Add(sqlReader["Hash"].ToString()); userData.Add(sqlReader["Role"].ToString()); } } connection.Close(); //send back userdata list. return userData; }
public void BeginVerify() { string connectionString = ConnectionInfoToConnectionString(_connectionInfo); _connection = new SqlConnection(connectionString); _task = _connection.OpenAsync(); _task.GetAwaiter().OnCompleted(OnOpenComplete); }
public async Task<List<TaskViewModels>> Get() { using (SqlConnection db = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["wmsConnection"].ConnectionString)) { await db.OpenAsync(); SqlCommand command = db.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = "select * from wmwhse1.taskdetail where status <>'9' and tasktype='PK' order by status desc,ORDERKEY desc,TASKDETAILKEY desc "; SqlDataReader reader = await command.ExecuteReaderAsync(); List<TaskViewModels> list = new List<TaskViewModels>(); while (await reader.ReadAsync()) { TaskViewModels item = new TaskViewModels(); list.Add(item); item.TaskKey = reader["TASKDETAILKEY"].ToString(); item.OrderKey = reader["ORDERKEY"].ToString(); item.Owner = reader["USERKEY"].ToString(); item.Qty = Convert.ToInt32(reader["QTY"]); item.Sku = reader["SKU"].ToString(); item.Status = reader["STATUS"].ToString(); item.Store = reader["STORERKEY"].ToString(); item.StartTime = Convert.ToDateTime(reader["STARTTIME"]); } db.Close(); return list; } }
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 <bool> DoLookupAsync(Bespoke.PosEntt.IposPems.Domain.IposPem item, Bespoke.PosEntt.Adapters.Oal.UspConsigmentInitialRtsRequest destination) { var connectionString = ConfigurationManager.ConnectionStrings["oal"].ConnectionString; const string queryString = @"SELECT [id] FROM [dbo].[consignment_initial] WHERE [number] = @babyConsignmentNo"; using (var connection = new System.Data.SqlClient.SqlConnection(connectionString)) { using (var command = new System.Data.SqlClient.SqlCommand(queryString, connection)) { command.Parameters.Add("@babyConsignmentNo", SqlDbType.VarChar, 255).Value = item.BabyConnoteNo.ToDbNull(); await connection.OpenAsync(); using (var reader = await command.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { destination.baby_item = reader["id"].ReadNullableString(); destination.parent = reader["id"].ReadNullableString(); } } } } return(true); }
public async Task<int> AddVote(int id) { using (var connection = new SqlConnection(ConnectionString)) using (var command = connection.CreateCommand()) { command.CommandText = "INSERT INTO Votes (Id) VALUES (@Id)"; command.Parameters.AddWithValue("@Id", id); await connection.OpenAsync(); using (var transaction = connection.BeginTransaction()) { command.Transaction = transaction; await command.ExecuteNonQueryAsync(); // simulate db activity & roundtrip await Task.Delay(10); transaction.Commit(); } IncrementVote(id); return 42; } }
public static async Task <SystemData.SqlConnection> CreateSystemDataSqlConnectionAsync() { var sqlConnection = new SystemData.SqlConnection(TestConfiguration.SqlConnectionString); await sqlConnection.OpenAsync(); return(sqlConnection); }
public static async Task CreateDatabaseAsync() { var conn = ConnectionManager.GetDefaultConnection(); var databaseName = conn.Connection.DatabaseName; var masterConn = conn.Connection.Clone() as Connection; masterConn.DatabaseName = "master"; var sqlConn = new SqlConnection(masterConn.ConnectionString); // Create a command object identifying the stored procedure using (var cmd = sqlConn.CreateCommand()) { // // Set the command object so it knows to execute a stored procedure cmd.CommandType = CommandType.Text; cmd.CommandText = EmbeddedAssembly.GetFromResources("DatabaseCreatorQuery.sql") .Replace("#DatabaseName", databaseName); // // execute the command try { await sqlConn.OpenAsync(); await cmd.ExecuteNonQueryAsync(); } finally { sqlConn.Close(); } } }
async private void dataGridView1_SelectionChanged(object sender, EventArgs e) { if (dataGridView1.SelectedRows.Count == 0) return; SqlConnection connection = new SqlConnection(); try { connection.ConnectionString = @"Data Source=(localdb)\v11.0;AttachDbFilename=D:\programming\cours\ITStepProjects\ADO.NET\6_CarsStore\6_CarsStore\DBAuto.mdf;Integrated Security=True"; await connection.OpenAsync(); SqlCommand cmdSelect = connection.CreateCommand(); cmdSelect.CommandText = "SELECT * FROM TMotor WHERE IDD=@IDD"; string IDD = dataGridView1.SelectedRows[0].Cells[4].Value.ToString(); cmdSelect.Parameters.AddWithValue("@IDD", IDD); SqlDataReader reader = await cmdSelect.ExecuteReaderAsync(); await reader.ReadAsync(); iDDTextBox.Text = reader[0].ToString(); markaTextBox.Text = reader[1].ToString(); mTextBox.Text = reader[2].ToString(); countryTextBox.Text = reader[3].ToString(); dateTextBox1.Text = reader[4].ToString(); } finally { connection.Close(); } }
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 async Task<List<SysObject>> GetSysObjects() { const string sqlText = "select name as Name, object_id as ObjectId, type as Type, type_desc as TypeDescription from sys.objects"; using (var connection = new SqlConnection(connectionString)) using (var command = new SqlCommand(cmdText: sqlText, connection: connection)) { await connection.OpenAsync().ConfigureAwait(continueOnCapturedContext: false); var result = new List<SysObject>(); using (var reader = await command.ExecuteReaderAsync()) { while(await reader.ReadAsync()) { var item = new SysObject { Name = reader.GetString(0), ObjectId = reader.GetInt32(1), Type = reader.GetString(2), TypeDescription = reader.GetString(3) }; result.Add(item); } return result; } } }
public async Task <IdentityResult> UpdateAsync(User u) { //cancellationToken.ThrowIfCancellationRequested(); using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnValue("FeedMeDB"))) { var param = new DynamicParameters(); param.Add("@UserID", u.UserID, DbType.Int32); param.Add("@FirstName", u.FirstName, DbType.String); param.Add("@LastName", u.LastName, DbType.String); param.Add("@TargetCals", u.TargetCals, DbType.Int32); param.Add("@TargetMacC", u.TargetMacC, DbType.Int32); param.Add("@TargetMacP", u.TargetMacP, DbType.Int32); param.Add("@TargetMacF", u.TargetMacF, DbType.Int32); await connection.OpenAsync(); await connection.ExecuteAsync( "dbo.UpdateUser", param, commandType : CommandType.StoredProcedure); } return(IdentityResult.Success); }
public async Task ReaderModel() { using (var con = new SqlConnection(connectionString)) { var sproc = SimulatedSelectStatement.Create(); var cmd = sproc.Build(con); await con.OpenAsync(); var reader = await cmd.ExecuteReaderAsync(); Assert.IsTrue(reader.Read()); var loader = new Loader<SelectData>(); var obj = loader.Model(reader); Assert.IsNotNull(obj); Assert.AreEqual(sproc.TestInt, obj.Identifier); Assert.AreEqual(sproc.TestBigInt, obj.BigInt); Assert.AreEqual(sproc.TestBit, obj.Bit); Assert.AreEqual(sproc.TestDate.Value.Date, obj.Date.Date); Assert.AreEqual(sproc.TestDateTime.Value.Date, obj.DateTime.Date); Assert.AreEqual(sproc.TestDateTime2.Value.Date, obj.DateTime2.Date); Assert.AreEqual(sproc.TestDecimal, obj.Decimal); Assert.AreEqual(sproc.TestFloat, obj.Float); Assert.AreEqual(Math.Round((decimal)sproc.TestMoney, 4), obj.Money); Assert.AreEqual(sproc.TestNChar, obj.NChar.ToString()); Assert.AreEqual(sproc.TestNText, obj.NText); Assert.AreEqual(sproc.TestText, obj.Text); CollectionAssert.AreEqual(sproc.TestBinary, obj.Binary); CollectionAssert.AreEqual(sproc.TestImage, obj.Image); Assert.AreEqual(sproc.TestGuid, obj.Unique); } }
async private void btnGetDataAsynch_Click(object sender, EventArgs e) { using (SqlConnection connection = new SqlConnection()) { connection.ConnectionString = @"Data Source=(localdb)\v11.0;AttachDbFilename=D:\KIN\DB\Shop.mdf;Integrated Security=True"; await connection.OpenAsync(); // асинхронное открытие соединения SqlCommand cmdSelect = connection.CreateCommand(); cmdSelect.CommandText = " WAITFOR DELAY '0:0:05' SELECT * FROM Customers"; SqlDataReader reader = await cmdSelect.ExecuteReaderAsync(); listInfo.Items.Clear(); listInfo.Items.Add("Данные получены в асинхронном режиме"); while (await reader.ReadAsync()) { String info = String.Format("{0} {1} {2} {3} {4} {5}", reader[0], reader[1], reader[2], reader[3], reader[4], reader[5]); listInfo.Items.Add(info); } } }
private static async Task Can_use_an_existing_closed_connection_test(bool openConnection) { var serviceCollection = new ServiceCollection(); serviceCollection .AddEntityFramework() .AddSqlServer(); var serviceProvider = serviceCollection.BuildServiceProvider(); using (var store = await SqlServerNorthwindContext.GetSharedStoreAsync()) { var openCount = 0; var closeCount = 0; var disposeCount = 0; using (var connection = new SqlConnection(store.Connection.ConnectionString)) { if (openConnection) { await connection.OpenAsync(); } connection.StateChange += (_, a) => { if (a.CurrentState == ConnectionState.Open) { openCount++; } else if (a.CurrentState == ConnectionState.Closed) { closeCount++; } }; #if !DNXCORE50 connection.Disposed += (_, __) => disposeCount++; #endif using (var context = new NorthwindContext(serviceProvider, connection)) { Assert.Equal(91, await context.Customers.CountAsync()); } if (openConnection) { Assert.Equal(ConnectionState.Open, connection.State); Assert.Equal(0, openCount); Assert.Equal(0, closeCount); } else { Assert.Equal(ConnectionState.Closed, connection.State); Assert.Equal(1, openCount); Assert.Equal(1, closeCount); } Assert.Equal(0, disposeCount); } } }
public async static Task<SqlConnection> GetOpenConnection(string connectionString) { var connection = new SqlConnection(connectionString); await connection.OpenAsync(); return connection; }
public async Task <User> FindByIdAsync(string ID, CancellationToken cancellationToken) { using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnValue("FeedMeDB"))) { await connection.OpenAsync(cancellationToken); return(await connection.QuerySingleOrDefaultAsync <User>("dbo.GetUser @UserID", new { UserID = ID })); } }
public static Task TryOpenAsync(this sql.SqlConnection connection) { if (connection?.State == System.Data.ConnectionState.Closed) { return(connection?.OpenAsync()); } else { return(Task.CompletedTask); } }
private async Task OpenWithCheckFastAsync(string checkQueryString, CancellationToken cancellationToken) { var connectionChecked = false; var restoreTriggered = false; while (!connectionChecked) { cancellationToken.ThrowIfCancellationRequested(); await underlyingConnection.OpenAsync(cancellationToken).ConfigureAwait(false); try { var command = underlyingConnection.CreateCommand(); await using (command.ConfigureAwait(false)) { command.CommandText = checkQueryString; _ = await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false); } connectionChecked = true; } catch (Exception exception) { if (InternalHelpers.ShouldRetryOn(exception)) { if (restoreTriggered) { throw; } var newConnection = new SqlServerConnection(underlyingConnection.ConnectionString); try { underlyingConnection.Close(); underlyingConnection.Dispose(); } catch { } underlyingConnection = newConnection; restoreTriggered = true; continue; } throw; } } }
public async Task <User> FindByNameAsync(string Username, CancellationToken cancellationToken) { cancellationToken.ThrowIfCancellationRequested(); using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnValue("FeedMeDB"))) { await connection.OpenAsync(cancellationToken); return(await connection.QuerySingleOrDefaultAsync <User>($@"SELECT * FROM [Users] WHERE [Username] = @{nameof(Username)}", new { Username })); } }
public async Task <User> GetUserByIdAsync(int id) { string Connectionstring = "Data Source=.\\SQLExpress;Initial Catalog=DapperTest;Integrated Security=True"; using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring)) { await sqlConnection.OpenAsync(); var sqlM = @"SELECT * from threads order by activities desc"; var resultM = await sqlConnection.QueryAsync <User>(sqlM); return(resultM.FirstOrDefault()); } }
public async Task <IdentityResult> CreateAsync(User user, CancellationToken cancellationToken) { cancellationToken.ThrowIfCancellationRequested(); using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnValue("FeedMeDB"))) { await connection.OpenAsync(cancellationToken); user.UserID = await connection.QuerySingleAsync <int>($@"INSERT INTO [Users] ([Username], [NormalizedUserName], [PasswordHash]) VALUES (@{nameof(User.Username)}, @{nameof(User.NormalizedUserName)}, @{nameof(User.PasswordHash)}); SELECT CAST(SCOPE_IDENTITY() as int)", user); } return(IdentityResult.Success); }
//public static TResult QuerySingle<TResult>(string queryName, DbQueryOption queryOption) //{ // using (var sqlConnection = new SqlConnection(queryOption.ConnectionID)) { // try { // if (sqlConnection.State != ConnectionState.Open) { // sqlConnection.Open(); // } // return (TResult)sqlConnection.QuerySingle<TResult>(queryName, queryOption.ParameterModel, commandType: CommandType.StoredProcedure); // } // catch (Exception) { // throw; // } // //finally { // // sqlConnection.Close(); // //} // } //} public static async Task <TResult> QuerySingleAsync <TResult>(string queryName, DbQueryOption queryOption) { using (var sqlConnection = new System.Data.SqlClient.SqlConnection(queryOption.ConnectionID)) { try { if (sqlConnection.State != ConnectionState.Open) { await sqlConnection.OpenAsync(); } return(await(Task <TResult>) sqlConnection.QuerySingleAsync <TResult>(queryName, queryOption.ParameterModel, commandType: CommandType.StoredProcedure)); } catch (Exception) { throw; } //finally { // sqlConnection.Close(); //} } }
public int DeletePerson(long amka) { using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("fwavesDB"))) { connection.OpenAsync(); try { connection.Execute("dbo.DeletePatient @AMKA", new { AMKA = amka }); return(1); } //$"select * from People where LastName='{lastName}'" - SQL injection threat catch (Exception ex) { MessageBox.Show(string.Format("Patient has not been deleted due to :{0}", ex.Message), "Not Deleted"); return(0); } } }
public void EditPerson(long amka, int diagnosis, string comment) { using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("fwavesDB"))) { connection.OpenAsync(); var person = new List <Patient>(); person.Add(new Patient { AMKA = amka, Diagnosis = diagnosis, Comment = comment }); try { connection.Execute("dbo.EditPatient @AMKA, @Diagnosis, @Comment", person); } //$"select * from People where LastName='{lastName}'" - SQL injection threat catch (Exception ex) { MessageBox.Show(string.Format("Patient has not been Updated due to :{0}", ex.Message), "Not Updated"); } } }
public void InsertPerson(long amka, int sex, float height, float armLength, float legLength, string comment) { using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("fwavesDB"))) { connection.OpenAsync(); var person = new List <Patient>(); person.Add(new Patient { AMKA = amka, Sex = sex, Height = height, ArmLength = armLength, LegLength = legLength, Comment = comment }); try { connection.Execute("dbo.Patient_Insert @AMKA, @Sex, @Height, @ArmLength, @Leglength, @Comment", person); } //$"select * from People where LastName='{lastName}'" - SQL injection threat catch (Exception ex) { MessageBox.Show(string.Format("Patient has not been Imported due to :{0}", ex.Message), "Not Imported"); } } }
public int DeleteMeasurement(long amka, long pk) { var measurementid = new List <MeasurementID>(); measurementid.Add(new MeasurementID { AMKA = amka, PK = pk }); using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("fwavesDB"))) { connection.OpenAsync(); try { connection.Execute("dbo.DeleteMeasurement @AMKA, @PK", measurementid); return(1); } catch (Exception ex) { MessageBox.Show(string.Format("Patient has not been deleted due to :{0}", ex.Message), "Not Deleted"); return(0); } } }
/// <summary> /// Open connection the asynchronous. /// </summary> /// <param name="cancellationToken">The cancellation token.</param> /// <returns>void</returns> /// <exception cref="System.InvalidOperationException"> /// The Connection was not Closed. /// The Connection current state is Open. /// </exception> public async Task OpenAsync(System.Threading.CancellationToken cancellationToken) { // // Can not use Lock block for async methods await SqlConn.OpenAsync(cancellationToken); }
public async Task <IActionResult> ExecuteQuery([FromBody] Query query) { if (!ModelState.IsValid) { return(BadRequest(ModelState)); } string connectionString = await GetConnectionString(query); var queryResult = new QueryResult(); using (SqlClient.SqlConnection sqlConnection = new SqlClient.SqlConnection(connectionString)) { await sqlConnection.OpenAsync(); sqlConnection.InfoMessage += (sender, args) => { queryResult.Messages.Add(args.Message); }; for (int batchIndex = 0; batchIndex < query.Sql.Count; batchIndex++) { string sql = query.Sql[batchIndex]; Log.LogInformation("Executing T-SQL batch {BatchNumber} of {BatchCount}...", batchIndex + 1, query.Sql.Count ); using (var sqlCommand = new SqlClient.SqlCommand(sql, sqlConnection)) { sqlCommand.CommandType = CommandType.Text; foreach (Parameter parameter in query.Parameters) { sqlCommand.Parameters.Add( parameter.ToSqlParameter() ); } try { using (SqlClient.SqlDataReader reader = await sqlCommand.ExecuteReaderAsync()) { await ReadResults(reader, queryResult); while (await reader.NextResultAsync()) { await ReadResults(reader, queryResult); } } queryResult.ResultCode = 0; } catch (SqlClient.SqlException sqlException) { Log.LogError(sqlException, "Error while executing T-SQL: {ErrorMessage}", sqlException.Message); queryResult.ResultCode = -1; queryResult.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 (query.StopOnError) { Log.LogInformation("Terminating query 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); queryResult.ResultCode = -1; queryResult.Errors.Add(new SqlError { Kind = SqlErrorKind.Infrastructure, Message = $"Unexpected error while executing T-SQL: {unexpectedException.Message}" }); if (query.StopOnError) { Log.LogInformation("Terminating query 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, query.Sql.Count ); } } return(Ok(queryResult)); }
/// <summary> /// <c>InsertExcel</c> inserts an Excel study file into dbo.Measurements and dbo.MeasurementsID. /// </summary> public void InsertExcel(long amka, float limpLength, string date, string fileName, int neuron, int side, float mLat, float marea = 0, float mamp = 0, float fper = 0, float fRepPer = 0, int rNs = 0, int rN5rep = 0, int fRepsTotal = 0) { /********** Insert Excel File to dbo.Measurements *************/ // Create Connection to Excel Workbook try { string constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", fileName); System.Data.DataTable dt = null; OleDbConnection Econ = new OleDbConnection(constr); Econ.Open(); dt = Econ.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); String[] excelSheets = new String[dt.Rows.Count]; int i = 0; // Add the sheet name to the string array. foreach (DataRow row in dt.Rows) { excelSheets[i] = row["TABLE_NAME"].ToString(); i++; } string Query = string.Format("Select * FROM [{0}]", excelSheets[0]); //query for what to select from excel sheet Econ.Close(); Econ.Open(); DataSet ds = new DataSet(); OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ); Econ.Close(); oda.Fill(ds); DataTable Exceldt = ds.Tables[0]; //creating object of SqlBulkCopy var objbulk = new SqlBulkCopy(Helper.CnnVal("fwavesDB")); //assigning Destination table name objbulk.DestinationTableName = "dbo.Measurements"; //Mapping Table column string[] columnNames = Exceldt.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray(); if (columnNames.Length >= 5) { objbulk.ColumnMappings.Add(columnNames[0], "WAVEID"); objbulk.ColumnMappings.Add(columnNames[1], "LATENCY"); objbulk.ColumnMappings.Add(columnNames[2], "DURATION"); objbulk.ColumnMappings.Add(columnNames[3], "AMPLITUDE"); objbulk.ColumnMappings.Add(columnNames[4], "AREA"); } else if (columnNames.Length == 4) { objbulk.ColumnMappings.Add(columnNames[0], "WAVEID"); objbulk.ColumnMappings.Add(columnNames[1], "LATENCY"); objbulk.ColumnMappings.Add(columnNames[3], "AMPLITUDE"); objbulk.ColumnMappings.Add(columnNames[4], "AREA"); } /* Computation of FCV */ object minLatObject; // object that contains the FLatMin minLatObject = Exceldt.Compute("Min(" + columnNames[1] + ")", string.Empty); float fcvMax = 2 * limpLength * 10 / (Convert.ToSingle(minLatObject) - mLat - 1); // limpLength * 10 to convert it to mm float numerator = fcvMax * (Convert.ToSingle(minLatObject) - 1); Exceldt.Columns.Add("FCV", typeof(float)); int notUsedRows = 0; foreach (DataRow row in Exceldt.Rows) { if (row[columnNames[0]] == DBNull.Value || row[columnNames[1]] == DBNull.Value) //check if WaveID or LATENCY columns are empty { row.Delete(); // if a row is empty, delete it from the DataTable notUsedRows++; // unfortunately, even if you delete the row Exceldt.Rows.Count still counts this row } else if (row[columnNames[1]] != DBNull.Value) //check Latency column { row["FCV"] = numerator / (Convert.ToSingle(row[columnNames[1]]) - 1); // computation of FCV for that row } } objbulk.ColumnMappings.Add("FCV", "FCV"); /* Insert Datatable Records to DataBase */ try { SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("fwavesDB")); objbulk.WriteToServer(Exceldt); objbulk.Close(); /********** Insert Data to dbo.MeasurementsID *************/ using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("fwavesDB"))) { long lastPK; connection.OpenAsync(); lastPK = (connection.Query <long>("dbo.GetLastPK", CommandType.StoredProcedure).First()); lastPK = lastPK + 1 - Exceldt.Rows.Count + notUsedRows;// Exceldt.Rows.Count contains the empty rows as well fileName = System.IO.Path.GetFileName(fileName); //$"select * from People where LastName='{lastName}'" - SQL injection threat List <AddMeasurement> measurement = new List <AddMeasurement>(); measurement.Add(new AddMeasurement { AMKA = amka, Date = date, FileName = fileName, PK = lastPK, Neuron = neuron, Side = side, Mlat = mLat, Marea = marea, Mamp = mamp, Fper = fper, FRepPer = fRepPer, RNs = rNs, RN5Rep = rN5rep, FRepsTotal = fRepsTotal }); try { connection.Execute("dbo.MeasurementsInsert @AMKA, @DATE, @FILENAME, @PK, @Neuron, @Side, @Mlat, @Marea, @Mamp, @Fper, @FRepPer, @RNs, @RN5Rep, @FRepsTotal", measurement); } //$"select * from People where LastName='{lastName}'" - SQL injection threat catch (Exception ex) { MessageBox.Show(string.Format("Data has not been Imported due to: {0}", ex.Message), "Not Imported"); } } } catch (Exception ex) { MessageBox.Show(string.Format("Data has not been Imported due to: {0}", ex.Message), "Not Imported"); } } catch (Exception ex) { MessageBox.Show(string.Format("Data has not been Imported due to: {0}", ex.Message), "Not Imported"); } }
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 async Task <bool> DoLookupAsync(Bespoke.PosEntt.Vasns.Domain.Vasn item, Bespoke.PosEntt.Adapters.Oal.dbo_ips_import destination) { Func <string, string> SetContent = (content) => { if (string.IsNullOrWhiteSpace(content)) { return(null); } return(content.Equals("01") ? "M" : "D"); }; Func <string, string> SetOriginCountryCode = (origin) => { if (!string.IsNullOrWhiteSpace(origin)) { return(origin); } else { var pattern = @"\w{2}\d{9}(?<country>\w{2})"; var match = System.Text.RegularExpressions.Regex.Match(item.ConsignmentNo, pattern); return(match.Success ? match.Groups["country"].Value : "-"); } }; Func <string, string> SetDestinationCountryCode = (dest) => { return(!string.IsNullOrWhiteSpace(dest) ? dest : null); }; var config = ConfigurationManager.ConnectionStrings["oal"].ConnectionString; var connectionString = @config; const string queryString = @"SELECT [item_category], [shipper_address_country], [consignee_address_country], [weight_double] FROM [dbo].[consignment_initial] WHERE [number] = @consignmentNo"; using (var connection = new System.Data.SqlClient.SqlConnection(connectionString)) { using (var command = new System.Data.SqlClient.SqlCommand(queryString, connection)) { command.Parameters.Add("@consignmentNo", SqlDbType.VarChar, 255).Value = item.ConsignmentNo.ToDbNull(); await connection.OpenAsync(); using (var reader = await command.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { destination.content = SetContent(reader["item_category"].ReadNullableString()); destination.orig_country_cd = SetOriginCountryCode(reader["shipper_address_country"].ReadNullableString()); destination.dest_country_cd = SetDestinationCountryCode(reader["consignee_address_country"].ReadNullableString()); destination.item_weight_double = reader["weight_double"].ReadNullable <double>(); } } } } if (string.IsNullOrWhiteSpace(destination.orig_country_cd)) { var pattern = @"\w{2}\d{9}(?<country>\w{2})"; var match = System.Text.RegularExpressions.Regex.Match(item.ConsignmentNo, pattern); if (match.Success) { destination.orig_country_cd = match.Groups["country"].Value; } } if (null == destination.item_weight_double) { destination.item_weight_double = 0d; } return(true); }
public async Task <ValidationResult> ValidationConnection(DataStream stream) { var result = new ValidationResult(); /* be careful when updating the SQL below, the rdr uses field indexes, * if this wasn't so small and self contained, I probably wouldn't be so lazy, * buf for one field...well...moving on.*/ var sql = $@" select b.name as ColumnName, type_name(b.xusertype) ColumnType, b.IsNullable, columnproperty(a.id, b.name, 'isIdentity') IsIdentity, sm.text AS DefaultValue from sysobjects a inner join syscolumns b on a.id = b.id LEFT JOIN sys.syscomments sm ON sm.id = b.cdefault WHERE a.xtype = 'U' and a.name = @tableName"; var fields = new List <SQLFieldMetaData>(); using (var cn = new System.Data.SqlClient.SqlConnection(_connectionString)) using (var cmd = new System.Data.SqlClient.SqlCommand(sql, cn)) { cmd.Parameters.AddWithValue("@tableName", stream.DBTableName); try { await cn.OpenAsync(); using (var rdr = await cmd.ExecuteReaderAsync()) { while (await rdr.ReadAsync()) { fields.Add(new SQLFieldMetaData() { ColumnName = rdr["ColumnName"].ToString(), IsRequired = !Convert.ToBoolean(rdr["IsNullable"]), DataType = rdr["ColumnType"].ToString(), IsIdentity = Convert.ToBoolean(rdr["IsIdentity"]), DefaultValue = Convert.ToString(rdr["DefaultValue"]) }); } } } catch (Exception ex) { result.AddUserError($"Could not access SQL Server: {ex.Message}."); return(result); } } if (fields.Count == 0) { result.AddUserError($"Table [{stream.DBTableName}] name not found on SQL Server database [{stream.DBName}] on server [{stream.DBURL}."); } else { result.Concat(stream.ValidateSQLSeverMetaData(fields)); } return(result); }
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<Album> GetAlbum(long albumId) { Album albumAggregate; using(var connection = new SqlConnection(_connectionString)) { var cmd = new CommandDefinition(SqlStatements.SelectAlbumAggregate, new { albumId = albumId }); await connection.OpenAsync(); var albums = await connection.QueryAsync<Album, Artist, AlbumArt, Album>(cmd, (album, artist, art) => { album.Artist = artist; album.AlbumArt = art; return album; }); albumAggregate = albums.FirstOrDefault(); if (albumAggregate == null) return null; cmd = new CommandDefinition(SqlStatements.SelectAlbumTracksByAlbum, new { albumId = albumId }); var tracks = await connection.QueryAsync<AlbumTrack>(cmd); var trackList = tracks.OrderBy(track => track.TrackNumber) .Select(track => new Track { Id = track.Id, Name = track.Name, DuationMs = track.DurationMs }) .ToList(); albumAggregate.TrackList = trackList; } return albumAggregate; }