Solved

Bind a Datagrid to an SQL Server table

Posted on 2004-08-18
4
215 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

743 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

11 Experts available now in Live!

Get 1:1 Help Now