===========================================
and code are given below for this:(Form1.CS page)
using System;
using System.Windows.Forms;
using ConnectToDataBase;//this is from Library file.
namespace GroupMailingSystem
{
public partial class Form1 : Form
{
Class1 tempClass = new Class1();
public static string tempGroup = string.Empty;
public Form1()
{
InitializeComponent();
}
//code for insert button.
private void button1_Click(object sender, EventArgs e)
{
string groupName = txtAddGroup.Text.Trim();
var set = tempClass.FromDatabase("SELECT COUNT(*) FROM [dbo].[Tab_GroupName] WHERE Groupname='" + txtAddGroup.Text + "' and IsDeleted=0");
if (!(Convert.ToInt32(set.Tables[0].Rows[0][0].ToString()) > 0))
{
tempClass.InsertDataIntoDatabase("INSERT [dbo].[Tab_GroupName] ( [GroupName],[IsDeleted]) VALUES ('" + groupName + "',0)");
lblError.Text = "successfully inserted";
}
else
lblError.Text = "Already data with same name.";
ShowDataInGridview();
}
private void ShowDataInGridview()
{
dataGridView1.DataSource = tempClass.FromDatabase("SELECT GroupName FROM [dbo].[Tab_GroupName] WHERE IsDeleted=0");
dataGridView1.DataMember = "Tables";
}
//code for next button click.
private void button2_Click(object sender, EventArgs e)
{
Form2 frm = new Form2(this);
frm.Show();
this.Hide();
}
//on form loading.
private void Form1_Load(object sender, EventArgs e)
{
ShowDataInGridview();
int rowToBeSelected = 0;//3; // third row
if (dataGridView1.Rows.Count >= rowToBeSelected)
{
// Since index is zero based, you have to subtract 1
dataGridView1.Rows[rowToBeSelected - 1].Selected = true;
}
}
//code for Edit button click.
private void button3_Click(object sender, EventArgs e)
{
int i;
i = dataGridView1.SelectedCells[0].RowIndex;
txtAddGroup.Text = dataGridView1.Rows[i].Cells[0].Value.ToString();
tempGroup = txtAddGroup.Text;
}
//code for UPdate button.
private void button5_Click(object sender, EventArgs e)
{
var set = tempClass.FromDatabase("SELECT COUNT(*) FROM [dbo].[Tab_GroupName] WHERE Groupname='" + txtAddGroup.Text + "' and IsDeleted=0");
if (!(Convert.ToInt32(set.Tables[0].Rows[0][0].ToString()) > 0))
{
int success = tempClass.UpdateGroupIntoDatabase("UPDATE [dbo].[Tab_GroupName] SET GroupName=@Name WHERE (GroupName='" + tempGroup + "')", txtAddGroup.Text);
if (success > 0)
{
txtAddGroup.Text = string.Empty;
lblError.Text = "successfully updated.";
}
else
lblError.Text = "unsuccessful .";
ShowDataInGridview();
}
}
//code for delete button.
private void button4_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Do you want to delete?", "Delete data", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
// a 'DialogResult.Yes' value was returned from the MessageBox
// proceed with your deletion
#region This code is used for deleting rows from database where Id is not Identity spceify.
//int i;
//i = dataGridView1.SelectedCells[0].RowIndex;
//if (dataGridView1.Rows.Count > 1 && i != dataGridView1.Rows.Count - 1)
//{
// tempClass.DeleteRowFromDatabase("DELETE FROM [dbo].[Tab_GroupName] WHERE ID=" + dataGridView1.SelectedCells[0].RowIndex + "");
// dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[i].Index);
//}
//ShowDataInGridview();
#endregion
int temp = dataGridView1.SelectedCells[0].RowIndex;
#region When Id is Identity specify and you dont want data from database
int success = tempClass.DeleteRowFromDatabase("UPDATE [dbo].[Tab_GroupName] SET IsDeleted=1 WHERE ID=" + (temp + 1) + "");
if (success > 0)
{
txtAddGroup.Text = string.Empty;
lblError.Text = "successfully updated.";
}
else
lblError.Text = "unsuccessful .";
ShowDataInGridview();
#endregion
}
}
}
}
==========================================================
for ConnectToDataBase (add a library project and reference it)
using System;
using System.Data;
using System.Data.SqlClient;
using ConnectToDataBase;
namespace ConnectToDataBase
{
public class Class1
{
private static int temp = 0;
SqlConnection con = new SqlConnection("Data Source=YourServerName;Initial Catalog=DataBaseName;Integrated Security=True");
DataSet ds = null;
public DataSet FromDatabase(string query)
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(query, con);
ds = new DataSet();
da.Fill(ds, "Tables");
con.Close();
return ds;
}
public int InsertDataIntoDatabase(string query)
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand(query, con);
if (cmd.ExecuteNonQuery() > 0)
{
temp = 1;
}
}
catch (Exception ex)
{
temp = 3;
}
finally
{
con.Close();
}
return temp;
}
public int DeleteRowFromDatabase(string query)
{
con.Open();
SqlCommand delcmd = new SqlCommand(query, con);
if (delcmd.ExecuteNonQuery() > 0)
{
con.Close();
return 1;
}
con.Close();
return 0;
}
public int UpdateGroupIntoDatabase(string query, string newGroup)
{
con.Open();
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@Name", newGroup);
if (cmd.ExecuteNonQuery() > 0)
{
con.Close();
return 1;
}
con.Close();
return 0;
}
}
}
=================================
and Script file for database:
USE [DataBaseName]
CREATE TABLE [dbo].[Tab_GroupName](
[Id] [int] IDENTITY(1,1) PRIMARY KEY,
[GroupName] [varchar](50) NOT NULL,
[IsDeleted] [bit] NOT NULL)
GO
CREATE TABLE [dbo].[Tab_User](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Email] [varchar](50) NOT NULL,
[PhoneNumber] [bigint] NOT NULL,
[GroupId] [int] NOT NULL,
[IsDeleted] [bit] NOT NULL,
PRIMARY KEY([GroupId],[PhoneNumber]))
GO
ALTER TABLE [dbo].[Tab_User] WITH CHECK ADD CONSTRAINT [FK_Tab_User_Tab_GroupName] FOREIGN KEY([GroupId])
REFERENCES [dbo].[Tab_GroupName] ([Id])
GO
ALTER TABLE [dbo].[Tab_User] CHECK CONSTRAINT [FK_Tab_User_Tab_GroupName]
GO
