5
\$\begingroup\$

My question is all about class design.

I have this app that is connecting to a SQL server, retrieving some tables, the user works on the data, and then it updates the database.

I should say that all that I know I learned on my own (youtube and reading) but there's still a lot of stuff that makes me confused though.

So for what I have seen, the best approach for my project is to create a class that encapsulates (nice word by the way) this model I just described.

I already have a version working very fine, but the code is all merged into the interface layer. (By working very fine I mean that I can retrieve the data from the database, work on the tables, and then update the database.)

But i fell like I'm achieving this in a very rudimentary way. Now I'm trying to make it more harmonious and well designed. Trying to master my code a bit more I guess.

So here is what I am achieving now based on the "Class" theory and OOP.

Could you help me learn whether this this is good, and if I'm designing my class in the proper way?

// Main class that i'm trying to model in such way to be a conceptual model of my real SQL Database (FootballProject Database).
public class fp_Database
{
    // Virtual DB.
    public static DataSet DS = new DataSet();    

    // Construtor.
    public fp_Database()
    {
        DS.Tables.Add("Seassons");
        DS.Tables.Add("Countries");
        DS.Tables.Add("Tournaments");
        DS.Tables.Add("TeamsAllowed");
        DS.Tables.Add("Matches");

        Tables = new fp_Tables();
    }


    // Public Object fp_Tables containing the fp_Database tables.
    public fp_Tables Tables;

    // fp_Tables Class definition.
    public class fp_Tables
    {

        // Instantiating all the Databases tables.
        public tableMatches Matches = new tableMatches();
        public tableSeassons Seassons = new tableSeassons();
        public tableCountries Countries = new tableCountries();
        public tableTournaments Tournaments = new tableTournaments();


        // Definition of all the fp_Tables Classes.
        public class tableSeassons
        {
            public DataTable Get_Table
            {
                get
                {
                    return DS.Tables["Seassons"];
                }
            }

            public tableSeassons()
            {
                DatabaseInterface DBInterface = new DatabaseInterface();
                SqlCommand CMD = new SqlCommand("SELECT * FROM fp.Seassons");

                DBInterface.GetTable(DS.Tables["Seassons"], CMD);
            }
        }
        public class tableCountries
        {
            public DataTable Get_Table
            {
                get
                {
                    return DS.Tables["Countries"];
                }
            }

            public tableCountries()
            {
                DatabaseInterface DBInterface = new DatabaseInterface();
                SqlCommand CMD = new SqlCommand("SELECT * FROM fp.Countries");

                DBInterface.GetTable(DS.Tables["Countries"], CMD);
            }
        }
        public class tableTournaments
        {
            public DataTable Get_Table
            {
                get
                {
                    return DS.Tables["Tournaments"];
                }
            }

            public void Set_Filters(int Seasson, int Country)
            {
                DS.Tables["Tournaments"].DefaultView.RowFilter = "Seasson = " + Seasson + " AND Country = " + Country;
            }

            public tableTournaments()
            {
                DatabaseInterface DBInterface = new DatabaseInterface();
                SqlCommand CMD = new SqlCommand("SELECT * FROM fp.Tournaments ORDER BY Tournament");

                DBInterface.GetTable(DS.Tables["Tournaments"], CMD);
            }
        }
        public class tableMatches
        {
            public DataTable Get_Table
            {
                get
                {
                    return DS.Tables["Matches"];
                }
            }

            public void Load_Table_By_Tournament(int TournamentID)
            {
                DatabaseInterface DBInterface = new DatabaseInterface();
                SqlCommand CMD = new SqlCommand("SELECT * FROM fp.Matches WHERE Tournament = " + TournamentID);

                DS.Tables["Matches"].Clear();
                DBInterface.GetTable(DS.Tables["Matches"], CMD);
            }
        }
    }
}
// DATA ACCESS LAYER. (SELECT - INSERT - UPDATE - DELETE)
class DatabaseInterface
{
    // Latter i will learn how to save the connection string into app.config file and read from int too.    
    private SqlConnection Conn = new SqlConnection("Data Source=DESKTOP-8MGVKID\\MSSQLSERVER2016;Initial Catalog=FootballProject;Integrated Security=True");


    // Select Method.
    public void GetTable (DataTable Table, SqlCommand SQLCommand)
    {
        SqlDataAdapter DA = new SqlDataAdapter(SQLCommand.CommandText, Conn);

        Conn.Open();
        DA.Fill(Table);
        Conn.Close();
    }

    // Here will be Insert Method

    // Here will be Update Method.

    // Here will be Delete Method
}
\$\endgroup\$

1 Answer 1

4
\$\begingroup\$

Class names should be PascalCase and should not contain non-alphanumeric characters, so fp_Database breaks those rules.

tableMatches etc. also break the PascalCase rule.

Methods shouldn't contain non-alphanumeric characters either; Get_Table breaks this rule.

Parameters should be camelCase, so for instance Seasson and Country as used in Set_Filters(int Seasson, int Country) violate this rule.

Local variables like DBInterface as used below should be camelCase:

        public tableCountries()
        {
            DatabaseInterface DBInterface = new DatabaseInterface();
            SqlCommand CMD = new SqlCommand("SELECT * FROM fp.Countries");

            DBInterface.GetTable(DS.Tables["Countries"], CMD);
        }

You have a spelling error: "Seassons". However, what's worse is that you repeatedly use this string instead of setting it as a const and using that. Same for other strings.


But the above are merely minor flaws. That they're omni-present is however a warning sign (as well as the obviously copy-pasted parts), and I'm afraid that I don't see any value in this code.

I fear you're relying on very outdated documentation; instead look into ORMs like Entity Framework and how those are used.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.