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
| ABOUT PROJECT | PROJECT DETAILS |
|---|---|
| Project Name : | Saving and Retrieving Data in the Database Using C# and MySQL Database |
| Project Platform : | C# |
| Programming Language Used: | C# Programming Language |
| Developer Name : | itsourcecode.com |
| IDE Tool (Recommended): | Visual Studio 2008 |
| Project Type : | Desktop Application |
| Database: | MYSQL DATABASE |
| Upload Date and Time: | July 20, 2016 – 6:10 am |