C# update and set of database (datediff) don't work when in C# but works if in SQL Server

Question

I have searched for solutions but I can't find one; please help.

I have this code fragment in C#:

using (SqlCommand command = new SqlCommand())
{
    command.Connection = openCon;
    command.CommandType = CommandType.Text;
    command.CommandText = "update logRecords set totalHours = DATEDIFF(HOUR,timeIn,timeOut)";

    try
    {
        openCon.Open();
        int recordsAffected = command.ExecuteNonQuery();
        MessageBox.Show("Records affected: " + recordsAffected);
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message);
     }
     finally
     {
         openCon.Close();
         GetLogData();
     }
}

but it doesn't work. It didn't show the message box in the try block neither the one in the catch block.

Thanks for helping :D

you didn't specify the `where` clause, it will update whole table — Ehsan Sajjad, Sep 22 '17 at 08:49
@jenel Instead of text command, I will create Stored Procedure to update logrecords.. — Bharat, Sep 22 '17 at 08:53
I'm assuming that the `openCon` is `SqlConnection`, so you don't need to use `finally` block, just add it into `using` block. `SqlCommand`'s `CommandType` is `Text` by default. — SᴇM, Sep 22 '17 at 08:58
@EhsanSajjad that's what i want to do, to update all the records in my table. — Jenel Gomez, Sep 22 '17 at 09:04
@SeM this is the declaration part of openCon SqlConnection openCon = new SqlConnection("server=localhost;" + "Trusted_Connection=yes;" + "database=dbSample; " + "connection timeout=30"); — Jenel Gomez, Sep 22 '17 at 09:05
What kind of application is this? Webapplication does not show message box. — Amit Kumar Singh, Sep 22 '17 at 09:07
i have other update queries in my code and they all worked well, that's the only query that doesn't work — Jenel Gomez, Sep 22 '17 at 09:07
Have you stepped through the code to see if it is even running? Have you put a breakpoint in the code? — Derek, Sep 22 '17 at 09:09
@JenelGomez: first thing, don't call sir, second thing, whatever you right overhere will come to SP. "command.CommandText = "update logRecords set totalHours = DATEDIFF(HOUR,timeIn,timeOut)"; " — Bharat, Sep 22 '17 at 09:10
@Bharat i'm sorry for that. :) thank you :D i'll try it now. :D — Jenel Gomez, Sep 22 '17 at 09:15
@JenelGomez Your code is fine. I checked with a proper connection string. — Amit Kumar Singh, Sep 22 '17 at 09:18
I fill the problem is with `DATEDIFF(HOUR,timeIn,timeOut)`. You can Try to run the query with some value through sql server then try it out with code. — Rojalin Sahoo, Sep 22 '17 at 11:12

score 1 · Accepted Answer · edited Sep 22 '17 at 14:05

1

You can access query with parameters, hope this help:

using(var openCon = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand())
{
    command.Connection = openCon;
    command.CommandType = CommandType.Text;
    command.CommandText = "update logRecords set totalHours = DATEDIFF(HOUR,@timeIn,@timeOut)";

    try
    {
        openCon.Open();
        command.Parameters.AddWithValue("@timeIN", timeIn);
        command.Parameters.AddWithValue("@timeOut", timeOut);
        int recordsAffected = command.ExecuteNonQuery();
        MessageBox.Show("Records affected: " + recordsAffected);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

    GetLogData();
}

edited Sep 22 '17 at 14:05

mason

31,774
10
77
121

answered Sep 22 '17 at 10:35

Nam Doan

51
5

This is the better answer, but downvotes: 1. random indenting; 2. not using a `using` block. – Richard Sep 22 '17 at 13:36
AddWithValue has [performance implications](https://stackoverflow.com/questions/21263536/addwithvalue-sql-injection-safe-why). – mason Sep 22 '17 at 14:13

Bharat · Answer 2 · 2017-09-22T13:57:29.583

First of all I will create Stored procedure and I will update any of my table through procedure.

So, Basic SP will be like below, and I will run it in a Database(SQL).

CREATE PROCEDURE Set_LogRecords_TotalHours
    -- Add the parameters for the stored procedure here
    @timeIn     datetime,
    @timeOut    datetime
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    UPDATE  LogRecords 
    SET     TotalHours = DATEDIFF(HOUR, @timeIn, @timeOut)

    -- returns number of rows
    SELECT @@ROWCOUNT
END
GO

Now, I will Move to code side.

I will Create a Generic method to call All of mine Stored procedures, see below.

public static DataSet GetRecordWithExtendedTimeOut(string SPName, params SqlParameter[] SqlPrms)
    {

        DataSet ds = new DataSet();

        try
        {
            //here give reference of your connection and that is "openCon"
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(SPName, conn))
                {
                    command.Parameters.AddRange(SqlPrms);
                    command.CommandTimeout = 0;
                    conn.Open();
                    command.CommandType = CommandType.StoredProcedure;

                    using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                    {
                        try
                        {
                            dataAdapter.SelectCommand = command;
                            dataAdapter.Fill(ds);
                        }
                        catch (Exception ex)
                        {                                
                            return null;
                        }
                        finally
                        {
                            conn.Close();
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            //Handle Errror
        }

        return ds;          
    }

Now at last, call this method from wherever you need to access database.

Over here is the example of calling generic method.

    //Add all the parameter that you want to pass to SP, here we have 2 and they are in DAteTime Formate
SqlParameter[] parameters = 
                    {
                        new SqlParameter { ParameterName = "@timeIn", Value = ValueOf_TimeIN_DateTIME }
                        new SqlParameter { ParameterName = "@timeOut", Value = ValueOf_TimeOUT__DateTIME}
                    };

DataSet ds = DAL.GetRecordWithExtendedTimeOut("Set_LogRecords_TotalHours", parameters);

if (ds != null && ds.Tables.Count >= 1 && ds.Tables[0].Rows.Count >= 1)
{
    //Debugg ds and you will see the number of records that affected in last update
}

What's the point in wrapping such a simple single statement in a stored proc (I know some places have "only stored procedure" rules, but that's about DBAs having control issues.) — Richard, Sep 22 '17 at 13:38
@Richard : only one, She needs to understand the SP based framework, check comments in question part..it can be helpfull for other SO.. — Bharat, Sep 22 '17 at 13:43
SProcs only came up when you suggested them ("what exactly is a Stored Procedure"). — Richard, Sep 22 '17 at 13:47
@Richard, Yes, that's true, but what I think is that SO is new, and she didn't even know about SP, and that's why I created that much mesh, I also know that Issue is in the parameters that she is passing but she can't check it because of inline query. maybe my answer can explain her a way of ADO.Net working pattern.. — Bharat, Sep 22 '17 at 13:52
You're really just confusing absolutely everything by suggesting a stored proc when there was no need to. Stored Procs aren't magic solutions. Also, this catching an exception and returning null business is just gross. — mason, Sep 22 '17 at 14:08

C# update and set of database (datediff) don't work when in C# but works if in SQL Server

2 Answers2