Navigating through records using Data Table and Stored procedure in Windows form

In this article, I am going to explain how we can get records form database table and navigate through them back and forth from first record to last record.

First, we need to create a table and populate our table with some records.

Let's get started by first creating our table as below:

CREATE TABLE [dbo].[blog_test_up]

(

    [name] [char](50) NOT NULL,

    [age] [numeric](5, 0) NOT NULL,

    [phone] [varchar](50) NULL

)

Inserting records into table:

insert into blog_test_up values('ankit',25,075125555);

insert into blog_test_up values('ankit2',26,075125554);

insert into blog_test_up values('ankit3',46,075125565);

insert into blog_test_up values('ankit5',58,075125575);

 

Below will be our stored procedure for fetching the data from table:

CREATE PROCEDURE [dbo].[blog_select_up]

(

@msg varchar(100) output

)

        

AS

BEGIN try

    

    SET NOCOUNT ON;

select name,age,phone from blog_test_up;

return 0

END try

begin catch

select @msg='Error!!!Problem Occured During Process'

return 1

end catch

 

 

 

Below is our Windows Form :

Finally our code for implementing the functionality in C#:

 

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.IO;

using System.Data.SqlClient;

 

namespace blog

{

public partial class Form2 : Form

{

SqlConnection con;

string constr = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=your catalog;Data Source=your datasource";

string sp_e_msg;

SqlCommand cmd;

SqlDataAdapter adpt;

DataTable dt;

int proc = 0;

string notific;

public Form2()

{

InitializeComponent();

toolStripStatusLabel1.Text = "";

label4.Text = "";

}

 

//************************next>>********************************

private void button1_Click(object sender, EventArgs e)

{

try

{

label4.Text = "";

con = new SqlConnection();

con.ConnectionString = constr;

con.Open();

cmd = new SqlCommand("blog_select_up", con);

cmd.CommandType = CommandType.StoredProcedure;

//******************************************************************************

SqlParameter parm2 = new SqlParameter("@returnval", SqlDbType.Int);

parm2.Size = 50;

parm2.Direction = ParameterDirection.ReturnValue;

cmd.Parameters.Add(parm2);

//**************************************************************

SqlParameter parm1 = new SqlParameter("@msg", SqlDbType.VarChar);

parm1.Size = 50;

parm1.Direction = ParameterDirection.Output;

cmd.Parameters.Add(parm1);

//***************************************************************

adpt = new SqlDataAdapter(cmd);

dt = new DataTable();

adpt.Fill(dt);

if (dt.Rows.Count > 0)

{

 

textBox1.Text= dt.Rows[proc]["name"].ToString();

textBox2.Text = dt.Rows[proc]["age"].ToString();

textBox3.Text = dt.Rows[proc]["phone"].ToString();

proc = proc + 1;

toolStripStatusLabel1.Text = proc.ToString()+"of"+dt.Rows.Count.ToString ();

cmd.ExecuteNonQuery();

notific = cmd.Parameters["@returnval"].Value.ToString();

//*************************************************************************

sp_e_msg = cmd.Parameters["@msg"].Value.ToString();

 

}

 

}

 

catch (Exception ex)

{

 

if (notific == "1")

{

label4.Text = sp_e_msg + notific;

}

}

finally

{

con.Close();

con.Dispose();

cmd.Dispose();

 

if (proc > dt.Rows.Count - 1)

{

label4.Text = "Records Ends";

proc = dt.Rows.Count-1;

}

}

}

 

//**********************<<previous******************************

private void button2_Click(object sender, EventArgs e)

{

try

{

label4.Text = "";

toolStripStatusLabel1.Text = "";

con = new SqlConnection();

con.ConnectionString = constr;

con.Open();

 

cmd = new SqlCommand("blog_select_up", con);

cmd.CommandType = CommandType.StoredProcedure;

//******************************************************************************

SqlParameter parm2 = new SqlParameter("@returnval", SqlDbType.Int);

parm2.Size = 50;

parm2.Direction = ParameterDirection.ReturnValue;

cmd.Parameters.Add(parm2);

//**************************************************************

SqlParameter parm1 = new SqlParameter("@msg", SqlDbType.VarChar);

parm1.Size = 50;

parm1.Direction = ParameterDirection.Output;

cmd.Parameters.Add(parm1);

//***************************************************************

adpt = new SqlDataAdapter(cmd);

dt = new DataTable();

adpt.Fill(dt);

if (dt.Rows.Count > 0)

{

 

textBox1.Text= dt.Rows[proc]["name"].ToString();

textBox2.Text = dt.Rows[proc]["age"].ToString();

textBox3.Text = dt.Rows[proc]["phone"].ToString();

proc = proc - 1;

cmd.ExecuteNonQuery();

notific = cmd.Parameters["@returnval"].Value.ToString();

//*************************************************************************

sp_e_msg = cmd.Parameters["@msg"].Value.ToString();

 

}

 

}

 

catch (Exception ex)

{

 

if (notific == "1")

{

label4.Text = sp_e_msg + notific;

}

 

}

finally

{

con.Close();

con.Dispose();

cmd.Dispose();

 

if (proc <0)

{

label4.Text = "Records Ends";

proc = 0;

}

}

}

}

}

 

Tags: , ,

Windows Form