How to get data from SQL Server using a stored procedure

Question

I cannot get values from database using a stored procedure. Please see code below:

class LaneModel
{
    public string Name { get; set; }
    public int Lane { get; set; }
    public int ManualCount { get; set; }
    public int ReadCount { get; set; }
    public int TotalTransaction { get; set; }
    public string DateTime { get; set; } = System.DateTime.Now.ToString("yyyy/MM/dd");
}

To fetch data from database

public async Task<List<LaneModel>> GetLaneData()
{
    try
    {
        var db = new ApplicationDbContext();
        db.Database.Initialize(true);

        using (var cnxn = db.Database.Connection)
        {
            cnxn.Open();

            var cmd = cnxn.CreateCommand();
            var sp = @"GetManualTransactions";
            cmd.CommandText = sp;
            cmd.CommandType = CommandType.StoredProcedure;

            var dtFrom = cmd.CreateParameter();
            dtFrom.ParameterName = "@DateFrom";
            dtFrom.DbType = DbType.Date;
            dtFrom.Direction = ParameterDirection.Input;
            dtFrom.Value = DateTime.Now.ToString("MM/dd/yyyy");

            cmd.Parameters.Add(dtFrom);

            using (var reader = await cmd.ExecuteReaderAsync())
            {
                try
                {
                    var result = ((IObjectContextAdapter) db)
                        .ObjectContext
                        .Translate<LaneModel>(reader)
                        .ToList();
                    return result;   =========================> I always get 0
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    return null;
                }
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
        return null;
    }
}

Stored procedure in SQL

 @DateFrom AS DATE
    if OBJECT_ID('tempdb..#etc_correlated') IS NOT NULL 
        DROP TABLE #etc_correlated

    SELECT 
        t2.Name, 
        t2.Lane,
        SUM(IIF(READ_STATUS = 'M', 1, 0)) As ManualCount,
        SUM(IIF(READ_STATUS = 'R', 1, 0)) As ReadCount
    into #etc_correlated 
    FROM [HQ_JOINT_DB_TPLEX].DBO.ETC_CORRELATED AS t1
    INNER JOIN [Gen_Report].dbo.Plaza AS t2
        ON t1.EXIT_PLAZA = t2.Code
    AND t1.EXIT_LANE = t2.Lane
    AND CONVERT(DATE, EXIT_TRXN_DTIME) = @DateFrom
    GROUP BY t2.Name, t2.Lane, READ_STATUS

    select Name,
           Lane,
           sum(ManualCount) ManualCount,
           sum(ReadCount) ReadCount,
           sum(ManualCount)  + sum(ReadCount) TotalTransaction
    from #etc_correlated
    group by Name,Lane

But if I supply the date ('02/10/2020') in the procedure itself, I get results. How can I make this work?

Why don't you print the DateFrom value in the SP? Maybe you can insert it to a dummy table and see if it has the value that you expect. — heuristican
– heuristican, Commented Feb 10, 2020 at 6:38
I got it. I just changed my 'DateTime.Now.ToString("MM/dd/yyyy");' to 'DateTime.Now' — Ibanez1408
– Ibanez1408, Commented Feb 10, 2020 at 6:39
Parameter DateFrom AS DATE, it should be @DateFrom AS VARCHAR(20) — Atk
– Atk, Commented Feb 10, 2020 at 6:40
@Ibanez1408 Makes sense. DbType was Date but the actual value assigned was string. — heuristican
– heuristican, Commented Feb 10, 2020 at 6:42

Athanasios Kataras · Accepted Answer · 2020-02-10 06:42:29Z

2

Have you tried with a proper date object?

    dtFrom.ParameterName = "@DateFrom";        
    dtFrom.DbType = DbType.Date;
    dtFrom.Direction = ParameterDirection.Input;
    dtFrom.Value = DateTime.Now.Date;

answered Feb 10, 2020 at 6:42

Athanasios Kataras

26.7k6 gold badges40 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get data from SQL Server using a stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related