how to make a list of static class in c#?

Question

hi I have a static class with static property . I want to query to db and fill a List of this static class in this list.

I don't know how to do that. this is my code:

public class App_Users     
{         
public static short UserId { get; set; }         
public static string UserName { get; set; }         
public static string Password { get; set; }         
public static string? FullName { get; set; }         
public static string? Post { get; set; }         
public static byte? DisciplineId { get; set; }         
public static string? Mobile { get; set; }         
public static bool? Status { get; set; }         
public static string? IP { get; set; }     
}

and query:

public static List<App_Users> GetAll()         
{            
 List<App_Users> list = new();            
 try             
{                 
  string query = "SELECT  *  FROM [dbo].[App_Users]";  
using SqlCommand cmd = new SqlCommand(query, Connection.Conn);                 Connection.Open();                
 using SqlDataReader rd = cmd.ExecuteReader();                 
if (rd.HasRows)                 
{                     
while (rd.Read())                     
{                         
    App_Users.UserId = rd.GetFieldValue<short>("UserId");
	App_Users.UserName = rd.GetFieldValue<string>("Username");
	App_Users.Password = rd.GetFieldValue<string>("Password");
	App_Users.FullName = rd.GetFieldValue<string>("FullName"); 
	App_Users.Post = rd.GetFieldValue<string>("Post");
	App_Users.DisciplineId = rd.GetFieldValue<byte>("DisciplineId");
    App_Users.Mobile = rd.GetFieldValue<string>("Mobile");
    App_Users.Status = rd.GetFieldValue<bool>("Status");
    list.Add(App_Users.UsersList);                     
	}                 
  }             
}             
catch (Exception ex)             
{                 
	Error = ex.Message;             
}             
finally             
{                 
	Connection.Close();             
}             
return list;         
}

Answer 1

Try using Dapper, the following example, no need to open the connection, Dapper opens and closes as needed

public static List<App_Users> GetAll()
{
    using SqlConnection cn = new("TODO");
    return cn.Query<App_Users>("SELECT * FROM [dbo].[App_Users]";).ToList();
}

Edit

If stuck on not using Dapper here is an example and note there is zero reason for static properties.

Notes, using GetInt32, GetString etc has better performance than GetFieldValue which has to resolve the column name while the former does not.

public class Customers
{
	public int Id { get; set; }
	public string FirstName { get; set; }
	public string LastName { get; set; }
}

internal class DataOperations
{
	public static async Task<List<Customers>> CustomersList()
	{
		List<Customers> list = new List<Customers>();

		var statement = "SELECT Id,FirstName,LastName FROM dbo.Customers;";
		await using var cn = new SqlConnection("TODO");
		await using var cmd = new SqlCommand { Connection = cn, CommandText = statement };

		await cn.OpenAsync();
		await using var reader = await cmd.ExecuteReaderAsync();
		while (reader.Read())
		{
			list.Add(new Customers() { Id = reader.GetInt32(0), FirstName = reader.GetString(1), LastName = reader.GetString(2) });
		}
		return list;
	}
}

Answer 2

First the List<> can only be a collection of class instances. You need to create an instance to add to the class. As you don’t set any instance properties you can:

list.Add(new App_Users());                  

Now there will a class instance per database result row. But you will find they all have the same column values (last ones set). This is because by definition a there is only one static value.

so to do what you want you can not use static properties.