Saving and Retrieving Data in the Database Using C# & MySQL Database

Saving and Retrieving Data in the Database Using C# and MySQL Database

In this tutorial, I will teach you how to save and retrieve data in the database using C#.net and MySQL Database. This method will help you add the user information in the MySQL database and display it onto the Datagridview.

Let’s get started:

• Create a database in the MySQL and name it “dbuser“.
• Do the following query for creating a table in the database that you have created.

 CREATE TABLE IF NOT EXISTS `tblusers` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `UNAME` varchar(40) NOT NULL,
 `UUSERNAME` varchar(40) NOT NULL,
 `UPASSWORD` varchar(90) NOT NULL,
 `UTYPE` varchar(30) NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

• After that, open Microsoft Visual Studio 2008 and create a new windows form application for C#. Then, do the form just like this.

• After setting up the form, go to the solution explorer and hit the “view code” to fire the code editor.

• In the code editor, do the following codes for declaring the classes and variables.

 //initialize sql connection
 MySqlConnection con = new MySqlConnection();
 //initialize all classes
 MySqlCommand cmd = new MySqlCommand();
 MySqlDataAdapter da = new MySqlDataAdapter();
 DataTable dt = new DataTable();

 //declaring variables
 string query;
 int res;

• After declaring the classes and variables, do the following codes for establishing the connection between MySQL database and C#.net

 private void Form1_Load(object sender, EventArgs e)
 {
 //set a connection string 
 con.ConnectionString = "server=localhost;user id=root;password=;Database=dbuser;";
 }

• Go back to the design view, double-click the “Save” button and do the following codes in the method.

 try
 {
 con.Open();
 //create a query for retrieving data in the database.
 query = "Insert Into tblusers (`UNAME`, `UUSERNAME`, `UPASSWORD`, `UTYPE`) VALUES ('" + txtName .Text + "','" + txtUsername.Text + "','" + txtPassword .Text + "','" + cboType .Text + "')";
 //initialize new Sql commands
 cmd = new MySqlCommand();
 //hold the data to be executed.
 cmd.Connection = con;
 cmd.CommandText = query;

 res = cmd.ExecuteNonQuery();

 if (res > 0)
 {
 MessageBox.Show("New user has been save in the database.");
 }
 }
 catch (Exception ex)
 {
 //catching error
 MessageBox.Show(ex.Message);
 }
 finally
 {
 da.Dispose();
 con.Close();
 }

• Go back to the design view again, double-click the “Load” button and do the following codes in the method.

 try
 {
 con.Open();
 //create a query for retrieving data in the database.
 query = "SELECT `UNAME` as Name, `UUSERNAME` as Username, `UPASSWORD` as Password, `UTYPE` as Type FROM `tblusers` ";
 //initialize new Sql commands
 cmd = new MySqlCommand();
 //hold the data to be executed.
 cmd.Connection = con;
 cmd.CommandText = query;
 //initialize new Sql data adapter
 da = new MySqlDataAdapter();
 //fetching query in the database.
 da.SelectCommand = cmd;
 //initialize new datatable
 dt = new DataTable();
 //refreshes the rows in specified range in the datasource. 
 da.Fill(dt);

 dataGridView1.DataSource = dt;


 }
 catch (Exception ex)
 {
 //catching error
 MessageBox.Show(ex.Message);
 }
 finally
 {
 da.Dispose();
 con.Close();
 }

Output:

For all students who need a programmer for your thesis system or anyone who needs a source code in any programming languages. You can contact me @ :

Email – [email protected]
Mobile No. – 09305235027 – TNT

Download Source Code