what should we after static class jobs done ?

Question

hello every programmers.

I am new in OOP and I am writing an application that works with db.(insert , update , delete in db).

I have used DAL and BLL and View Layers for it.

DAL Class:

public class Mat_Issue 
{         
	public short ItemsId { get; set; }        
	public short ReportId { get; set; }         
	public int MtoId { get; set; }         
	public decimal Qty { get; set; }         
	public string? Remark { get; set; }         
	public string? SubName { get; set; }         
	public byte? Wid { get; set; } 
}

 public static class Mat_IssueDAL     
{         
public static string Error { get; set; }         
public static List<Mat_Issue> GetAll()         
{             
List<Mat_Issue> list = new();             
try             
	{                 
	string query = "SELECT  *  FROM [dbo].[Mat_Issue]";                 
	using SqlCommand cmd = new SqlCommand(query, Connection.Conn); 
    Connection.Open();                 
	using SqlDataReader rd = cmd.ExecuteReader();                 
	if (rd.HasRows)                 
	{                     
		while (rd.Read()){                        
			Mat_Issue obj = new();                         
			obj.ItemsId = rd.GetFieldValue<short>("ItemsId");                         
			obj.ReportId = rd.GetFieldValue<short>("ReportId");                        
		    obj.MtoId = rd.GetFieldValue<int>("MtoId");                         
			obj.Qty = rd.GetFieldValue<decimal>("Qty");                         
			obj.Remark = rd.GetFieldValue<string>("Remark");                         
			obj.SubName = rd.GetFieldValue<string>("SubName");                         
			obj.Wid = rd.GetFieldValue<byte>("Wid");                         
			list.Add(obj);                    
		 }                 
		}             
	}             
catch (Exception ex)             
{                 
	Error = ex.Message;             
}             
finally{
	Connection.Close();}            
	return list;         
}         
public static int Delete(short ItemsId)         
{             
	int result = 0;             
	try             
	{                 
	SqlCommand cmd = new SqlCommand("DELETE FROM [dbo].[Mat_Issue] WHERE ItemsId=@ItemsId", Connection.Conn);                 
	cmd.Parameters.AddWithValue("@ItemsId", ItemsId);                 
	Connection.Open();                 
	result = cmd.ExecuteNonQuery();                
	Connection.Close();}             

	catch (Exception ex)             
	{Error = ex.Message; }             
	finally {
        Connection.Close();             
	}             
	return result;  }        

 public static int Add(Mat_Issue obj)        
 {             
	int result = 0;            
	 try             
	{                 
		SqlCommand cmd = new SqlCommand("INSERT INTO [dbo].[Mat_Issue] VALUES (@ReportId,@MtoId,@Qty,@Remark,@SubName,@Wid )", Connection.Conn); 
		cmd.Parameters.AddWithValue("@ReportId", obj.ReportId);
		cmd.Parameters.AddWithValue("@MtoId", obj.MtoId); 
		cmd.Parameters.AddWithValue("@Qty", obj.Qty); 
		cmd.Parameters.AddWithNullValue("@Remark", obj.Remark);
		cmd.Parameters.AddWithNullValue("@SubName", obj.SubName);
		cmd.Parameters.AddWithNullValue("@Wid", obj.Wid); 
		Connection.Open();                 
		result = cmd.ExecuteNonQuery();            
	 }             
	catch (SqlException ex)             
	{                 
		Error = ex.Number == 2627 ? "Duplicate Data" : ex.Message;             
	}             
	finally             
	{                 
		Connection.Close();}             
		return result;        
	 }         
	public static int Update(Mat_Issue obj)        
	{             
		int result = 0;             
		try             {                  
		SqlCommand cmd = new SqlCommand("UPDATE Mat_Issue SET Qty = @Qty,Remark = @Remark,SubName = @SubName 	WHERE 	ItemsId=@ItemsId", Connection.Conn);
		cmd.Parameters.AddWithValue("@ItemsId", obj.ItemsId);
		cmd.Parameters.AddWithValue("@ReportId", obj.ReportId);
		cmd.Parameters.AddWithValue("@MtoId", obj.MtoId);
		cmd.Parameters.AddWithValue("@Qty", 	obj.Qty); 
		cmd.Parameters.AddWithNullValue("@Remark", obj.Remark);
		cmd.Parameters.AddWithNullValue("@SubName", obj.SubName);
		Connection.Open();                 
		result = cmd.ExecuteNonQuery();             
	}             
	catch (SqlException ex)            
	 {                 
		Error = ex.Number == 2627 ? "Duplicate Data" : ex.Message;             
	}             
	finally             
	{                 
		Connection.Close();             
	}            
 return result;         
}     }

AND BLL:

public class Mat_IssueBLL     
{
	public static string GetError()         
	{             
		return Mat_IssueDAL.Error;         
	}         
	public static void SetError(string msg = "")         
	{             
		Mat_IssueDAL.Error = msg;         
	}         
	public static List<Mat_Issue> GetAll()         
	{             
		return Mat_IssueDAL.GetAll();         
	}         
	public static bool Delete(short itemsId)         
	{             
		return Mat_IssueDAL.Delete(itemsId) == 1 ? true : false;         
	}                 
	public static bool Add(Mat_Issue obj)         
	{             
		return Mat_IssueDAL.Add(obj) > 0 ? true : false;         
	}         
	public static bool Update(Mat_Issue obj)         
	{             
	return Mat_IssueDAL.Update(obj) > 0 ? true : false;         
	}     
}

View:(form control)

I read from DataGridView:

int _add = 0;
int _notAdd = 0;
Mat_Issue _issue = new();
foreach (DataGridViewRow row in DG_MtoIssue.Rows)
{
	if (row.Cells[0].GetType() == typeof(DataGridViewCheckBoxCell))
	{
		DataGridViewCheckBoxCell? chk = row.Cells[0] as DataGridViewCheckBoxCell;
		if (chk != null)
		{
			if (chk.Value == chk.TrueValue)
			{
				int.TryParse(row.Cells["MtoID"].Value.ToString(), out int mtoId);
				decimal.TryParse(row.Cells["Siv_Qty"].Value.ToString(), out decimal qty);
				byte.TryParse(row.Cells["Wid"].Value.ToString(), out byte wid);
				_issue.ReportId = _report.ReportId;
				_issue.MtoId = mtoId;
				_issue.Qty = qty;
				_issue.Wid = wid;
				_issue.Remark = row.Cells["Remark"].Value.ToString();
				_issue.SubName = row.Cells["SubName"].Value.ToString();
				if (Mat_IssueBLL.Add(_issue))
					_add++;
				else
					_notAdd++;
			}
		}
	}
}

I want every body comment on my code.

1:are they standard?

2:should I dispose my class after doing CRUD operation? for example when user submit 1000 record the for loop call add 1000 times and add open db connection 100 times and close it 1000 times.

3:Can I move my view code into BLL layer to reduce code in view?

Answer 1

Hi @Farshad Valizade , Welcome to Microsoft Q&A,

1.Are they standard?

Your code basically follows the traditional three-tier architecture (DAL, BLL, View). Some aspects could be slightly modified.

In your DAL layer, you catch the exception and store the error information in the Error property. This is an efficient way to handle it, but a better approach is to log the exception information to make troubleshooting easier. Additionally, you may consider throwing custom exceptions to make it easier to handle different types of errors.

At the BLL layer, you can add some parameter validation to ensure that the parameters passed to the method are valid. This can help prevent unnecessary database operations.

2. Database operations

Regarding the issue of handling database connections, you can consider using a connection pool to manage database connections. Connection pooling can help improve performance by reducing the number of times connections are opened and closed. Generally speaking, when performing a large number of database operations, you do not need to explicitly open and close the connection for each operation. The database connection pool automatically manages the opening and closing of connections when needed.

3:Can I move my view code into BLL layer to reduce code in view?

It's usually a good idea to move your view code to the BLL layer. This helps you separate your business logic from the view, making the code easier to maintain and test. You can of course add appropriate methods in the BLL layer to handle the data in the view layer and call these methods in the view layer. This way, you can reuse logic, improve code testability, and reduce duplicate code in the view layer.

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

A better approach would be to use EF Core.

Your model would than be something like the following. Selected property is used in the DataGridView for selections.

In the form you would use a BindingSource and a SortableBindingList<MatIssue>

This way

• Code is reduced from current code as EF Core handles CRUD for you which means if something changes in the DataGridView when done properly when calling SaveChanges EF Core handles updates for instance with no action on your part.
• There is no need to touch the DataGridView to get selected data

In regards to separation of concerns, first get the code working then commit to source control e.g. GitHub repository then work on separating data operations from business logic then front end logic

At this point the class below violates the above and in this case we could create a class that inherits MatIssues (best not to use a underscore) and adds Selected property which means another level of complexity as we now need to use DTO (Data Transfer Object) to push data to the data layer.

Check out the following project which is a start to understand how EF Core can reduce your code complexity, it does not separate to different layers, its a starter to get started.

public class MatIssue : INotifyPropertyChanged
{
    private bool _selected;
    private short _itemsId;
    private short _reportId;
    private int _mtoId;
    private decimal _qty;
    private string _remark;
    private string _subName;
    private byte? _wid;

    [NotMapped]
    public bool Selected
    {
        get => _selected;
        set
        {
            if (value == _selected) return;
            _selected = value;
            OnPropertyChanged();
        }
    }

    [Key]
    public int Id { get; set; }

    public short ItemsId
    {
        get => _itemsId;
        set => _itemsId = value;
    }

    public short ReportId
    {
        get => _reportId;
        set => _reportId = value;
    }

    public int MtoId
    {
        get => _mtoId;
        set => _mtoId = value;
    }

    public decimal Qty
    {
        get => _qty;
        set => _qty = value;
    }

    public string? Remark
    {
        get => _remark;
        set => _remark = value;
    }

    public string? SubName
    {
        get => _subName;
        set => _subName = value;
    }

    public byte? Wid
    {
        get => _wid;
        set => _wid = value;
    }
    public event PropertyChangedEventHandler PropertyChanged;
    protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
    {
        PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
    }
}

Note: In the class above I copied your class and let Jetbrain's Resharper do the property change code with just the click of the mouse.

If you decide not to use EF Core

• In current code, create a connection for each method rather than use one
• Never use AddWithValue, see the following. Always use Add instead.
• Use int rather than short