How to Save And Retrieve Data with SQL Server and C#
In this tutorial, I will teach you how to save and retrieve data with SQL Server Management Studio Express 2005 database and C#.net. With this, you can save and retrieve data in the database to the datagridview. I will use here Microsoft Visual Studio 2008 for creating this application.
Let’s begin:
- Create a database and name it “userdb”.
- Open Microsoft Visual Studio 2008 and create new Windows Form Application.
- Do the Form as follows.
- Double click the Form and do the following codes for connecting SQL server to C#.
Note: Put using System.Data.SqlClient; above the namespace to access sql server library.
//initialize all classes SqlConnection conn = new SqlConnection(); SqlCommand cmd = new SqlCommand(); SqlDataAdapter da = new SqlDataAdapter(); DataTable dt = new DataTable(); private void Form1_Load(object sender, EventArgs e) { //connection between sql server to c# con.ConnectionString = "Data Source=.\\SQLEXPRESS;Database=cruddb;trusted_connection=true;"; }
- Go back to the design view and double click the “Save” button. After that, do the following codes for saving data in the database.
private void btnsave_Click(object sender, EventArgs e) { try { //opening connection con.Open(); //create an insert query; string query = "INSERT INTO tbluser (NAME,UNAME,PASS,UROLE) VALUES('" + txtname.Text + "','" + txtuname.Text + "','" + txtpass.Text + "','" + cboRole.Text + "')"; //it holds the data to be executed. cmd.Connection = con; cmd.CommandText = query; //execute the data. int result = cmd.ExecuteNonQuery(); //validate if the result of the executed query. if (result > 0) { MessageBox.Show("Data has been saved in the SQL database"); } else { MessageBox.Show("SQL QUERY ERROR"); } //closing connection con.Close(); } catch (Exception ex)//catch exeption { //displaying error message. MessageBox.Show(ex.Message); } }
- Go back to the design view an double click the “Load” button. After that, do the following codes for retrieving data in the database to the datagridview.
private void btnLoad_Click(object sender, EventArgs e) { try { con.Open(); //create a query for retrieving data in the database. string query = "SELECT ID as 'Id',NAME as 'Name',UNAME as 'Username', UROLE as 'Role' FROM tbluser"; //initialize new Sql commands cmd = new SqlCommand(); //hold the data to be executed. cmd.Connection = con; cmd.CommandText = query; //initialize new Sql data adapter da = new SqlDataAdapter(); //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); //set the data that to be display in the datagridview dataGridView1.DataSource = dt; } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { da.Dispose(); con.Close(); } }
- Finally, press F5 on the keyboard to run your project in the computer.
Output:
For all students who need programmer for your thesis system or anyone who needs a sourcecode in any programming languages. You can contact me @ :
Email – [email protected]
Mobile No. – 09305235027 – tnt