Solved

C# Databinding to SQL

Posted on 2009-04-11
4
673 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

744 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

16 Experts available now in Live!

Get 1:1 Help Now