public DataTable GetListByDraftee(long id) { try { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder .Append("SELECT ") .Append("pl.id id, ") .Append("pl.draftee_id draftee_id, ") .Append("pl.name name, ") .Append("pl.education education, ") .Append("pl.institution_type institution_type, ") .Append("pl.end_date end_date, ") .Append("pl.faculty faculty, ") .Append("pl.specialty specialty ") .Append("FROM study_places pl ") .Append("WHERE pl.draftee_id=") .Append(id) .Append(";"); var dataAdapter = new MySqlDataAdapter(sqlBuilder.ToString(), ConnectionUtils.GetConnection()); DataTable dataTable = new DataTable(); dataAdapter.Fill(dataTable); return(dataTable); } catch { return(null); } }
public DataTable GetByDraftee(long id) { try { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder .Append("SELECT ") .Append("addr.draftee_id draftee_id, ") .Append("addr.municipal_district municipal_district, ") .Append("addr.mail_index mail_index, ") .Append("addr.street_name street_name, ") .Append("addr.house_number house_number, ") .Append("addr.housing_number housing_number, ") .Append("addr.apartment apartment, ") .Append("addr.home_phone home_phone ") .Append("FROM addresses addr ") .Append("WHERE draftee_id=") .Append(id) .Append(";"); var dataAdapter = new MySqlDataAdapter(sqlBuilder.ToString(), ConnectionUtils.GetConnection()); DataTable dataTable = new DataTable(); dataAdapter.Fill(dataTable); return(dataTable); } catch { return(null); } }
public DataTable GetListByDraftee(long id) { try { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder .Append("SELECT ") .Append("rel.id id, ") .Append("rel.draftee_id draftee_id, ") .Append("rel.full_name full_name, ") .Append("rel.relationship_type relationship_type, ") .Append("rel.birth_year birth_year, ") .Append("rel.birth_place birth_place, ") .Append("rel.work_place work_place ") .Append("FROM relatives rel ") .Append("WHERE rel.draftee_id=") .Append(id) .Append(";"); var dataAdapter = new MySqlDataAdapter(sqlBuilder.ToString(), ConnectionUtils.GetConnection()); DataTable dataTable = new DataTable(); dataAdapter.Fill(dataTable); return(dataTable); } catch { return(null); } }
static void Main(string[] args) { //获取连接 var connection = ConnectionUtils.GetConnection(); //通过连接创建通道 var channel = connection.CreateModel(); //定义队列的消费者 var consumer = new EventingBasicConsumer(channel); consumer.Received += (ch, ea) => { try { var msg = Encoding.UTF8.GetString(ea.Body.ToArray()); Console.WriteLine($"[consumer] msg:{msg}-----------------tagId:${ea.DeliveryTag}"); } catch (Exception) { throw new Exception(); } finally { channel.BasicAck(ea.DeliveryTag, false); } }; //监听队列 channel.BasicConsume(QUEUE_NAME, false, consumer); Console.ReadKey(); }
public DataTable GetByDraftee(long id) { try { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder .Append("SELECT ") .Append("doc.draftee_id draftee_id, ") .Append("doc.passport_series passport_series, ") .Append("doc.passport_number passport_number, ") .Append("doc.passport_issue_date passport_issue_date, ") .Append("doc.passport_issued_by passport_issued_by, ") .Append("doc.certificate_series certificate_series, ") .Append("doc.certificate_number certificate_number, ") .Append("doc.certificate_issue_date certificate_issue_date, ") .Append("doc.ticket_series ticket_series, ") .Append("doc.ticket_number ticket_number, ") .Append("doc.ticket_issue_date ticket_issue_date ") .Append("FROM documents doc ") .Append("WHERE doc.draftee_id=") .Append(id) .Append(";"); var dataAdapter = new MySqlDataAdapter(sqlBuilder.ToString(), ConnectionUtils.GetConnection()); DataTable dataTable = new DataTable(); dataAdapter.Fill(dataTable); return(dataTable); } catch { return(null); } }
static void Main(string[] args) { //定义连接 var connection = ConnectionUtils.GetConnection(); //从连接中获取一个通道 var channel = connection.CreateModel(); //创建队列声明 channel.QueueDeclare(QUEUE_NAME, false, false, false, null); for (int i = 0; i < 100000000; i++) { var msg = "hello simple" + i; channel.BasicPublish("", QUEUE_NAME, false, null, Encoding.UTF8.GetBytes(msg)); Console.WriteLine("--send msg:" + msg); System.Threading.Thread.Sleep(3000); } Console.ReadKey(); //channel.Close(); //connection.Close(); }
public AdoDbAccess(string key) { ConnectionElement ConnectionElement = new DS.AFP.Common.Core.ConfigurationNameSpace.DsConfigurationManager().DsRootConfigurationSection.Connections[key]; this.provider = ConnectionElement.Provider; this.ConnnectString = ConnectionElement.ConnectionString; IDbProvider.ConnectionString = ConnectionElement.ConnectionString; this.DbConnection = ConnectionUtils.GetConnection(IDbProvider); //this.DbConnection = IDbProvider.CreateConnection() as DbConnection; _AdoTemplate = new AdoTemplate(IDbProvider); }
public void Update(EducationPlace educationPlace) { MySqlConnection connection = ConnectionUtils.GetConnection(); try { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder .Append("UPDATE study_places SET ") .Append("name='") .Append(educationPlace.Name) .Append("', ") .Append("education='") .Append(educationPlace.Education) .Append("', ") .Append("institution_type='") .Append(educationPlace.InstitutionType) .Append("', "); if (educationPlace.EndDate != null) { sqlBuilder.Append("end_date='") .Append(educationPlace.EndDate.Value.ToString("yyyy-MM-dd")) .Append("', "); } else { sqlBuilder.Append("end_date=") .Append("NULL") .Append(", "); } sqlBuilder.Append("faculty='") .Append(educationPlace.Faculty) .Append("', ") .Append("specialty='") .Append(educationPlace.Specialty) .Append("' ") .Append("WHERE id=") .Append(educationPlace.Id) .Append(";"); MySqlCommand command = new MySqlCommand(sqlBuilder.ToString(), connection); connection.Open(); command.ExecuteNonQuery(); } finally { connection.Close(); } }
public DataTable GetListByCriteria(TableDrafteeCriteria criteria) { try { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder .Append("SELECT dr.id id, ") .Append("CONCAT(dr.last_name, ' ', dr.first_name, ' ', dr.patronymic) full_name, ") .Append("YEAR(dr.birth_date) birth_year ") .Append("FROM draftees dr "); bool havingExists = false; bool needAnd = false; if (!string.IsNullOrEmpty(criteria.FullName)) { sqlBuilder.Append("HAVING full_name LIKE '%") .Append(criteria.FullName) .Append("%' "); havingExists = true; needAnd = true; } if (criteria.BirthYear != null) { if (!havingExists) { sqlBuilder.Append("HAVING "); } if (needAnd) { sqlBuilder.Append("AND "); } sqlBuilder.Append("birth_year=") .Append(criteria.BirthYear.Value) .Append(" "); } sqlBuilder.Append(";"); var dataAdapter = new MySqlDataAdapter(sqlBuilder.ToString(), ConnectionUtils.GetConnection()); DataTable dataTable = new DataTable(); dataAdapter.Fill(dataTable); return(dataTable); } catch { return(null); } }
public void Insert(EducationPlace educationPlace) { MySqlConnection connection = ConnectionUtils.GetConnection(); try { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder .Append("INSERT INTO study_places ") .Append("(draftee_id, name, education, institution_type, end_date, faculty, specialty) ") .Append("VALUES (") .Append(educationPlace.DrafteeId) .Append(", ") .Append("'") .Append(educationPlace.Name) .Append("', ") .Append("'") .Append(educationPlace.Education) .Append("', ") .Append("'") .Append(educationPlace.InstitutionType) .Append("', "); if (educationPlace.EndDate != null) { sqlBuilder.Append("'") .Append(educationPlace.EndDate.Value.ToString("yyyy-MM-dd")) .Append("', "); } else { sqlBuilder.Append("NULL, "); } sqlBuilder.Append("'") .Append(educationPlace.Faculty) .Append("', ") .Append("'") .Append(educationPlace.Specialty) .Append("');"); connection.Open(); MySqlCommand drafteeCommand = new MySqlCommand(sqlBuilder.ToString(), connection); drafteeCommand.ExecuteNonQuery(); } finally { connection.Close(); } }
public void Update(Draftee draftee) { MySqlConnection connection = ConnectionUtils.GetConnection(); try { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder .Append("UPDATE draftees SET ") .Append("last_name='") .Append(draftee.LastName) .Append("', ") .Append("first_name='") .Append(draftee.FirstName) .Append("', ") .Append("patronymic='") .Append(draftee.Patronymic) .Append("', ") .Append("birth_date='") .Append(draftee.BirthDate?.ToString("yyyy-MM-dd")) .Append("', ") .Append("birth_place='") .Append(draftee.BirthPlace) .Append("', ") .Append("category='") .Append(draftee.Category) .Append("', ") .Append("troop_type='") .Append(draftee.TroopType) .Append("', ") .Append("foreign_languages='") .Append(draftee.ForeignLanguages) .Append("' ") .Append("WHERE id=") .Append(draftee.Id) .Append(";"); MySqlCommand command = new MySqlCommand(sqlBuilder.ToString(), connection); connection.Open(); command.ExecuteNonQuery(); } finally { connection.Close(); } }
public void Insert(Draftee draftee) { MySqlConnection connection = ConnectionUtils.GetConnection(); try { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder .Append("INSERT INTO draftees ") .Append("(last_name, first_name, patronymic, birth_date, birth_place, category, troop_type, foreign_languages) ") .Append("VALUES (") .Append("'") .Append(draftee.LastName) .Append("', ") .Append("'") .Append(draftee.FirstName) .Append("', ") .Append("'") .Append(draftee.Patronymic) .Append("', ") .Append("'") .Append(draftee.BirthDate?.ToString("yyyy-MM-dd")) .Append("', ") .Append("'") .Append(draftee.BirthPlace) .Append("', ") .Append("'") .Append(draftee.Category) .Append("', ") .Append("'") .Append(draftee.TroopType) .Append("', ") .Append("'") .Append(draftee.ForeignLanguages) .Append("');"); connection.Open(); MySqlCommand drafteeCommand = new MySqlCommand(sqlBuilder.ToString(), connection); drafteeCommand.ExecuteNonQuery(); } finally { connection.Close(); } }
public DataTable GetList() { try { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder .Append("SELECT dr.id id, dr.last_name last_name, dr.first_name first_name, ") .Append("dr.patronymic patronymic, dr.birth_date birth_date, dr.birth_place, ") .Append("dr.category category, dr.troop_type troop_type, dr.foreign_languages foreign_languages ") .Append("FROM draftees dr;"); var dataAdapter = new MySqlDataAdapter(sqlBuilder.ToString(), ConnectionUtils.GetConnection()); DataTable dataTable = new DataTable(); dataAdapter.Fill(dataTable); return(dataTable); } catch { return(null); } }
public void Delete(long id) { MySqlConnection connection = ConnectionUtils.GetConnection(); try { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder .Append("DELETE FROM study_places ") .Append("WHERE id=") .Append(id) .Append(";"); MySqlCommand command = new MySqlCommand(sqlBuilder.ToString(), connection); connection.Open(); command.ExecuteNonQuery(); } finally { connection.Close(); } }
public void InsertUpdate(Address address) { MySqlConnection connection = ConnectionUtils.GetConnection(); try { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder .Append("INSERT INTO addresses (draftee_id, municipal_district, mail_index, ") .Append("street_name, house_number, housing_number, apartment, home_phone) ") .Append("VALUES (") .AppendFormat("{0}, ", address.DrafteeId) .AppendFormat("'{0}', ", address.MunicipalDistrict) .AppendFormat("'{0}', ", address.MailIndex) .AppendFormat("'{0}', ", address.StreetName) .AppendFormat("'{0}', ", address.HouseNumber) .AppendFormat("'{0}', ", address.HousingNumber) .AppendFormat("'{0}', ", address.Apartment) .AppendFormat("'{0}'", address.HomePhone) .Append(") ") .Append("ON DUPLICATE KEY UPDATE ") .AppendFormat("municipal_district='{0}', ", address.MunicipalDistrict) .AppendFormat("mail_index='{0}', ", address.MailIndex) .AppendFormat("street_name='{0}', ", address.StreetName) .AppendFormat("house_number='{0}', ", address.HouseNumber) .AppendFormat("housing_number='{0}', ", address.HousingNumber) .AppendFormat("apartment='{0}', ", address.Apartment) .AppendFormat("home_phone='{0}'", address.HomePhone) .Append(";"); MySqlCommand command = new MySqlCommand(sqlBuilder.ToString(), connection); connection.Open(); command.ExecuteNonQuery(); } finally { connection.Close(); } }
static void Main(string[] args) { //获取连接 var connection = ConnectionUtils.GetConnection(); //获取channel var channel = connection.CreateModel(); //保证每次只发一个 在消费者没有ack之前 channel.BasicQos(prefetchSize: 0, prefetchCount: 1, global: false); //声明队列 bool durable=true消息持久化 channel.QueueDeclare(QUEUE_NAME, true, false, false, null); for (int i = 0; i < 50; i++) { string msg = "hello" + i; channel.BasicPublish("", QUEUE_NAME, false, null, Encoding.UTF8.GetBytes(msg)); System.Threading.Thread.Sleep(i * 20); Console.WriteLine("--send msg:" + msg); } channel.Close(); connection.Close(); }
static void Main(string[] args) { //获取连接 var connection = ConnectionUtils.GetConnection(); //声明通道 var channel = connection.CreateModel(); //声明队列 channel.QueueDeclare(QUEUE_NAME, true, false, false); //绑定交换机 channel.QueueBind(QUEUE_NAME, EXCHANGE_NAME, "info"); //定义消费者 var consumer = new EventingBasicConsumer(channel); //监听队列 consumer.Received += (ch, ea) => { try { var msg = Encoding.UTF8.GetString(ea.Body.ToArray()); Console.WriteLine("[1] consumer msg:" + msg); System.Threading.Thread.Sleep(1000); } catch (Exception) { } finally { //channel.BasicAck(ea.DeliveryTag, true); } }; //手动应答 channel.BasicConsume(QUEUE_NAME, false, consumer); Console.ReadKey(); }
public void Insert(Relative relative) { MySqlConnection connection = ConnectionUtils.GetConnection(); try { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder .Append("INSERT INTO relatives ") .Append("(draftee_id, relationship_type, full_name, birth_year, birth_place, work_place) ") .Append("VALUES (") .Append(relative.DrafteeId) .Append(", ") .Append("'") .Append(relative.RelationshipType) .Append("', ") .Append("'") .Append(relative.FullName) .Append("', ") .Append(relative.BirthYear) .Append(", ") .Append("'") .Append(relative.BirthPlace) .Append("', ") .Append("'") .Append(relative.WorkPlace) .Append("');"); connection.Open(); MySqlCommand drafteeCommand = new MySqlCommand(sqlBuilder.ToString(), connection); drafteeCommand.ExecuteNonQuery(); } finally { connection.Close(); } }
public void Update(Relative relative) { MySqlConnection connection = ConnectionUtils.GetConnection(); try { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder .Append("UPDATE relatives SET ") .Append("full_name='") .Append(relative.FullName) .Append("', ") .Append("birth_year=") .Append(relative.BirthYear) .Append(", ") .Append("birth_place='") .Append(relative.BirthPlace) .Append("', ") .Append("relationship_type='") .Append(relative.RelationshipType) .Append("', ") .Append("work_place='") .Append(relative.WorkPlace) .Append("' ") .Append("WHERE id=") .Append(relative.Id) .Append(";"); MySqlCommand command = new MySqlCommand(sqlBuilder.ToString(), connection); connection.Open(); command.ExecuteNonQuery(); } finally { connection.Close(); } }
public void InsertUpdate(Document document) { MySqlConnection connection = ConnectionUtils.GetConnection(); try { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder .Append("INSERT INTO documents (draftee_id, passport_series, ") .Append("passport_number, passport_issue_date, passport_issued_by, ") .Append("certificate_series, certificate_number, certificate_issue_date, ") .Append("ticket_series, ticket_number, ticket_issue_date) ") .Append("VALUES (") .AppendFormat("{0}, ", document.DrafteeId) .AppendFormat("'{0}', ", document.PassportSeries) .AppendFormat("'{0}', ", document.PassportNumber) .AppendFormat("{0}, ", document.PassportIssueDate != null ? "'" + document.PassportIssueDate?.ToString("yyyy-MM-dd") + "'" : "NULL") .AppendFormat("'{0}', ", document.PassportIssuedBy) .AppendFormat("'{0}', ", document.CertificateSeries) .AppendFormat("'{0}', ", document.CertificateNumber) .AppendFormat("{0}, ", document.CertificateIssueDate != null ? "'" + document.CertificateIssueDate?.ToString("yyyy-MM-dd") + "'" : "NULL") .AppendFormat("'{0}', ", document.TicketSeries) .AppendFormat("'{0}', ", document.TicketNumber) .AppendFormat("{0}", document.TicketIssueDate != null ? "'" + document.TicketIssueDate?.ToString("yyyy-MM-dd") + "'" : "NULL") .Append(") ") .Append("ON DUPLICATE KEY UPDATE ") .AppendFormat("passport_series='{0}', ", document.PassportSeries) .AppendFormat("passport_number='{0}', ", document.PassportNumber) .AppendFormat("passport_issue_date={0}, ", document.PassportIssueDate != null ? "'" + document.PassportIssueDate?.ToString("yyyy-MM-dd") + "'" : "NULL") .AppendFormat("passport_issued_by='{0}', ", document.PassportIssuedBy) .AppendFormat("certificate_series='{0}', ", document.CertificateSeries) .AppendFormat("certificate_number='{0}', ", document.CertificateSeries) .AppendFormat("certificate_issue_date={0}, ", document.CertificateIssueDate != null ? "'" + document.CertificateIssueDate?.ToString("yyyy-MM-dd") + "'" : "NULL") .AppendFormat("ticket_series='{0}', ", document.TicketSeries) .AppendFormat("ticket_number='{0}', ", document.TicketNumber) .AppendFormat("ticket_issue_date={0}", document.TicketIssueDate != null ? "'" + document.TicketIssueDate?.ToString("yyyy-MM-dd") + "'" : "NULL") .Append(";"); MySqlCommand command = new MySqlCommand(sqlBuilder.ToString(), connection); connection.Open(); command.ExecuteNonQuery(); } finally { connection.Close(); } }
static void Main(string[] args) { //获取连接 var connection = ConnectionUtils.GetConnection(); //获取通道 var channnel = connection.CreateModel(); //声明交换机 channnel.ExchangeDeclare(EXCHANGE_NAME, ExchangeType.Direct, true, false, null); //将通道设置成confirm模式 channnel.ConfirmSelect(); //异步处理 Publisher Confirms var outstandingConfirms = new ConcurrentDictionary <ulong, string>(); //success ack channnel.BasicAcks += (sender, ea) => { outstandingConfirms.TryGetValue(ea.DeliveryTag, out string body); Console.WriteLine($"Message with body {body} has been ack-ed. " + $"Sequence number: {ea.DeliveryTag}," + $" multiple: {ea.Multiple}"); //处理成功 if (ea.Multiple) { var confirmed = outstandingConfirms.Where(k => k.Key <= ea.DeliveryTag); foreach (var entry in confirmed) { outstandingConfirms.TryRemove(entry.Key, out _); } } else { outstandingConfirms.TryRemove(ea.DeliveryTag, out _); } }; //error nack channnel.BasicNacks += (sender, ea) => { //处理失败 outstandingConfirms.TryGetValue(ea.DeliveryTag, out string body); Console.WriteLine($"Message with body {body} has been nack-ed. Sequence number: {ea.DeliveryTag}, multiple: {ea.Multiple}"); if (ea.Multiple) { var confirmed = outstandingConfirms.Where(k => k.Key <= ea.DeliveryTag); foreach (var entry in confirmed) { outstandingConfirms.TryRemove(entry.Key, out _); } } else { outstandingConfirms.TryRemove(ea.DeliveryTag, out _); } }; for (int i = 0; i < 2222222; i++) { var msg = "第" + (i + 1) + "条消息"; outstandingConfirms.TryAdd(channnel.NextPublishSeqNo, msg); //消息持久化 var basicProperties = channnel.CreateBasicProperties(); basicProperties.DeliveryMode = 2; channnel.BasicPublish(EXCHANGE_NAME, "info", basicProperties, Encoding.UTF8.GetBytes(msg)); Console.WriteLine($"send msg:{msg}"); System.Threading.Thread.Sleep(3000); } Console.ReadKey(); }
public void Insert(Draftee draftee, Address address) { MySqlConnection connection = ConnectionUtils.GetConnection(); MySqlTransaction transaction = null; try { StringBuilder sqlBuilder = new StringBuilder(); sqlBuilder .Append("INSERT INTO draftees ") .Append("(last_name, first_name, patronymic, birth_date, birth_place, category, troop_type, foreign_languages) ") .Append("VALUES (") .Append("'") .Append(draftee.LastName) .Append("', ") .Append("'") .Append(draftee.FirstName) .Append("', ") .Append("'") .Append(draftee.Patronymic) .Append("', ") .Append("'") .Append(draftee.BirthDate?.ToString("yyyy-MM-dd")) .Append("', ") .Append("'") .Append(draftee.BirthPlace) .Append("', ") .Append("'") .Append(draftee.Category) .Append("', ") .Append("'") .Append(draftee.TroopType) .Append("', ") .Append("'") .Append(draftee.ForeignLanguages) .Append("');"); connection.Open(); transaction = connection.BeginTransaction(); MySqlCommand drafteeCommand = new MySqlCommand(sqlBuilder.ToString(), connection, transaction); drafteeCommand.ExecuteNonQuery(); StringBuilder addressBuilder = new StringBuilder(); addressBuilder .Append("INSERT INTO addresses ") .Append("(draftee_id, municipal_district, mail_index, street_name, ") .Append("house_number, housing_number, apartment, home_phone) ") .Append("VALUES (") .Append("last_insert_id()") .Append(", '") .Append(address.MunicipalDistrict) .Append("', ") .Append("'") .Append(address.MailIndex) .Append("', ") .Append("'") .Append(address.StreetName) .Append("', ") .Append("'") .Append(address.HouseNumber) .Append("', ") .Append("'") .Append(address.HousingNumber) .Append("', ") .Append("'") .Append(address.Apartment) .Append("', ") .Append("'") .Append(address.HomePhone) .Append("');"); MySqlCommand addressCommand = new MySqlCommand(addressBuilder.ToString(), connection, transaction); addressCommand.ExecuteNonQuery(); string documentSql = "INSERT INTO documents (draftee_id) VALUES (last_insert_id());"; MySqlCommand documentCommand = new MySqlCommand(documentSql, connection, transaction); documentCommand.ExecuteNonQuery(); transaction.Commit(); } catch { transaction?.Rollback(); } finally { transaction?.Dispose(); connection.Close(); } }