Answer the following questions based on the tutorial and program listed below. 1

Question

Answer the following questions based on the tutorial and program listed below.

1. Read the program and locate the codes which make connection to the database. List the codes and comment each line.

2. What are the codes being used to make query to a database?

3. What is the difference between MySqlDataReader and MySqlDataAdapter? (Note: Reference to Documentations to MySql.Net Connect on www.mysql.com to find your answer.)

Tutorial: Database Programming using MySql .Net Connector and C#

1. Make sure that you have the LASTEST Microsoft Visual Studio Professional Edition or Visual C# Express installed on your computer. If not, you can download the software at www.dreamspark.com. DreamSpark is a Microsoft website which gives students Microsoft Professional tools at no charge.

2. (If necessary) Download ADO.NET driver for MySQL (Connector/Net) at the following website: http://www.mysql.com/downloads/connector/net/. It contains C# API/database engine for MySQL. Download MSI Installer and install it on your computer.

3. Open Visual Studio, create a C# Windows Forms Application by clicking menu FileNew…Project. You can give a name to your project and click OK

4. An empty form is generated in the new project. You can add all kinds of windows controls to the form by double-clicking or dragging controls from the toolbox to the form. Add controls to the form and make it like the one shown in the next page. Your controls should include TextBox, DropDownList, Labels, ListBoxes, DataGridView, and Buttons. 2 In the properties window, name your TextBoxes as txtServer, txtUser, txtPort, and txtPassword. Name your ListBoxes as listDatabases and listTables. Type “*” in the PasswordChar property of the control txtPassword. It will hide the password you input into “*”. Label TextBox ListBox Button DataGridView 3 5. Next step is to add a reference to MySQL .Net connector. In the Solution Explorer, right click on the References and select Add References… In the Add Reference dialog, select MySql.Data. If you see there are two versions of MySql.Data in the list, choose the Runtime v2.x.xxx, which is for Visual Studio 2008. Version 4.x is for integration with Visual Studio 2010. Now it is time to add codes in the program. Add the following two lines of codes in the Form1.cs (Code) right before the declaration of the namespace: using MySql.Data; using MySql.Data.MySqlClient; namespace WindowsApplication1 { 4 6. You can double click necessary controls in the Form (Design Window) to automatically activate an event function and then, add codes in the function. a. Double-click button “Connect”, add the following codes in the button-click function. private void button1_Click(object sender, EventArgs e) { MySqlConnection conn = new MySqlConnection(); string conString = "server="+txtServer.Text + ";user=" + txtUser.Text + ";password=" + txtPassword.Text + ";port=" + txtPort.Text; conn.ConnectionString = conString; try { conn.Open(); MySqlCommand cmd = new MySqlCommand("show databases", conn); MySqlDataReader rdr = cmd.ExecuteReader(); listDatabases.Items.Clear(); while (rdr.Read()) { listDatabases.Items.Add(rdr[0]); } listDatabases.SelectedIndex = 0; rdr.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } conn.Close(); } b. Double-click Listbox, listDatabases, and add the following codes in the index-selection change function. private void listDatabases_SelectedIndexChanged(object sender, EventArgs e) { if (listDatabases.SelectedItems.Count > 0) { MySqlConnection conn = new MySqlConnection(); string conString = "server=" + txtServer.Text + ";user=" + txtUser.Text + ";password=" + txtPassword.Text + ";database=" + listDatabases.Items[listDatabases.SelectedIndex].ToString() + ";port=" + txtPort.Text; conn.ConnectionString = conString; try

5 { conn.Open(); MySqlCommand cmd = new MySqlCommand("show tables", conn); MySqlDataReader rdr = cmd.ExecuteReader(); listTables.Items.Clear(); while (rdr.Read()) { listTables.Items.Add(rdr[0]); } listTables.SelectedIndex = 0; rdr.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } conn.Close(); } } c. Double click Listbox, listTables, and add the following codes in the new function: private void listTables_SelectedIndexChanged(object sender, EventArgs e) { if (listTables.SelectedItems.Count > 0) { MySqlConnection conn = new MySqlConnection(); string conString = "server=" + txtServer.Text + ";user=" + txtUser.Text + ";password=" + txtPassword.Text + ";database=" + listDatabases.Items[listDatabases.SelectedIndex].ToString() + ";port=" + txtPort.Text; conn.ConnectionString = conString; try { MySqlCommand cmd = new MySqlCommand(); cmd.CommandText = "Select * from " + listTables.Items[listTables.SelectedIndex].ToString(); cmd.Connection = conn; MySqlDataAdapter adapter = new MySqlDataAdapter(cmd); DataSet dbTableContent = new DataSet(); adapter.SelectCommand = cmd; adapter.Fill(dbTableContent, "tableContent"); dataGridTableView.DataSource = dbTableContent; dataGridTableView.DataMember = "tableContent"; } catch (Exception ex)

6 { MessageBox.Show(ex.ToString()); } } } After completing coding, you can start to run the program. Press F5 to start debugging.

Explanation / Answer

1. Read the program and locate the codes which make connection to the database. List the codes and comment each line.

These lines are involved in making connections to the database. All such lines in parts a b and c are there, rest all must be commented.

MySqlConnection conn = new MySqlConnection();
string conString = "server="+txtServer.Text + ";user=" + txtUser.Text + ";password=" + txtPassword.Text + ";port=" + txtPort.Text;
conn.ConnectionString = conString;
conn.Open();
MySqlCommand cmd = new MySqlCommand("show databases", conn);

2. What are the codes being used to make query to a database?
These lines are involved in querying.

cmd.CommandText = "Select * from " + listTables.Items[listTables.SelectedIndex].ToString();
cmd.Connection = conn; MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

3. What is the difference between MySqlDataReader and MySqlDataAdapter?

1. The Reader holds the connection open until you are finished whereas the adapter lets you close the connection as soon it's done loading data.
2. Reader holds only a single objec/dataset whereas Adapter can hold an entire ResultSet.
3. Reader is not useful for updating back to the database, whereas Adapter is useful for that purpose.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.