Solved

Bind a Datagrid to an SQL Server table

Posted on 2004-08-18
4
216 Views
Last Modified: 2010-04-15
Hi,

   I have a table called country in the SQL Server database called DEV.

   I want to bind "Select * from country" to a DataGrid called DG_COUNTRY.

   Please give me the C# code to do that.



Thanks



Robert
0
Comment
Question by:rogaut1
4 Comments
 
LVL 9

Accepted Solution

by:
s_sansanwal earned 250 total points
ID: 11837453
You should try to this by yourself rather asking for full question as this would help you to learn.

Anyway, the solution to your question is

private void Form1_Load(object sender, System.EventArgs e)
            {
                  System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection("Initial Catalog=DEV;Data Source=localhost;Integrated Security=SSPI;");
                  System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
                  try
                  {
                        //connection.Open();
                        string sql = "SELECT * FROM country";
                        System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
                        command.CommandText = sql;
                        command.Connection = connection;
                        adapter.SelectCommand = command;
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        this.DG_COUNTRY.DataSource  = ds.Tables[0];
                  }
                  catch(Exception exp)
                  {
                        
                        MessageBox.Show(String.Format("An exception occurred : {0}. Please contact your system administrator.", exp.Message));
                  }
                  
                  }


Cheers,
S Sansanwal
0
 
LVL 3

Assisted Solution

by:nhewitt21
nhewitt21 earned 250 total points
ID: 11839311
is this for the web or for a windows form?

Here's my connections string that i put in the web.config (replace xxx with your details):
<appSettings>
      <add key="SQLDBConnectionString" value="server=xxx;database=xxx;uid=xxx;pwd=xxx;" />
</appSettings>

Here a web solution to get the data and put it into a dataset:
public static System.Data.DataSet Populate_Country()
{
      DataSet ds = new DataSet();
      SqlConnection oCn = new SqlConnection(ConfigurationSettings.AppSettings["SQLDBConnectionString"]);
      SqlCommand oCm = new SqlCommand("sp_Country#Select_All", oCn);
      oCm.CommandType = CommandType.StoredProcedure;

      SqlDataAdapter da = new SqlDataAdapter();
      da.SelectCommand = oCm;  
      da.Fill(ds);

      try
      {
            oCn.Open();
            oCm.ExecuteNonQuery() ;
      }
      catch (Exception ex)
      {
            ds.Tables.Add(ex.ToString());
      }      
      finally
      {
            oCm.Dispose();
            oCn.Dispose();
      }

      return ds;
}

Code to call the above function and bind to dataset:
DataSet ds = YourProject.location_of_file_containing_function.Populate_Country();

DG_COUNTRY.DataSource = ds;
DG_COUNTRY.DataBind();

Your stored procedure code:
CREATE PROCEDURE sp_Country#Select_All

AS
BEGIN TRAN
      SELECT * FROM Country

IF (@@error = 0)
BEGIN
      COMMIT TRANSACTION
      RETURN(0)
END
ELSE
BEGIN
      GOTO ErrorHandler
END
ErrorHandler:
ROLLBACK TRANSACTION
RETURN (1)
GO

I would use a stored procedure over inline sql code, its more secure.

if you want to implement sorting to your columns check this out:
http://www.experts-exchange.com/Programming/Programming_Languages/C_Sharp/Q_21082798.html
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
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…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

948 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

22 Experts available now in Live!

Get 1:1 Help Now