Retrieving int Database and storing it in a variable in C#

Question

I am new to C# i would like to know how do i retrieve an integer value from the database and store it in a variable.

After that i need to use this value to add another integer that is already done and store it back into the database.

I have searched many methods but most of the methods that were given stores it in a data grid view but i want to do the calculation without displaying.

SqlDataAdapter sql1 = new SqlDataAdapter("Select finaldays from LeaveTest where Id  = '" + comboBox1.SelectedText + "'", con);//it will take from the selection of combobox
DataTable dt = new DataTable();
sql1.Fill(dt);
con.Open();
TimeSpan timespan1;

timespan1 = dateTimePicker3.Value - dateTimePicker2.Value;
int TotalDays = timespan1.Days+1;//total days that are taken from the datetimepicker
MessageBox.Show(TotalDays.ToString());//displaying the date for testing

I have not added the variable yet for the integer value but lets take it that the variable i want to use is int = finaldays — Darren, Jul 26 '17 at 08:36
[Read this](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) when you get a minute — musefan, Jul 26 '17 at 08:37
Replace the above code with the follwing code. SqlCommand com = new SqlCommand("Select finaldays from LeaveTest where Id = '" + comboBox1.SelectedText + "'", con); int x = (int)com.ExecuteScalar(); — Koderzzzz, Jul 26 '17 at 08:39
@Koderzzzz: Why are you putting that as a comment instead of an answer? — musefan, Jul 26 '17 at 08:40
@Koderzzzz Hi the code that you ask me to replace is it to assign the database value to the variable x? — Darren, Jul 26 '17 at 09:04

Romano Zumbé · Answer 1 · 2017-07-26T08:44:27.363

2

Use SqlCommand for this. If you only expect to retrieve one value ExecuteScalar is the best option to go. Additionally you should use parameters to prevent SQL injection.

SqlCommand cmd = new SqlCommand("Select finaldays from LeaveTest where Id  = @id", con);
cmd.Parameters.Add("@id", SqlDbType.VarChar);
cmd.Parameters["@id"].Value = comboBox1.SelectedText;
try
{
    conn.Open();
    int result = (Int32)cmd.ExecuteScalar();
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}

If there could be multiple results per ID you should use a SqlReader and store the results in a List (like in the example) or work with them when they are retrieved:

SqlCommand cmd = new SqlCommand("Select finaldays from LeaveTest where Id  = @id", con);
cmd.Parameters.Add("@id", SqlDbType.VarChar);
cmd.Parameters["@id"].Value = comboBox1.SelectedText;
try
{
    List<int> resultList = new List<int>();
    conn.Open();
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        resultList.Add((Int32)reader[0]);
    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}

edited Jul 26 '17 at 08:44

answered Jul 26 '17 at 08:36

Romano Zumbé

7,893
4
33
55

Hi the value i would get is final days? – Darren Jul 26 '17 at 08:38
@Darren Yes, you would get the value finaldays for the ID selected in the ComboBox – Romano Zumbé Jul 26 '17 at 08:39
@Darren When you step through the code in debugging, what is the value of result after `ExecuteScalar`? – Romano Zumbé Jul 26 '17 at 09:00
For some reason i cannot go past ExecuteScalar but i tried debugging to the ExecuteScalar the value is 0... – Darren Jul 26 '17 at 09:12
For simplicity, just add a line `string a = "A";` after the line with `ExecuteScalar`. You should be abled to debug until the new line then – Romano Zumbé Jul 26 '17 at 09:13
Zumbe still cannot after adding the line string a = "A" – Darren Jul 26 '17 at 09:21
Does it jump to the `catch` block? – Romano Zumbé Jul 26 '17 at 09:22
You shouldn't post that as an answer. But from your code I can't see what is wrong. If you debug it. It must either reach the line after executescalar or jump to the catch block. – Romano Zumbé Jul 26 '17 at 09:28
i tried changing the con.close and it manage to jump to the catch block but it does not reach the line after executescalar – Darren Jul 27 '17 at 01:44
Which con.Close? – Romano Zumbé Jul 27 '17 at 03:52
i put the con.close after the catch block – Darren Jul 27 '17 at 03:56
i added it to the end but is there anything i can do now to solve this problem or is there any other alternatives? – Darren Jul 27 '17 at 09:22

score 0 · Answer 2 · answered Jul 26 '17 at 08:36

0

You're on the right track, but this really is something that you could easily find an answer to on Google...

Now that you've filled your DataTable, you just need to get a reference to the column value.

int FinalDays = int.TryParse(dt.Rows[0][0], out FinalDays) ? FinalDays : 0;

Since you're new to C#, I'll explain what this is doing.

It's a bit of "syntactical sugar" which is attempting to parse the value from the DataTable column (that is, the value you got from your database) as an integer and output the parsed integer value to FinalDays. If the parse worked (ie. the value is actually a number), then FinalDays gets set to the value from the database, and if it didn't work, then FinalDays gets set to 0.

answered Jul 26 '17 at 08:36

Ortund

8,095
18
71
139

I tried doing that but it does not work they said cannot convert object to string – Darren Jul 26 '17 at 09:02
Last time I did this, I was just doing `Convert.ToInt32(dt.Rows[0][0])` which is safe enough if you know for a fact the value will be a number so maybe try that as an alternative – Ortund Jul 26 '17 at 09:03
As this obviously isn't working, I'd recommend having a closer look at Romano's answer instead. Sorry about that :( – Ortund Jul 26 '17 at 09:23

score 0 · Accepted Answer · answered Jul 28 '17 at 12:30

Hi guys i found another alternative that worked for me...Hope it helps others who encounter the same difficulty as me

        con.Open();

        string sqlSelectQuery = "Select * FROM LeaveTest";
        SqlCommand cmd = new SqlCommand(sqlSelectQuery, con);
        SqlDataReader dr = cmd.ExecuteReader();
        if (dr.Read())
        {
            TimeSpan timespan1;
            timespan1 = dateTimePicker3.Value - dateTimePicker2.Value;
            int totaldays = timespan1.Days;
            textBox2.Text = (dr["finaldays"].ToString());
            int finaldays = Convert.ToInt32(textBox2.Text)+totaldays;
       //textbox2 is a temporary textbox(which is invisible)that is used to put the value finaldays in from the database

            string sql1 = ("Update LeaveTest SET finaldays = '"+ finaldays + "'  WHERE Id='" + comboBox1.Text + "'"); //updating the finaldays in database
            SqlCommand cmd2 = new SqlCommand(sql1, con);
            con.Close();
            con.Open();
          SqlDataReader dr2 =cmd2.ExecuteReader();
            con.Close();


        }
        con.Close();

The code would calculate the total days(totaldays) from the 2 datetime pickers.The amount of days from the database would be retrieved and added with the total days (finaldays).My explanation might be a bit bad but i hope you guys would benefit from this code.:)

Retrieving int Database and storing it in a variable in C#

3 Answers3