Sharepoint SPGridView Data Reneder-SQL

Question

I am tryiny to display a column from SQL Server Databse in Sharepoint Environment. I am using SPGridView for Consistent look and feel.

There is stored procedure called "sp_GetMyApplications" which gives all the records in the table.

I do not know, what I am missing. I cannot attach worker process to debug because it says the breakpoint cannot be hit.

Can anyone help me, What is wrong with this code and where should be the correction. This is how the webpart renders in the site when used.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;

using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;
using System.Text;

namespace Application
{
    [Guid("c51a50e8-661b-4711-bd5d-c73531c893f3")]
    public class Application : System.Web.UI.WebControls.WebParts.WebPart
    {
        SqlCommand cmd;
        SqlConnection con;
        SPGridView spGrid = new SPGridView();


        public Application()
        {
        }

        protected override void CreateChildControls()
        {




        }
        protected override void Render(HtmlTextWriter writer)
        {

            con = new SqlConnection("Data Source=.;Initial Catalog=MyProject;Integrated Security=True");
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;

            cmd.CommandText = "sp_GetMyApplications";
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            da.SelectCommand = cmd;
            da.Fill(ds);
            SPBoundField spFName = new SPBoundField();
            spFName.DataField = "FirstName";
            spGrid.DataSource = ds.Tables[0];
            spGrid.AutoGenerateColumns = false;
            spGrid.Columns.Add(spFName);
            spGrid.DataBind();
            writer.Write(spGrid);
        }
    }
}

Update:

I have modified my code as per the comments provided by the below users and my code works great.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;

using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;
using System.Text;


namespace EditData
{
    [Guid("3707b6d7-d757-4508-8bf4-edb35a53764f")]
    public class EditData : System.Web.UI.WebControls.WebParts.WebPart
    {
        public EditData()
        {
        }
        SqlCommand cmd;
        SqlConnection con;
        DataSet ds;
        SqlDataAdapter da;
        SPGridView spGrid = new SPGridView();


        protected override void CreateChildControls()
        {
            con = new SqlConnection("Data Source=.;Initial Catalog=MyProject;Integrated Security=True");
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;


            cmd.CommandText = "sp_GetMyApplications";
            cmd.CommandType = CommandType.StoredProcedure;
             da = new SqlDataAdapter();
            ds = new DataSet();

            da.SelectCommand = cmd;
            da.Fill(ds);

            spGrid.AutoGenerateColumns = false;
            spGrid.AllowSorting = true;
            spGrid.Sorting += new GridViewSortEventHandler(oGrid_Sorting);


            //Firstname
            SPBoundField spFName = new SPBoundField();
            spFName.DataField = "FirstName";
            spFName.SortExpression = "FirstName";
            spFName.HeaderText = "First Name";

            //Lastname
            SPBoundField spLName = new SPBoundField();
            spLName.DataField = "LastName";
            spLName.SortExpression = "LastName";
            spLName.HeaderText = "LastName";

            //ManagerName
            //Lastname
            SPBoundField spMName = new SPBoundField();
            spMName.DataField = "ManagerName";
            spMName.SortExpression = "ManagerName";
            spMName.HeaderText = "Manager Name";


            spGrid.DataSource = ds.Tables[0];
            spGrid.AutoGenerateColumns = false;
            spGrid.Columns.Add(spFName);
            spGrid.Columns.Add(spMName);

            spGrid.Columns.Add(spLName);

            spGrid.DataBind();
            this.Controls.Add(spGrid);
        }

        void oGrid_Sorting(object sender, GridViewSortEventArgs e)
        {
            string lastExpression = "";
            if (ViewState["SortExpression"] != null)
                lastExpression = ViewState["SortExpression"].ToString();

            string lastDirection = "asc";
            if (ViewState["SortDirection"] != null)
                lastDirection = ViewState["SortDirection"].ToString();

            string newDirection = "asc";
            if (e.SortExpression == lastExpression)
                newDirection = (lastDirection == "asc") ? "desc" : "asc";

            ViewState["SortExpression"] = e.SortExpression;
            ViewState["SortDirection"] = newDirection;
            spGrid.DataBind();
        }

    }
}

This is how it looks.. I have undescore for sorting, when clicked on them does not work. I know I am missing some thing.

I want the sorting and Editing bit. How do I do that? Thank you

Answer 1

Whilst I'm not really a developer, I am fairly certain that you shouldn't override the Render() method in SharePoint Web Parts as the ASP.NET framework handles rendering of the Web Part chrome (common UI elements) within this method.

To create child controls from a Web Part properly, you should override the CreateChildControls method.

As regards the specific code you are running, have you tried running this from within a console application to ensure that it works?

If it works, it could be a permissions issue - by default SharePoint will impersonate the current user so your account will need to have access to execute the "sp_GetMyApplications" stored procedure within the "MyProject" DB.

Answer 2

@BenjaminAthawes brings up some really good points in his answer:

1. You should be using CreateChildControls instead of Render (although I understand this is a test web part)
2. Permissions will probably be an issue.

There are a lot of resources already posted on the site that I think you should read:

1. As @VedranRasol points out in his answer on your other question, you really should read Erik Burger's excellent guide to building an SPGridView with an ObjectDataSource.
2. I recommend that your first iteration only use dummy data. This will help you get the grid working first without all the other moving parts. See my answer here for a complete working example.
3. After you have a working example, either:
   • Move your SQL code you have already into the ObjectDataSource select method
   • Use a SqlDataSource instead of an ObjectDataSource

See also, a similar high-level question someone asked about querying the SharePoint search index.

Answer 3

Have a look at this article http://blogs.msdn.com/b/powlo/archive/2007/02/25/displaying-custom-data-through-sharepoint-lists-using-spgridview-and-spmenufield.aspx shows how to use SPGridView with external datasources. Blog post is not specific to SharePoint 2010 but the approach still is the same

Answer 4

Start by moving your code to OnPreRender method override. Never override Render (RenderContent is ok for override) as you will loose the ability to create CSS adapters later if ou need it.

CCC is mostly used for adding controls, and has issues since its event get fired differently depending on if you are doing a postback or not.

I recommend using secure store service (SP2010) to store connection strings instead of hardcoding them!

Your breakpoint should trigger. Try and copy the PDB file from /bin to where file is located in GAC. Also doing IISRESET and re-opening Visual Studio have helped me before...