How to Save And Retrieve Data with SQL Server and C#

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

Download Sourcecode