This question was asked on a forum and I took some time to construct a reasonably lengthy reply so I’m copying it to my blog for a bit of permanence.
I suspect that many of my regular readers will be dismayed at the lack of proper architecture (e.g. layering) but we all had to start somewhere and I suspect that your first programs were not properly layered or structured either. I know mine certainly weren’t. My aim with this was to show how a simple goal can be achieved, what basic things are needed and how to fit it all together by doing the simplest thing that would work (a mantra from the agile world).
Here’s the post (slightly edited to put back some of the original context):
Okay - Let's step back and show the whole thing from text box to database. NOTE: that this example shows everything in one place. This is generally considered poor practice, but as you are only just starting I'll not burden you with the principles of layered architecture and the single responsibility principle and so on. (Just be aware they exist and one day you'll have to learn about them)
So, let's say you have a form with two text boxes, one for a name, and one for an age. Lets call them NameTB and AgeTB. The user can enter information in these text boxes and press a button that adds them to the database.
First, we need to get the data from the text boxes into a form we can use.
string name = NameTB.Text;
int age = Convert.ToInt32(AgeTB.Text);
Since text boxes only deal with strings we have to convert the string into a number (an Int32 - a 32bit integer) for the age value.
Now, we need to set up the database connection and command in order to insert this. I'll assume you already have a connections string to your database, I've called it myConnectionString for this example.
SqlConnection myConnection = new SqlConnection(myConnectionString);
SqlCommand myCommand = new SqlCommand("INSERT Person(NameField, AgeField) "+
VALUES (@nameParam, @ageParam)", myConnection);
I've now set up the SQL Command with an insert statement. I've assumed there is a table called
Person and it has two columns called
NameField and
AgeField. I'm also going to insert the values via parameters, which I've indicated with
@nameParam and
@ageParam. SQL Server requires that all parameter names start with an @ symbol. Other databases may vary.
myCommand.Parameters.AddWithValue("@nameParam", name);
myCommand.Parameters.AddWithValue("@ageParam", age);
We've now added the parameters into the SQL command and we've given each parameter the value we got earlier. Finally:
myConnection.Open();
myComment.ExecuteNonQuery();
myConnection.Close();
This opens the connection, runs the INSERT statement and closes the connection again. We're using ExecuteNonQuery because we don't expect any results back from SQL Server. If we were expecting data back (e.g. because we were using a SELECT statement) we could use ExecuteReader (for many rows/columns) or ExecuteScalar (for a single value).
This is a very basic example. I’ve not shown any error checking or exception handling. There is also the implicit assumption that all this code resides inside a button click event, which is considered poor practice for anything but a small or throw away application.
O/RMs help us bridge the gap between the database and the code base we love to write. On the night we will be looking at two O/RMs, NHibernate the most mature O/RM in the Alt.Net space and Microsoft's recently released Enitiy Framework, the young pretender to the O/RM thrown.
ScottLogic, a leading financial software and consultancy company based in Edinburgh, have been kind enough to offer the use of their premises for an evening of O/RM knowlege sharing and dicussion. The event will take place on 2nd of July at 7pm, 17 Gayfield Square Edinburgh EH1 3NX. All are welcome!
The agenda
19:00 – 19:30 Paul Cowan - An Introuction to NHibernate
19:30 – 20:00 Chris Canal - FluentNHibernate in 15 minutes
20:00 - 20:10 Break
20:10 – 20:40 Colin Gemmell - NHibernate vs Entity Framework - which is best?
After the meeting we will retire for a beer and some heated discussion. If you are planning to attend, please let us know by registering at the Scot Alt.Net Edinburgh EventBrite page (http://altdotnetedinburgh.eventbrite.com/).
About the speakers
Paul Cowan has recently started his own business Cutting-Edge Solutions. He is a keen advocate of iterative development, test driven development, continuous integration and modern techniques. Paul is a regular committer to the horn open source project. He recently gave a presentation on horn at the Dsl DevCon at Microsoft in Seattle. You can follow his blog here.
Chris Canal has worked at a Web Developer for the past 7 years. Starting with procedural languages like ASP and PHP, he quickly moved onto the .NET Platform when first released. A great believer is continual–improvement, Chris is constantly looking for new technologies, tools and methodologies that will help in creating robust and maintainable software applications. Having felt the pain of using Microsoft "Demoware", Chris has become an active member of the Scottish Alt.Net Community to share his findings and ideas with like-minded developers.
Colin Gemmel is a Web/Application Developer working in the Medical Faculty of Glasgow University for the past 3 years. An avid follower of agile principles and practices he is always happy to pass on his views of software development to anyone that will listen. Colin is also a regular participant of the Scottish Alt.Net Community
http://scotalt.net/blog/2009/06/11/an-evening-of-orm/
http://www.scottlogic.co.uk/contact_info
http://altdotnetedinburgh.eventbrite.com/
http://groups.google.com/group/scotaltnet