User CRUD in C# and SQL Server

User CRUD in C# and SQL Server

This time, I will teach you how to perform Crud using C# and SQL server 2005. CRUD is very important for a system because these are the basic methods in making a system. That’s why, this tutorial will guide you on how to perform a User CRUD with ease.

So let’s get started:

• Create a database and name it “myFirstCruddb”.
• Open Microsoft Visual Studio and create new Windows Form Application. After that, do the Form as follows.

• Go to the Solution Explorer, click the “View Code”  to display the code editor.

• Declare all the classes and variables that are needed.

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();

 //declaring variables
 string query;
 int result;
 int userid;

• Go back to the design view, double click the Form and do the following codes for establishing a connection between SQL server and C#.net.

private void Form1_Load(object sender, EventArgs e)
 {
 //set a connection between SQL server and Visual C#
 conn.ConnectionString = "Data Source=.\\SQLEXPRESS;database=myFirstCruddb;trusted_connection=true;";
 }

• Go back to the design view, double click the “Create” button and do the following codes for saving data in the database.

 //Creating Method
 private void btnsave_Click(object sender, EventArgs e)
 {
 try
 {
 //opening connection
 conn.Open();
 //create an insert query;
 query = "INSERT INTO tblcrud (NAME,UNAME,PASS,UTYPE) VALUES('" + txtname.Text + "','" + txtuname.Text + "','" + txtpass.Text + "','" + cborole.Text + "')";
 //it holds the data to be executed.
 cmd.Connection = conn;
 cmd.CommandText = query;
 //execute the data.
 result = cmd.ExecuteNonQuery();
 //validate the result of the executed query.
 if (result > 0)
 {
 
 MessageBox.Show("Data has been saved in the SQL database");
 //calling a method
 btnreload_Click(sender, e);

 }
 else
 {
 MessageBox.Show("SQL QUERY ERROR");
 }
 //closing connection
 conn.Close();
 
 }
 catch(Exception ex)//catch exeption
 {
 //displaying error message.
 MessageBox.Show(ex.Message);
 }
 
 }

• Go back to the design view, double click the “Reload” button and do the following codes for retrieving data in the database.

 //load method
 private void btnreload_Click(object sender, EventArgs e)
 {
 try
 {
 //create a query for retrieving data in the database.
 query = "SELECT ID as 'Id',NAME as 'Name',UNAME as 'Username', UTYPE as 'Role',PASS FROM tblcrud";
 //initialize new Sql commands
 cmd = new SqlCommand();
 //hold the data to be executed.
 cmd.Connection = conn;
 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
 dtgList.DataSource = dt;
 //hide the password column in the datagridview 
 dtgList.Columns["PASS"].Visible = false;
 }
 catch(Exception ex)
 {
 MessageBox.Show(ex.Message);
 }
 finally
 {
 da.Dispose();
 
 }
 userid = 0;

 }

• Do the following codes for passing the data in the DataGridView to the TextBoxes.

//diplay the specific data from the datagridview to the textbox 
 private void dtgList_DoubleClick(object sender, EventArgs e)
 {
 userid = Int32.Parse(dtgList.CurrentRow.Cells["Id"].FormattedValue.ToString());
 txtname.Text = dtgList.CurrentRow.Cells["Name"].FormattedValue.ToString();
 txtuname.Text = dtgList.CurrentRow.Cells["Username"].FormattedValue.ToString();
 txtpass.Text = dtgList.CurrentRow.Cells["PASS"].FormattedValue.ToString();
 cborole.Text = dtgList.CurrentRow.Cells["Role"].FormattedValue.ToString(); 
 }

• Go back to the design view, double click the “Update” button and do the following codes for updating data in the database.

 //update method
 private void btnupdate_Click(object sender, EventArgs e)
 {
 try
 {
 conn.Open();
 query = "UPDATE tblcrud SET NAME='" + txtname.Text + "',UNAME='" + txtuname.Text + "',PASS='" + txtpass.Text + "' ,UTYPE='" + cborole.Text + "' WHERE ID = " + userid;
 cmd.Connection = conn;
 cmd.CommandText = query;
 result = cmd.ExecuteNonQuery();

 if (result > 0)
 {
 MessageBox.Show("Data has been updated in the SQL database");
 //calling a method
 btnreload_Click(sender, e);
 }
 else
 {
 MessageBox.Show("SQL QUERY ERROR");
 }
 }
 catch (Exception ex)
 {
 MessageBox.Show(ex.Message);
 }
 finally
 {
 conn.Close();
 }
 }

• Go back to the design view, double click the “Delete” button and do the following codes for deleting data in the database.

 //delete method
 private void btndel_Click(object sender, EventArgs e)
 {
 try
 {

 conn.Open();
 //delete query
 query = "DELETE FROM tblcrud WHERE ID = " + dtgList.CurrentRow.Cells["ID"].FormattedValue;
 //it holds the data to be executed.
 cmd.Connection = conn;
 cmd.CommandText = query;
 //execute the data.
 result = cmd.ExecuteNonQuery();
 //validate the result of the executed query.
 if (result > 0)
 {
 MessageBox.Show("Data has been deleted in the SQL database");
 //calling a method
 btnreload_Click(sender, e);
 }
 else
 {
 MessageBox.Show("SQL QUERY ERROR");
 }
 
 }
 
 catch (Exception ex)
 {
 MessageBox.Show(ex.Message ); 
 }
 finally
 { 
 conn.Close();
 }

 }

• Go back to the design view, double click the “New” button and do the following codes for clearing all the textboxes in the form.

 //a method for clearing all textbox
 private void btnnew_Click(object sender, EventArgs e)
 {
 userid = 0;
 txtname.Clear();
 txtuname.Clear();
 txtpass.Clear();
 cborole.Text = "Select"; 
 }

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