public void UpdateMatchup(MatchupModel model) { // spMatchups_Update @id, @Winnerid using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(db))) { var p = new DynamicParameters(); if (model.Winner != null) { p.Add("@id", model.Id); p.Add("@Winnerid", model.Winner.Id); connection.Execute("dbo.spMatchups_Update", p, commandType: CommandType.StoredProcedure); } // spMatchupEntries_Update id, TeamCompetingId, Score foreach (MatchupEntryModel me in model.Entries) { if (me.TeamCompeting != null) { p = new DynamicParameters(); p.Add("@id", me.Id); p.Add("@TeamCompetingId", me.TeamCompeting.Id); p.Add("@Score", me.Score); connection.Execute("dbo.spMatchupEntries_Update", p, commandType: CommandType.StoredProcedure); } } } }
private static void Setup() { using (var connection = new SqlConnection(@"Data Source=(LocalDB)\v11.0;Initial Catalog=Master;Integrated Security=True")) { connection.Open(); try { connection.Execute(@" DROP DATABASE DapperSimpleCrudTestDb; "); } catch (Exception) { } connection.Execute(@" CREATE DATABASE DapperSimpleCrudTestDb; "); } using (var connection = new SqlConnection(@"Data Source = (LocalDB)\v11.0;Initial Catalog=DapperSimpleCrudTestDb;Integrated Security=True")) { connection.Open(); connection.Execute(@" create table Users (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null, Age int not null, ScheduledDayOff int null, CreatedDate datetime DEFAULT(getdate())) "); connection.Execute(@" create table Car (CarId int IDENTITY(1,1) not null, Id int null, Make nvarchar(100) not null, Color nvarchar(100) not null, AgeInYears int not null, Model nvarchar(100) not null) "); connection.Execute(@" create table BigCar (CarId bigint IDENTITY(2147483650,1) not null, Make nvarchar(100) not null, Model nvarchar(100) not null) "); connection.Execute(@" create table City (Name nvarchar(100) not null, Population int not null) "); connection.Execute(@" CREATE SCHEMA Log; "); connection.Execute(@" create table Log.CarLog (Id int IDENTITY(1,1) not null, LogNotes nvarchar(100) NOT NULL) "); connection.Execute(@" CREATE TABLE [dbo].[GUIDTest]([Id] [uniqueidentifier] NOT NULL,[name] [varchar](50) NOT NULL, CONSTRAINT [PK_GUIDTest] PRIMARY KEY CLUSTERED ([Id] ASC))"); connection.Execute(@" create table StrangeColumnNames (ItemId int IDENTITY(1,1) not null Primary Key, word nvarchar(100) not null, colstringstrangeword nvarchar(100) not null) "); connection.Execute(@" create table UserWithoutAutoIdentity (Id int not null Primary Key, Name nvarchar(100) not null, Age int not null) "); } Console.WriteLine("Created database"); }
public void Delete(Guid orderId) { using (var connection = new SqlConnection(SqlConnectionLocator.LocalhostSqlExpress())) { connection.Execute(SqlQueries.DeleteOrderLineQuery, new {OrderId = orderId}); connection.Execute(SqlQueries.DeleteOrderQuery, new {OrderId = orderId}); } }
public void EditGradebook(GradeBookView.StudentGrade gbUpdate) { //this approach makes two calls to the database there is probably a way to do this all in one SQL Stored Procedure using (var cn = new SqlConnection(Config.GetConnectionString())) { var p = new DynamicParameters(); p.Add("@RosterId", gbUpdate.RosterId); p.Add("@AssignmentId", gbUpdate.AssignmentId); p.Add("@Points",gbUpdate.Points); p.Add("@Score", gbUpdate.Score); if (//checks to see if grade exists cn.Query<AssignmentGrade>("AssignmentGrade_GetByRosterIdAndAssignmentId", p, commandType: CommandType.StoredProcedure) .Any()) { cn.Execute("AssignmentGrade_UpdateScore", p, commandType: CommandType.StoredProcedure); //updates existing grade } else { cn.Execute("AssignmentGrade_Insert", p, commandType: CommandType.StoredProcedure); //creates new grade } } }
public void EditUser(EditUserRequest editUser) { using (var cn = new SqlConnection(Settings.GetConnectionString())) { var p = new DynamicParameters(); p.Add("@UserId", editUser.LmsUser.UserId); p.Add("@IsStudent", editUser.IsStudent ? 1 : 0); p.Add("@IsParent", editUser.IsParent ? 1 : 0); p.Add("@IsTeacher", editUser.IsTeacher ? 1 : 0); p.Add("@IsAdmin", editUser.IsAdmin ? 1 : 0); cn.Execute("spUpdateUserRoles", p, commandType: CommandType.StoredProcedure); var p2 = new DynamicParameters(); p2.Add("@UserId", editUser.LmsUser.UserId); p2.Add("@LastName", editUser.LmsUser.LastName); p2.Add("@FirstName", editUser.LmsUser.FirstName); p2.Add("@Email", editUser.LmsUser.Email); p2.Add("@SuggestedRole", editUser.LmsUser.SuggestedRole); p2.Add("@GradeLevelId", editUser.LmsUser.GradeLevelId); cn.Execute("spUpdateUserDetails", p2, commandType: CommandType.StoredProcedure); } }
public void InitDataBase(string connectionString) { Dictionary<int, int> provinceDictionary = new Dictionary<int, int>(); using (var connection = new SqlConnection(connectionString)) { connection.Open(); connection.Execute(Const.DBCreateScript); connection.Execute("insert into Province values(@Name, @Code);" , ProvinceData.GetProvinces()); var provinces = connection.Query("select code, id from Province;"); foreach (var province in provinces) { provinceDictionary.Add((int)province.code, (int)province.id); } connection.Close(); } BulkUploadToSql bulk = BulkUploadToSql.Load( HomeData.GetHomes() .Select( i => new Bulk.Home { AddTime = DateTime.Now, BuildYear = i.BuildYear, City = i.City, Description = i.Description, Price = i.Price, Surface = i.Surface, ProvinceId = provinceDictionary[i.HomeProvince.Code] }), "Home", 10000, connectionString); bulk.Flush(); }
/// <summary> /// Updates team entry already in the DB /// </summary> /// <param name="model">Team entry to be updated</param> public void UpdateTeam(TeamModel model) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString("TournamentTracker"))) { var dbData = new DynamicParameters(); dbData.Add("@id", model.Id); dbData.Add("@TeamName", model.TeamName); connection.Execute("dbo.spTeam_UpdateById", dbData, commandType: CommandType.StoredProcedure); dbData = new DynamicParameters(); dbData.Add("@TeamId", model.Id); connection.Execute("dbo.spTeamMembers_DeleteByTeamId", dbData, commandType: CommandType.StoredProcedure); foreach (PersonModel personModel in model.TeamMembers) { dbData = new DynamicParameters(); dbData.Add("@TeamId", model.Id); dbData.Add("@PersonId", personModel.Id); dbData.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output); connection.Execute("dbo.spTeamMembers_Insert", dbData, commandType: CommandType.StoredProcedure); } } }
public void Initialize() { using (var connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=tempdb;Integrated Security=True")) { connection.Open(); try { connection.Execute(@"ALTER DATABASE DapperSimpleCrudTestDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE DapperSimpleCrudTestDb ; "); } catch { } connection.Execute(@" CREATE DATABASE DapperSimpleCrudTestDb; "); } using (IDbConnection connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=DapperSimpleCrudTestDb;Integrated Security=True")) { connection.Open(); connection.Execute(@" create table Users (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null, Age int not null, ScheduledDayOff int null) "); connection.Execute(@" create table Car (CarId int IDENTITY(1,1) not null, Make nvarchar(100) not null, Model nvarchar(100) not null) "); connection.Execute(@" create table City (Name nvarchar(100) not null, Population int not null, Version rowversion) "); connection.Execute(@" CREATE SCHEMA Log; "); connection.Execute(@" create table Log.CarLog (Id int IDENTITY(1,1) not null, LogNotes nvarchar(100) NOT NULL) "); connection.Execute("INSERT INTO USERS VALUES ('teste', 21, 1)"); } Console.WriteLine("Created database"); }
public void Add(Order order) { using (var connection = new SqlConnection(SqlConnectionLocator.LocalhostSqlExpress())) { connection.Execute(SqlQueries.InsertOrderQuery, order); connection.Execute(SqlQueries.InsertOrderLineQuery, order.Lines); } }
/// <summary> /// Delete training. /// Contains: - delete all booking entries with training /// - delete prerequisites entries /// - delete training /// </summary> /// <param name="trainingId">Training Id</param> public void DeleteTraining(int trainingId) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(CONNECTION_STRING))) { var p = new DynamicParameters(); p.Add("@Training", trainingId); //--------Delete booking entries-------- connection.Execute("dbo.spTrainingBooking_DeleteEntry", p, commandType: CommandType.StoredProcedure); //--------Delete prerequisites entries-------- p = new DynamicParameters(); p.Add("@id", trainingId); connection.Execute("dbo.spTraining_DeletePrerequisite", p, commandType: CommandType.StoredProcedure); //--------Delete training -------- p = new DynamicParameters(); p.Add("@id", trainingId); connection.Execute("dbo.spTraining_Delete", p, commandType: CommandType.StoredProcedure); } }
public void AddLogin(string Email, string PasswordHash, string PasswordSalt, DateTime DateCreated, string Submitted, string StudentType) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("EnrollmentDB"))) { Login l = new Login() { email = Email, passwordHash = PasswordHash, passwordSalt = PasswordSalt, dateCreated = DateCreated, submitted = Submitted, studentType = StudentType }; if (StudentType == "High School") { connection.Execute("dbo.AddHSLogin @email, @passwordHash, @passwordSalt, @dateCreated, @submitted, @studentType", l); } else { connection.Execute("dbo.AddAdultLogin @email, @passwordHash, @passwordSalt, @dateCreated, @submitted, @studentType", l); } } }
private static void ReCommit(SqlConnection db, SqlTransaction transaction, List<MovementRegisterItem> docs) { foreach (var item in docs.Reverse<MovementRegisterItem>()) db.Execute(item.RollbackCommand, new { item.DocId }, transaction: transaction); foreach (var item in docs) db.Execute(item.CommitCommand, new { item.DocId }, transaction: transaction); }
public void Add(Order order) { var persistentModel = _orderMapper.ToPersistentModel(order); using (var connection = new SqlConnection(SqlConnectionLocator.LocalhostSqlExpress())) { connection.Execute(SqlQueries.InsertOrderQuery, persistentModel); connection.Execute(SqlQueries.InsertOrderLineQuery, persistentModel.Lines); } }
public void Add(Order order) { var orderState = new Order.ToState().Build(order); using (var connection = new SqlConnection(SqlConnectionLocator.LocalhostSqlExpress())) { connection.Execute(SqlQueries.InsertOrderQuery, orderState); connection.Execute(SqlQueries.InsertOrderLineQuery, orderState.Lines); } }
public void Update(Order order) { using (var connection = new SqlConnection(SqlConnectionLocator.LocalhostSqlExpress())) { connection.Execute(SqlQueries.UpdateOrderQuery, order); connection.Execute(SqlQueries.DeleteOrderLineQuery, new {OrderId = order.Id}); connection.Execute(SqlQueries.InsertOrderLineQuery, order.Lines); } }
public override void WriteDeal(string path, string serializedDeal) { using (SqlConnection connection = new SqlConnection(_connectionString)) { connection.Open(); connection.Execute(@"delete from Deals where Id = @Id", new { Id = path }); connection.Execute(@"insert Deals(Id, Value) values (@Id, @Value)", new {Id = path, Value = serializedDeal}); } }
public void Save(string id, string serializedDeal) { using (SqlConnection connection = new SqlConnection(_connectionString)) { connection.Open(); connection.Execute(@"delete from Deals where Id = @Id", new { Id = id }); connection.Execute(@"insert Deals(Id, Value) values (@Id, @Value)", new {Id = id, Value = serializedDeal}); } }
public void SaveACI(AdultConfidentialInfoClass aci) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("EnrollmentDB"))) { connection.Execute($"Update AdultConfidentialInfo Set formCompletionDate = @FormCompletionDate, foodStamps = @FoodStamps, dependentChildrenAid = @DependentChildrenAid, supplementaryIncome = @SupplementaryIncome, housingAssistance = @HousingAssistance, none = @None, homeless = @Homeless, agedOutFosterCare = @AgedOutFosterCare, outOfWorkforce = @OutOfWorkforce Where Id = @id", new { FormCompletionDate = aci.formCompletionDate, FoodStamps = aci.foodStamps, DependentChildrenAid = aci.dependentChildrenAid, SupplementaryIncome = aci.supplementaryIncome, HousingAssistance = aci.housingAssistance, None = aci.none, Homeless = aci.homeless, AgedOutFosterCare = aci.agedOutFosterCare, OutOfWorkforce = aci.outOfWorkforce, id = aci.Id }); connection.Execute($"Update AdultLogins Set submitted = @Submitted, dateSubmitted = @DateSubmitted Where Id = @id", new { Submitted = "Yes", DateSubmitted = DateTime.Now, id = aci.Id }); } }
public void SaveHSCI(HighSchoolConfidentialInfoClass hsci) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("EnrollmentDB"))) { connection.Execute($"Update HighSchoolConfidentialInfo Set formCompletionDate = @FormCompletionDate, foodStamps = @FoodStamps, dependentChildrenAid = @DependentChildrenAid, supplementaryIncome = @SupplementaryIncome, housingAssistance = @HousingAssistance, none = @None, homeless = @Homeless, agedOutFosterCare = @AgedOutFosterCare, parentsActiveDuty = @ParentsActiveDuty, reducedLunch = @ReducedLunch, parentSignature = @parentSig Where Id = @id", new { FormCompletionDate = hsci.formCompletionDate, parentSig = hsci.parentSignature, FoodStamps = hsci.foodStamps, DependentChildrenAid = hsci.dependentChildrenAid, SupplementaryIncome = hsci.supplementaryIncome, HousingAssistance = hsci.housingAssistance, None = hsci.none, Homeless = hsci.homeless, AgedOutFosterCare = hsci.agedOutFosterCare, ParentsActiveDuty = hsci.parentsActiveDuty, ReducedLunch = hsci.reducedLunch, id = hsci.Id }); connection.Execute($"Update HighSchoolLogins Set submitted = @Submitted, dateSubmitted = @DateSubmitted Where Id = @id", new { Submitted = "Yes", DateSubmitted = DateTime.Now, id = hsci.Id }); } }
static SqlServerTestSuite() { using (var connection = new SqlConnection(ConnectionString)) { // ReSharper disable once AccessToDisposedClosure Action<string> dropTable = name => connection.Execute($@"IF OBJECT_ID('{name}', 'U') IS NOT NULL DROP TABLE [{name}]; "); connection.Open(); dropTable("Stuff"); connection.Execute(@"CREATE TABLE Stuff (TheId int IDENTITY(1,1) not null, Name nvarchar(100) not null, Created DateTime null);"); dropTable("People"); connection.Execute(@"CREATE TABLE People (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null);"); dropTable("Users"); connection.Execute(@"CREATE TABLE Users (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null, Age int not null);"); dropTable("Automobiles"); connection.Execute(@"CREATE TABLE Automobiles (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null);"); dropTable("Results"); connection.Execute(@"CREATE TABLE Results (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null, [Order] int not null);"); dropTable("ObjectX"); connection.Execute(@"CREATE TABLE ObjectX (ObjectXId nvarchar(100) not null, Name nvarchar(100) not null);"); dropTable("ObjectY"); connection.Execute(@"CREATE TABLE ObjectY (ObjectYId int not null, Name nvarchar(100) not null);"); dropTable("ObjectZ"); connection.Execute(@"CREATE TABLE ObjectZ (Id int not null, Name nvarchar(100) not null);"); } }
public ActionResponse Execute() { using (var cn = new SqlConnection(_output.Connection.GetConnectionString())) { cn.Open(); try { cn.Execute(_output.SqlDropStarView()); } catch (SqlException) { } cn.Execute(_output.SqlCreateStarView()); } return new ActionResponse(); }
/// <summary> /// Updates an existing patient in the repository /// </summary> /// <param name="entity">Entity to update</param> public void Update(Domain.Patient entity) { const string CurrentAddressSql = "UPDATE patient.CurrentAddress SET Street = @Street, Unit = @Unit, City = @City, State = @State, ZipCode = @ZipCode, ZipCodeSupplement = @ZipCodeSupplement WHERE CurrentAddressId = @currentAddressId;"; const string PatientSql = "UPDATE patient.Patient SET FirstName = @FirstName, LastName = @LastName WHERE PatientId = @PatientId"; using (IDbConnection connection = new SqlConnection(_connectionString)) { connection.Open(); connection.Execute(CurrentAddressSql, entity.CurrentAddress); connection.Execute(PatientSql, entity); } }
public static void ProvisionDatabase(SqlConnection connection, string schemaName = "[dbo]", bool dropExisting = false) { if (dropExisting) { Database.Tables.ForEach(z => connection.DropTableIfExists(schemaName, z)); } if (false == connection.SchemaExists(schemaName)) { connection.Execute($"CREATE SCHEMA {schemaName}"); } Database.Structure(schemaName).ForEach(z => connection.Execute(z)); }
private void POUpdateSaveButton_Click(object sender, System.EventArgs e) { string status = "Pending"; string invoiceNumber = "-"; DateTime today = DateTime.Today; string poId = _poId; byte checkInitialRun = 0; using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(connectionString)) { //Add entries to the Purchase Order table. PurchaseOrderModel poModel = new PurchaseOrderModel( poId, invoiceNumber, POUpdateTotalAmountTextBox.Text, today, status); var p = new DynamicParameters(); p.Add("@POId", poModel.POId); p.Add("@POInvoiceNumber", poModel.POInvoiceNumber); p.Add("@POTotalAmount", poModel.POTotalAmount); p.Add("@PODate", poModel.PODate); p.Add("@POStatus", poModel.POStatus); connection.Execute("dbo.spPO_UpdatePO", p, commandType: CommandType.StoredProcedure); //Add entries to the Purchase Order Item table. for (int j = 0; j < POUpdateListView.Items.Count; j++) { PurchaseOrderModel orderModel = new PurchaseOrderModel( poId, POUpdateListView.Items[j].SubItems[2].Text, //Quantity today, status, POUpdateListView.Items[j].SubItems[0].Text, //Item Id decimal.Parse(POUpdateListView.Items[j].SubItems[3].Text), //Item Price checkInitialRun); var o = new DynamicParameters(); o.Add("@POId", orderModel.POId); o.Add("@ItemId", orderModel.ItemId); o.Add("@ItemPrice", orderModel.ItemPrice); o.Add("@POQuantity", orderModel.POQuantity); o.Add("@checkInitialRun", orderModel.CheckInitialRun); connection.Execute("dbo.spPO_UpdateItemsToOrderItems", o, commandType: CommandType.StoredProcedure); //TODO - create stored procedure checkInitialRun = 1; } } }
private void Run() { int count = 0; using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Database"].ConnectionString)) { connection.Execute("create table List (Id int identity primary key, payload nvarchar(max))"); while (true) { connection.Execute("insert into list (payload) values (@payload)", new {payload = "@@@"}); Console.WriteLine("Count: {0}", ++count); } } }
/// <summary> /// Creates new employee and new user account with email and password /// </summary> /// <param name="employee">Object of an Employee</param> /// <param name="userCredentials">Object of an User Account</param> /// <returns>Created Employee</returns> public void CreateEmployeeUserAccount(Employee employee, UserAccountEntry userCredentials) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(CONNECTION_STRING))) { // IDbTransaction transaction = connection.BeginTransaction(); // ----------- Get Department Id ----------- var p = new DynamicParameters(); p.Add("@name", employee.Department.ToString()); int departmentId = 0; List <int> tmp_ids = connection.Query <int>("dbo.spDepartment_GetDepartmentId", p, commandType: CommandType.StoredProcedure).ToList(); foreach (var id in tmp_ids) { departmentId = id; break; } // ----------- Create Employee ----------- p = new DynamicParameters(); p.Add("@FirstName", employee.FirstName); p.Add("@LastName", employee.LastName); p.Add("@Department", departmentId); p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output); connection.Execute("dbo.spEmployee_Insert", p, commandType: CommandType.StoredProcedure); employee.Id = p.Get <int>("@id"); // ----------- Create User Account ----------- p = new DynamicParameters(); p.Add("@EmployeeId", employee.Id); p.Add("@PasswordHash", userCredentials.PasswordHash); p.Add("@PasswordSalt", userCredentials.PasswordSalt); p.Add("@EmailAddress", userCredentials.EmailAddress); p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output); connection.Execute("dbo.spUserAccount_Insert", p, commandType: CommandType.StoredProcedure); } }
private void SOUpdateInvoiceButton_Click(object sender, EventArgs e) { _invoiceAmount = decimal.Parse(SOUpdateTotalAmountTextBox.Text.ToString()); int i = 0; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand cmd = new SqlCommand("dbo.spInv_GetLastRecordId", connection); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { lastInvoiceNumber = dr["InvoiceNumber"].ToString(); } resultString = Regex.Match(lastInvoiceNumber, @"\d+").Value; if (resultString != "") { i = Convert.ToInt32(resultString); } i += 1; _invoiceNumber = "I-" + i.ToString("D5"); connection.Close(); } //update invoice table. using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(connectionString)) { var p = new DynamicParameters(); p.Add("SOId", _soId); p.Add("SOStatus", "Invoiced"); p.Add("InvoiceNumber", _invoiceNumber); connection.Execute("dbo.spInvoice_TransferSOItemsToInvoice", p, commandType: CommandType.StoredProcedure); var q = new DynamicParameters(); q.Add("SOId", _soId); q.Add("InvoiceAmount", _invoiceAmount); q.Add("InvoiceStatus", _invoiceStatus); q.Add("InvoiceDate", _invoiceDate); q.Add("InvoiceNumber", _invoiceNumber); connection.Execute("dbo.spInvoice_UpdateInvoiceTable", q, commandType: CommandType.StoredProcedure); } MessageBox.Show("Sales Order " + _soId + " has been invoiced."); this.Close(); }
public void RenameImageAndUsages(PlantPhoto plantPhoto) { var dateString = _now.ToString("yyyy-MM-dd HH:mm:ss.fff"); var fileManager = new TxFileManager(); try { using (var conn = new SqlConnection(_settings.ConnectionString)) using (var scope = new TransactionScope()) { conn.Open(); conn.Execute(_photoUpdateQuery, new { OldPhotoId = plantPhoto.PhotoId, NewPhotoId = plantPhoto.PhotoId.AddTif(), NewUpdatedAt = _now }); conn.Execute(_usageUpdateQuery, new { plantPhoto.PhotoId, NewPhotoId = plantPhoto.PhotoId.AddTif() }); foreach (var imageRoot in _settings.ImageRoots) { var imagePath = plantPhoto.GetActualImagePath(imageRoot); var newPath = plantPhoto.GetReplacementPath(imageRoot, _settings.TargetExtension); if (File.Exists(imagePath)) { fileManager.Move(imagePath, newPath); } } foreach (var thumbnailRoot in _settings.ThumbnailRoots) { var thumbPath = plantPhoto.GetThumbnailPath(thumbnailRoot); var newPath = plantPhoto.GetReplacementPath(thumbnailRoot, _settings.TargetExtension, true); if (File.Exists(thumbPath)) { fileManager.Move(thumbPath, newPath); } } scope.Complete(); var message = string.Format("{0}\t{0}{1}\t{2}", plantPhoto.PhotoId, _settings.TargetExtension, dateString); Logger.Info(message); } } catch (TransactionAbortedException trex) { Logger.Error(string.Format("{0}\t{1}", plantPhoto.PhotoId, trex.Message.Replace(Environment.NewLine, " "))); } catch (Exception exc) { Logger.Error(string.Format("{0}\t{1}", plantPhoto.PhotoId, exc.Message.Replace(Environment.NewLine, " "))); } }
private void SOUpdateSaveButton_Click(object sender, EventArgs e) { string status = "Pending"; DateTime today = DateTime.Today; string soId = _soId; byte checkInitialRun = 0; using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(connectionString)) { //Add entries to the Sales Order table. SalesOrderModel soModel = new SalesOrderModel( soId, decimal.Parse(SOUpdateTotalAmountTextBox.Text.ToString()), today, status); var p = new DynamicParameters(); p.Add("@SOId", soModel.SOId); p.Add("@SOTotalAmount", soModel.SOTotalAmount); p.Add("@SODate", soModel.SODate); p.Add("@SOStatus", soModel.SOStatus); connection.Execute("dbo.spSO_UpdateSO", p, commandType: CommandType.StoredProcedure); //Add entries to the Sales Order Item table. for (int j = 0; j < SOUpdateListView.Items.Count; j++) { SalesOrderModel orderModel = new SalesOrderModel( soId, SOUpdateListView.Items[j].SubItems[2].Text, //Quantity today, status, SOUpdateListView.Items[j].SubItems[0].Text, //Item Id decimal.Parse(SOUpdateListView.Items[j].SubItems[3].Text), //Item Price checkInitialRun); var o = new DynamicParameters(); o.Add("@SOId", orderModel.SOId); o.Add("@ItemId", orderModel.ItemId); o.Add("@ItemPrice", orderModel.ItemPrice); o.Add("@SOQuantity", orderModel.SOQuantity); o.Add("@checkInitialRun", orderModel.CheckInitialRun); connection.Execute("dbo.spSO_UpdateItemsToOrderItems", o, commandType: CommandType.StoredProcedure); //TODO - create stored procedure checkInitialRun = 1; } } }
public int UnSet_Void_Transaction_by_InvoiceNo(int iSelInvNo) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("POS1"))) { string query = "UPDATE OrderComplete SET IsVoid = 0, VoidDate='" + DateTime.Now.ToString("yyyy-MM-dd") + "', VoidTime='" + DateTime.Now.ToString("hh:mm:ss") + "' " + "WHERE InvoiceNo=" + iSelInvNo.ToString(); var count1 = connection.Execute(query); query = "UPDATE TranCollection SET IsVoid = 0, VoidDate='" + DateTime.Now.ToString("yyyy-MM-dd") + "', VoidTime='" + DateTime.Now.ToString("hh:mm:ss") + "' " + "WHERE InvoiceNo=" + iSelInvNo.ToString(); var count2 = connection.Execute(query); return(count1); } }
/// <summary> /// Deletes passed TeamModel along with associated members from the DB /// </summary> /// <param name="model">Team to be deleted</param> public void DeleteTeam(TeamModel model) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString("TournamentTracker"))) { var dbData = new DynamicParameters(); dbData.Add("@TeamId", model.Id); connection.Execute("dbo.spTeamMembers_DeleteByTeamId", dbData, commandType: CommandType.StoredProcedure); dbData = new DynamicParameters(); dbData.Add("@id", model.Id); connection.Execute("dbo.spTeam_DeleteById", dbData, commandType: CommandType.StoredProcedure); } }
internal void InsertCar(Cars car) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("SampleDB"))) { connection.Execute("dbo.Car_Insert @Make, @Model,@Colour, @Price, @Comm, @netValue", car); } }
public void Write(IEnumerable<Row> rows) { var sql = _context.SqlUpdateCalculatedFields(_original); var temp = new List<Field> { _context.Entity.Fields.First(f => f.Name == Constants.TflKey) }; temp.AddRange(_context.Entity.CalculatedFields.Where(f => f.Output && f.Name != Constants.TflHashCode)); var fields = temp.ToArray(); var count = 0; using (var cn = new SqlConnection(_context.Connection.GetConnectionString())) { cn.Open(); foreach (var batch in rows.Partition(_context.Entity.UpdateSize)) { var trans = cn.BeginTransaction(); var batchCount = cn.Execute( sql, batch.Select(r => r.ToExpandoObject(fields)), trans, _context.Connection.Timeout, CommandType.Text ); trans.Commit(); count += batchCount; _context.Increment(batchCount); } _context.Info("{0} to {1}", count, _context.Connection.Name); } _context.Entity.Updates += count; }
public bool AddSignature(Annotations customAnnotation) { using (IDbConnection db = new SqlConnection(ConfigurationValues.PostOfficeDatabaseConnection)) { try { const string query = "INSERT INTO [Annotation]" + " (" + " [ActiveDirectoryUser]" + ",[Name]" + " ,[Annotation]" + " )" + " VALUES" + " (" + "@ActiveDirectoryUser,@Name,@Annotation" + " )"; int rowsAffectd = db.Execute(query, new { @ActiveDirectoryUser = customAnnotation.ActiveDirectoryUser, @Name = customAnnotation.Name, @Annotation = customAnnotation.NewAnnotation }); return true; } catch (Exception er) { string s1 = er.ToString(); return false; //Log.LogMessage(er.ToString()); } } }
//Updates the selected item after clicking on the update button. private void UpdateItemButton_Click(object sender, EventArgs e) { ItemModel model = new ItemModel( UpdateItemIdTextbox.Text, UpdateItemNameTextBox.Text, UpdateItemDescriptionTextBox.Text, UpdateItemPriceTextBox.Text, UpdateItemDiscountTextBox.Text); if (UpdateItemCheckBox.Checked) { model.ItemDisabled = "1"; } else { model.ItemDisabled = "0"; } using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(this.connectionString)) { var p = new DynamicParameters(); p.Add("@ItemId", model.ItemId); p.Add("@ItemName", model.ItemName); p.Add("@ItemDescription", model.ItemDescription); p.Add("@ItemPrice", model.ItemPrice); p.Add("@ItemDiscount", model.ItemDiscount); p.Add("@ItemDisabled", model.ItemDisabled); MessageBox.Show("Item " + model.ItemName + " has been updated."); connection.Execute("dbo.spItem_Update", p, commandType: CommandType.StoredProcedure); } this.Close(); }
public int LogETSubscriberChange(string listName, int id, string status, string email, string action, Guid committeeID, string committeeName) { using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DynamicsExtension"].ConnectionString)) { return conn.Execute( @"INSERT INTO [dbo].[ETAutomation_ChangeLog] ([ETListID] ,[ETListName] ,[ETSubscriberKey] ,[ETSubscriberStatus] ,[Action] ,[CommitteeID] ,[CommitteeName] ,[CreatedDateTime]) VALUES (@ETListID ,@ETListName ,@ETSubscriberKey ,@ETSubscriberStatus ,@Action ,@CommitteeID ,@CommitteeName ,@CreatedDateTime)", new { ETListID = id, ETListName = listName, ETSubscriberStatus = status, ETSubscriberKey = email, Action = action, CommitteeID = committeeID, CommitteeName = committeeName, CreatedDateTime = DateTime.Now } ); } }
public int AddNewItem(ItemModel model) { var headers = OperationContext.Current.IncomingMessageProperties["httpRequest"]; var token = ((HttpRequestMessageProperty)headers).Headers["Token"]; using (var dbContext = new TokenDbContext()) { ITokenValidator validator = new DatabaseTokenValidator(dbContext); if (validator.IsValid(token) == true) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString("GyrodataTracker"))) { var p = new DynamicParameters(); p.Add("@Item", model.Item); p.Add("@Asset", model.Asset); p.Add("@Arrived", model.Arrived); p.Add("@Invoice", model.Invoice); p.Add("@CCD", model.CCD); p.Add("@NameRus", model.NameRus); p.Add("@PositionCCD", model.PositionCCD); p.Add("@ItemStatus", model.ItemStatus); p.Add("@Box", model.Box); p.Add("@Container", model.Container); p.Add("@Comment", model.Comment); p.Add("@ItemImage", model.ItemImage); p.Add("@Id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output); connection.Execute("dbo.spItems_Insert", p, commandType: CommandType.StoredProcedure); model.Id = p.Get <int>("@Id"); } } } return(model.Id); }
private static void DapperExample() { // select Name, SecName, Position, BirthDay, IQ from UserNames; using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(GLOBAL_CONNECTION_STRING)) { conn.Open(); // Чтение данных с помощью Dapper var userColl = conn.Query <UserInfo>("select Name, SecName, Position, BirthDay, IQ from UserNames"); foreach (var usin in userColl) { Log($"\t{usin.Name} {usin.SecName}, {usin.Position}, BD: {usin.BirthDay:yyyy-MM-dd}, IQ: {usin.IQ}"); } // Вставка данных // Для вставки данных используется команда Execute // Параметры @par заменяются соответствующими полями объекта, переданного в качестве параметра conn.Execute(@"insert into UserNames (Name, SecName, Position, BirthDay, iq) values (@par1, @par2, @par3, @par4, @par5)", new object[] { new { par1 = "Вячеслав", par2 = "Кудрявцев", par3 = "Специалист", par4 = new DateTime(1999, 5, 25), par5 = 120 }, new { par1 = "Вячеслав2", par2 = "Кудрявцев2", par3 = "Специалист2", par4 = new DateTime(1999, 5, 25), par5 = 120 } }); //UserInfo UserInfoObj = null; //conn.Execute(@"insert into UserNames (Name, SecName, Position, BirthDay, iq) // values (Name, SecName, Position, BirthDay, Iq)", // UserInfoObj); // Если мы имеем объект типа UserInfo, то мы можем назвать параметры в соответствии с именами полей/свойств // и передавать в качестве параметра конкретный объект с данными } }
public bool DeleteIndustryCategory(int IndustryCategoryId) { con = connManager.GetConnection(); con.Open(); try { DynamicParameters param = new DynamicParameters(); param.Add("@IndustryCategoryId", IndustryCategoryId); rowsAffected = con.Execute(deleteIndustryCategory, param, commandType: CommandType.StoredProcedure); con.Close(); return(true); } catch (Exception ex) { throw; } //using (dbConnection) //{ // string sqlQuery = "Delete From [dbo].[IndustryCategory] WHERE IndustryCategoryId = " + IndustryCategoryId; // rowsAffected = dbConnection.Execute(sqlQuery); //} //if (rowsAffected > 0) // return true; //else // return false; }
public MatchupEntryModel CreateMatchupEntry(MatchupEntryModel model) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString("TournamentTracker"))) { var dbData = new DynamicParameters(); dbData.Add("@TournamentId", model.TournamentId); dbData.Add("@MatchupId", model.MatchupId); if (model.ParentMatchup == null) { dbData.Add("@ParentMatchupId", null); } else { dbData.Add("@ParentMatchupId", model.ParentMatchup.Id); } if (model.TeamCompeting == null) { dbData.Add("@TeamCompetingId", null); } else { dbData.Add("@TeamCompetingId", model.TeamCompeting.Id); } dbData.Add("@Score", 0); dbData.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output); connection.Execute("dbo.spMatchupEntries_Insert", dbData, commandType: CommandType.StoredProcedure); model.Id = dbData.Get <int>("@id"); } return(model); }
public BatteryModel CreateBattery(BatteryModel model) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString("GyrodataTracker"))) { var p = new DynamicParameters(); p.Add("@BoxNumber", model.BoxNumber); p.Add("@BatteryCondition", model.BatteryCondition); p.Add("@SerialOne", model.SerialOne); p.Add("@SerialTwo", model.SerialTwo); p.Add("@SerialThr", model.SerialThr); p.Add("@CCD", model.CCD); p.Add("@Invoice", model.Invoice); p.Add("@BatteryStatus", model.BatteryStatus); p.Add("@Arrived", model.Arrived); p.Add("@Container", model.Container); p.Add("@Comment", model.Comment); p.Add("@Id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output); connection.Execute("dbo.spBatteries_Insert", p, commandType: CommandType.StoredProcedure); model.Id = p.Get <int>("@Id"); return(model); } }
public void EditItem(string Id, ItemModel model) { int id = int.Parse(Id); var headers = OperationContext.Current.IncomingMessageProperties["httpRequest"]; var token = ((HttpRequestMessageProperty)headers).Headers["Token"]; using (var dbContext = new TokenDbContext()) { ITokenValidator validator = new DatabaseTokenValidator(dbContext); if (validator.IsValid(token) == true) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString("GyrodataTracker"))) { var p = new DynamicParameters(); p.Add("@Id", id); p.Add("@ItemItem", model.Item); p.Add("@ItemAsset", model.Asset); p.Add("@ItemArrived", model.Arrived); p.Add("@ItemInvoice", model.Invoice); p.Add("@ItemCCD", model.CCD); p.Add("@ItemNameRus", model.NameRus); p.Add("@PositionCCD", model.PositionCCD); p.Add("@ItemStatus", model.ItemStatus); p.Add("@ItemBox", model.Box); p.Add("@Container", model.Container); p.Add("@Comment", model.Comment); p.Add("@ItemImage", model.ItemImage); connection.Execute("dbo.spUpdate_Item", p, commandType: CommandType.StoredProcedure); } } } }
public OperationResult DeleteFaxEntry(int sendToFaxID) { OperationResult operationResult = new OperationResult(); using (IDbConnection db = new SqlConnection(ConfigurationValues.PostOfficeDatabaseConnection)) { try { const string query = "delete from FaxSendingInformation " + " where SendFaxID = @SendFaxID"; int rowsAffectd = db.Execute(query, new { @SendFaxID = sendToFaxID.ToString() }); operationResult.Success = true; return operationResult; } catch (Exception er) { operationResult.Success = true; operationResult.AddMessage(er.ToString()); return operationResult; } } }
public bool UpdateFaxEntry(SendFaxInformation faxEntry ) { using (IDbConnection db = new SqlConnection(ConfigurationValues.PostOfficeDatabaseConnection)) { try { const string query = "update FaxSendingInformation " + " set FirstName = @FirstName" + " , LastName = @LastName" + " , Name = @Name" + " , FaxNumber = @FaxNumber" + " , Prefix = @Prefix" + " , Suffix = @Suffix" + " where SendFaxID = @SendFaxID"; int rowsAffectd = db.Execute(query, new { @FirstName = faxEntry.FirstName, @LastName = faxEntry.LastName, @Name = faxEntry.Name, @FaxNumber = faxEntry.FaxNumber, @SendFaxID = faxEntry.SendFaxID, @Prefix = faxEntry.Prefix, @Suffix = faxEntry.Suffix }); return true; } catch (Exception er) { string s1 = er.ToString(); return false; //Log.LogMessage(er.ToString()); } } }
/// <summary> /// Update employee /// </summary> /// <param name="employee">Object of employeef </param> public void UpdateEmployee(Employee employee) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(CONNECTION_STRING))) { // Get Department Id var p = new DynamicParameters(); p.Add("@name", employee.Department.ToString()); int departmentId = 0; List <int> tmp_ids = connection.Query <int>("dbo.spDepartment_GetDepartmentId", p, commandType: CommandType.StoredProcedure).ToList(); foreach (var id in tmp_ids) { departmentId = id; break; } // Update Employee p = new DynamicParameters(); p.Add("@LastName", employee.LastName); p.Add("@FirstName", employee.FirstName); p.Add("@Department", departmentId); p.Add("@id", employee.Id); connection.Execute("spEmployee_Update", p, commandType: CommandType.StoredProcedure); } }
public bool Save() { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("PresentaoDB"))) { try { connection.Execute($"INSERT INTO FORNECEDOR (NOME, TELEFONE, CIDADE, ESTADO, LOGRADOURO, NUMERO, CNPJ, EMAIL, BANCO, AGENCIA, CONTACORRENTE) " + $"VALUES ('{ this.Nome }'," + $"'{ this.Telefone }', " + $"'{ this.Cidade }', " + $"'{ this.Estado }', " + $"'{ this.Logradouro }', " + $"'{ this.Numero }', " + $"'{ this.CNPJ }', " + $"'{ this.Email }', " + $"'{ this.Banco }', " + $"'{ this.Agencia }', " + $"'{ this.ContaCorrente }');"); return(true); } catch (SqlException e) { return(false); } } }
public void DeleteLoyaltyCard(Guid guid) { using (SqlConnection connection = new SqlConnection(this._connectionString)) { connection.Execute("Delete From VintageRabbit.LoyaltyCards Where Guid = @Guid", new { Guid = guid }); } }
public void UpdateData <T>(T person, string sql) { using (IDbConnection cn = new System.Data.SqlClient.SqlConnection(LoadConnectionString())) { cn.Execute(sql, person); } }
public void Drop(DatabaseObjectName name) { using (var connection = new SqlConnection(connectionString)) { connection.Execute(dropProcedureCommand, name); } }
private void AddNewItemButton_Click(object sender, EventArgs e) { ItemModel model = new ItemModel( NewItemIdTextbox.Text, NewItemNameTextBox.Text, NewItemDescriptionTextBox.Text, NewItemPriceTextBox.Text, NewItemDiscountTextBox.Text); //NOTE: ItemDisabled is set as 0 by default in dbo.spItem_CreateNew store procedure. using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(this.connectionString)) { var p = new DynamicParameters(); p.Add(@"ItemId", model.ItemId); p.Add(@"ItemName", model.ItemName); p.Add(@"ItemDescription", model.ItemDescription); p.Add(@"ItemPrice", model.ItemPrice); p.Add(@"ItemDiscount", model.ItemDiscount); connection.Execute("dbo.spItem_CreateNew", p, commandType: CommandType.StoredProcedure); } MessageBox.Show("Item " + NewItemNameTextBox.Text + "has successfully been added."); NewItemIdTextbox.Text = ""; NewItemNameTextBox.Text = ""; NewItemDescriptionTextBox.Text = ""; NewItemPriceTextBox.Text = ""; NewItemDiscountTextBox.Text = ""; RePopulateInventoryFromItems(); }
private static void EnsureDatabase() { using (var connection = SqlConnections.NewByKey("Default")) try { connection.Open(); } catch { var cb = new DbConnectionStringBuilder(); cb.ConnectionString = SqlConnections.GetConnectionString("Default").ConnectionString; var catalog = cb["Initial Catalog"]; cb["Initial Catalog"] = null; cb["AttachDBFilename"] = null; using (var serverConnection = new SqlConnection(cb.ConnectionString)) { serverConnection.Open(); serverConnection.Execute(String.Format( @"CREATE DATABASE [{0}] ON PRIMARY (Name = N'{0}', FILENAME = '{1}\{0}.mdf') LOG ON (NAME = N'{0}_log', FILENAME = '{1}\{0}.ldf');", catalog, HostingEnvironment.MapPath("~/App_Data"))); } SqlConnection.ClearAllPools(); } RunMigrations(); }
public void InsertRoom(string number, string temp, string humidity) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.ConnVal("con"))) { List <Rooms> room = new List <Rooms>(); room.Add(new Rooms { Number = number, Temperature = temp, Humidity = humidity }); var command = new SqlCommand("dbo.spSchoolRooms_GetByRoom @Number"); command.Parameters.AddWithValue("@Number", number); int Exist = (int)command.ExecuteScalar(); if (Exist > 0) { UpdateRoom(temp, humidity); } else { connection.Execute("dbo.RoomInsert @Number, @Temperature, @Humidity", room); } } }
public static void ToggleIssue(string tableName, long id) { using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(WebInfo.Conn)) { string sql = "Update " + tableName + " Set IsIssue = 1 - IsIssue Where ID = " + id; conn.Execute(sql); } }
public void UpSertInviteInfo(InviteModel model) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(db))) { var p = new DynamicParameters(); if (model.Id == 0) { p.Add("@EventId", model.Event.Id); p.Add("@ClientId", model.Client.Id); } else { p.Add("@EventId", 0); p.Add("@ClientId", 0); } p.Add("@InviteApproved", model.InviteApproved); p.Add("@EmailSentDate", model.EmailSentDate); p.Add("@ClientAttending", model.ClientAttending); p.Add("@PlacesReserved", model.PlacesReserved); p.Add("@id", model.Id, dbType: DbType.Int32, direction: ParameterDirection.InputOutput); connection.Execute("dbo.spUpSertInvitationInfo_ByInviteId", p, commandType: CommandType.StoredProcedure); model.Id = p.Get <int>("@id"); } }
//TODO - Make the CreateEmployee method actually save to the database /// <summary> /// Saves a new employee to the database. /// </summary> /// <param name="employee">The employee information.</param> /// <returns>The employee information, including the unique identifier.</returns> public Employee CreateEmployee(Employee employee) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.ConnectionString(CONNECTION_STRING))) { var p = new DynamicParameters(); p.Add("@name", employee.Department.ToString()); int departmentId = 0; List <int> tmp_ids = connection.Query <int>("dbo.spDepartment_GetDepartmentId", p, commandType: CommandType.StoredProcedure).ToList(); foreach (var id in tmp_ids) { departmentId = id; break; } p = new DynamicParameters(); p.Add("@FirstName", employee.FirstName); p.Add("@LastName", employee.LastName); p.Add("@Department", departmentId); p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output); connection.Execute("dbo.spEmployee_Insert", p, commandType: CommandType.StoredProcedure); employee.Id = p.Get <int>("@id"); return(employee); } }
public void RemoveFolder(string folderName) { try { string query = "delete from [PostOfficeFoldersByUser]" + " where [Name] = @Name" + " and [ActiveDirectoyUser] = @ActiveDirectoyUser"; using (SqlConnection db = new SqlConnection(ConfigurationValues.PostOfficeDatabaseConnection)) { int rowsAffectd = db.Execute(query, new { @ActiveDirectoyUser = Utility.GetUserName(), @Name = folderName, } ); } //operationResult.Success = true; // return operationResult; } catch (Exception er) { Logging.LogErrors(ConfigurationValues.ErrorLogPath, er.ToString()); } }
public int Insert_CardReceipt(CCardReceipt pos_CardReceiptModel) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("POS"))) { string query = "INSERT INTO CardReceipt (ReceiptInformation,SeqNo,TransactionType,TransactionStatus,TransactionDate,TransactionTime, " + "TransactionAmount,TipAmount,CashBackAmount,SurchargeAmount,TaxAmount,TotalAmount,InvoiceNo,PurchaseOrderNo,ReferenceNo," + "TransactionSequenceNo,TicketNo,VoucherNo,ClerkId,GiftCardReferenceNo,OriginalTransactionType," + "CustomerCardType,CustomerCardDescription,CustomerAccountNumber,CustomerLanguage,CustomerAccountType,CustomerCardEntryMode," + "EmvAid,EmvTvr,EmvTsi,EMVApplicationLabel,CVMResult,AuthorizationNo,HostResponseCode,HostResponseText,HostResponseISOCode," + "RetrievalReferenceNo,AmountDue,TraceNo,CardBalance,HostTransactionRefNbr,BatchNumber,TerminalId,DemoMode,MerchId,MerchCurrencyCode," + "ReceiptHeader1,ReceiptHeader2,ReceiptHeader3,ReceiptHeader4,ReceiptHeader5,ReceiptHeader6,ReceiptHeader7," + "ReceiptFooter1,ReceiptFooter2,ReceiptFooter3,ReceiptFooter4,ReceiptFooter5,ReceiptFooter6,ReceiptFooter7," + "EndorsementLine1,EndorsementLine2,EndorsementLine3,EndorsementLine4,EndorsementLine5,EndorsementLine6," + "EmvRespCode,TransactionData,CreateInvoiceNo,CreateDate,CreateTime,CreateUserId,CreateUserName,CreateStation)" + "VALUES (@ReceiptInformation,@SeqNo,@TransactionType,@TransactionStatus,@TransactionDate,@TransactionTime,@TransactionAmount," + "@TipAmount,@CashBackAmount,@SurchargeAmount,@TaxAmount,@TotalAmount,@InvoiceNo,@PurchaseOrderNo,@ReferenceNo," + "@TransactionSequenceNo,@TicketNo,@VoucherNo,@ClerkId,@GiftCardReferenceNo,@OriginalTransactionType," + "@CustomerCardType,@CustomerCardDescription,@CustomerAccountNumber,@CustomerLanguage,@CustomerAccountType,@CustomerCardEntryMode," + "@EmvAid,@EmvTvr,@EmvTsi,@EMVApplicationLabel,@CVMResult,@AuthorizationNo,@HostResponseCode,@HostResponseText,@HostResponseISOCode," + "@RetrievalReferenceNo,@AmountDue,@TraceNo,@CardBalance,@HostTransactionRefNbr,@BatchNumber,@TerminalId,@DemoMode,@MerchId,@MerchCurrencyCode," + "@ReceiptHeader1,@ReceiptHeader2,@ReceiptHeader3,@ReceiptHeader4,@ReceiptHeader5,@ReceiptHeader6,@ReceiptHeader7," + "@ReceiptFooter1,@ReceiptFooter2,@ReceiptFooter3,@ReceiptFooter4,@ReceiptFooter5,@ReceiptFooter6,@ReceiptFooter7," + "@EndorsementLine1,@EndorsementLine2,@EndorsementLine3,@EndorsementLine4,@EndorsementLine5,@EndorsementLine6," + "@EmvRespCode,@TransactionData,@CreateInvoiceNo,@CreateDate,@CreateTime,@CreateUserId,@CreateUserName,@CreateStation)"; var count = connection.Execute(query, pos_CardReceiptModel); return(count); } }
public void Update(ParsedMessage entity) { using (var conn = new SqlConnection(Settings.Default.ConnectionString)) { conn.Open(); conn.Execute("UPDATE ParsedMessage SET " + "BodyParsed = @BodyParsed, " + "BodyPlain = @BodyPlain, " + "BodyRich = @BodyRich, " + "ClientType = @ClientType, " + "CreatedAt = @CreatedAt, " + "IsDirectMessage = @IsDirectMessage, " + "GroupId = @GroupId, " + "RepliedToId = @RepliedToId, " + "SenderId = @SenderId, " + "ThreadId = @ThreadId, " + "Url = @Url, " + "WebUrl = @WebUrl, " + "LikedByCount = @LikedByCount, " + "LikedByNames = @LikedByNames " + "WHERE Id = @Id", entity); } }
public ItemModel CreateItem(ItemModel model) { using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString("GyrodataTracker"))) { var p = new DynamicParameters(); p.Add("@Item", model.Item); p.Add("@Asset", model.Asset); p.Add("@Arrived", model.Arrived); p.Add("@Invoice", model.Invoice); p.Add("@CCD", model.CCD); p.Add("@NameRus", model.NameRus); p.Add("@PositionCCD", model.PositionCCD); p.Add("@ItemStatus", model.ItemStatus); p.Add("@Box", model.Box); p.Add("@Container", model.Container); p.Add("@Comment", model.Comment); p.Add("@Id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output); connection.Execute("dbo.spItems_Insert", p, commandType: CommandType.StoredProcedure); model.Id = p.Get <int>("@Id"); return(model); } }
private static void TableValuedParms() { //HERE DATATABLE NAME SHOULD BE THE TABLEVALUE TYPE NAME OTHERWISE WILL GET AN ERROR TYPENAME IS FOUND var dt = new DataTable { TableName = "DepartmentType" }; dt.Columns.Add("DepartmentId", typeof(int)); dt.Columns.Add("Name", typeof(string)); var arrayName = new[] { "HR", "Dev", "DevOps", "Admin" }; for (var i = 0; i <= 3; i++) { var row = dt.NewRow(); row["DepartmentId"] = i + 2; row["Name"] = arrayName[i]; dt.Rows.Add(row); } using (var con = new SqlConnection(ConString)) { var parameters = new DynamicParameters(); parameters.Add("@DepartmentDetails", dt, DbType.Structured, ParameterDirection.Input); parameters.Add("@ReturnValue", 0, direction: ParameterDirection.ReturnValue); con.Execute("spBulkInsert", parameters, CommandType.StoredProcedure); Console.WriteLine("\nExpected - {0}, Got - {1}", 4, parameters.Get<int>("@ReturnValue")); } }