User CRUD in C# and SQL Server

4
978

CRUDSQLuotputThis 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 2008 and create new Windows Form Application. After that, do the Form as follows.

CRUDSQLfig.1t

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

CRUDSQLfig.2t

  • 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:

CRUDSQLuotput

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 – jannopalacios@gmail.com
Mobile No. – 09305235027 – tnt

 

 

Download Sourcecode

Facebook Comments
(Visited 1,148 times, 1 visits today)

4 COMMENTS

  1. Howdy I am so excited I found your webpage, I really found you by mistake, while I was searching on Askjeeve for something else, Anyways I am here now and would just like to say many thanks for a tremendous post and a all round enjoyable blog (I also love the theme/design), I don’t have time to read it all at the moment but I have book-marked it and also added your RSS feeds, so when I have time I will be back to read much more, Please do keep up the great work.

  2. I do not know whether it’s just me or if everyone else experiencing problems with your website. It appears as if some of the written text on your content are running off the screen. Can someone else please provide feedback and let me know if this is happening to them as well? This may be a problem with my web browser because I’ve had this happen previously. Cheers

LEAVE A REPLY

Please enter your comment!
Please enter your name here