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>"; } } }