What should be the principles of multilayer software design?

[farshad valizade 2023]

hi every body.I am coding with c# .net core and my first app is a multilayer database project. this app get datat from user input control and add/del/edit on it. I have made 3 project in my solution: 1 : Datalayer 2:Business Layer 3:View in my forms I should Join three or four or more table with each other and show in a datagridview to the user.so I have coded all sql query in datalayer with ado.net bcoz it is easy to join table instead of EF. I put my code here and you please see it and notice me about each layers.(I think in BLL layer I just didn't do anything special) and In the UI I put all of my code that I think it is not very interesting. so see it : DAL :

    public static DataTable GetAll()
    {
        using DataTable dt = new();
        try
        {
            string query = "
							select 
								l.LineID,
								s.SheetID,
								z.ZoneID,
								j.JointTypeID,
								j.JointClassID ,
								j.PipeClassID ,
								j.HoldID,
								l.\[LineNo\] , 
								s.Sheet,
								s.Rev , 
								s.RevIndex , 
								z.Unit,
								z.Area,
								s.SheetStatus as LineStatus,
								j.JointID,
								j.JointNo , 
								j.JointIndex , 
								j.JointStatus , 
								cast(CAST(j.JointSize as decimal(18,2)) as float) as JointSize,
								j.\[Location\],
								j.Spool,
								jt.JointType,
								jc.JointClassName as Class,
								pc.PipeClass,
								m.MaterialName as Mat, 
								j.Thk 
							from 
							Joint  as j
							inner join Sheet as s on s.SheetID = j.SheetID and s.VoidDate is null
							inner join  Line as l on l.LineID=s.LineID
							left join Zone as z on z.ZoneID = j.ZoneID
							left join JointType as jt on jt.JointTypeID = j.JointTypeID    
							left join JointClass as jc on jc.JointClassID = j.JointClassID  
							left join PipingClass as pc on pc.PipeClassID = j.PipeClassID    
							left join Material as m on m.MaterialID = pc.MaterialID
							left join MTO as m1 on m1.SheetID = j.SheetID AND m1.PTNo = Mat1
							left join MTO as m2 on m2.SheetID = j.SheetID AND m2.PTNo = Mat2
							";
            using SqlCommand cmd = new SqlCommand(query, Connection.Conn);
            Connection.Open();
            using SqlDataReader rd = cmd.ExecuteReader();
            dt.Load(rd);

        }
        catch (SqlException ex)
        {
            Error = ex.Message;
        }

[Richard Deeming]

There are quite a few problems with the code you've shown. Firstly, your code won't compile. You can't have line-breaks in a standard string. You would need to use either a verbatim string[^] or a raw string[^] for your query. You should make the query a local const so that you're not tempted to try to inject parameter values into it incorrectly and introduce a SQL Injection[^] vulnerability into your code. You seem to be using a shared database connection instance. That's a terrible idea - either your code must be restricted to only service one request at a time, or you'll end up with cross-contamination when multiple users try to access your application, since the connection is not thread-safe. Instead, create the connection when you need it, and wrap it in a using block to ensure it's disposed of properly. Similarly, the SqlCommand and SqlDataReader instances need to be wrapped in using blocks. Your code currently swallows any exceptions, and returns an empty DataTable instead. The caller is expected to examine the Error property to determine whether an error occurred, and retrieve a tiny portion of the details of that error - assuming the property hasn't been overwritten by a call from a different user in the meantime. Instead, you should let the exception propagate to the caller naturally. If you need to add more details, then throw a different exception, making sure to include the original exception as the InnerException.

public static DataTable GetAll()
{
const string Query = """
SELECT
...
FROM
...
""";

try
{
    using (var connection = Connection.CreateAndOpenConnection()) // Make this method open the c