Question getting data from db and put into list

[rwahdan2023]

Hi,

I have the following code and I need to put them in a list:

SqlConnection conn1 = new SqlConnection("Server=(localdb)\\mssqllocaldb;Database=aspnet-ExpenseTracker-63f88f9b-d2f8-444d-a998-9ce2ab1e3f81;Trusted_Connection=True;MultipleActiveResultSets=true");
conn1.Open();
SqlCommand cmd = new SqlCommand("Select * from CalEvents");
List<string> thedata = new List<cmd>;

I am getting error using cmd.

 

[jmcilhinney]

I suggest that you read a basic tutorial on ADO.NET to learn how to execute a query and retrieve the results because that's not it. Apart from anything else, if you have a variable of type List<string> then you have to assign an object of that type to it, so assigning a List<cmd> couldn't possibly work.

 

[rwahdan2023]

How to make a list of items from the cmd (table fields)

SqlCommand cmd = new SqlCommand("Select * from CalEvents");

 

[jmcilhinney]

I guess you decided not to bother consulting a tutorial, as I suggested. You should be calling ExecuteReader on the command and then reading the results from the data reader it returns. You should read the documentation for that method to learn the details. I'm on a phone so I won't be writing code. You really ought to read a proper tutorial because if you don't know the basics then you're going to have a lot of issues.

 

[rwahdan2023]

here what i tried:

 using (SqlConnection connection = new SqlConnection
     ("Server=(localdb)\\mssqllocaldb;Database=aspnet-ExpenseTracker-63f88f9b-d2f8-444d-a998-9ce2ab1e3f81;Trusted_Connection=True;MultipleActiveResultSets=true"))
 {
     connection.Open();

     SqlCommand command = new SqlCommand("Select * From CalEvents", connection);
     using (SqlDataReader reader = command.ExecuteReader())
     {
         while (reader.Read())
         {
             Console.WriteLine(String.Format("{0}", reader[0]));
         }
     }
 }

now how to get that data into list?

 

[Skydiver]

A list has a Add() method. So instead of writing the results of the query to the console, you can add to the list.

It really feels like you jumped into this problem without having the basics of C# understood first. I would go even further than @jmcilhinney , and suggest that you take time to learn C# first, before you learn how to use a database with C#, otherwise you will have issues down the road. There are very few programming languages that can be learned by "if I copy-and-paste enough code, I will understand it." C# is not one of those languages.

 

[kareninstructor]

A good places to start,

• learn C# Microsoft
• C# W3 schools
• SQL W3 schools

Tips

• Take your time learning C# and SQL basics
• Start with the basics for C#, do not be jumping into the deep end (deep end means you are not familiar with a specific method of coding).
• Work with console projects to learn rather than projects such as windows forms, ASP.NET Core etc.
• Learn asynchronous programming, when to use and when not to use.
• Same with SQL, use SSMS (SQL-Server Management Studio) to learn and to write SQL statements for your projects then dump the valid SQL into your code
• Never use SELECT * FROM xxx, always specify column names even if you want all columns
• Database connection strings are best stored in appsettings.json (search the web, there are plenty of useful resources out there)
• Learn the basics of using various aspects of SqlClient namespace followed by Dapper and EF Core.

Once mastering the basics you may find that what you want is as simple as (each as advantages and disadvantages dependent on the task at hand and business requirements)

public static async Task<IEnumerable<Categories>> GetCategories()
{
    await using SqlConnection cn = new(ConnectionString());
    return await cn.QueryAsync<Categories>(SqlStatements.GetCategories);
}

List<Categories> categories = await _context.Categories.ToListAsync();

public static List<Category> ReadCategories()
{
    var list = new List<Category>();

    using var cn = new SqlConnection() { ConnectionString = ConnectionString };
    var selectStatement = "SELECT CategoryID, CategoryName FROM dbo.Categories;";
    using var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement };
    cn.Open();
    var reader = cmd.ExecuteReader();

    while (reader.Read())
    {
        list.Add(new Category() { Id = reader.GetInt32(0), Name = reader.GetString(1) });
    }

    return list;

}

There are other possible paths but the above are ones to start with.