0

I have been programming in C# for a while but I was never good at design patterns or applying OOP, which I'm trying to change right now.

The problem I'm having is that I have all my SQL statements in a class called "SQLConn" but now I wan't to split it up into child classes instead.

In my application there is a database and two of these tables "Rules" and "Log".

My idea is to create a child class for each table (that I'm using in my application) and move all the methods to that class and open a new connection.

So, if I have a child class called SQLLog and SQLRules which are child classes of SQLConn is it a good idea that when the user inserts a new rule into the application it creates a new instance of SQLConn.SQLRules and runs "InsertRule()" and it also creates a new instance of SQLConn.SQLLog and runs "InsertLog("User inserted Rule X")?

According to this post Is it better to execute many sql commands with one connection, or reconnect everytime? connection pooling will be activated so there should be no big performance difference.

So, to summarize the question: Is it good to design the SQL classes like: * Parent class: SQLConn - Contains the conncetion information * Child class: SQLLog - Contains the "InsertLog()" method * Child class: SQLRules - Contains the "InsertRule()" method

And if I need both SQLRules and SQLLog in a part of my application should I create two instances, one of SQLLog and one of SQLRules, and call it's respectively methods? Is this a good design?

Best regards, Tomas

Community
  • 1
  • 1
Westerlund.io
  • 2,743
  • 5
  • 30
  • 37

1 Answers1

1

If you have two seperate classes with their own connections, the connections should pool if you are using an appropriate version of SQL Server with the same connection string.

As far as design goes, it would be good practice to seperate your database and application code. That is, have your SQL code in your DB as stored procedures (unless it's dynamic SQL or LINQ-SQL or something like that) in your SQL database. There is some contention surrounding the use of stored procedures, but I think it is appropriate in your case.

You can then have methods that call these sprocs in your C# code without actually having to have SQL code as strings in your code. This is generally easier to maintain, as you're able to work on the SQL code in SSMS, rather than Visual Studio.

Failing that, at least put your SQL code in its own script files within the project.

This doesn't sound like a bad idea, splitting functionality up according to the Law Of Demeter is a good way to make a project more testable and maintainable.

Codeman
  • 12,157
  • 10
  • 53
  • 91
  • Thanks for the tip about Law of Demeter. There is a very good Wikipedia link about it. I'm interested in learning more about OOP and how to design and program bigger and bigger systems. The problem I had before (and still do) was that all of my programs seemed to end up very few classes so one class (usually in "FormX.cs) had all the logic but I'm getting away from that now, thankfully, but still seems to end up with a lot of design flaws. I'm reading Head First Object-Oriented Analysis and Design and will read Head First Design Pattern and hopefully that will help me with my problems. Thanks! – Westerlund.io Sep 06 '13 at 01:50