// https://stackoverflow.com/q/73272457/1690217

​

using System;

using System.Collections.Generic;

using System.Diagnostics;

using System.Linq;

using System.Data.Entity;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Data;

public class Program

{

    private static string mainconn = FiddleHelper.GetConnectionStringSqlServer();

    public static List<UserClass> Get(DateTime date)

    {

        List<UserClass> Test = new List<UserClass>();

        //string mainconn = ConfigurationManager.ConnectionStrings["myconn"].ConnectionString;

        SqlConnection sqlconn = new SqlConnection(mainconn);

        string sqlquery = "SELECT UserID, Name, Mobile, Age, Date From tbluser where Date BETWEEN @fromDate AND @toDate";

        sqlconn.Open();

        SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);

        sqlcomm.Parameters.Add("@fromDate ", SqlDbType.Date);

        sqlcomm.Parameters["@fromDate "].Value = date.Date;

        sqlcomm.Parameters.Add("@toDate ", SqlDbType.Date);

        sqlcomm.Parameters["@toDate "].Value = date.AddDays(1).Date;

        SqlDataReader reader = sqlcomm.ExecuteReader();

        while (reader.Read())

        {

            Test.Add(new UserClass()

                {

                    UserId = reader.GetValue(0).ToString(),

                    Name = reader.GetString(1),

                    Mobile = reader.GetString(2),

                    Access = Convert.ToInt32(reader.GetValue(3)),

                    Date = (reader.GetValue(4) != DBNull.Value) ? Convert.ToDateTime(reader.GetValue(4)) : (DateTime?)null

                });

            }

        return Test;

    }

    public static void Main()

    {

        SeedDB();

        var data = Get(new DateTime(2020,10,12));

        FiddleHelper.Dump(data);

    }

    private static void SeedDB()

    {

        using(SqlConnection sqlconn = new SqlConnection(mainconn))

        {

            string createScript = @"

CREATE TABLE tbluser (

  UserId int NOT NULL,

  Name varchar(50) NOT NULL,

  Mobile varchar(50) NOT NULL,

  Age int NOT NULL,

  [Date] DateTime2 NOT NULL

);

INSERT INTO tbluser (UserId,Name,Mobile,Age,[Date]) VALUES

(341,'Emily','386754298',24,'2021-11-06 16:04:00'),

(2555,'Peter','48295729',45,'2020-10-12 20:35:00')

;

";

            SqlCommand sqlcomm = new SqlCommand(createScript, sqlconn); 

            sqlconn.Open();

            sqlcomm.ExecuteNonQuery();

            sqlconn.Close();

        }

    }

}

​

public class UserClass

{

    public string UserId { get; set; }

    public string Name { get; set; }

    public string Mobile { get; set; }

    public int Access { get; set; }

    public int Age { get; set; }

    public DateTime? Date { get; set; }

}