Business logic layer

[Mitja Bonca]

I am learing programming for a bit more then 6 months, and recently someone told me my concept is not the best one, he proposed me that I rather split my code into business logic layer and data access layer.

And now I would like to ask someone if he can help me out on a simple example of how to do business logic for this example (it`s about library):

Database tables(Parameters):

1. Author (IDAuthor, Name, SureName)
2. Book (IDBook, IDAuthorFK, Title)

I would like to have methods:
1.1. Get author name and surename
1.2. Add new author (insert)
1.3. Update author
1.4. Delete author

2.1 Get books (returns all books)
2.2. Get book by author`s ID (returns a value by author`s ID)
2.3. Add new book
2.4. Update book
2.5. Delete book

[DdoubleD]

It's really a simple concept once you get a grasp on why you want to do this. Although you might have already considered this, I would expand the design to ensure that the presentation layer is also separated.

The idea is to reduce the overall maintenance and increase the scalability of your application. For instance, by putting the data access layer into its own division, you don't have to touch the business layer should you decide to incorporate a new database, or you decide to use another database altogether. Same thing goes for the presentation layer, because if you decide to switch to some other GUI you will find it much easier if you have not embedded your business logic (methods) inside the form code...

For the functions you have listed, most of the methods should go into the business layer even though these methods represent what you are wanting to achieve with your form presentation. However, you can easily call these business objects (classes and methods) from the form code to achieve the desired outcome and maintain separation.

For the data access layer, that is where you want you OleDb, Sql Server, MySql, or other data access specific code to be contained. This data access layer would be called upon by your business layer to manipulate data to and from the database.

That is a very simple, high-level, overview of good object layer-separation.

[DdoubleD]

Here is a crude example of what you wanted, but with only a few of the methods and with the code guts removed. After responding to your new thread, I decided to put together the sample you were looking for. If this answers you original question in this thread, go ahead and mark as solved and we can continue to work out any details in the other thread. I went ahead and used a DataTable object for the table's records to further elaborate on what I was telling you in the other thread too...

class DataAccessLayer
            {
                public static DataTable GetAuthorTable()
                {
                    // dao/db code here to get the records
                    // ...
                    DataTable authorTable = new DataTable();
                    // Fill above with records from dao/db code...

                    // return the records of the Author table
                    return authorTable;
                }
                public static void UpdateAuthor(DataRow authorRecord)
                {
                    // dao/db code here to update the records
                }
                public static void InsertAuthor(DataRow authorRecords)
                {
                    // dao/db code here to insert the record
                }
                public static void DeleteAuthor(int authorId)
                {
                    // dao/db code here to delete the record
                }
            }
            class BusinessLogicLayer
            {
                DataTable authorTable = null;

                public void LoadAuthorTable()
                {
                    // Load records from DAL table...
                    authorTable = DataAccessLayer.GetAuthorTable();
                }

                public string [] GetAuthorNames()
                {
                    if (authorTable == null)
                        LoadAuthorTable();

                    // Load an array of author names to return to our GUI...
                    string [] authorNames = new string[authorTable.Rows.Count];
                    int ndx = 0;
                    foreach (DataRow dr in authorTable.Rows)
                    {
                        authorNames[ndx] = (string)dr["Name"];
                        ndx++;
                    }
                    return authorNames;
                }

                public void DeleteAuthor(string authorName)
                {
                    // Lookup author name and find id to pass to DAL delete method...
                    foreach (DataRow dr in authorTable.Rows)
                    {
                        if (dr["Name"].ToString() == authorName)
                        {
                            DataAccessLayer.DeleteAuthor((int)dr["Id"]);
                                break;
                        }
                    }
                }
            }

[Mitja Bonca]

Thanks for your effort, I really appreciate it. This is what I did (just one example) for the DAL:

public class DataAccessLayer
    {
        public static DataSet GetAuthors(string AuthorsName)
        {
            using (SqlConnection sqlConn = new SqlConnection())
            {
                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    da.SelectCommand cmd = new SqlCommand("GetAllAuthors", sqlConn); //This is a stored procedure
                    da.SelectCommand.CommandText = AuthorsName;
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                                        
                    DataSet empDataSet = new DataSet();
                    try
                    {
                        da.SelectCommand.Connection.Open();
                        da.Fill(empDataSet);
                        da.SelectCommand.Connection.Close();
                    }
                    catch
                    {
                        return null;
                    }
                    finally
                    {
                        // do other things...calling Close() or Dispose() 
                        // for SqlConnection or SqlDataAdapter objects not necessary
                        // as its taken care of in the nested "using" statements
                    }
                    return empDataSet;
                }
            }
        }
    }

Is this example any good?
Anyway, I will work on this code tomorrow, and don`t worry, I`ll mark this as salved.

[DdoubleD]

No worries... Looking at that example, it looks ok to me on the surface except for the line: da.SelectCommand cmd = new SqlCommand("GetAllAuthors", sqlConn); , which I don't think will compile with the "cmd" segment...

[Mitja Bonca]

You are right, it has to be like:

using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    SqlCommand cmd = new SqlCommand("GetAllAuthors", sqlConn); //This is a stored procedure
                    da.SelectCommand.CommandText = AuthorsName;
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;

right?

[Mitja Bonca]

As you said, this is not working, it does not get the stored procedure.
How would you do it:

using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    da.SelectCommand cmd = new SqlCommand("GetAllAuthors", sqlConn); //This is a stored procedure
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;

I was aloso trying with the code bellow, but its nto working either, it can not get the stored procedure:

da.SelectCommand = new SqlCommand();
 da.SelectCommand.Connection = sqlConn;
 da.SelectCommand.CommandText = "GetAllAuthors";
 da.SelectCommand.CommandType = CommandType.StoredProcedure;

GetAllAuthors is the name of the stored procedure:

ALTER PROCEDURE GetAllAuthors
@IDAuthor int
AS
SELECT 
  NameSurename
FROM 
  Authors

[Mitja Bonca]

All set. I had a wrong connection string, that`s why it didn`t work.
Thread salved!