/// <inheritdoc/> public async Task<FileDetails> GetDetailsForFileById(int id) { FileDetails details = null; using (var connection = new SqlConnection(connectionString)) { using (var command = new SqlCommand("GetFileMetaDatabyId", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("Id", id); await connection.OpenAsync(); using (var reader = await command.ExecuteReaderAsync()) { if (await reader.ReadAsync()) { details = new FileDetails(); details.ApplicationName = reader["ApplicationName"].ToString(); details.FileName = reader["FileName"].ToString(); details.FileId = (int)reader["Id"]; details.MimeType = reader["MimeType"].ToString(); details.StoragePath = reader["InternalStoragePath"].ToString(); details.Checksum = reader["Checksum"].ToString(); details.DateStoredUtc = (DateTime)reader["DateStoredUtc"]; } } } } return details; }
/// <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_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); }
public static async Task<ServiceTable> GetServiceTableAsync(string ConnString, int ServiceTableID, TaskLoggingHelper Log) { ServiceTable result = new ServiceTable(); SqlConnection conn = new SqlConnection(ConnString); conn.Open(); SqlCommand cmd = new SqlCommand("select ServiceTableID, DescServiceTable, Value, CreationDate, StringField1, StringField2 " + "from ServiceTable where ServiceTableID = @ServiceTableID", conn); using (conn) { SqlParameter p1 = cmd.Parameters.Add("@ServiceTableID", SqlDbType.Int); p1.Value = ServiceTableID; SqlDataReader rd = await cmd.ExecuteReaderAsync(); rd.Read(); using (rd) { result.ServiceTableID = rd.GetInt32(0); result.DescServiceTable = rd.GetString(1); result.Value = (float)rd.GetDouble(2); result.CreationDate = rd.GetDateTime(3); result.StringField1 = rd.GetString(4); result.StringField2 = rd.GetString(5); } } if (Log != null) Log.LogMessage("Getting ServiceTableID: " + ServiceTableID.ToString()); return result; }
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); }
/// <summary> /// Returns List of Property objects based on the table /// </summary> /// <param name="conString">User input Connection String</param> /// <param name="query">Query based on table</param> /// <returns>Returns list of columns, reference to a table</returns> public async Task<IEnumerable<Property>> ExecuteReaderOnColumnsAsync(string conString, string query) { var resultList = new List<Property>(); using (var connection = new SqlConnection(conString)) { connection.Open(); string commandText = query; SqlCommand command = new SqlCommand { CommandText = commandText, Connection = connection }; using (var reader = await command.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { resultList.Add(new Property { TableName = reader.GetString(0), ColumnName = reader.GetString(1), DataType = reader.GetString(2), MaxLength = reader.GetValue(3).ToString(), IsNullable = reader.GetString(4) == "YES", Schema = reader.GetString(5), TableCatalog = reader.GetString(6) }); } } } return resultList; }
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 async Task<List<Session>> GetSpeakerSessions(int speakerId) { Debug.WriteLine("START: GetSpeakerSessions"); var list = new List<Session>(); using (var connection = new SqlConnection("Data Source=(local); Initial Catalog=MADExpoKJ;Integrated Security=SSPI;")) { using (var command = new SqlCommand("SELECT * FROM Session WHERE SpeakerId = @SpeakerId ORDER BY Title", connection)) { command.Parameters.AddWithValue("SpeakerId", speakerId); connection.Open(); using (var reader = await command.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { var session = new Session { SessionId = reader.GetInt32(0), SpeakerId = reader.GetInt32(1), Title = reader.GetString(2), Level = reader.GetInt16(3), Abstract = reader.GetString(4) }; list.Add(session); } } } } Debug.WriteLine("END: GetSpeakerSessions"); return list; }
public async Task<Speaker> GetSpeaker(int speakerId) { Debug.WriteLine("START: GetSpeaker"); using (var connection = new SqlConnection("Data Source=(local); Initial Catalog=MADExpoKJ;Integrated Security=SSPI;")) { using (var command = new SqlCommand("SELECT * FROM Speaker WHERE SpeakerId = @SpeakerId", connection)) { command.Parameters.AddWithValue("SpeakerId", speakerId); connection.Open(); using (var reader = await command.ExecuteReaderAsync(CommandBehavior.SingleRow)) { while (await reader.ReadAsync()) { Debug.WriteLine("END: GetSpeaker"); return new Speaker { SpeakerId = reader.GetInt32(0), FirstName = reader.GetString(1), LastName = reader.GetString(2), Twitter = reader.IsDBNull(3) ? null : reader.GetString(3), Bio = reader.GetString(4) }; } return null; } } } }
public async Task<IList<Speaker>> GetSpeakers() { var list = new List<Speaker>(); using (var connection = new SqlConnection("Data Source=(local); Initial Catalog=MADExpoKJ;Integrated Security=SSPI;")) { using (var command = new SqlCommand("SELECT * FROM Speaker ORDER BY LastName, FirstName", connection)) { connection.Open(); using (var reader = await command.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { var speaker = new Speaker { SpeakerId = reader.GetInt32(0), FirstName = reader.GetString(1), LastName = reader.GetString(2), Twitter = reader.IsDBNull(3) ? null : reader.GetString(3), Bio = reader.GetString(4) }; list.Add(speaker); } } } } return list; }
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); }
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 static Task<List<Speaker>> Select( SqlConnection Connection, SqlTransaction Transaction = null ) { List<Speaker> Speakers = new List<Speaker>(); using( SqlCommand Command = new SqlCommand() ) { Command.Connection = Connection; if( null != Transaction ) Command.Transaction = Transaction; Command.CommandText = "SELECT Id, EmailAddress, DisplayName, PasswordHash, Bio, Twitter, Website, DisplayEmail, DisplayTwitter, DisplayWebsite, SessionToken, SessionExpires FROM Speakers WHERE ID IN ( SELECT DISTINCT SpeakerId FROM Sessions WHERE Accepted = 1 ) ORDER BY DisplayName"; using( SqlDataReader DataReader = await Command.ExecuteReaderAsync() ) { while( DataReader.Read() ) { Speaker FoundSpeaker = new Speaker(); FoundSpeaker.Id = DataReader.GetInt16( 0 ); FoundSpeaker.EmailAddress = DataReader.GetString( 1 ); FoundSpeaker.DisplayName = DataReader.GetString( 2 ); FoundSpeaker.PasswordHash = DataReader.GetSqlBinary( 3 ).Value; FoundSpeaker.Bio = DataReader.IsDBNull( 4 ) ? null : DataReader.GetString( 4 ); FoundSpeaker.Twitter = DataReader.IsDBNull( 5 ) ? null : DataReader.GetString( 5 ); FoundSpeaker.Website = DataReader.IsDBNull( 6 ) ? null : DataReader.GetString( 6 ); FoundSpeaker.DisplayEmail = DataReader.IsDBNull( 7 ) ? false : DataReader.GetBoolean( 7 ); FoundSpeaker.DisplayTwitter = DataReader.IsDBNull( 8 ) ? false : DataReader.GetBoolean( 8 ); FoundSpeaker.DisplayWebsite = DataReader.IsDBNull( 9 ) ? false : DataReader.GetBoolean( 9 ); FoundSpeaker.SessionToken = DataReader.IsDBNull( 10 ) ? Guid.Empty : DataReader.GetGuid( 10 ); FoundSpeaker.SessionExpires = DataReader.IsDBNull( 11 ) ? DateTime.MinValue : DataReader.GetDateTime( 11 ); Speakers.Add( FoundSpeaker ); } } } return Speakers; }
/// <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 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 static Task<SqlDataReader> ExecuteReaderAsyncWithRetry( SqlCommand command, CancellationToken cancellationToken, IRetryPolicy retryPolicy) { return retryPolicy.ExecuteAsyncWithRetry(() => command.ExecuteReaderAsync(cancellationToken)); }
public static Task<SqlDataReader> ExecuteReaderAsyncWithRetry( SqlCommand command, CommandBehavior behavior, IRetryPolicy retryPolicy) { return retryPolicy.ExecuteAsyncWithRetry(() => command.ExecuteReaderAsync(behavior)); }
public async Task<IReadOnlyCollection<Entry>> Query() { using (var connection = new SqlConnection(connectionString)) { connection.Open(); using (var transaction = connection.BeginTransaction()) { using (var command = new SqlCommand(@" SELECT [Owner], [Type], [Value] FROM [Data] WHERE [Owner] <> @Owner ", connection, transaction)) { command.Parameters.AddWithValue("Owner", owner).DbType = DbType.AnsiString; var results = new List<Entry>(); using (var reader = await command.ExecuteReaderAsync()) { while (reader.Read()) { results.Add(new Entry((string)reader[0], (string)reader[1], (string)reader[2])); } } return results; } } } }
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 static Task<Speaker> Select( int Id, SqlConnection Connection, SqlTransaction Transaction = null ) { Speaker FoundSpeaker = null; using( SqlCommand Command = new SqlCommand() ) { Command.Connection = Connection; if( null != Transaction ) Command.Transaction = Transaction; Command.CommandText = "SELECT Id, EmailAddress, DisplayName, PasswordHash, Bio, Twitter, Website, DisplayEmail, DisplayTwitter, DisplayWebsite, SessionToken, SessionExpires FROM Speakers WHERE Id = @Id;"; Command.Parameters.Add( "Id", System.Data.SqlDbType.SmallInt ).Value = Id; using( SqlDataReader DataReader = await Command.ExecuteReaderAsync() ) { if( DataReader.Read() ) { FoundSpeaker = new Speaker(); FoundSpeaker.Id = DataReader.GetInt16( 0 ); FoundSpeaker.EmailAddress = DataReader.GetString( 1 ); FoundSpeaker.DisplayName = DataReader.GetString( 2 ); FoundSpeaker.PasswordHash = DataReader.GetSqlBinary( 3 ).Value; FoundSpeaker.Bio = DataReader.IsDBNull( 4 ) ? null : DataReader.GetString( 4 ); FoundSpeaker.Twitter = DataReader.IsDBNull( 5 ) ? null : DataReader.GetString( 5 ); FoundSpeaker.Website = DataReader.IsDBNull( 6 ) ? null : DataReader.GetString( 6 ); FoundSpeaker.DisplayEmail = DataReader.IsDBNull( 7 ) ? false : DataReader.GetBoolean( 7 ); FoundSpeaker.DisplayTwitter = DataReader.IsDBNull( 8 ) ? false : DataReader.GetBoolean( 8 ); FoundSpeaker.DisplayWebsite = DataReader.IsDBNull( 9 ) ? false : DataReader.GetBoolean( 9 ); FoundSpeaker.SessionToken = DataReader.IsDBNull( 10 ) ? Guid.Empty : DataReader.GetGuid( 10 ); FoundSpeaker.SessionExpires = DataReader.IsDBNull( 11 ) ? DateTime.MinValue : DataReader.GetDateTime( 11 ); } } } return FoundSpeaker; }
/// <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 OpenConnectionAndExecuteReaderAsync() { m_connection = m_connectionFactory.CreateConnection(); await m_connection.OpenAsync(); m_command = m_connection.CreateCommand(); m_setupCommandAction( m_command ); m_reader = await m_command.ExecuteReaderAsync(); }
async Task<object> ExecuteQuery(string connectionString, string commandString, IDictionary<string, object> parameters) { List<object> rows = new List<object>(); using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand command = new SqlCommand(commandString, connection)) { this.AddParamaters(command, parameters); await connection.OpenAsync(); using (SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection)) { object[] fieldNames = new object[reader.FieldCount]; for (int i = 0; i < reader.FieldCount; i++) { fieldNames[i] = reader.GetName(i); } rows.Add(fieldNames); IDataRecord record = (IDataRecord)reader; while (await reader.ReadAsync()) { object[] resultRecord = new object[record.FieldCount]; record.GetValues(resultRecord); for (int i = 0; i < record.FieldCount; i++) { Type type = record.GetFieldType(i); if (resultRecord[i] is System.DBNull) { resultRecord[i] = null; } else if (type == typeof(byte[]) || type == typeof(char[])) { resultRecord[i] = Convert.ToBase64String((byte[])resultRecord[i]); } else if (type == typeof(Guid) || type == typeof(DateTime)) { resultRecord[i] = resultRecord[i].ToString(); } else if (type == typeof(IDataReader)) { resultRecord[i] = "<IDataReader>"; } } rows.Add(resultRecord); } } } } return rows; }
public async Task<object> Invoke(IDictionary<string, object> parameters) { var commandText = (string)parameters["source"]; var connectionString = (string)parameters["connectionString"]; var dataSets = new List<List<object>>(); var rows = new List<object>(); using (var connection = new SqlConnection(connectionString)) { using (var command = new SqlCommand(commandText, connection)) { await connection.OpenAsync(); using (var reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection)) { var record = (IDataRecord)reader; do { while (await reader.ReadAsync()) { var dataObject = new ExpandoObject() as IDictionary<string, Object>; var resultRecord = new object[record.FieldCount]; record.GetValues(resultRecord); for (int i = 0; i < record.FieldCount; i++) { var type = record.GetFieldType(i); if (type == typeof(byte[]) || type == typeof(char[])) { resultRecord[i] = Convert.ToBase64String((byte[])resultRecord[i]); } else if (type == typeof(Guid) || type == typeof(DateTime)) { resultRecord[i] = resultRecord[i].ToString(); } else if (type == typeof(IDataReader)) { resultRecord[i] = "<IDataReader>"; } dataObject.Add(record.GetName(i), resultRecord[i]); } rows.Add(dataObject); } dataSets.Add(rows); rows = new List<object>(); } while(await reader.NextResultAsync()); } } } return dataSets; }
async private void ExecuteButton_Click(object sender, RoutedEventArgs e) { try { var builder = new SqlConnectionStringBuilder(); builder.DataSource = serverIdTxt.Text; builder.IntegratedSecurity = SqlradioButton.IsChecked != null ? SqlradioButton.IsChecked.Value : false; if(!builder.IntegratedSecurity) { builder.UserID = LoginTxt.Text; builder.Password = PassTxt.Text; } // builder.InitialCatalog = Databasetxt.Text; status.Text = "Connecting to Database"; SqlConnection conn = new SqlConnection(builder.ConnectionString); await conn.OpenAsync(); status.Text = "Executing SQLCommand"; var query = CommandTxt.Text; SqlCommand comm = new SqlCommand(query, conn); var reader =await comm.ExecuteReaderAsync(); var result = new DataTable(); // for (int i = 0; i < reader.FieldCount; i++) { result.Columns.Add(reader.GetName(i)); } while(await reader.ReadAsync()) { var row = result.NewRow(); for (int i = 0; i < reader.FieldCount; i++) { row[i] = reader[i]; } result.Rows.Add(row); } conn.Close(); conn.Dispose(); dataGrid.ItemsSource = result.DefaultView; } catch(Exception ex) { MessageBox.Show(ex.Message); } }
// Application transferring a large Xml Document from SQL Server in .NET 4.5 private static async Task PrintXmlValues() { using (SqlConnection connection = new SqlConnection(connectionString)) { await connection.OpenAsync(); using (SqlCommand command = new SqlCommand("SELECT [id], [ConstructData] FROM [Factors]", connection)) { // The reader needs to be executed with the SequentialAccess behavior to enable network streaming // Otherwise ReadAsync will buffer the entire Xml Document into memory which can cause scalability issues or even OutOfMemoryExceptions using (SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess)) { while (await reader.ReadAsync()) { Console.WriteLine("{0}: ", reader.GetGuid(0)); if (await reader.IsDBNullAsync(1)) { Console.WriteLine("\t(NULL)"); } else { using (XmlReader xmlReader = reader.GetXmlReader(1)) { int depth = 1; // NOTE: The XmlReader returned by GetXmlReader does NOT support async operations // See the example below (PrintXmlValuesViaNVarChar) for how to get an XmlReader with asynchronous capabilities while (xmlReader.Read()) { switch (xmlReader.NodeType) { case XmlNodeType.Element: Console.WriteLine("{0}<{1}>", new string('\t', depth), xmlReader.Name); depth++; break; case XmlNodeType.Text: Console.WriteLine("{0}{1}", new string('\t', depth), xmlReader.Value); break; case XmlNodeType.EndElement: depth--; Console.WriteLine("{0}</{1}>", new string('\t', depth), xmlReader.Name); break; } } } } } } } } }
public async Task<User> AuthenticateAsync(string username, string password) { using (var connection = new SqlConnection(_connectionString)) { await connection.OpenAsync(); var command = new SqlCommand( " SELECT TOP 1 U.[Username], U.[EmailAddress], U.[ApiKey], C.[Type] AS [CredentialType], C.[Value] AS [CredentialValue]" + " FROM [dbo].[Users] AS U" + " INNER JOIN [dbo].[Credentials] AS C ON C.[UserKey] = U.[Key]" + " WHERE (C.[Type] IN ('password.pbkdf2', 'password.sha512', 'password.sha1'))" + " AND (Username = @UsernameOrEmail OR EmailAddress = @UsernameOrEmail)", connection); command.Parameters.AddWithValue("@UsernameOrEmail", username); var reader = await command.ExecuteReaderAsync(); if (await reader.ReadAsync()) { // Validate credential var validCredential = false; var credentialType = reader["CredentialType"].ToString(); var credentialValue = reader["CredentialValue"].ToString(); if (credentialType == "password.pbkdf2") { validCredential = CryptographyService.ValidateSaltedHash(credentialValue, password, Constants.PBKDF2HashAlgorithmId); } else if (credentialType == "password.sha512") { validCredential = CryptographyService.ValidateSaltedHash(credentialValue, password, Constants.Sha512HashAlgorithmId); } else if (credentialType == "password.sha1") { validCredential = CryptographyService.ValidateSaltedHash(credentialValue, password, Constants.Sha1HashAlgorithmId); } // Valid? If so, return a user instance if (validCredential) { return new User { Username = reader["Username"].ToString(), EmailAddress = reader["EmailAddress"].ToString(), ApiKey = reader["ApiKey"].ToString() }; } } } return null; }
private async Task ReadSqlData() { using (var connection = new SqlConnection(_connectionString)) { connection.Open(); var command = new SqlCommand("SELECT Id, Forename, Surname FROM Customers WHERE ID = 'B9F83382-2A28-4461-83E2-9CC700DF1AB9'", connection); using (var reader = await command.ExecuteReaderAsync()) { while (reader.Read()) { } } } }
public async static Task<Session> Select( int Id, SqlConnection Connection, SqlTransaction Transaction = null ) { short SpeakerId = -1; Session FoundSession = null; using( SqlCommand Command = new SqlCommand() ) { Command.Connection = Connection; if( null != Transaction ) Command.Transaction = Transaction; Command.CommandText = "SELECT Sessions.Id, SpeakerId, Title, Abstract, Notes, Accepted, Text AS TimeSlot FROM Sessions LEFT JOIN TimeSlots ON Sessions.TimeSlotId = TimeSlots.Id WHERE Sessions.Id = @Id;"; Command.Parameters.Add( "Id", System.Data.SqlDbType.SmallInt ).Value = Id; using( SqlDataReader DataReader = await Command.ExecuteReaderAsync() ) { if( DataReader.Read() ) { FoundSession = new Session(); FoundSession.Id = DataReader.GetInt16( 0 ); SpeakerId = DataReader.GetInt16( 1 ); FoundSession.Title = DataReader.GetString( 2 ); FoundSession.Abstract = DataReader.GetString( 3 ); FoundSession.Notes = DataReader.IsDBNull( 4 ) ? null : DataReader.GetString( 4 ); FoundSession.Accepted = DataReader.IsDBNull( 5 ) ? false : DataReader.GetBoolean( 5 ); FoundSession.TimeSlot = DataReader.IsDBNull( 6 ) ? null : DataReader.GetString( 6 ); } } } if( null != FoundSession ) { FoundSession.Speaker = await Speaker.Select( SpeakerId, Connection ); FoundSession.Tags = new List<Tag>(); using( SqlCommand Command = new SqlCommand() ) { Command.Connection = Connection; if( null != Transaction ) Command.Transaction = Transaction; Command.CommandText = "SELECT Id, Text FROM Tags WHERE Id IN ( SELECT TagId FROM SessionTags WHERE SessionId = @SessionId );"; Command.Parameters.Add( "SessionId", System.Data.SqlDbType.SmallInt ).Value = FoundSession.Id; using( SqlDataReader DataReader = await Command.ExecuteReaderAsync() ) { while( DataReader.Read() ) { FoundSession.Tags.Add( new Tag { Id = DataReader.GetInt16( 0 ), Text = DataReader.GetString( 1 ) } ); } } } } return FoundSession; }
public static async Task SelectMultipleResultSets() { string connectionString = ConfigurationManager.ConnectionStrings["ProgrammingCSharpConnection"].ConnectionString; using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand("SELECT * FROM People; SELECT TOP 1 * FROM People ORDER BY LastName", connection); await connection.OpenAsync(); SqlDataReader dataReader = await command.ExecuteReaderAsync(); await ReadQueryResults(dataReader); await dataReader.NextResultAsync(); // Move to the next result set await ReadQueryResults(dataReader); dataReader.Close(); } }
public async Task<User> Authenticate(AuthenticationRequest authRequest) { string procName = "proc_Users_Authenticate"; Task<User> t = Task.Run<User>(async () => { User currentUser = null; SqlCommand cmd = null; using (SqlConnection con = new SqlConnection(conString)) { await con.OpenAsync(); using (cmd = new SqlCommand(procName, con)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@userName", authRequest.UserName); cmd.Parameters.AddWithValue("@password", authRequest.Password); var rdr = await cmd.ExecuteReaderAsync(System.Data.CommandBehavior.Default); if (rdr.HasRows) { currentUser = new User(); while (rdr.Read()) { currentUser.UserID = int.Parse(rdr["UserID"].ToString()); currentUser.UserName = rdr["UserName"].ToString(); currentUser.SessionGuid = rdr["SessionGuid"].ToString(); Board x = new Board() { BoardID = int.Parse(rdr["BoardID"].ToString()), BoardName = rdr["BoardName"].ToString(), UserID = int.Parse(rdr["UserID"].ToString()) }; currentUser.BoardList.Add(x); } } } } return currentUser; }); return await t; }
public static async Task SelectDataFromTable() { // string connectionString = ConfigurationManager.ConnectionStrings[ConfigurationManager.ConnectionStrings["CONNECTIONSTRING"].ConnectionString].ConnectionString; string connectionString = ConfigurationManager.ConnectionStrings[".CONNECTIONSTRING"].ConnectionString; using (SqlConnection connection = new SqlConnection(connectionString)) { //Executing a SQL query with multiple result sets SqlCommand command = new SqlCommand("SELECT * FROM KEPLER_CUSTOMER; SELECT * FROM WF_DATA", connection); await connection.OpenAsync(); SqlDataReader dataReader = await command.ExecuteReaderAsync(); await ReadQueryResults(dataReader); await dataReader.NextResultAsync(); // Move to the next result set await ReadQueryResults(dataReader); dataReader.Close(); } }
public async Task<IEnumerable<Car>> GetCarsViaSPAsync() { using (var conn = new SqlConnection(_connectionString)) { using (var cmd = new SqlCommand()) { cmd.Connection = conn; cmd.CommandText = _spName; cmd.CommandType = CommandType.StoredProcedure; conn.Open(); using (var reader = await cmd.ExecuteReaderAsync()) { return reader.Select(r => carBuilder(r)).ToList(); } } } }
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 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); } }
protected async Task <System.Data.DataSet> ExecuteAsync(sql.SqlCommand command) //, IContextInfo contextInfo { var dataSet = new System.Data.DataSet(); using (var connection = new sql.SqlConnection(_connectionString)) { //connection.StateChange += new System.Data.StateChangeEventHandler((sender, e) => //{ // if (e.CurrentState == System.Data.ConnectionState.Open) // setContextInfo(connection, contextInfo); //}); command.Connection = connection; await connection.TryOpenAsync(); using (var reader = await command.ExecuteReaderAsync()) { do { if (reader.HasRows && reader.FieldCount > 0) { using (var table = dataSet.Tables.Add()) { table.Load(reader); } } else { reader.Close(); } } while (!reader.IsClosed); } //clearContextInfo(connection, contextInfo); connection.TryClose(); } return(dataSet); }
public async Task <LagoVista.Core.Models.UIMetaData.ListResponse <DataStreamResult> > GetItemsAsync(string deviceId, LagoVista.Core.Models.UIMetaData.ListRequest request) { var sql = new StringBuilder("select "); if (request.PageSize == 0) { request.PageSize = 50; } sql.Append($"[{_stream.TimeStampFieldName}]"); foreach (var fld in _stream.Fields) { sql.Append($", [{fld.FieldName}]"); } sql.AppendLine(); sql.AppendLine($" from [{_stream.DBTableName}]"); sql.AppendLine($" where [{_stream.DeviceIdFieldName}] = @deviceId"); if (!String.IsNullOrEmpty(request.NextRowKey)) { sql.AppendLine($" and {_stream.TimeStampFieldName} < @lastDateStamp"); } if (!String.IsNullOrEmpty(request.StartDate)) { sql.AppendLine($" and {_stream.TimeStampFieldName} >= @startDateStamp"); } if (!String.IsNullOrEmpty(request.EndDate)) { sql.AppendLine($" and {_stream.TimeStampFieldName} <= @endDateStamp"); } sql.AppendLine($" order by [{_stream.TimeStampFieldName}] desc"); sql.AppendLine(" OFFSET @PageSize * @PageIndex ROWS"); sql.AppendLine(" FETCH NEXT @PageSize ROWS ONLY "); Console.WriteLine(sql.ToString()); var responseItems = new List <DataStreamResult>(); using (var cn = new System.Data.SqlClient.SqlConnection(_connectionString)) using (var cmd = new System.Data.SqlClient.SqlCommand(sql.ToString(), cn)) { cmd.Parameters.AddWithValue("@deviceId", deviceId); cmd.Parameters.AddWithValue("@PageSize", request.PageSize); cmd.Parameters.AddWithValue("@PageIndex", request.PageIndex); if (!String.IsNullOrEmpty(request.NextRowKey)) { cmd.Parameters.AddWithValue($"@lastDateStamp", request.NextRowKey.ToDateTime()); } if (!String.IsNullOrEmpty(request.StartDate)) { cmd.Parameters.AddWithValue($"@startDateStamp", request.StartDate.ToDateTime()); } if (!String.IsNullOrEmpty(request.EndDate)) { cmd.Parameters.AddWithValue($"@endDateStamp", request.EndDate.ToDateTime()); } cmd.CommandType = System.Data.CommandType.Text; await cmd.Connection.OpenAsync(); using (var rdr = await cmd.ExecuteReaderAsync()) { while (rdr.Read()) { var resultItem = new DataStreamResult(); resultItem.Timestamp = Convert.ToDateTime(rdr[_stream.TimeStampFieldName]).ToJSONString(); foreach (var fld in _stream.Fields) { resultItem.Fields.Add(fld.FieldName, rdr[fld.FieldName]); } responseItems.Add(resultItem); } } } var response = new Core.Models.UIMetaData.ListResponse <DataStreamResult>(); response.Model = responseItems; response.PageSize = responseItems.Count; response.PageIndex = request.PageIndex; response.HasMoreRecords = responseItems.Count == request.PageSize; if (response.HasMoreRecords) { response.NextRowKey = responseItems.Last().Timestamp; } return(response); }
public async Task <List <DynoCard> > GetPreviousCards(DynoCardAnomalyResult anomalyCard) { DateTime start = anomalyCard.Timestamp.Subtract(TimeSpan.FromMinutes(30)); DateTime end = anomalyCard.Timestamp; int startEpoch = (int)(start.ToUniversalTime().Subtract(new DateTime(1970, 1, 1))).TotalSeconds; int endEpoch = (int)(end.ToUniversalTime().Subtract(new DateTime(1970, 1, 1))).TotalSeconds; System.Console.WriteLine($"Start: {startEpoch}"); System.Console.WriteLine($"End: {endEpoch}"); var sql = new StringBuilder() .Append("WITH cteEndCardID (cardID) ") .Append("AS ( ") .Append("SELECT TOP 1 h.DC_ID ") .Append("FROM ACTIVE.CARD_HEADER h ") .Append($"WHERE h.CH_EPOC_DATE >= {endEpoch} ") .Append("ORDER BY h.CH_EPOC_DATE ), ") .Append("cteStartCardID(cardID) ") .Append("AS ( ") .Append("SELECT TOP 1 h.DC_ID ") .Append("FROM ACTIVE.CARD_HEADER h ") .Append($"WHERE h.CH_EPOC_DATE <= {startEpoch} ") .Append("OR h.CH_EPOC_DATE = (SELECT MIN(CH_EPOC_DATE) FROM ACTIVE.CARD_HEADER) ") .Append("ORDER BY h.DC_ID DESC) ") .Append("SELECT h.CH_CARD_TYPE, ") .Append("dc.DC_ID, ") .Append("h.CH_ID, ") .Append("h.CH_SCALED_MAX_LOAD, ") .Append("h.CH_SCALED_MIN_LOAD, ") .Append("h.CH_STROKE_LENGTH, ") .Append("h.CH_STROKE_PERIOD, ") .Append("h.CH_GROSS_STROKE, ") .Append("h.CH_NET_STROKE, ") .Append("h.CH_PUMP_FILLAGE, ") .Append("h.CH_FLUID_LOAD, ") .Append("d.CD_ID, ") .Append("d.CD_POSITION, ") .Append("d.CD_LOAD, ") .Append("h.CH_EPOC_DATE, ") .Append("dc.PU_ID, ") .Append("h.CH_NUMBER_OF_POINTS ") .Append("FROM [ACTIVE].[DYNO_CARD] dc ") .Append("JOIN [ACTIVE].[CARD_HEADER] h ON dc.DC_ID = h.DC_ID ") .Append("JOIN [ACTIVE].[CARD_DETAIL] d ON h.CH_ID = d.CH_ID ") .Append("JOIN cteStartCardID sc ON h.DC_ID >= sc.cardID ") .Append("JOIN cteEndCardID ec ON h.DC_ID <= ec.cardID ") .Append("ORDER BY h.CH_ID DESC"); Dictionary <int, DynoCard> cardList = new Dictionary <int, DynoCard>(); // //Store the data in SQL db using (Sql.SqlConnection conn = new Sql.SqlConnection(ConnectionString)) { conn.Open(); using (Sql.SqlCommand cardHistorySelect = new Sql.SqlCommand(sql.ToString(), conn)) { var results = await cardHistorySelect.ExecuteReaderAsync(); if (results.HasRows) { PumpCard pumpCard = null; SurfaceCard surfaceCard = null; int previousCardID = 0; DateTime epoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc); while (await results.ReadAsync()) { string cardTypeValue = results.GetString(0); int dynoCardID = results.GetInt32(1); CardType cardType = "P".Equals(cardTypeValue) ? CardType.Pump : CardType.Surface; if (previousCardID != dynoCardID) { previousCardID = dynoCardID; pumpCard = null; surfaceCard = null; } if (cardType == CardType.Surface) { if (surfaceCard == null) { surfaceCard = new SurfaceCard() { Id = results.GetInt32(2), Timestamp = epoch.AddSeconds(results.GetInt32(14)), ScaledMaxLoad = (int)results.GetFloat(3), ScaledMinLoad = (int)results.GetFloat(4), StrokeLength = (int)results.GetFloat(5), StrokePeriod = (int)results.GetFloat(6), NumberOfPoints = results.GetInt32(16), CardType = cardType, CardCoordinates = new List <CardCoordinate>() }; surfaceCard.CardCoordinates.Add(new CardCoordinate() { Order = results.GetInt32(11), Position = (int)results.GetFloat(12), Load = (int)results.GetFloat(13) }); DynoCard dynoCard = null; if (cardList.ContainsKey(dynoCardID)) { dynoCard = cardList[dynoCardID]; } else { dynoCard = new DynoCard(); dynoCard.Id = dynoCardID; dynoCard.Timestamp = surfaceCard.Timestamp; } dynoCard.SurfaceCard = surfaceCard; cardList[dynoCardID] = dynoCard; } else { cardList[dynoCardID].SurfaceCard.CardCoordinates.Add(new CardCoordinate() { Position = (int)results.GetFloat(12), Load = (int)results.GetFloat(13) }); } } else if (cardType == CardType.Pump) { if (pumpCard == null) { pumpCard = new PumpCard() { Id = results.GetInt32(2), Timestamp = epoch.AddSeconds(results.GetInt32(14)), ScaledMaxLoad = (int)results.GetFloat(3), ScaledMinLoad = (int)results.GetFloat(4), GrossStroke = (int)results.GetFloat(7), NetStroke = (int)results.GetFloat(8), PumpFillage = (int)results.GetFloat(9), FluidLoad = (int)results.GetFloat(10), NumberOfPoints = results.GetInt32(16), CardType = cardType, CardCoordinates = new List <CardCoordinate>() }; pumpCard.CardCoordinates.Add(new CardCoordinate() { Order = results.GetInt32(11), Position = (int)results.GetFloat(12), Load = (int)results.GetFloat(13) }); DynoCard dynoCard = null; if (cardList.ContainsKey(dynoCardID)) { dynoCard = cardList[dynoCardID]; } else { dynoCard = new DynoCard(); dynoCard.Id = dynoCardID; dynoCard.Timestamp = pumpCard.Timestamp; } dynoCard.PumpCard = pumpCard; cardList[dynoCardID] = dynoCard; } else { cardList[dynoCardID].PumpCard.CardCoordinates.Add(new CardCoordinate() { Position = (int)results.GetFloat(12), Load = (int)results.GetFloat(13) }); } } } } } } var cards = cardList?.Values?.OrderBy(c => c.Timestamp).ToList(); if (cards != null && cards.Count > 0) { cards.Last().TriggeredEvents = true; } return(await Task.FromResult(cards)); }
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)); }
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 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 <IActionResult> Get() { var method = ""; Stopwatch stopwatch = Stopwatch.StartNew(); StringBuilder stringLogger = new StringBuilder(); try { var req = Request; var res = Response; // echo headers foreach (var header in req.Headers) { res.Headers.Add(header); } var queryDictionary = Microsoft.AspNetCore.WebUtilities.QueryHelpers.ParseQuery(req.QueryString.Value); if (queryDictionary.ContainsKey("method") == false) { queryDictionary.Add("method", "plain"); } switch (queryDictionary["method"]) { case "sql-client": method = "sql-client"; var stringBuilder = new StringBuilder(); using (var conn = new System.Data.SqlClient.SqlConnection(getConnectionstring())) { using (var cmd = new System.Data.SqlClient.SqlCommand("SELECT TOP 350 * FROM dbo.[users-test];", conn)) { cmd.CommandType = System.Data.CommandType.Text; conn.Open(); var reader = cmd.ExecuteReader(); while (reader.Read()) { Guid id = reader.GetGuid(0); stringBuilder.AppendLine(id.ToString()); } } } break; case "sql-client-async": method = "sql-client-async"; var stringBuilder2 = new StringBuilder(); using (var conn = new System.Data.SqlClient.SqlConnection(getConnectionstring())) { using (var cmd = new System.Data.SqlClient.SqlCommand("SELECT TOP 350 * FROM dbo.[users-test];", conn)) { cmd.CommandType = System.Data.CommandType.Text; conn.Open(); var reader = await cmd.ExecuteReaderAsync(); while (await reader.ReadAsync()) { Guid id = reader.GetGuid(0); stringBuilder2.AppendLine(id.ToString()); } } } break; default: method = "plain"; break; } } catch (System.Exception ex) { dynamic errorResponse = new System.Dynamic.ExpandoObject(); errorResponse.Message = ex.Message; errorResponse.StackTrace = ex.StackTrace; errorResponse.Url = HttpContext.Request.GetDisplayUrl(); errorResponse.ElapsedMilliseconds = stopwatch.ElapsedMilliseconds; Console.WriteLine(JsonConvert.SerializeObject(errorResponse)); return(StatusCode(500, errorResponse)); } return(Ok(method + $" took {stopwatch.ElapsedMilliseconds}ms" + Environment.NewLine + stringLogger.ToString())); }