ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

User CRUD in C# and SQL Server

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

Check Your Domain Ranking

4 Comments

Add a Comment
  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. Para la realizacion del clip, en el que la cantante aparece como una mujer de accion y combativa, se requerian atuendos de latex.

  3. Hi my family member! I want to say that this article is amazing, nice written and include almost all important infos. I’d like to peer extra posts like this .

  4. 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

Your email address will not be published. Required fields are marked *

ITSourceCode.com © 2016 Frontier Theme

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!