Insert numerical (decimal) data from textbox value

问题:

I am confused by the following issue;

I have a C# (WindowsForms) application which I connect to a SQL Server DB and have no problem to INSERT, SELECT, UPDATE... until I started to work with numerical data;

Purpose of this application is to manage employees, their contracts, rate of work, contracts durations, hourly rates... and do some funny calculations with that, nothing magic.

Basically, I need to store some values (decimal? double? float?) with the format "0000,0000" in my DB.

• In my DB, I have set my table with all columns where I require these "000,0000" values to decimal

• In my forms, I haven't specified any specific properties to my textboxes,

• To insert I use a method for which I defined decimal arguments

      public void createNewContract(int employeeId, string agency, string role, string contractType, string startDate,
      string endDate, string lineManager, string reportTo, string costCenter, string functionEng, string atrNo, string atrDate, string prNo, string prDate,
      string poNo, string poDate, string comments, decimal duration, decimal workRatePercent, string currency, decimal hourlyRate, decimal value)
  {
      if (conn.State.ToString() == "Closed")
      {
          conn.Open();
      }
      SqlCommand newCmd = conn.CreateCommand();
      newCmd.Connection = conn;
      newCmd.CommandType = CommandType.Text;
      newCmd.CommandText = "INSERT INTO tblContracts (CreatedById, CreationDate, EmployeeId, Role, ContractType, StartDate, "
      + "EndDate, Agency, LineManager, ReportTo, CostCenter, FunctionEng, AtrNo, AtrDate, PrNo, PrDate, PoNo, PoDate, Comments, Duration, WorkRatePercent, Currency, HourlyRate, Value)"
      + "VALUES ('" + connectedUser.getUserId() + "','" + DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss") + "','" + employeeId + "','" + role + "','" + contractType
      + "','" + startDate + "','" + endDate + "','" + agency + "','" + lineManager + "','" + reportTo + "','" + costCenter + "','" + functionEng + "','" + atrNo + "','" + atrDate + "','" + prNo
       + "','" + prDate + "','" + poNo + "','" + poDate + "','" + comments + "','" + duration + "','" + workRatePercent + "','" + currency + "','" + hourlyRate + "','" + value + "')";
      newCmd.ExecuteNonQuery();
      MessageBox.Show("Contract has been successfully created", "Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
  }
  

(through this method, I only need to insert as 00,0000 a duration (nb hours), workrate percentage, an hourly rate (money in a currency) and a value (money in a currency))

• To capture my textboxes values and send them through my method 'createNewContrat', I have tried Convert.ToDecimal(this.txtDuration.Text) and plenty other things that seemed good to me, but i don't manage to understand the mechanic and i'm certainly not using the most pratical/clever solution...

I keep getting the following error;

System.FormatException: Le format de la chaîne d'entrée est incorrect. = The format of the input/entry string is incorrect
à System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
à System.Number.ParseDecimal(String value, NumberStyles options, NumberFormatInfo numfmt)
à System.Convert.ToDecimal(String value)

What would you recommend?

回答1:

First of all, Always use using when dealing with SqlConnection and SqlCommand and all other classes that implements IDisposable just read more about it..

Second thing, Always use parameters with SqlCommand and never pass the values as a string to the sql string. This is a serious security issue. In addition to that parameters makes your code human friendly!

// Always use (using) when dealing with Sql Connections and Commands
using (sqlConnection conn = new SqlConnection())
{
    conn.Open();

    using (SqlCommand newCmd = new SqlCommand(conn))
    {
        newCmd.CommandType = CommandType.Text;

        newCmd.CommandText = 
              @"INSERT INTO tblContracts (CreatedById, CreationDate, EmployeeId, Role, ContractType, StartDate, EndDate, Agency, LineManager, ReportTo, CostCenter, FunctionEng, AtrNo, AtrDate, PrNo, PrDate, PoNo, PoDate, Comments, Duration, WorkRatePercent, Currency, HourlyRate, Value) 
              VALUES (@UserID, @CreationDate, @EmployeeID, @Role.....etc)";

        // for security reasons (Sql Injection attacks) always use parameters
        newCmd.Parameters.Add("@UserID", SqlDbType.NVarChar, 50)
             .Value = connectedUser.getUserId();

        newCmd.Parameters.Add("@CreationDate", SqlDbType.DateTime)
             .Value = DateTime.Now;

        // To add a decimal value from TextBox
        newCmd.Parameters.Add("@SomeValue", SqlDbType.Decimal)
             .Value = System.Convert.ToDecimal(txtValueTextBox.Text);

        // complete the rest of the parameters
        // ........

        newCmd.ExecuteNonQuery();

        MessageBox.Show("Contract has been successfully created", "Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
}

回答2:

This is not a direct answer to your question, but please (!) replace this ugly method by this:

Create a class for your contracts. This will make it much easier to handle contracts. If you have several methods handlings contracts in some way, you will not have to change the almost endless parameter lists of all of them, when properties are added to the contract.

public class Contract
{
    public int EmployeeID { get; set; }
    public string Agency { get; set; }
    public string Role { get; set; }
    ... and so on
}

and change the method signature to

public void CreateNewContract(Contract contract)

Headers of methods loading contracts form the database would look like this

public List<Contract> LoadAllContracts()

// Assuming contractID is the primary key
public Contract LoadContractByID(int contractID)

Much easier than returning 1000 variables!

You can create a new contract with

var contract = new Contract {
    EmployeeID = 22,
    Agency = "unknown",
    Role = "important", 
    ...
};

---

Also (as others have pointed out already) use command parameters.

newCmd.Parameters.AddWithValue("@EmployeeID", contract.EmployeeID);
newCmd.Parameters.AddWithValue("@Agency", contract.Agency);
newCmd.Parameters.AddWithValue("@Role", contract.Role);

(HaLaBi's post shows how to formulate your insert command string.)