Saad SoNu Ali: C# Tutorial - Insert Update Delete into multiple tables in SQL (Project ...

Aslam-u-Alikuum Beautiful People

C# Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace AcountingSoftware
{
public partial class Form4 : Form
{
SqlConnection sqlCon = new SqlConnection(@"Data Source=DESKTOP-DODHH2V\SAAD;Initial Catalog=acoountingsoftware;Integrated Security=True");
int Id = 0;
public Form4()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
string user = textBox1.Text;
returnclass rc = new returnclass();
label1.Text = rc.scalarReturn("select CCode from [dbo].[Company Name] where CCode='" + user + "'");
label2.Text = rc.scalarReturn("select CName from [dbo].[Company Name] where CCode='" + user + "'");
label3.Text = rc.scalarReturn("select CAddress from [dbo].[Company Name] where CCode='" + user + "'");
label4.Text = rc.scalarReturn("select CContactinfo from [dbo].[Company Name] where CCode='" + user + "'");
}

private void Form4_Load(object sender, EventArgs e)
{
Reset();
FillDataGridView();
}

private void btnSave_Click(object sender, EventArgs e)
{

try
{
if (sqlCon.State == ConnectionState.Closed)
sqlCon.Open();
if (btnSave.Text == "Save")
{
SqlCommand sqlcmd = new SqlCommand("itemprocedures", sqlCon);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@mode", "Add");
sqlcmd.Parameters.AddWithValue("@Id", "0");
sqlcmd.Parameters.AddWithValue("@CCode", label1.Text.Trim());
sqlcmd.Parameters.AddWithValue("@CName", label2.Text.Trim());
sqlcmd.Parameters.AddWithValue("@CAddress", label3.Text.Trim());
sqlcmd.Parameters.AddWithValue("@CContactinfo", label4.Text.Trim());

sqlcmd.Parameters.AddWithValue("@Item1C", ic1.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item2C", ic2.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item3C", ic3.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item4C", ic4.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item5C", ic5.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item6C", ic6.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item7C", ic7.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item8C", ic8.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item9C", ic9.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item10C", ic10.Text.Trim());

sqlcmd.Parameters.AddWithValue("@Item1N", in1.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item2N", in2.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item3N", in3.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item4N", in4.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item5N", in5.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item6N", in6.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item7N", in7.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item8N", in8.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item9N", in9.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item10N", in10.Text.Trim());

sqlcmd.Parameters.AddWithValue("@Item1P", ip1.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item2P", ip2.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item3P", ip3.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item4P", ip4.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item5P", ip5.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item6P", ip6.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item7P", ip7.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item8P", ip8.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item9P", ip9.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item10P", ip10.Text.Trim());
sqlcmd.Parameters.AddWithValue("@AdminN", textBox5.Text.Trim());

sqlcmd.ExecuteNonQuery();
MessageBox.Show("Saved Success fully...:-)");
}
else
{

SqlCommand sqlcmd = new SqlCommand("itemprocedures", sqlCon);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@mode", "Edit");
sqlcmd.Parameters.AddWithValue("@Id", Id);
sqlcmd.Parameters.AddWithValue("@CCode", label1.Text.Trim());
sqlcmd.Parameters.AddWithValue("@CName", label2.Text.Trim());
sqlcmd.Parameters.AddWithValue("@CAddress", label3.Text.Trim());
sqlcmd.Parameters.AddWithValue("@CContactinfo", label4.Text.Trim());

sqlcmd.Parameters.AddWithValue("@Item1C", ic1.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item2C", ic2.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item3C", ic3.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item4C", ic4.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item5C", ic5.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item6C", ic6.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item7C", ic7.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item8C", ic8.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item9C", ic9.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item10C", ic10.Text.Trim());

sqlcmd.Parameters.AddWithValue("@Item1N", in1.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item2N", in2.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item3N", in3.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item4N", in4.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item5N", in5.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item6N", in6.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item7N", in7.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item8N", in8.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item9N", in9.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item10N", in10.Text.Trim());

sqlcmd.Parameters.AddWithValue("@Item1P", ip1.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item2P", ip2.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item3P", ip3.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item4P", ip4.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item5P", ip5.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item6P", ip6.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item7P", ip7.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item8P", ip8.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item9P", ip9.Text.Trim());
sqlcmd.Parameters.AddWithValue("@Item10P", ip10.Text.Trim());
sqlcmd.Parameters.AddWithValue("@AdminN", textBox5.Text.Trim());
sqlcmd.ExecuteNonQuery();
MessageBox.Show("updates Saved Success fully...:-)");

}
Reset();
FillDataGridView();

}
catch (Exception ex)
{

MessageBox.Show(ex.Message, "Error...!!");
}

finally
{
sqlCon.Close();
}
}

void FillDataGridView()
{

if (sqlCon.State == ConnectionState.Closed)
sqlCon.Open();
SqlDataAdapter SqlDA = new SqlDataAdapter("itemviewrsearch", sqlCon);
SqlDA.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlDA.SelectCommand.Parameters.AddWithValue("@CCode", textBox2.Text.Trim());
SqlDA.SelectCommand.Parameters.AddWithValue("@CName", textBox3.Text.Trim());
SqlDA.SelectCommand.Parameters.AddWithValue("@CAddress", textBox4.Text.Trim());

DataTable dtbl = new DataTable();
SqlDA.Fill(dtbl);
dataGridView1.DataSource = dtbl;
dataGridView1.Columns[0].Visible = false;
sqlCon.Close();

}

private void button2_Click(object sender, EventArgs e)
{
try
{
FillDataGridView();
}
catch (Exception ex)
{

MessageBox.Show(ex.Message, "Error...!!");
}
}
void Reset()
{
ic1.Text ="";
ic2.Text ="";
ic3.Text ="";
ic4.Text ="";
ic5.Text ="";
ic6.Text ="";
ic7.Text ="";
ic8.Text ="";
ic9.Text ="";
ic10.Text ="";

in1.Text ="";
in2.Text ="";
in3.Text ="";
in4.Text ="";
in5.Text ="";
in6.Text ="";
in7.Text ="";
in8.Text ="";
in9.Text ="";
in10.Text ="";

ip1.Text ="";
ip2.Text ="";
ip3.Text ="";
ip4.Text ="";
ip5.Text ="";
ip6.Text ="";
ip7.Text ="";
ip8.Text ="";
ip9.Text ="";
ip10.Text ="";

textBox1.Text = textBox2.Text = textBox3.Text = textBox4.Text = "";

btnSave.Text = "Save";
FillDataGridView();
btnDelete.Enabled = false;
}

private void btnReset_Click(object sender, EventArgs e)
{
Reset();
}

private void btnDelete_Click(object sender, EventArgs e)
{
try
{

if (sqlCon.State == ConnectionState.Closed)
sqlCon.Open();

SqlCommand sqlcmd = new SqlCommand("[dbo].[Ideletion]", sqlCon);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@Id", Id);

sqlcmd.ExecuteNonQuery();
MessageBox.Show("Deleted Success fully...:-)");
Reset();
FillDataGridView();

}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error...!!");

}
}

private void dataGridView1_DoubleClick(object sender, EventArgs e)
{
Id = Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value.ToString());

label1.Text= dataGridView1.CurrentRow.Cells[1].Value.ToString();
label2.Text= dataGridView1.CurrentRow.Cells[2].Value.ToString();
label3.Text= dataGridView1.CurrentRow.Cells[3].Value.ToString();
label4.Text= dataGridView1.CurrentRow.Cells[4].Value.ToString();

ic1.Text = dataGridView1.CurrentRow.Cells[5].Value.ToString();
in1.Text = dataGridView1.CurrentRow.Cells[6].Value.ToString();
ip1.Text = dataGridView1.CurrentRow.Cells[7].Value.ToString();

ic2.Text = dataGridView1.CurrentRow.Cells[8].Value.ToString();
in2.Text = dataGridView1.CurrentRow.Cells[9].Value.ToString();
ip2.Text = dataGridView1.CurrentRow.Cells[10].Value.ToString();

ic3.Text = dataGridView1.CurrentRow.Cells[11].Value.ToString();
in3.Text = dataGridView1.CurrentRow.Cells[12].Value.ToString();
ip3.Text = dataGridView1.CurrentRow.Cells[13].Value.ToString();

ic4.Text = dataGridView1.CurrentRow.Cells[14].Value.ToString();
in4.Text = dataGridView1.CurrentRow.Cells[15].Value.ToString();
ip4.Text = dataGridView1.CurrentRow.Cells[16].Value.ToString();

ic5.Text = dataGridView1.CurrentRow.Cells[17].Value.ToString();
in5.Text = dataGridView1.CurrentRow.Cells[18].Value.ToString();
ip5.Text = dataGridView1.CurrentRow.Cells[19].Value.ToString();

ic6.Text = dataGridView1.CurrentRow.Cells[20].Value.ToString();
in6.Text = dataGridView1.CurrentRow.Cells[21].Value.ToString();
ip6.Text = dataGridView1.CurrentRow.Cells[22].Value.ToString();

ic7.Text = dataGridView1.CurrentRow.Cells[23].Value.ToString();
in7.Text = dataGridView1.CurrentRow.Cells[24].Value.ToString();
ip7.Text = dataGridView1.CurrentRow.Cells[25].Value.ToString();

ic8.Text = dataGridView1.CurrentRow.Cells[26].Value.ToString();
in8.Text = dataGridView1.CurrentRow.Cells[27].Value.ToString();
ip8.Text = dataGridView1.CurrentRow.Cells[29].Value.ToString();

ic9.Text = dataGridView1.CurrentRow.Cells[30].Value.ToString();
in9.Text = dataGridView1.CurrentRow.Cells[31].Value.ToString();
ip9.Text = dataGridView1.CurrentRow.Cells[32].Value.ToString();

ic10.Text = dataGridView1.CurrentRow.Cells[33].Value.ToString();
in10.Text = dataGridView1.CurrentRow.Cells[34].Value.ToString();
ip10.Text = dataGridView1.CurrentRow.Cells[35].Value.ToString();


btnSave.Text = "Update";
btnDelete.Enabled = true;
}

}
}

Sql Code:

CREATE TABLE [dbo].[Items](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CCode] [nvarchar](50) NULL,
[CName] [nvarchar](50) NULL,
[CAddress] [nvarchar](300) NULL,
[CContactinfo] [nvarchar](50) NULL,
[Item1C] [nvarchar](100) NULL,
[Item1N] [nvarchar](100) NULL,
[Item1P] [int] NULL,
[Item2C] [nvarchar](100) NULL,
[Item2N] [nvarchar](100) NULL,
[Item2P] [int] NULL,
[Item3C] [nvarchar](100) NULL,
[Item3N] [nvarchar](100) NULL,
[Item3P] [int] NULL,
[Item4C] [nvarchar](100) NULL,
[Item4N] [nvarchar](100) NULL,
[Item4P] [int] NULL,
[Item5C] [nvarchar](100) NULL,
[Item5N] [nvarchar](100) NULL,
[Item5P] [int] NULL,
[Item6C] [nvarchar](100) NULL,
[Item6N] [nvarchar](100) NULL,
[Item6P] [int] NULL,
[Item7C] [nvarchar](100) NULL,
[Item7N] [nvarchar](100) NULL,
[Item7P] [int] NULL,
[Item8C] [nvarchar](100) NULL,
[Item8N] [nvarchar](100) NULL,
[Item8P] [int] NULL,
[Item9C] [nvarchar](100) NULL,
[Item9N] [nvarchar](100) NULL,
[Item9P] [int] NULL,
[Item10C] [nvarchar](100) NULL,
[Item10N] [nvarchar](100) NULL,
[Item10P] [int] NULL,
[AdminN] [nvarchar](50) NULL,
CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Store procedurde
For update an save:
PROCEDURE [dbo].[itemprocedures]
@mode nvarchar(50),
@Id int,
@CCode nvarchar(50),
@CName nvarchar(50),
@CAddress nvarchar(500),
@CContactinfo nvarchar(50),
@Item1C nvarchar(100),
@Item1N nvarchar(100),
@Item1P int,
@Item2C nvarchar(100),
@Item2N nvarchar(100),
@Item2P int,
@Item3C nvarchar(100),
@Item3N nvarchar(100),
@Item3P int,
@Item4C nvarchar(100),
@Item4N nvarchar(100),
@Item4P int,
@Item5C nvarchar(100),
@Item5N nvarchar(100),
@Item5P int,
@Item6C nvarchar(100),
@Item6N nvarchar(100),
@Item6P int,
@Item7C nvarchar(100),
@Item7N nvarchar(100),
@Item7P int,
@Item8C nvarchar(100),
@Item8N nvarchar(100),
@Item8P int,
@Item9C nvarchar(100),
@Item9N nvarchar(100),
@Item9P int,
@Item10C nvarchar(100),
@Item10N nvarchar(100),
@Item10P int,
@AdminN nvarchar(50)

AS
if @mode = 'Add'
BEGIN
INSERT INTO [dbo].[Items]
(
CCode ,
CName ,
CAddress ,
CContactinfo ,
Item1C ,
Item1N ,
Item1P ,
Item2C ,
Item2N ,
Item2P ,
Item3C ,
Item3N ,
Item3P ,
Item4C ,
Item4N ,
Item4P ,
Item5C ,
Item5N ,
Item5P ,
Item6C ,
Item6N ,
Item6P ,
Item7C ,
Item7N ,
Item7P ,
Item8C ,
Item8N ,
Item8P ,
Item9C ,
Item9N ,
Item9P ,
Item10C ,
Item10N ,
Item10P ,
AdminN

)
VALUES(
@CCode ,
@CName ,
@CAddress ,
@CContactinfo ,
@Item1C ,
@Item1N ,
@Item1P ,
@Item2C ,
@Item2N ,
@Item2P ,
@Item3C ,
@Item3N ,
@Item3P ,
@Item4C ,
@Item4N ,
@Item4P ,
@Item5C ,
@Item5N ,
@Item5P ,
@Item6C ,
@Item6N ,
@Item6P ,
@Item7C ,
@Item7N ,
@Item7P ,
@Item8C ,
@Item8N ,
@Item8P ,
@Item9C ,
@Item9N ,
@Item9P ,
@Item10C ,
@Item10N ,
@Item10P ,
@AdminN
)
END
ELSE If @mode='Edit'
BEGIN
UPDATE [dbo].[Items]
SET

CCode =@CCode ,
CName =@CName ,
CAddress =@CAddress ,
CContactinfo =@CContactinfo,
Item1C =@Item1C ,
Item1N =@Item1N ,
Item1P =@Item1P ,
Item2C =@Item2C ,
Item2N =@Item2N ,
Item2P =@Item2P ,
Item3C =@Item3C ,
Item3N =@Item3N ,
Item3P =@Item3P ,
Item4C =@Item4C ,
Item4N =@Item4N ,
Item4P =@Item4P ,
Item5C =@Item5C ,
Item5N =@Item5N ,
Item5P =@Item5P ,
Item6C =@Item6C ,
Item6N =@Item6N ,
Item6P =@Item6P ,
Item7C =@Item7C ,
Item7N =@Item7N ,
Item7P =@Item7P ,
Item8C =@Item8C ,
Item8N =@Item8N ,
Item8P =@Item8P ,
Item9C =@Item9C ,
Item9N =@Item9N ,
Item9P =@Item9P ,
Item10C =@Item10C ,
Item10N =@Item10N ,
Item10P =@Item10P ,
AdminN =@AdminN

WHERE Id = @Id
END

For View and search:
Create PROCEDURE [dbo].[itemviewrsearch]
@CCode nvarchar(100),
@CName nvarchar(100),
@CAddress nvarchar(100)

As
select * from [dbo].[Items]
Where CCode LIKE @CCode+'%' AND CName LIKE @CName+'%' AND CAddress LIKE @CAddress+'%'

For Delete;
Create PROCEDURE [dbo].[Ideletion]
@Id int
AS
DELETE [dbo].[Items]
WHERE Id=@Id

Saad SoNu Ali

Tuesday, 10 October 2017

C# Tutorial - Insert Update Delete into multiple tables in SQL (Project ...

Aslam-u-Alikuum Beautiful People

No comments:

Post a Comment

Funny Vital Tea Add | Arshad Chai Wala | Vital Tea