Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

C# Databinding to SQL

Posted on 2009-04-11
4
Medium Priority
?
683 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 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

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 …
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

926 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