How to organize my code (methods I create for various uses on my website)

Question

I wrote some code to connect the application to it's database, then I created some code to use the connection code and retrieve, update or add some values to the database, Also I might have some code to deal with other stuff than to deal with the database

The code is a little complicated, maybe it's simple but it's not short, for example to write a good piece of code to just retrieve a single value so I could set the controller with this value I used this :

SqlConnection sqlconnection= new SqlConnection(ConfigurationManager.ConnectionStrings["DefConnectionString"].ConnectionString);
        SqlCommand sqlcommand = new SqlCommand("SELECT name FROM message  WHERE id = 3", sqlconnection);

        try
        {
            sqlconnection.Open();
            lbl_name.Text = (string)sqlcommand.ExecuteScalar();
            Status.Text = "Done";
        }

        catch (Exception ex)
        {
            Status.Text = ex.Message;
        }

        finally
        {
            sqlconnection.Close();
        }

I might even add some code to store some info in the database about any exception is thrown, I think the code is pretty basic, yet it's not small, given that it's the smallest piece I'll need, some huge code is written for adding or editing new items, and also consider that it's not the only code I'll write in the page, the page has more needs :D!

I've provided a detailed case I hope!...So what do you think will be the best way to make my page fast and easy to read and have an organized code I've started placing every code in an appropriate method, but some methods are common so I create them again in every page that needs them like a ConnectionToDataBase Method, and mean while I think I just organized the page so I could mainly look at Page_Load and see what are the called methods and then scroll down to read the definition but It's still a big page and note the best practice I think

Sorry for all the big question, I just provide details so I could get a reasonable answer for my question, I hope everyone benefits from it as most questions are pretty basic, newbies like me needs some detailed cases and answers to get a better start...Thanks for your time!

EDIT: I know I'm new, So any comments on my code are more than welcome!

Answer 1

Some quick observations:

1. Do not use inline SQL, since it's a bear to maintain (go with Stored Procedures or if you're doing basic CRUD operations, consider an ORM)

Also (Per the comment below, which is very relevant) if you MUST use inline SQL, in cases where you end up concatonating your values into a command text, you can be leaving yourself open to SQL injection attacks (and in general you should avoid inline SQL in any case).

1. Do not globally capture and swallow exceptions - only capture exceptions where you are actually handling them - otherwise, bubble them back up.

2. You will want to either abstract your data access code into a data access layer independent of your business object (I'm a fan of a repository pattern), or (preferrably) consider an ORM (I strongly recommend NHibernate, possibly Entity Framework 4 which has much nicer POCO support).

Generally how I structure this kind of data is have a business object have a reference to a repository object - this is defined as an interface, so I can swap out my test and real implementations via dependency injection.

The data repository then does the relevant heavy lifting (generally via an ORM, sometimes in ADO.Net for some of our legacy code) - so if I needed a list of customers, I would just call a 'GetCustomers' method on the repository. The business layer has no knowledge of data access, it just knows that when it calls a method it gets back a nice strongly typed list.

[Answering the request RE organization]

When I use this pattern for an MVC website, I generally have my controller invoke a business logic class. It's purpose in life is to encapsulate the non-data specific calculations, etc. that it retrieves from my data access layer.

The business logic class will, in turn, reference one or more repositories that encapsulate data transport between my data, and my business classes (things like saving/retrieving data, etc.).

The repository is responsible for serving up the data (again, ADO.Net, NHibernate, EF, whatever works for you). It should have no business logic, just enough to take the data from my database, shape it appropriately, and return basic non-data specific objects back to the business layer.

I have some samples on this organization on my blog (available in my profile) that while they are NHibernate specific, show a pretty basic useage of a repository pattern.

Hope that helps out :)