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"));
}
}
