C# Windows Application Data Form sample Module: Save and Retrieve Data using MS SQL database

Methods Used:

1. Created a database as NameDB and Database table as namedata as follows.

--table for saving retrieving datas

create table namedata
(
 ID int identity,
 Firstname nvarchar(500),
 Secondname nvarchar(500),
 Fullname nvarchar(500)
)

--get  query result
select * from namedata


2. Create a  stored procedure for saving data


-- creating stored procedure for saving data
create proc nameprocedure 
@Firstname as nvarchar(500),
@Secondname as nvarchar(500),
@Fullname as nvarchar(500)
as
begin 
 SET NOCOUNT ON;
Insert into  namedata(Firstname,Secondname,Fullname)
values(@Firstname,@Secondname,@Fullname)
end
-- execute strored procedure
exec nameprocedure

3. Windows Application design for save and Retrieve data form

set formborderstyle as none

4. App.config settings to Connect database


<?xml version="1.0"?>
<configuration>
  <configSections>
  </configSections>
  <connectionStrings>
    <add name="Connectionstring1" connectionString="Data Source=localhost\sqlexpress; Initial Catalog=NameDB;Integrated Security=true"
      providerName="System.Data.SqlClient" />
    <add name="saveandretrieve.Properties.Settings.ConnectionString1"
      connectionString="Data Source=localhost\sqlexpress;Initial Catalog=NameDB;Integrated Security=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
<startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/></startup></configuration>

5. Class Diagram analysis with SQL query and stored procedure

  

6. Form Designer Coding

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.Sql; using System.Data.SqlClient; using System.Configuration; using System.Net; using System.Collections.Specialized; namespace saveandretrieve { public partial class DataForm : Form { public DataForm() { InitializeComponent(); } //Full screen method public void fullscreen() { int i = Screen.PrimaryScreen.Bounds.Width; int j = Screen.PrimaryScreen.Bounds.Height; this.Location = new Point(0, 0); this.Size = new Size(i, j); } //connection setting with database and App.config using Configuration manager public static string getConnection() { /*Reading Connection string from App.config File */ string constr = ConfigurationManager.ConnectionStrings["Connectionstring1"].ConnectionString; return constr; //return true; } private void button1_Click(object sender, EventArgs e) { string fn; string sn; string fullname; fn = textBox1.Text; sn = textBox2.Text; fullname = textBox3.Text; Register(fn, sn, fullname); } public void Register(string Fname, string Sname, string Fullname) { /* Assigning coonection string to 'constring' variable by calling getConnection function */ string constring = getConnection(); /* Declaring Connection Variable */ SqlConnection con = new SqlConnection(constring); /* Checking Connection is Opend or not If its not open the Opens */ if (con.State != ConnectionState.Open) con.Open(); /* Calling Stored Procedure as SqlCommand */ SqlCommand cmd = new SqlCommand("nameprocedure", con); cmd.CommandType = CommandType.StoredProcedure; /* Passing Input Parameters with Command */ cmd.Parameters.AddWithValue("@Firstname", Fname); cmd.Parameters["@Firstname"].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("@Secondname", Sname); cmd.Parameters["@Secondname"].Direction = ParameterDirection.Input; cmd.Parameters.AddWithValue("@Fullname", Fullname); cmd.Parameters["@Fullname"].Direction = ParameterDirection.Input; /* Executing Stored Procedure */ cmd.ExecuteNonQuery(); con.Close(); MessageBox.Show("Data Inserted Succesfully"); } public void getdata() { SqlCommand sqlCmd;// For passing Command to sql SqlDataReader sqlDrd;// For storing Output Data from sql command try { /* Assigning coonection string to 'constring' variable by calling getConnection function */ string constring = getConnection(); /* Declaring Connection Variable */ SqlConnection sqlCon = new SqlConnection(constring); sqlCon.Open(); sqlCmd = new SqlCommand("SELECT * FROM namedata", sqlCon); sqlDrd = sqlCmd.ExecuteReader(); while (sqlDrd.Read()) { if (textBox3.Text == sqlDrd[3].ToString()) { textBox1.Text = sqlDrd[1].ToString(); textBox2.Text = sqlDrd[2].ToString(); textBox3.Text = sqlDrd[3].ToString(); } else { // MessageBox.Show("Enter Full name"); } } sqlCon.Close(); } catch (Exception ea) { MessageBox.Show("Error: " + ea.Message.ToString()); } label1.Show(); textBox1.Show(); label2.Show(); textBox2.Show(); label3.Text = "Full Name"; button2.Text = "Retrieve"; } private void button2_Click(object sender, EventArgs e) { label1.Hide(); textBox1.Hide(); label2.Hide(); textBox2.Hide(); label3.Text = "Enter Full Name"; button2.Text = "show"; getdata(); } private void DataForm_Load(object sender, EventArgs e) { fullscreen(); } } }

6. Outputs

a) Storing Data from Windows Application to Sql DataBase.

b) Data stored in MS SQL Database





c) Retrieve Data(K Kumaran) from database to windows Application

After Clicking Retrieve Button