static ParkeonTransactions DataTabletoTransactions(DataRow datat) { var trans = new ParkeonTransactions() { SystemID = Int32.Parse(datat["System ID"].ToString()), PrintedID = Int32.Parse(datat["Printed ID"].ToString()), ServerTime = DateTime.Parse(datat["Server Time"].ToString()), TerminalDate = DateTime.Parse(datat["Terminal Date"].ToString()), EndDate = DateTime.Parse(datat["End Date"].ToString()), PaymentType = datat[0].ToString(), Amount = Decimal.Parse(datat["Amount"].ToString()), Currency = datat["Currency"].ToString(), CardType = datat["Card Type"].ToString(), CardNumber = datat["Card #:"].ToString(), ZoneDesc = datat["Zone Desc"].ToString(), CircuitDesc = datat["Circuit Desc"].ToString(), ParkCode = Int32.Parse(datat["Park Code"].ToString()), Park = datat["Park"].ToString(), MeterCode = Int32.Parse(datat["Meter Code"].ToString()), MeterDesc = datat["Meter Description"].ToString(), Address = datat["Address"].ToString(), Type = datat["Type"].ToString(), UserType = datat["User Type:"].ToString(), TotalDuration = datat["Total Duration"].ToString(), PaidDuration = datat["Paid Duration"].ToString(), FreeDuration = datat["Free Duration:"].ToString(), TotalDurationinMins = Int32.Parse(datat["Total Duration in mins"].ToString()), PaidDurationinMins = Int32.Parse(datat["Paid Duration in mins"].ToString()), FreeDurationinMins = Int32.Parse(datat["Free Duration in mins"].ToString()), SpaceCount = datat["Space #"].ToString(), PlateNumber = datat["Plate #"].ToString() }; return(trans); }
public static void UpsertParkeonTransaction(ParkeonTransactions trans) { string ParkeonupsertCommand = @"IF NOT EXISTS (SELECT 1 FROM [Parkeon].[ParkeonTransactions] WHERE [SystemID] = @SystemID) BEGIN INSERT INTO [Parkeon].[ParkeonTransactions] ([SystemID] ,[PrintedID] ,[ServerTime] ,[TerminalDate] ,[EndDate] ,[PaymentType] ,[Amount] ,[Currency] ,[BankingID] ,[CardType] ,[CardNumber] ,[ZoneDesc] ,[CircuitDesc] ,[ParkCode] ,[Park] ,[MeterCode] ,[MeterDesc] ,[Address] ,[Type] ,[UserType] ,[TotalDuration] ,[PaidDuration] ,[FreeDuration] ,[TotalDurationinMins] ,[PaidDurationinMins] ,[FreeDurationinMins] ,[SpaceCount] ,[Plate#]) VALUES (@SystemID ,@PrintedID ,@ServerTime ,@TerminalDate ,@EndDate ,@PaymentType ,@Amount ,@Currency ,@BankingID ,@CardType ,@CardNumber ,@ZoneDesc ,@CircuitDesc ,@ParkCode ,@Park ,@MeterCode ,@MeterDesc ,@Address ,@Type ,@UserType ,@TotalDuration ,@PaidDuration ,@FreeDuration ,@TotalDurationinMins ,@PaidDurationinMins ,@FreeDurationinMins ,@SpaceCount ,@PlateNumber ) END ELSE BEGIN UPDATE [Parkeon].[ParkeonTransactions] SET [PrintedID] = @PrintedID ,[ServerTime] = @ServerTime ,[TerminalDate] = @TerminalDate ,[EndDate] = @EndDate ,[PaymentType] = @PaymentType ,[Amount] = @Amount ,[Currency] = @Currency ,[BankingID] = @BankingID ,[CardType] = @CardType ,[CardNumber] = @CardNumber ,[ZoneDesc] = @ZoneDesc ,[CircuitDesc] = @CircuitDesc ,[ParkCode] = @ParkCode ,[Park] = @Park ,[MeterCode] = @MeterCode ,[MeterDesc] = @MeterDesc ,[Address] = @Address ,[Type] = @Type ,[UserType] = @UserType ,[TotalDuration] = @TotalDuration ,[PaidDuration] = @PaidDuration ,[FreeDuration] = @FreeDuration ,[TotalDurationinMins] = @TotalDurationinMins ,[PaidDurationinMins] = @PaidDurationinMins ,[FreeDurationinMins] = @FreeDurationinMins ,[SpaceCount] = @SpaceCount ,[Plate#] = @PlateNumber ,[record_updated_date] = @RecordUpdatedAt WHERE [SystemID] = @SystemID END"; try { using (var connection = new SqlConnection(SQLServerConnectionString)) { connection.Open(); using (SqlCommand cmd = new SqlCommand(ParkeonupsertCommand, connection)) { cmd.Parameters.Add("@SystemID", SqlDbType.Int).Value = trans.SystemID; cmd.Parameters.Add("@PrintedID", SqlDbType.Int).Value = trans.PrintedID; cmd.Parameters.Add("@ServerTime ", SqlDbType.DateTime2).Value = trans.ServerTime; cmd.Parameters.Add("@TerminalDate", SqlDbType.DateTime2).Value = trans.TerminalDate; cmd.Parameters.Add("@EndDate", SqlDbType.DateTime2).Value = trans.EndDate; cmd.Parameters.Add("@PaymentType", SqlDbType.NVarChar, 255).Value = trans.PaymentType ?? (object)DBNull.Value; cmd.Parameters.Add("@Amount", SqlDbType.Decimal).Value = trans.Amount ?? (object)DBNull.Value; cmd.Parameters.Add("@Currency", SqlDbType.NVarChar, 10).Value = trans.Currency ?? (object)DBNull.Value; cmd.Parameters.Add("@BankingID", SqlDbType.Int).Value = trans.BankingID; cmd.Parameters.Add("@CardType", SqlDbType.NVarChar, 50).Value = trans.CardType ?? (object)DBNull.Value; cmd.Parameters.Add("@CardNumber", SqlDbType.NVarChar, 25).Value = trans.CardNumber ?? (object)DBNull.Value; cmd.Parameters.Add("@ZoneDesc", SqlDbType.NVarChar, 255).Value = trans.ZoneDesc ?? (object)DBNull.Value; cmd.Parameters.Add("@CircuitDesc", SqlDbType.NVarChar, 255).Value = trans.CircuitDesc ?? (object)DBNull.Value; cmd.Parameters.Add("@ParkCode", SqlDbType.Int).Value = trans.ParkCode; cmd.Parameters.Add("@Park", SqlDbType.NVarChar, 255).Value = trans.Park ?? (object)DBNull.Value; cmd.Parameters.Add("@MeterCode", SqlDbType.Int).Value = trans.MeterCode; cmd.Parameters.Add("@MeterDesc", SqlDbType.NVarChar, 255).Value = trans.MeterDesc ?? (object)DBNull.Value; cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 255).Value = trans.Address ?? (object)DBNull.Value; cmd.Parameters.Add("@Type", SqlDbType.NVarChar, 255).Value = trans.Type ?? (object)DBNull.Value; cmd.Parameters.Add("@UserType", SqlDbType.NVarChar, 50).Value = trans.UserType ?? (object)DBNull.Value; cmd.Parameters.Add("@TotalDuration", SqlDbType.NVarChar, 128).Value = trans.TotalDuration ?? (object)DBNull.Value; cmd.Parameters.Add("@PaidDuration", SqlDbType.NVarChar, 128).Value = trans.PaidDuration ?? (object)DBNull.Value; cmd.Parameters.Add("@FreeDuration", SqlDbType.NVarChar, 128).Value = trans.FreeDuration ?? (object)DBNull.Value; cmd.Parameters.Add("@TotalDurationinMins", SqlDbType.Int).Value = trans.TotalDurationinMins; cmd.Parameters.Add("@PaidDurationinMins", SqlDbType.Int).Value = trans.PaidDurationinMins; cmd.Parameters.Add("@FreeDurationinMins", SqlDbType.Int).Value = trans.FreeDurationinMins; cmd.Parameters.Add("@SpaceCount", SqlDbType.NVarChar, 50).Value = trans.SpaceCount ?? (object)DBNull.Value; cmd.Parameters.Add("@PlateNumber", SqlDbType.NVarChar, 255).Value = trans.PlateNumber ?? (object)DBNull.Value; cmd.Parameters.Add("@RecordUpdatedAt", SqlDbType.DateTime2).Value = DateTime.UtcNow; var resultCount = cmd.ExecuteNonQuery(); Console.WriteLine("RowsAffected: {0}", resultCount); } } } catch (Exception ex) { Console.WriteLine("InsertParkeonTransaction error", ex); } }