How can I take a data from a DB and add it to another DateTime data in C #?

Question

How can I take a data from a DB and add it to another DateTime data in C #?

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

My question is this: I am using Visual Studio 2015 , and I am programming in C # for Windows Forms , the case that is I have a variable that is stored in a database of SQL Server 2008 , and the data that is there is of type int . What I want to do is take the data found in the database and convert it to type DateTime to be able to add it with another data I have previously in my program and then show it in a DGV . If someone could help me with the necessary syntax, I would appreciate it.

Edited:

I clarify what is in the database is a variable that is called duración , and is an integer type, I can only make queries to the database, but I can not change the data type, What I want to do is that through a query made from C #, I can obtain that data and pass it to a data of datetime if possible, and later add it to another data of the same type, this because I am programming a control for the activities in which the activities are within the database and each one has a time duration already assigned. So the first data that is of datetime is one that I call HoraInicio , in which the user determines what time their activities start and what I want to do is that once having the start time of the agenda, depending on the service that is scheduled, the duration is calculated.

An example of the above would be something like this:

servicios                    hora
junta de ejecutivos          09:00 - 11:00
Inventario                   11:00 - 11:45

Explaining the above, the service "executive board" is taken and it is selected that the service or the agenda starts at 9:00 . This data is saved as datetime . Now, in the database it is already saved that the service "board of executives" , will always have a duration of 2hrs , and the same for inventory, will always last 45min , the sum I plan to do it with the datetime.addminutes() method since the duration is in the form of minutes in the database. What I try to do is that depending on the service that has been selected, I also select the duration of it and it becomes a data of type datetime to add it with HoraInicio , and thus be able to achieve what I need. / p>

The code I have is the following:

class conexion
{
    SqlConnection con1;
    SqlCommand cmd;
    SqlDataReader dr;
    public conexion()
    {
        try
        {
            con1 = new SqlConnection("Data Source=OROCHI;Initial Catalog=AGENDAS;Persist Security Info=True;User ID=user;Password=holamundo");
            con1.Open();
        }

        catch(Exception ex)
        {
            MessageBox.Show("Error en la conexión" + ex.ToString());    
        }
 }

The process of connecting to the database is done through a class, which is called conexion , in which I have called all the services that are in the database and add them to a ComboBox to display them as a list.

In the main I tried to do this, but obviously it does not work, and I really expected it, because I do not even know how to do it.

void duracion(int tiempo)
{
    SqlConnection con2;
    SqlCommand cmd2;
    SqlDataReader dr2;
    try
    {
        DateTime hora = DateTime.ParseExact(textBox3.Text, "HH:mm", System.Globalization.CultureInfo.InvariantCulture);
        con2 = new SqlConnection("Data Source=OROCHI;Initial Catalog=AGENDAS;Persist Security Info=True;User ID=USER;Password=Holamundo");
        con2.Open();
        cmd2 = new SqlCommand("select NB_SERVICIO, NO_BLOQUES * 5 from tablas.C_SERVICIO where NB_SERVICIO == '" + comboBox1.Text + "'", con2);
        dr2 = cmd2.ExecuteReader();
        SqlDataAdapter sda = new SqlDataAdapter("select NO_BLOQUES * 5 from tablas.C_SERVICIO where NB_SERVICIO == '" + comboBox1.Text + "'", con2);
        DataTable dt= new DataTable();
        sda.Fill(dt);
        if (dt.Rows[0][0].ToString() == "1")
        {
            DateTime horaN = hora;
            double minutos = dt.Rows;
        }
    } 
    catch(Exception ex) {
        MessageBox.Show("Error: " + ex.ToString());
    }
}

Another part of the code that I think may be useful is this:

private void button5_Click(object sender, EventArgs e)
{
    dataGridView2.Columns["Column2"].DefaultCellStyle.Format = "HH:mm";//<-formato de Horas/minutos
    checkedListBox1.Visible = false;
    comboBox1.Enabled = false;
    comboBox2.Enabled = false;
    textBox2.Enabled = false;
    button3.Enabled = false;
    button2.Enabled = false;
    DateTime hora = DateTime.ParseExact(textBox3.Text, "HH:mm", System.Globalization.CultureInfo.InvariantCulture);

    if (checkBox1.Checked == true)
    {  
        dataGridView2.Rows.Add(comboBox1.Text,hora);          
    }

What is done here is the data collection of the start time of the calendar through a TextBox and it is saved in the data type, datetime , and then it is added at DGV .

I just need to know how to take the duration of the database and add it to the start time.

c# windows-forms sql-server datetime ado.net

asked by user7070220 08.11.2016 в 20:37

source

2 answers

1

I think you should make several modifications ...

What value do you receive in textBox3 ? if you only receive for example:

01:00

I do not recommend you this way, since you need a value that will be converted to DateTime and 01:00 would not be a valid DateTime value.

To create a variable of type DateTime , I suggest you change your line in this way:

// El campo "textBox3.Text" tiene como valor: "09/11/2016"
// o "09/11/2016 00:00:00"
DateTime hora = DateTime.Parse(textBox3.Text);

Then, to add the minutes to the variable hora , do the following:

hora = hora.AddMinutes(minutos);
// Donde "minutos" es la variable de tipo double que deberías declarar por fuera de la condición "if".

The second query seems to me to be redundant, it is not necessary, since you are getting the same information as in the first query.

I have modified your code in this way:

NOTE: I have not tried the code, so I suggest you have a copy of your current code.

/// <summary>
/// Obtener DateTime con el las horas añadidas.
/// </summary>
/// <param name="tiempo">tiempo "valor del campo textBox3.Text".</param>
/// <returns>DateTime</returns>
public DateTime ObtenerFechaModificada(DateTime tiempo)
{
    SqlConnection con2;
    SqlCommand cmd2;
    SqlDataReader dr2;
    DataTable dt = new DataTable();
    double minutos = 0;
    DateTime hora = DateTime.Today();

    try
    {           
        // Validar si es una fecha.
        if (DateTime.TryParse(tiempo, out hora)) {

            // Convertir a fecha el valor del campo de texto.
            hora = DateTime.Parse(tiempo);

            // Consultar información.
            con2 = new SqlConnection("Data Source=OROCHI;Initial Catalog=AGENDAS;Persist Security Info=True;User ID=USER;Password=Holamundo");
            con2.Open();
            cmd2 = new SqlCommand("select NB_SERVICIO, NO_BLOQUES * 5 from tablas.C_SERVICIO where NB_SERVICIO == '" + comboBox1.Text + "'", con2);
            dr2 = cmd2.ExecuteReader();
            SqlDataAdapter sda = new SqlDataAdapter(con2);
            sda.Fill(dt);

            if (dt.Rows[0][0].ToString() == "1")
            {
                // Obtener los minutos.
                minutos = double.parse(dt.Rows[0]["NO_BLOQUES"].ToString());

                // Adicionar los minutos a la variable DateTime llamada "hora".
                hora = hora.AddMinutes(minutos);
            }
        } else {
            MessageBox.Show("No se reconoce como una fecha válida.");
            return hora;
        }
    } 
    catch (Exception ex) 
    {
        MessageBox.Show("Error: " + ex.ToString());
    }

    return hora;
}

I just need to know how to take the duration of the database and add it to the start time.

With the previous code, you use this method to pass it the value of textbox3.Text and the method returns a new variable of type DateTime with the duration added.

Like this:

textBox3.Text = 09/11/2016

The method converts it to DateTime , leaving it like this: 09/11/2016 00:00:00

and assuming you have chosen the Inventory option, the resulting date would be:

11/09/2016 00:45:00

answered by 09.11.2016 в 20:42

Exception java.lang.NumberFormatException when trying a ".size" inside an EL Help with a Code Online Calendar

score 2 · Accepted Answer

Here I leave you a model that can be used to extract the duration of the database and then add it to your variable of type DateTime . You will notice that I favor the use of parameters instead of concatenating values directly to the SQL string. I recommend you to develop this custom to avoid problems, among them the SQL injection .

// Copiado de tu código existente que extrae la hora inicial.
DateTime horaInicio = DateTime.ParseExact(
    textBox3.Text,
    "HH:mm",
    System.Globalization.CultureInfo.InvariantCulture);

// Extraer la duración de la base de datos.
int duracionEnMinutos;
using (var conn = new SqlConnection("..."))
{
    conn.Open();

    using (var cmd = new SqlCommand(
        "select NO_BLOQUES * 5 from tablas.C_SERVICIO where NB_SERVICIO = @nbServicio",
        conn))
    {
        // Uso de parámetros en vez de concatenar directamente al SQL.
        cmd.Parameters.Add(new SqlParameter("@nbServicio", comboBox1.Text));

        duracionEnMinutos = (int)cmd.ExecuteScalar();
    }
}

// Agregar duración a la hora de inicio
DateTime horaFin = horaInicio.AddMinutes(duracionEnMinutos);

// Si deseas obtener una cadena con la hora de inicio y fin combinados así:
// 09:00 - 11:00
string intervaloFormateado =
    $"{horaInicio.ToString("HH:mm")} - {horaFin.ToString("HH:mm")}"