Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

C# Databinding to SQL

Posted on 2009-04-11
4
Medium Priority
?
681 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

719 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