Windows Application with Stored Procedures
1. Create a windows application.
my interface like this
This is my database design.
this the data view stored procedure
USE [Contactdetails]
GO
/****** Object: StoredProcedure [dbo].[viewcontactdetails] Script Date: 18-04-2018 10:10:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
ALTER PROCEDURE [dbo].[viewcontactdetails]
-- Add the parameters for the stored procedure here
@Name varchar(150)
AS
BEGIN
SET NOCOUNT ON;
SELECT * from PersonDetails where Name like'%'+ @Name +'%'
END
This is the Data Update Stored Procedure
USE [Contactdetails]
GO
/****** Object: StoredProcedure [dbo].[Updatecontactdetails] Script Date: 18-04-2018 10:12:29 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Updatecontactdetails]
-- Add the parameters for the stored procedure here
@Name varchar(150),
@Mobile int,
@Location varchar(50),
@ID int
AS
BEGIN
SET NOCOUNT ON;
update PersonDetails set Name=@Name,
Mobile=@Mobile,Location=@Location
where ID=@ID
END
This is the Data Insert Stored Procedure
USE [Contactdetails]
GO
/****** Object: StoredProcedure [dbo].[Insertcontactdetails] Script Date: 18-04-2018 10:13:20 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Insertcontactdetails]
-- Add the parameters for the stored procedure here
@Name varchar(150),
@Mobile int,
@Location varchar(50)
AS
BEGIN
SET NOCOUNT ON;
Insert into PersonDetails (Name,Mobile,Location)values (@Name,@Mobile,@Location)
END
This is the Data Delete Stored Procedure
USE [Contactdetails]
GO
/****** Object: StoredProcedure [dbo].[Deletecontactdetails] Script Date: 18-04-2018 10:14:02 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
ALTER PROCEDURE [dbo].[Deletecontactdetails]
-- Add the parameters for the stored procedure here
@ID int
AS
BEGIN
SET NOCOUNT ON;
Delete From PersonDetails
where ID=@ID
END
my project files like this
create a class file name it as filmdbcontext inside DAL Folder
this like this.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace windowsAppCRUDoperations.DAL
{
class filmdbcontext
{
public static string sqlcon = "Data Source=My-PC;Initial Catalog=Contactdetails;Integrated Security=True";
}
}
Main form Like this. I have created a separate class. You can see it below.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace windowsAppCRUDoperations
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void bt_save_Click(object sender, EventArgs e)
{
try
{
//create object the class test and pass value to the constructor
test insert = new test(tb_name.Text,int.Parse(tb_mobile.Text),tb_location.Text);
MessageBox.Show("Successfully Saved ", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
searchdetails();
cleartextbox();
}
catch(Exception ex)
{
MessageBox.Show(ex + "");
}
}
//details search method
public void searchdetails()
{
SqlConnection con = new SqlConnection();
DataTable dt = new DataTable();
try
{
con.ConnectionString = DAL.filmdbcontext.sqlcon;
con.Open();
//excute the stored procedure
SqlCommand myCmd = new SqlCommand("viewcontactdetails", con );
myCmd.CommandType = CommandType.StoredProcedure;
//pass the parameter
myCmd.Parameters.Add(new SqlParameter("@Name",tb_name.Text));
SqlDataAdapter da = new SqlDataAdapter(myCmd);
da.Fill(dt);
dataGridView1.DataSource = dt;
}
catch(Exception ex)
{
MessageBox.Show(ex + "");
}
}
private void Form1_Load(object sender, EventArgs e)
{
searchdetails();
}
private void tb_name_TextChanged(object sender, EventArgs e)
{
searchdetails();
}
private void bt_clear_Click(object sender, EventArgs e)
{
//clear the text boxes
cleartextbox();
}
public void cleartextbox()
{
tb_name.Text = "";
tb_mobile.Text = "";
tb_location.Text = "";
}
private void bt_update_Click(object sender, EventArgs e)
{
try
{
SqlConnection con = new SqlConnection();
con.ConnectionString = DAL.filmdbcontext.sqlcon;
con.Open();
//excute the stored procedure
SqlCommand myCmd = new SqlCommand("Updatecontactdetails", con);
myCmd.CommandType = CommandType.StoredProcedure;
//pass parameters
myCmd.Parameters.Add(new SqlParameter("@Name", tb_name.Text));
myCmd.Parameters.Add(new SqlParameter("@Mobile", tb_mobile.Text));
myCmd.Parameters.Add(new SqlParameter("@Location", tb_location.Text));
myCmd.Parameters.Add(new SqlParameter("@ID", lb_ID.Text));
myCmd.ExecuteNonQuery();
con.Close();//Data Saved
MessageBox.Show("Successfully Updated ", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
searchdetails();
}
catch(Exception ex)
{
MessageBox.Show(ex + "");
}
}
private void dataGridView1_Click(object sender, EventArgs e)
{
try
{
lb_ID.Text= this.dataGridView1.CurrentRow.Cells[0].Value.ToString();
tb_name.Text = this.dataGridView1.CurrentRow.Cells[1].Value.ToString();
tb_mobile.Text = this.dataGridView1.CurrentRow.Cells[2].Value.ToString();
tb_location.Text = this.dataGridView1.CurrentRow.Cells[3].Value.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex + "");
}
}
private void tb_location_TextChanged(object sender, EventArgs e)
{
searchdetails();
}
private void bt_delete_Click(object sender, EventArgs e)
{
try
{
//create object of the seperate class and Pass value to it
test dele = new test(int.Parse(lb_ID.Text));
MessageBox.Show("Successfully Deleted ", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
cleartextbox();
searchdetails();
}
catch(Exception ex)
{
MessageBox.Show(ex + "");
}
}
}
}
This is a separate class. I have named it a test. Inside this class, I have created constructors.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace windowsAppCRUDoperations
{
class test
{
int a1 = 0;
string command = "";
public int A1
{
get { return a1; }
set { a1 = value; }
}
int b1 = 0;
public int B1
{
get { return b1; }
set { b1 = value; }
}
//this is the constructor.
public test(int aa)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = DAL.filmdbcontext.sqlcon;
A1 = aa;
con.Open();
// Deletecontactdetails is the stored procedure name, and execute here
SqlCommand myCmd = new SqlCommand("Deletecontactdetails", con);
myCmd.CommandType = CommandType.StoredProcedure;
myCmd.Parameters.Add(new SqlParameter("@ID", A1));
myCmd.ExecuteNonQuery();
con.Close();
}
string Name;
public string Val1
{
get { return Name; }
set { Name = value; }
}
int Mobile;
public int Val2
{
get { return Mobile; }
set { Mobile = value; }
}
string Location;
public string Val3
{
get { return Location; }
set { Location = value; }
}
//this is the constructor.
public test(string val1 ,int val2, string val3)
{
Name = val1;
Mobile = val2;
Location = val3;
SqlConnection con = new SqlConnection();
con.ConnectionString = DAL.filmdbcontext.sqlcon;
con.Open();
//excute the stored procedure
SqlCommand myCmd = new SqlCommand("Insertcontactdetails", con);
myCmd.CommandType = CommandType.StoredProcedure;
//pass the parameters
myCmd.Parameters.Add(new SqlParameter("@Name", Name));
myCmd.Parameters.Add(new SqlParameter("@Mobile", Mobile));
myCmd.Parameters.Add(new SqlParameter("@Location", Location));
myCmd.ExecuteNonQuery();
con.Close();
}
public int cals()
{
return this.A1;
}
}
}
You download the complete Program. You have to create a database as my structure.
?
ReplyDelete