Solved

C# Databinding to SQL

Posted on 2009-04-11
4
674 Views
Last Modified: 2012-05-06
Im new to C# and to databinding so any feedback would be useful, i have a frm with a txt, box a listbox and a btn and a SQL database that has a client table (ID,Name,Address etc), what i want the form to do is whenever the button is clicked it searchs for the Client name in the Client table 'LIKE' any chars entered in the txt box then display the ClientName/Names in the ListBox. I need some help to on how to take whats entered in a txt box, run a query then transfer results to the Listbox? and do i need to specify the connection string everytime i have to reference a db? I said i was new!!!
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.Windows.Forms;
 

namespace ClientDetailsApplication1

{

    public partial class frmSearchClient : Form

    {

        public frmSearchClient()

        {

            InitializeComponent();

        }        

        

        //Enter part or all Client Name

        private void txtSearchClient_TextChanged(object sender, EventArgs e)

        {
 

        }
 

        //Open Connection and run Query to find Client thats entered in TxtSearchClient Box

        private void btnFind_Click(object sender, EventArgs e)

        {

            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();

            conn.ConnectionString = "integrated security=SSPI;data source=InstanceName;" +

            "persist security info=False;initial catalog=<DatabaseName>";

            using (SqlCommand cmd = new SqlCommand())

            {

                cmd.Connection = conn;

                cmd.Connection.Open();

                cmd.CommandText = ("SELECT ClientName FROM Client WHERE ClientName LIKE '%'");             

                //ListBox1 to display whatever the above result returns from txtSearchClient      
 

                
 

                SqlDataReader reader = cmd.ExecuteReader();             
 

                cmd.Connection.Close();

            }  

        }

        

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)

        {
 

        }
 

        

    }

       

        

    }

Open in new window

0
Comment
Question by:wilko100
  • 2
4 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24124761
you don't need to give connecting string everytime, you can create one databinding class or in case of web application, give it in web.config
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24124792
here is a code you needed.

table and data used.

--create table

USE [adventureworks]

GO

CREATE TABLE [dbo].[emps](

[Name] [varchar](50),

[Dept] [varchar](10),

[Company] [varchar](15)

) ON [PRIMARY]

GO


--insert records

INSERT INTO emps

SELECT 'RITESH','MIS','ECHEM' UNION ALL

SELECT 'Rajan','MIS','mar'

private void btnFind_Click(object sender, EventArgs e)

        {

            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();

            conn.ConnectionString = @"Data Source=RAJANWORK\SQL2K5;Initial Catalog=adventureworks;Integrated Security=True";

            //setup SqlCommand and assign SQL query in command
 

            System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();

            command.CommandType = System.Data.CommandType.Text;

            command.Connection = conn;

            command.CommandText = "Select name From emps where name like '" + textBox1.Text + "%'";
 
 

            //create one data adapter which will execute the command and fill the data into data set
 

            System.Data.SqlClient.SqlDataAdapter recordAdp = new System.Data.SqlClient.SqlDataAdapter();

            recordAdp.SelectCommand = command;

            System.Data.DataSet recordSet = new System.Data.DataSet();

            recordAdp.Fill(recordSet);

            command.Dispose();

            conn.Close();
 

            listBox1.DataSource = recordSet.Tables[0];

            listBox1.DisplayMember = "name";

            listBox1.ValueMember = "name";

            
 
 

        }

Open in new window

0
 
LVL 1

Expert Comment

by:sidkud
ID: 24125123
As you are displaying the data, you can use the read only and faster datareader class.
Regarding the connection string, yes you need to specify it every time you open a connection. However, you can put it in the web.config, or in constants or even a custom data access class and then use at multiple places.

private void btnFind_Click(object sender, EventArgs e)

{

	SqlConnection MyConn = new SqlConnection(@"Data Source=home-pc;initial catalog=db_siddharth;Integrated Security=True;");

	SqlCommand MyComm    = new SqlCommand("select * from table1 where name like '" + MyTextBox.Text + "%'", MyConn);

	MyConn.Open();

	SqlDataReader dr = MyComm.ExecuteReader();

	MyListBox.DataSource = dr;

	MyListBox.DataTextField = "Name";

        MyListBox.DataValueField = "Col1";
 

	MyListBox.DataBind();

	MyConn.Close();

}

Open in new window

0
 

Author Closing Comment

by:wilko100
ID: 31569317
Already got DB but good example, excellent again!!  Thanks!! works a treat,  I have also created a server connection in VS 2008 that has created me a dataset and appconfig file with server properties which i now refer to everytime i bind to a datasource.
Once again thanks
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now