Form Listbox & SQL Database

Question

I'm designing a C# Form program that uses a listbox to display search results from a SQL database. The search function works but now I want to be able to select one of the listbox rows and load the selected data (which is from the SQL database) to a new form. The program keeps track of clients at our company. The listbox gets populated when the user types in some criteria. I'm new to SQL and C# form design, so any help would be great. I left a screenshot below of the listbox and search boxes.

Answer 1

I had a quick search many month ago.This is my code.I hope it help you:

It is a full code of my stored procedure in SQL:

    USE [Khane]
GO
/****** Object:  StoredProcedure [dbo].[QuickSerch]    Script Date: 01/11/2012 22:24:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[QuickSerch] 
    @item nvarchar(300)=null
AS
BEGIN
    select * from PersonsDataTbl 
where 
Name like '%'+@item+'%' or 
LastName like '%'+@item+'%' or
FatherName like '%'+@item+'%' or
NationalCode like '%'+@item+'%' or
ShenasnameCode like '%'+@item+'%' or
BirthDate like '%'+@item+'%' or
State like '%'+@item+'%' or
City like '%'+@item+'%' or
Address like '%'+@item+'%' or
PostalCode like '%'+@item+'%' or
SportType like '%'+@item+'%' or
SportStyle like '%'+@item+'%' or
RegisterType like '%'+@item+'%' or
Ghahremani like '%'+@item+'%' 
END

if you don't know about stored procedure you can search about it.

and it's my C# code to send data to the stored procedure and get data form it:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace DL
{

public class DLQuickSerch
{
    List<Common.CommonPersonSerchResult> SerchResult = new List<Common.CommonPersonSerchResult>();

    public DLQuickSerch(string Item)
    {
        SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=Khane;Integrated Security=True");

        SqlCommand command = new SqlCommand();
        command.Connection = connection;

        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "QuickSerch";

        SqlParameter item = new SqlParameter("item", SqlDbType.NVarChar, 300);
        item.Value = Item;
        command.Parameters.Add(item);

        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
            Common.CommonPersonSerchResult res = new Common.CommonPersonSerchResult();

            res.ID = (int)reader.GetValue(0);
            res.FirstName = reader.GetValue(1).ToString();
            res.LastName = reader.GetValue(2).ToString();
            res.FatherName = reader.GetValue(3).ToString();
            res.NationalCode = (int)reader.GetValue(4);
            res.ShenasnameCode = (int)reader.GetValue(5);
            res.BirthDate = reader.GetValue(6).ToString();
            res.State = reader.GetValue(7).ToString();
            res.City = reader.GetValue(8).ToString();
            res.Address = reader.GetValue(9).ToString();
            res.PostalCode = reader.GetValue(10).ToString();
            res.SportType = reader.GetValue(11).ToString();
            res.SportStyle = reader.GetValue(12).ToString();
            res.RegisterType = reader.GetValue(13).ToString();
            res.Ghahremani = reader.GetValue(14).ToString();
            SerchResult.Add(res);

        }

        connection.Close();

    }

    public List<Common.CommonPersonSerchResult> GetQuickSerchResult()
    {
        return SerchResult;
    }
}
}

and you should change the SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=Khane;Integrated Security=True"); using your database's data

and it is my code for showing the data in a ListView:

private void QuickSearch(string item)
{
    DL.DLQuickSerch qc = new DL.DLQuickSerch(item);
    List<Common.CommonPersonSerchResult> reslt = qc.GetQuickSerchResult();
    FillListView(reslt);
} 

private void FillListView(List<Common.CommonPersonSerchResult> list)
    {
        SerchResultList.Items.Clear();   
        foreach (Common.CommonPersonSerchResult c in list)
        {
            ListViewItem item = new ListViewItem();
            item.Text = c.ID.ToString();
            item.SubItems.Add(c.FirstName);
            item.SubItems.Add(c.LastName);
            item.SubItems.Add(c.FatherName);
            item.SubItems.Add(c.NationalCode.ToString());
            item.SubItems.Add(c.ShenasnameCode.ToString());
            item.SubItems.Add(c.BirthDate);
            item.SubItems.Add(c.State);
            item.SubItems.Add(c.City);
            item.SubItems.Add(c.PostalCode.ToString());
            item.SubItems.Add(c.SportType);
            item.SubItems.Add(c.SportStyle);
            item.SubItems.Add(c.RegisterType);
            item.SubItems.Add(c.Ghahremani);

            item.Tag = c;

            SerchResultList.Items.Add(item);

        }
    }

and my Common.CommonPersonSerchResult was just a class of properties.

be careful It was my code and you should change it as you need to use it in your project

for showing data in new form you can save the data that you got from DB in listbox's tag and after that get the data in new form's constructor and work with it in you new form.It is so easy.To make a new form you can work with Listbox's selected change event like this:

private void SerchResultList_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (SerchResultList.SelectedItems.Count != 0)
        {
            Form2 = new Form2(SerchResultList.SelectedItems[0].Tag);
        }
    }

this code was for a ListView and I think there is just a SelectedItem in ListBox and your method will be like this:

private void SerchResultList_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (SerchResultList.SelectedItem != null)
        {
            Form2 = new Form2(SerchResultList.SelectedItem);
        }
    }

and you should give to list box your Data as a listbox's Item and you should override .ToString() method of you Data calss to Show the your needed data in ListBox.

Answer 2

To take the item you want you can do this(in code behind) :

    public void Test(object sender, EventArgs e)
    {
        ListBox list = (ListBox)sender;
        var item = list.SelectedItem;
    }

Once you have the item you can associate it to an event or a method that load data.

Answer 3

What you want to investigate is the DataBinding property of the various controls you're looking at.