How do I connect gridview to sql using C# in asp.net?

Question

I know how to connect, open, read, close as you see below. I also have awesome tutorial how to add update/delete etc.

I can connect dataTable to sql using asp.net controls, but I want to learn how to manipulate it from C#.

MasterCust is my gridview table name. How do I connect the to it?

protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection Conn = new SqlConnection("Data Source=aserver;Initial Catalog=KennyCust;Persist Security Info=True;user id=sa;pwd=qwerty01");
        SqlDataReader rdr = null;
        string commandString = "SELECT * FROM MainDB";

        try
        {
            Conn.Open();
            SqlCommand Cmd = new SqlCommand(commandString, Conn);

            rdr = Cmd.ExecuteReader();

            while (rdr.Read())
            {
                Console.WriteLine(rdr[0]);
            }
        }
        finally
        {
            if (rdr != null)
            {
                rdr.Close();
            }
            if (Conn != null)
            {
                Conn.Close();
            }
        }
        //MasterCust.
        //MasterCust.DataSource = commandString;
        //MasterCust.DataBind();
    }

Edit: This code worked

            try
        {
            Conn.Open();
            SqlCommand Cmd = new SqlCommand(commandString, Conn);
            SqlDataAdapter sdp = new SqlDataAdapter(Cmd);
            DataSet ds = new DataSet();

            sdp.Fill(ds);
            //rdr = Cmd.ExecuteReader();
            MasterCust.DataSource = ds.Tables[0];
            MasterCust.DataBind();


            }

Shyju · Accepted Answer · 2011-12-31 03:01:48Z

1

Set the GridView's Datasource property and simply call the DataBind method.

This code will work. ( Tested)

 SqlConnection Conn = new SqlConnection("Data Source=Localhost\\SQLEXPRESS;Initial Catalog=Flash2;Integrated Security=True;");
 SqlDataReader rdr = null;
 string commandString = "SELECT * FROM USER_MASTER";

 try
 {
        Conn.Open();
        SqlCommand Cmd = new SqlCommand(commandString, Conn);
        rdr = Cmd.ExecuteReader();

        MasterCustView.DataSource = rdr;
        MasterCustView.DataBind();
 }
 catch (Exception ex)
 {
      // Log error
 }
 finally
 {
     if (rdr != null)
     {
         rdr.Close();
     }
     if (Conn != null)
     {
         Conn.Close();
     }
  }

edited Dec 31, 2011 at 3:01

answered Dec 31, 2011 at 1:30

Shyju

220k106 gold badges420 silver badges499 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

healxph0enix Over a year ago

Where is myReader coming from?

healxph0enix Over a year ago

Is it a bad sign I get an error without the catch? If I add catch in it works fine, but it loads really slow...could that be from bad coding?

Shyju Over a year ago

That code should work. I tested it. For slowness, How much data you are talking about ? 1 million records ? Do you have proper indexes in the tables ?

Collectives™ on Stack Overflow

How do I connect gridview to sql using C# in asp.net?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related