Over here we will see how we can manage data and do crud operations in one Form itself
Objective
- Inserting,Updating data through the same Interface
- Deleting the data directly from the data-grid.
Using the code
- Create a New Windows Application project in Visual Studio 2010.
- Drage and Drop 2 Textboxes and One Button and 1 DataGridView
- Name the button as btnSave
- Now click on the Arrow at the right corner of the gridview as shown in the screen
- Now click on Add Columns and add the following Column as shown in the figure below and click on Add
- Similarly add the Name column as well
- Note :- The name of the column should be same as that of the table in the database that too Case sensitive
- Now we will be adding edit and delete column in the Data Grid
- The process of adding the column is same as the above step but the type of the column will be of Image type check the Screen below
- Change the type from the drop down to Image type and click Add
- Same way add the delete column as well
- Now we will assign the Image to that column since we have set the type of the column to imagetype as seen in the screen above
- Once you click add you will again get the dialog box Edit column that select the Edit Column from the Left Side and go to the image property and click on it and browse the image and set the image for the column.Check the Screen below.
- Do it for the delete column as well.
- Now we will go to the code behind and write the code for Save and Update
- Insert and Update will be done on the same button.
- To Update the data we will click the edit column of the grid and the data related on that row will be shown in the text boxes above and the button text will change to Update.
- Once you change the data and click the button it will update the data.
- Once the Data is updated it the text box will become blank and the mode of saving the data will change to Insert not update.
- To distinguish when the data will be inserted or updated we will use a string known as Save Mode and if the Save Mode is = 0 then it will Insert the data.
- If the Save mode = 1 then the update query will fire.
- The Save mode will be set to 1 when you click the edit column in the Data Grid.
Check the Code for Code behind
Table and Stored Procedure for the article
Table : Crud
CREATE TABLE [dbo].[Crud](
[ID] [int] NULL,
[Names] [varchar](20) NULL
)
Stored Procedure
This is to Save and Update data
create proc [dbo].[INS_UPD_Crud]
(
@mode char(1),
@id int,
@Name varchar(20)
)
as
begin
if(@mode='0')
begin
insert into Crud(id,Names) values (@id,@Name)
end
else if(@mode='1')
begin
update Crud set Names = @Name where id = @id
end
end
This proc is used to fill grid
create proc [dbo].[Getdata]
as
begin
select * from Crud
end
//This will fetch data when clicked on edit column in the application
create proc [dbo].[Getdatatotextbox]
(@id int)
as
begin
select * from Crud where ID = @id
end
// This will delete the data
create proc [dbo].[deleteCrud]
(
@id int
)
as
begin
delete from Crud where id = @id
end
// Code Behind
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Crud
{
public partial class Form1 : Form
{
string GlobalMode = "0"; // This is the Mode to Passed as Parameter to SaveData function
public Form1()
{
InitializeComponent();
}
// This event sets the mode for edit i.e. GlobalMode = 1 and also deletes the data by identifying the column name
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{ try
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=DotNetFunda;User id=sa;Password=sqluser");
if (gvDetails.Columns[e.ColumnIndex].Name == "Edit")
{
GetDataforUpdate(getdatatoTextbox(Convert.ToInt32(gvDetails.Rows[e.RowIndex].Cells[0].Value.ToString())));
btnSave.Text = "UPDATE";
GlobalMode = "1";
}
else if (gvDetails.Columns[e.ColumnIndex].Name == "Delete")
{
DialogResult result1 = MessageBox.Show("Are you sure you want to delete\nrecord of " + gvDetails.Rows[e.RowIndex].Cells[1].Value.ToString() + " ?", "Warning", MessageBoxButtons.YesNo);
if (result1 == DialogResult.Yes)
con.Open();
SqlCommand sqlcmd = new SqlCommand("deleteCrud", con);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@id", SqlDbType.Int).Value = gvDetails.Rows[e.RowIndex].Cells[0].Value.ToString();
int result = sqlcmd.ExecuteNonQuery();
if (result == 1)
{
MessageBox.Show("Record Deleted");
BindGrid();
}
}
catch(Exception ex)
{
}
finally
{
con.dispose();
}
}
// This function gets the data into datatable
private void GetDataforUpdate(DataTable dtdata)
{
textBox1.Text = dtdata.Rows[0]["ID"].ToString();
textBox2.Text = dtdata.Rows[0]["Names"].ToString();
}
// This function actually passes the data to textbox
private DataTable getdatatoTextbox(int id)
{
try
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=DotNetFunda;User id=sa;Password=sqluser");
con.Open();
SqlCommand sqlcmd = new SqlCommand("Getdatatotextbox", con);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@id", SqlDbType.Int).Value = id;
DataTable dtdatanew = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
da.Fill(dtdatanew);
con.Close();
return dtdatanew;
}
catch (exception ex)
{
}
finally
{
con.dispose();
}
}
private void btnSave_Click(object sender, EventArgs e)
{
SaveData(GlobalMode);
btnSave.Text = "Save";
GlobalMode = "0";
Clear();
}
// Save Function for Inserting and Updating Data
private void SaveData(string InsertMode) // Insert Mode will check whether to Insert New Data or to Update
{
try
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=DotNetFunda;User id=sa;Password=sqluser");
con.Open();
SqlCommand sqlcmd = new SqlCommand("INS_UPD_Crud", con);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@mode", SqlDbType.Char).Value = InsertMode; // This mode is checked in the stored procedure...If the mode is 0 then new data is inserted if mode is 1 the data is updated on the where clause of the id.
sqlcmd.Parameters.AddWithValue("@id", SqlDbType.Int).Value = textBox1.Text.Trim();
sqlcmd.Parameters.AddWithValue("@Name", SqlDbType.VarChar).Value = textBox2.Text.Trim();
int result = sqlcmd.ExecuteNonQuery();
if (result == 1)
{
MessageBox.Show("Data Saved");
con.Close();
BindGrid();
con.Close();
}
else
{
MessageBox.Show("OOPS!!! Something went wrong");
}
}
catch (exception ex)
{
}
finally
{
con.dispose();
}
}
private void BindGrid()
{
try
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=DotNetFunda;User id=sa;Password=sqluser");
gvDetails.Rows.Clear();
int Snu = 0;
DataSet ds = new DataSet();
con.Open();
SqlCommand cmd = new SqlCommand("Getdata", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
gvDetails.Rows.Add();
gvDetails.Rows[Snu].Cells[0].Value = Convert.ToString(row["id"]);
gvDetails.Rows[Snu].Cells[1].Value = Convert.ToString(row["Names"]);
++Snu;
con.Close();
}
}
}
catch(exception ex)
{
}
finally
{
con.dispose();
}
}
private void Form1_Load(object sender, EventArgs e)
{
BindGrid();
}
private void Clear()
{
textBox1.Text = "";
textBox2.Text = "";
}
}
} Explanation for Inserting and Updating and deleting the data
Function Name:- private void SaveData(string InsertMode)
- As the form Loads the Insert Mode is set to 0.
- This means that the data that you enter and when you save the data it will be a new entry because in the stored proc we have a if condition where it checks the mode.
- If the mode is 0 then it will execute the Insert Query.
- Now when we click on the edit button in the grid the insert mode is set to 1 and the id is captured and the data related to that id is fetched from the database in the data table and assigned to the text boxes and the button text is changed to Update.
- Now we can make changes to the data and save it.Over here it will not fire the insert query rather it will fire the update query as we have set the Insert Mode to 1 and in the Stored Procedure the mode = 1 is checked for the update Query.
- To delete the data we will click on the Delete button in the grid over here we will again capture the id and execute the delete SQL Command and the command type is stored procedure where we are passing the id as parameter to the Store Procedure.
Output Screen
Conclusion
Hope this will be useful........