Surfacing Business Data in a Web Part that you Create by Using SharePoint Tools in Visual Studio

So you add a Visual Web Part to your SharePoint solution and you want the Web Part to show some data. No problem. I’ll show you three ways to do it. This post shows you how to surface data in your Visual Web Part by performing the following tasks:

• Binding data to a GridView at design time.
• Binding data to a GridView by using LINQ to SQL code.
• Binding data to a GridView by querying the Business Data Catalog.

I have also published a sample that shows these tasks here. For now, let’s make some data appear!

Binding Data to a GridView at Design Time

1. In Visual Studio, create a Visual Web Part project.

2. From the Toolbox, drag a GridView to the designer.

3. Attempt to configure a data source for the GridView. Wait! Where can I select Database? I only see XML File. Could this be a bug?

Nope. This is not a bug. The ability to configure other data sources by using the Data Source Configuration Wizard is a feature that has not been implemented in the Visual Web Designer for SharePoint projects in Visual Studio. You have two options.

· Write XML that defines the data source in the Source view of the designer (Not fun).

· Configure your data source in an ASP.NET project and then copy over the data source object (fun).

For this post, I’ll configure a SQLDataSource object in an ASP.NET project. Then, just copy control over to my Visual Web Part project. Then, I will bind the GridView to that control.

1. In Visual Studio, create a new ASP.NET Web Application project.

2. From the Toolbox, drag a SQLDataSource control to the designer of any page.

3. Configure the control by using the Data Source Configuration Wizard (Shown earlier). Use the wizard to generate create, update, delete, and select commands.

4. In the designer of the ASP.NET project, copy the SQLDataSource control.

5. In the designer of the Visual Web Part project, paste the SQLDataSource control.

6. Bind the GridView to that data source.

7. In the GridView Tasks dialog box, select Enable Editing, and Enable Deleting. Selecting these options will add Edit and Delete links to the GridView.

8. Finally, add the name of the primary key column of your database table to the DataKeyNames property of the GridView.

9. Design the rest of the GridView (look and feel etc.).

If you open the designer in Source view, your GridView should look something like this:

GridView

 <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" DataKeyNames="CustomerID" PageSize="3" EnableModelValidation="True">

  <Columns>

    <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />

  </Columns>

</asp:GridView>

SQLDataSource

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString%>" ProviderName="System.Data.SqlClient"

  SelectCommand="SELECT TOP 3 [CustomerID], [FirstName], [MiddleName], [LastName] FROM [SalesLT].[Customer]"

  DeleteCommand="DELETE FROM [SalesLT].[Customer] WHERE [CustomerID] = @CustomerID"

  InsertCommand="INSERT INTO [SalesLT].[Customer] ([FirstName], [MiddleName], [LastName]) VALUES (@FirstName, @MiddleName, @LastName)"

  UpdateCommand="UPDATE [SalesLT].[Customer] SET [FirstName] = @FirstName, [MiddleName] = @MiddleName, [LastName] = @LastName WHERE [CustomerID] = @CustomerID">

  <DeleteParameters>

    <asp:Parameter Name="CustomerID" Type="Int32" />

  </DeleteParameters>

  <InsertParameters>

    <asp:Parameter Name="FirstName" Type="String" />

    <asp:Parameter Name="MiddleName" Type="String" />

    <asp:Parameter Name="LastName" Type="String" />

  </InsertParameters>

  <UpdateParameters>

    <asp:Parameter Name="FirstName" Type="String" />

    <asp:Parameter Name="MiddleName" Type="String" />

    <asp:Parameter Name="LastName" Type="String" />

    <asp:Parameter Name="CustomerID" Type="Int32" />

  </UpdateParameters>

</asp:SqlDataSource>

A Quick note about Connection Strings

Notice the following line in the above source:

ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString%>"

This line grabs the connection string from the web.config file of the root site. This is one way to persist a connection string. There are other ways to do this as well. You can read about them in the MSDN topic Managing Application Configuration.

The sample contains code that adds the connection string to web.config by using code in the feature receiver of the Visual Web Part project.

Bind Data to a GridView by using LINQ to SQL Code

There are probably a dozen ways to structure this. Here is one way to do it.

· Create a class library.

· Generate objects to access the data.

· Add methods that retrieve, edit, and delete the data.

· Bind the GridView to those methods.

Create a Class Library

1. Add a Class Library project to your solution.

2. Compile the project.

3. Strong name sign the assembly of the project. You can read more about how to do that here.

4. Add the assembly to the package manifest. You can read more about how to do that here.

Generate objects to access the data

1. Add a new data source to the Class Library project. You can read more about how to do that here.

2. Add a LINQ to SQL Classes item to the Class Library project.

3. In Server Explorer, drag a table onto the O/R Designer.

This creates an Entity that represents the table. Your code will read, edit, and delete data by using the entity not the actual table.

Add methods that retrieve, edit, and delete the data

1. In the class file of the Class Library project, add code to retrieve, update and delete data.

Example:

 public class DAL

{

  public static AdventureWorksDataContext GetDataContext()

  {

    string strConnString = ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ConnectionString;

    return new AdventureWorksDataContext(strConnString);

  }

  public static IEnumerable<Customer> GetCustomers()

  {

    AdventureWorksDataContext dataContext = GetDataContext();

    IEnumerable<Customer> myCustomers = from customers in dataContext.Customers.Take(3)

                                        select customers;

    return myCustomers;

  }

  public static void UpdateCustomer(int customerID, string firstName, string middleName, string lastName)

  {

    AdventureWorksDataContext dataContext = GetDataContext();

    var CustomerToUpdate = (from Customers in dataContext.Customers

                            where Customers.CustomerID == customerID

                            select Customers).Single();

    CustomerToUpdate.CustomerID = customerID;

    CustomerToUpdate.FirstName = firstName;

    CustomerToUpdate.MiddleName = middleName;

    CustomerToUpdate.LastName = lastName;

    dataContext.SubmitChanges();

  }

  public static void DeleteCustomer(int CustomerID)

  {

    AdventureWorksDataContext dataContext = GetDataContext();

    Customer Customer =(from Customers in dataContext.Customers.AsEnumerable().Take(5)

                        where Customers.CustomerID == CustomerID

                        select Customers).Single();

    dataContext.Customers.DeleteOnSubmit(Customer);

    dataContext.SubmitChanges();

  }

}

Binding the GridView to your methods

1. In the designer of your Visual Web part, create event handlers for the RowCancelingEdit, RowDeleting, RowEditing, and RowUpdating events of the GridView.

2. Right-click the designer and then click View Code.

3. Add code for each of the event handlers.

Example:

 public partial class VisualWebPart1UserControl : UserControl

{

  protected void Page_Load(object sender, EventArgs e)

  {

    if (!IsPostBack)

    {

      BindGridView(GridView2);

    }

  }

  private void BindGridView(GridView GridView)

  {

    GridView.DataSource = DAL.DAL.GetCustomers();

    GridView.DataBind();

  }

  protected void GridView_RowDeleting(object sender, GridViewDeleteEventArgs e)

  {

    GridView TempGridView = (GridView)sender;

    TableCell cell = TempGridView.Rows[e.RowIndex].Cells[1];

    DAL.DAL.DeleteCustomer(Convert.ToInt32(cell.Text));

    BindGridView(TempGridView);

  }

  protected void GridView_RowEditing(object sender, GridViewEditEventArgs e)

  {

    GridView TempGridView = (GridView)sender;

    TempGridView.EditIndex = e.NewEditIndex;

    BindGridView(TempGridView);

  }

  protected void GridView_RowUpdating(object sender, GridViewUpdateEventArgs e)

  {

    int CustomerID = 0;

    string FirstName ="";

    string MiddleName="";

    string LastName="";

    GridView TempGridView = (GridView)sender;

    GridViewRow gvr = TempGridView.Rows[e.RowIndex];

    for (int i = 0; i < gvr.Cells.Count; i++)

    {

      switch (TempGridView.HeaderRow.Cells[i].Text)

      {

        case "CustomerID":

          CustomerID = Convert.ToInt32(((TextBox)(gvr.Cells[i].Controls[0])).Text);

          break;

        case "FirstName":

          FirstName = ((TextBox)(gvr.Cells[i].Controls[0])).Text;

          break;

        case "MiddleName":

          MiddleName = ((TextBox)(gvr.Cells[i].Controls[0])).Text;

          break;

        case "LastName":

          LastName = ((TextBox)(gvr.Cells[i].Controls[0])).Text;

          break;

        default:

          break;

       }

    }

    DAL.DAL.UpdateCustomer(CustomerID, FirstName, MiddleName, LastName);

    TempGridView.EditIndex = -1;

    BindGridView(TempGridView);

  }

  protected void GridView_RowUpdated(object sender, GridViewUpdatedEventArgs e)

  {

    GridView TempGridView = (GridView)sender;

    e.KeepInEditMode = false;

    TempGridView.EditIndex = -1;

  }

  protected void GridView_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

  {

    GridView TempGridView = (GridView)sender;

    TempGridView.EditIndex = -1;

    BindGridView(TempGridView);

  }

}

Binding Data to a GridView by Querying the Business Data Connectivity Service

Perhaps your organization has a bunch of data in the Business Data Connectivity Service in SharePoint. No problem. You can bind your GridView to that too.

<Quick Shameless Plug>

If you are not familiar with the Business Data Connectivity Services in SharePoint, it is the coolest thing since ice cubes. You can read more about it here. Visual Studio has a project template to help deploy data models to the service. You can read more about that here.

</Quick Shameless Plug>

1. Add a class to your Class Library project.

2. Add a reference to the Microsoft.BusinessData assembly. Unfortunately, you won’t find that assembly in the .NET tab of the Add Reference dialog box. I found it by browsing to this location - C:\program files\common files\microsoft shared\web server extensions\14\ISAPI\Microsoft.BusinessData.dll.

3. In your class, add the following using (or for VB Imports) statements:

using Microsoft.SharePoint;

using Microsoft.SharePoint.BusinessData.SharedService;

using Microsoft.BusinessData.MetadataModel;

using Microsoft.SharePoint.Administration;

4. Add code.

Example:

 class BDC_DAL

{

  const string nameSpace = "AdventureWorksCustomers.BdcModel1";

  const string entityName = "Customer";

  private static IMetadataCatalog GetCatalog()

  {

    BdcService service = SPFarm.Local.Services.GetValue<BdcService>(String.Empty);

    IMetadataCatalog catalog = service.GetDatabaseBackedMetadataCatalog(

    SPServiceContext.Current);

    return catalog;

  }

  public static object GetCustomers()

  {

    IMetadataCatalog catalog = GetCatalog();

    IEntity entity = catalog.GetEntity(nameSpace, entityName);

    ILobSystemInstance LobSysteminstance = entity.GetLobSystem().

    GetLobSystemInstances()[0].Value;

    IMethodInstance methodInstance = entity.GetMethodInstance("ReadList", MethodInstanceType.Finder);

    object result = entity.Execute(methodInstance, LobSysteminstance);

    return result;

  }

  public static void UpdateCustomer(int CustomerID, string FirstName, string MiddleName, string LastName)

  {

    IMetadataCatalog catalog = GetCatalog();

    IEntity entity = catalog.GetEntity(nameSpace, entityName);

    ILobSystemInstance LobSysteminstance = entity.GetLobSystem().

    GetLobSystemInstances()[0].Value;

    IMethodInstance methodInstance = entity.GetMethodInstance ("Update", MethodInstanceType.Updater);

    object[] args = { CustomerID, FirstName, MiddleName, LastName };

    entity.Execute(methodInstance, LobSysteminstance, ref args);

  }

  public static void DeleteCustomer(int CustomerID)

  {

    IMetadataCatalog catalog = GetCatalog();

    IEntity entity = catalog.GetEntity(nameSpace, entityName);

    ILobSystemInstance LobSysteminstance = entity.GetLobSystem().

    GetLobSystemInstances()[0].Value;

    IMethodInstance methodInstance = entity.GetMethodInstance("Delete", MethodInstanceType.Deleter);

    object[] args = { CustomerID };

    entity.Execute(methodInstance, LobSysteminstance, ref args);

  }

}

5. Now, just call these methods from the event handlers of your GridView.

Surfacing data in a Visual Web Part, application page, or a user control, is not a whole lot different than the way that you do this in a plain vanilla ASP.NET Web application. Here a few take-away points:

· You can configure data sources visually by using an ASP.NET project and port over your data source object. – or – you can just write XML that describes the data source yourself.

· If you use a separate assembly for your data access code, make sure to sign the assembly and add that assembly to the package manager.

You can get to data described in the Business Data Connectivity service by using the Business Data Connectivity services object model.

Norm Estabrook

Comments

• Anonymous
  January 27, 2011
  The comments are not really very true: "Nope. This is not a bug. The ability to configure other data sources by using the Data Source Configuration Wizard is a feature that has not been implemented in the Visual Web Designer for SharePoint projects in Visual Studio" I followed some other click to right click on toolbox and remove sql data source and click ok. then again I click and add it again. The Sql data source started showing in toolbox for sharepoint project. It is working well. Nilesh

• Anonymous
  September 06, 2011
  Nilesh: could you please clarify?  If I right click on the "Data" group in the toolbox, then select "show all", I can see the SqlDataSource, but it is grayed out.  If I try to drag it out, it won't let me.

• Anonymous
  January 04, 2016
  This is a great article, and was exactly what I was looking for. I mapped SQL Data Source to the Insert, Delete, and Update commands and everything worked perfectly. Thanks Norm!

• Anonymous
  January 04, 2016
  I recommend using a SPSqlDataSource to get past the Trusted Provider error: "This control does not allow connection strings with the following keywords: ‘Integrated Security’, ‘Trusted_Connection’"