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