Presenting source code

Accessing a database in C#

There is a rich library in .NET for handling databasedata. This example use MySql and ODBC. You can install MysQl and an ODBC-driver on your computer as described in: http://dev.mysql.com/tech-resources/articles/dotnet/.

Form

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace db1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            
            // initiate combo boxes, hardcoded in this simple demo
            comboLand.Items.AddRange(
                    new String[4] { "England", "Italia", "Tyskland", "Spania" });
            comboLand.SelectedItem=comboLand.Items[0];
            comboLevel.Items.AddRange(
                    new String[2] { "1", "2" });
            comboLevel.SelectedItem = comboLevel.Items[0];
            comboAr.Items.AddRange(
                    new String[6] { "2000-2001", "2001-2002", "2002-2003", "2003-2004", "2004-2005", "2005-2006" });
            comboAr.SelectedItem = comboAr.Items[0];
        }

        private void buttonVis_Click(object sender, EventArgs e)
        {
            // Control selection, collect data and show result
            String country = comboLand.SelectedItem.ToString();
            String level = comboLevel.SelectedItem.ToString();
            String year = comboAr.SelectedItem.ToString();
            // fix parameters to match database
            year = year.Substring(0, 4);
            if (country.ToLower().CompareTo("england") == 0) country = "en";
            else if (country.ToLower().CompareTo("italia") == 0) country = "it";
            else if (country.ToLower().CompareTo("spania") == 0) country = "es";
            else country = "de"; //Tyskland
            // do it
            if (sender.Equals(buttonVis))
                webBrowser1.DocumentText = DBAccess.getTeams(country, level, year);
            else
                webBrowser1.DocumentText = DBAccess.getGames(country, level, year);
        }

    }
}

DBAccess

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;

namespace db1
{
    class DBAccess
    {
        // using mysql, see http://dev.mysql.com/tech-resources/articles/dotnet/
        static String connectString =
                    @"DRIVER={MySQL ODBC 3.51 Driver};
                    SERVER=localhost;
                    DATABASE=football;
                    UID=root;
                    PASSWORD=oliver;
                    OPTION=3";
        static public String getGames(String country, String level, String year)
        {
            // always return something, result or errormsg
            String retString = "<html><body>";
            // set up a query based on the parameters
            String myQuery =String.Format(
                @"SELECT hteam.name,ateam.name,h_goals,a_goals FROM fmatch,team hteam, team ateam 
                    WHERE fmatch.h_team_id = hteam.team_id AND
                        fmatch.a_team_id = ateam.team_id AND liga_id in
                        (SELECT liga_id FROM liga WHERE 
                            season_id='{0}'AND country_id='{1}' AND level='{2}' 
                        )
                    ;", year,country,level);
            OdbcConnection con=null;
            OdbcDataReader myReader=null;
            try
            {
                con = new OdbcConnection(connectString);
                con.Open();
                // execute a query
                OdbcCommand myCommand = new OdbcCommand(myQuery, con);
                myReader = myCommand.ExecuteReader();
                // and process the results
                while (myReader.Read())
                    retString += "<div>"+
                        myReader.GetString(0) + " - " +
                        myReader.GetString(1) + " : " +
                        Convert.ToString(myReader.GetInt32(2))  + " - " +
                        Convert.ToString(myReader.GetInt32(3))  +
                        "</div>\r\n";
            }
            catch (Exception ex)
            {
                retString += "<p>"+ex.Message+"</p>";
            }
            if (con!=null) 
                con.Close();
            if (myReader!=null) 
                myReader.Close();
            return retString + "</body></html>";
        }

        static public String getTeams(String country, String level, String year)
        {
            // always return something, result or errormsg
            String retString = "<html><body>";
            // set up a query based on the parameters
            String myQuery = String.Format(
                @"SELECT name from team WHERE team_id IN 
                    (SELECT team_id FROM member WHERE liga_id IN 
                        (SELECT liga_id FROM liga WHERE 
                            season_id='{0}'AND country_id='{1}' AND level='{2}' 
                        )
                    );", year, country, level);
            OdbcConnection con = null;
            OdbcDataReader myReader = null;
            try
            {
                con = new OdbcConnection(connectString);
                con.Open();
                // execute a query
                OdbcCommand myCommand = new OdbcCommand(myQuery, con);
                myReader = myCommand.ExecuteReader();
                // and process the results
                while (myReader.Read())
                    retString += "<div>" + myReader.GetString(0) + "</div>\r\n";
            }
            catch (Exception ex)
            {
                retString += "<p>" + ex.Message + "</p>";
            }
            if (con != null)
                con.Close();
            if (myReader != null)
                myReader.Close();
            return retString + "</body></html>";
        }
    }
}
../common/prev.gif ../common/home.gif ../common/next.gif

Valid XHTML
Bygget med WXT : 02.nov.2005