Logo
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
EPL Standings
 
 
Windows Azure

Integrating the SharePoint Server Object Model and the Entity Data Model (part 2) - Create a Meeting Scheduler Visual Web Part

8/17/2011 6:38:48 PM

2. Create a Meeting Scheduler Visual Web Part

2.1. Create a Meeting Scheduler Visual Web Part
  1. Open the Visual Studio solution you created in the previous exercise, right-click the solution, and select Add | New Project.

  2. Select the SharePoint installed templates, and then select Empty SharePoint Project.

  3. Provide a name for the project (for example, SchedulingWebPart), and click OK.

  4. When prompted, select Deploy As Farm Solution, and click Finish.

  5. Right-click the project, and select Add | New Item.

  6. Select SharePoint, and then select Visual Web Part. Provide a name for the Web Part (such as ScheduleWebPart), and click Add.

  7. When the Web Part item has been added, walk through the same process you did in the first exercise in this article to add the SQL Azure database by using the ADO.NET EDM. You can use the same name to configure the model (that is, CustomerEntities).

  8. After adding the EDM, create a UI that looks similar to the following image. To do this, right-click the Visual Web Part ASCX file (that is, ScheduleWebPartUserControl.ascx), and select View Designer.



    Note that you’ll add the controls in the following table to your Visual Web Part UI.

    Control TypeControl Name
    Labellblcompanies
    Drop Down ListdrpdwnlstStores
    LabellblDate
    CalendarclndrControl
    LabellblMeetingTime
    Drop Down ListdrpdwnMeetingTime
    LabellblLocation
    Drop Down ListdrpdwnStoreLocation
    CheckboxchkbxAddAnnouncement
    ButtonbtnSubmit
    LabellblStatus
    LabellblMessageToUser

  9. The code for the Visual Web Part UI is shown in the following code snippet. Note that the code has been trimmed to remove the default code that was created when you first created the Visual Web Part:

    ...
    <style type="text/css">
    .style1
    {
    color: #000066;
    font-family: Calibri;
    }
    </style>

    <p class="style1">
    <strong>Store Meeting Scheduler</strong></p>
    <table>
    <tr>
    <td><asp:Label ID="lblCompanies" runat="server" Font-Names="Calibri"
    Font-Size="Small" ForeColor="#000066" Text="Stores: " Font-Bold="True"></
    asp:Label></td>
    <td>
    <asp:DropDownList ID="drpdwnlstStores" runat="server" Font-Names="Calibri"
    Font-Size="Small" ForeColor="#000066" Height="24px" Width="307px">
    </asp:DropDownList>
    </td>
    </tr>
    <tr>
    <td><asp:Label ID="lblDate" runat="server" Font-Names="Calibri" Font-Size="Small"
    ForeColor="#000066" Text="Date:" Font-Bold="True"></asp:Label></td>
    <td><asp:Calendar ID="clndrControl" runat="server" Font-Names="Calibri"
    Font-Size="Small" ForeColor="#000066" Height="100px" Width="217px"></
    asp:Calendar></td>
    </tr>
    <tr>
    <td><asp:Label ID="lblMeetingTime" runat="server" Font-Names="Calibri"
    Font-Size="Small" ForeColor="#000066" Text="Time:" Font-Bold="True"></asp:Label>
    </td>
    <td>

    <asp:DropDownList ID="drpdwnMeetingTime" runat="server" Height="24" Width="302px"
    Font-Names="Calibri" Font-Size="Small" ForeColor="#000066">
    <asp:ListItem>Breakfast Meeting</asp:ListItem>
    <asp:ListItem>Lunch Meeting</asp:ListItem>
    <asp:ListItem>Dinner Meeting</asp:ListItem>
    </asp:DropDownList>

    </td>
    </tr>
    <tr>
    <td><asp:Label ID="lblLocation" runat="server" Font-Names="Calibri"
    Font-Size="Small" ForeColor="#000066" Text="Location:" Font-Bold="True">
    </asp:Label></td>
    <td>
    <asp:DropDownList ID="drpdwnStoreLocation" runat="server" Height="24"
    Width="302px"
    Font-Names="Calibri" Font-Size="Small" ForeColor="#000066">
    <asp:ListItem>Store Location</asp:ListItem>
    <asp:ListItem>Corporate</asp:ListItem>
    </asp:DropDownList>
    </td>
    </tr>

    <tr>
    <td>&nbsp;</td>
    <td><asp:CheckBox ID="chkbxAddAnnouncement" runat="server" Font-Names="Calibri"
    Font-Size="Small" ForeColor="#000066" Text="Add an Announcement"
    Font-Bold="True" /></td>
    </tr>
    <tr>
    <td>&nbsp;</td>
    <td><asp:Button ID="btnSubmit" runat="server" Font-Names="Calibri"
    Font-Size="Small" ForeColor="#000066" Text="Schedule"
    onclick="btnSubmit_Click" /></td>
    </tr>
    <tr>
    <td><asp:Label ID="lblStatus" runat="server" Font-Names="Calibri"
    Font-Size="Small" ForeColor="#000066" Text="Status:" Font-Bold="True">
    </asp:Label></td>
    <td><asp:Label ID="lblMessageToUser" runat="server" Font-Names="Calibri"
    Font-Size="Small" ForeColor="#000066" Text=" " Font-Bold="True"></asp:Label></td>
    </tr>
    </table>


  10. Right-click the ASCX file, and select View Code. In the code-behind, add the following bold code:

    using System;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Data.SqlClient;
    using System.Data;
    using Microsoft.SharePoint;
    using System.Collections.Generic;
    using System.Linq;

    namespace SchedulingWebPart.ScheduleWebPart
    {
    public partial class ScheduleWebPartUserControl : UserControl
    {
    CustomersEntities dc = new CustomersEntities();

    string queryString = "SELECT * from Customers.dbo.StoreInformation;";
    DataSet azureDataset = new DataSet();

    string mySPSite = "http://blueyonderdemo";

    protected void Page_Load(object sender, EventArgs e)
    {
    populateDropDownBox();
    }

    private void populateDropDownBox()
    {
    string storeName = "";

    string connectionString = "Server=tcp:<your server name>.database.windows.
    net;Database=" +
    "Customers;User ID=<your user ID>;Password=<your password>;
    Trusted_Connection=False;" +
    "Encrypt=True;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
    SqlDataAdapter adaptor = new SqlDataAdapter();
    adaptor.SelectCommand = new SqlCommand(queryString, connection);
    adaptor.Fill(azureDataset);

    foreach (DataRow custRecord in azureDataset.Tables[0].Rows)
    {
    storeName = custRecord[2].ToString();
    drpdwnlstStores.Items.Add(storeName);
    }
    }
    }

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
    string storeNameFromList = drpdwnlstStores.SelectedItem.ToString();
    string meetingLocation = drpdwnStoreLocation.SelectedItem.ToString();
    string meetingDate = clndrControl.SelectedDate.ToShortDateString();
    string meetingTime = drpdwnMeetingTime.SelectedItem.ToString();
    double meetingTimeHours = 0;

    if (meetingTime == "Breakfast Meeting")
    {
    meetingTimeHours = 8;
    }
    else if (meetingTime == "Lunch Meeting")
    {
    meetingTimeHours = 12;
    }
    else if (meetingTime == "Dinner Meeting")
    {
    meetingTimeHours = 17;
    }

    if (chkbxAddAnnouncement.Checked == true)
    {
    createAnEvent(storeNameFromList, meetingLocation, meetingDate,
    meetingTimeHours);
    createAnAnnouncement(storeNameFromList, meetingLocation, meetingDate);
    lblMessageToUser.Text = "A new event and an announcement have been " +
    "created for this meeting.";
    }
    else
    {
    createAnEvent(storeNameFromList, meetingLocation, meetingDate,
    meetingTimeHours);
    lblMessageToUser.Text = "A new event has been created for this
    meeting.";
    }
    }

    private void createAnAnnouncement(string storeNameFromList,
    string meetingLocation,
    string meetingDate)
    {
    string finalStoreLocation = "";

    var query = from store in dc.StoreInformations
    where store.StoreName== storeNameFromList
    select store;

    foreach (var item in query)
    {
    finalStoreLocation = item.StoreAddress;
    }

    using (SPSite site = new SPSite(mySPSite))
    {
    using (SPWeb web = site.OpenWeb())
    {
    web.AllowUnsafeUpdates = true;

    SPList list = web.Lists["Announcements"];
    SPListItem item = list.Items.Add();
    item["Title"] = storeNameFromList + " Meeting";
    item["Body"] = storeNameFromList + " Meeting";
    item["Store Address"] = meetingLocation + ": " +
    finalStoreLocation;
    item["Meeting Date"] = meetingDate;
    item.Update();

    web.AllowUnsafeUpdates = false;
    }
    }
    }

    private void createAnEvent(string storeNameFromList, string meetingLocation,
    string meetingDate, double meetingTimeHours)
    {
    string finalStoreLocation = "";

    DateTime startOfMeeting = DateTime.Parse(meetingDate).
    AddHours(meetingTimeHours);
    DateTime endOfMeeting = startOfMeeting.AddHours(2);

    var query = from store in dc.StoreInformations
    where store.StoreName == storeNameFromList
    select store;

    foreach (var item in query)
    {
    finalStoreLocation = item.StoreAddress;
    }

    using (SPSite site = new SPSite(mySPSite))
    {
    using (SPWeb web = site.OpenWeb())
    {
    web.AllowUnsafeUpdates = true;

    SPList list = web.Lists["Calendar"];
    SPListItem item = list.Items.Add();
    item["Title"] = storeNameFromList + " Meeting";
    item["Location"] = finalStoreLocation;
    item["Description"] = storeNameFromList + " Meeting";
    item["Start Time"] = startOfMeeting;
    item["End Time"] = endOfMeeting;
    item.Update();

    web.AllowUnsafeUpdates = false;
    }
    }

    }
    }
    }


    You’ll recognize some of the code in this example because you created similar code in the first exercise to query the ADO.NET EDM and insert records into SharePoint. For example, you’ll recognize the code that issues a SQL command to query the SQL Azure database, the code that sets the ADO.NET EDM data context and queries for a specific record, and the code that inserts the record (based on the retrieved information from SQL Azure) into SharePoint. (Note that in this example, though, you hard-coded the SQL connection string [including credentials]; this is something you would never do in practice. You would need to include these separately, such as string variables in the project’s resources.) Two differences to note in this code, though, are as follows:

    • The different data types across the SharePoint lists (for example, DateTime and string) and how you need to convert data that you’re retrieving from the Visual Web Part controls.

    • How you’re creating simple conditionals with the Morning, Lunch, or Dinner options in the meeting time drop-down list (drpdwnMeetingTime). Having three conditionals here made it more optimal when using the AddHours method in the example.

  11. Now that you’ve created the Visual Web Part, you need to configure SharePoint for the EDM you just added. To do this, double-click the app.config file in your Visual Studio project to open it. Copy the connectionStrings setting from the app.config file, and then navigate to the SharePoint web.config file (that is, C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config). Paste the connectionStrings configuration at the end of the web.config file as in the following code listing:

    <configuration>
    ...
    <connectionStrings>
    <add name="CustomersEntities" connectionString="metadata=res://*/AzureStores.
    csdl|res://*/AzureStores.ssdl|res://*/AzureStores.msl;provider=System.Data.
    SqlClient;provider connection string=&quot; Data Source=<server name>.database.
    windows.net; Initial Catalog=Customers;
    Persist Security Info=True; User ID=<user ID>; Password=<password>; MultipleActiveRes
    ultSets=False&quot;" providerName="System.Data.EntityClient" />
    </connectionStrings>
    </configuration>


  12. Return to the Visual Studio project. Right-click the SharePoint project, and select Deploy. (Be sure that you’ve updated the Site URL property to point to your local SharePoint site.)

  13. After the project has successfully deployed, navigate to your SharePoint site. Click Site Actions | Edit Page, select the Insert tab, and click Web Part.

  14. Navigate to where you deployed your Visual Web Part (that is, to the SP And Azure category), select the Web Part, and click Add. Save the page to propagate your changes to the server.

  15. After you’ve added the Web Part to SharePoint, select a store by using the autopopulated drop-down list. Then select a date and a time of day for the meeting. Select Store Location (because we didn’t add any code to handle the Corp Location), and select the Add An Announcement check box. Click Submit when done.

The result is shown in Figure 1.

Figure 1. The Scheduling Web Part deployed to SharePoint.


When you’re done, you should see the Status label automatically update with a message that indicates that both an event and an announcement have been added to the SharePoint site. Thus, the Visual Web Part has used both SQLDataAdapter and the ADO.NET EDM to query and filter SQL Azure data and then insert data into SharePoint by using the server object model. If you navigate to the Announcements and Calendar lists, you should see the newly added list items. Figure 2 illustrates the newly added calendar event that has inserted data from SQL Azure into the list item.

This example provided a glimpse into the process of creating a Visual Web Part that uses both the ADO.NET EDM and the SQLDataAdapter object to interact with SQL Azure. You also saw how you used the server object model to insert data into SharePoint.

However, what if you want to do something more complex; what if you want to use a remote API that uses Silverlight as the client and perhaps even pulls in another cloud service or data source beyond SQL Azure into your application? This is absolutely possible, and remarkably, it’s not only straightforward, but you can also do something similar to what you created in this article using Business Connectivity Services (BCS). That is, you can use SharePoint Designer to create an external list pointing to the StoreInformation table in the Customers database, and then you can use the external list to integrate with Bing Maps and Silverlight to dynamically load data from SQL Azure (via the external list) into a Silverlight application. This example not only illustrates how you can integrate multiple technologies (for example, Bing Maps, SharePoint, Silverlight, and SQL Azure), but also shows how you can use Silverlight to create a more dynamic user experience in SharePoint. You’ll walk through this example in the next section.

Figure 2. A newly added calendar item.

Other -----------------
- Collaborating on Presentations : Evaluating Web-Based Presentation Applications (part 2)
- Collaborating on Presentations : Evaluating Web-Based Presentation Applications (part 1)
- Securing the Connection to SQL Azure (part 3) - Surface SQL Azure Data in a Visual Web Part
- Securing the Connection to SQL Azure (part 2) - Set Permissions for an External Content Type
- Securing the Connection to SQL Azure (part 1) - Create an Application ID & Create an External Content Type
- Consuming SQL Azure Data : Integrating SQL Azure with BCS by Using SharePoint Designer 2010
- Collaborating on Databases : Exploring Web-Based Databases
- Consuming SQL Azure Data : Introduction to Business Connectivity Services
- Integrating Silverlight, Windows Azure DataMarket, and SharePoint
- Local Application for Updates : JupiterMotorsERP local application
- Collaborating on Spreadsheets : Exploring Web-Based Spreadsheets (part 3)
- Collaborating on Spreadsheets : Exploring Web-Based Spreadsheets (part 2)
- Collaborating on Spreadsheets : Exploring Web-Based Spreadsheets (part 1) - Google Spreadsheets
- Integrating DataMarket Data with a Visual Web Part : Create a WCF Service to Retrieve DATA.gov Crime Data (part 2)
- Integrating DataMarket Data with a Visual Web Part : Create a WCF Service to Retrieve DATA.gov Crime Data (part 1)
- Integrating DataMarket Data with Excel and SharePoint - Consume Windows Azure Data in Excel 2010
- Using Cloud Services : Collaborating on Word Processing (part 2)
- Using Cloud Services : Collaborating on Word Processing (part 1)
- Using Cloud Services : Collaborating on Project Management
- Windows Azure Marketplace DataMarket (part 2)
 
 
Most view of day
- SQL Server 2012 : Running SQL Server in A Virtual Environment - MONITORING VIRTUALIZED DATABASE SERVERS
- Adobe Dreamweaver CS5 : Working with Multimedia and Online Tools - Checking for Plug-ins
- Windows Server 2012 : DHCP,IPv6 and IPAM - Understanding the Components of an Enterprise Network
- Troubleshooting Hardware and Performance : First Aid for Troubleshooting Hardware
- Configuring Startup and Troubleshooting Startup Issues : What's New with Windows Startup
- System Center Configuration Manager 2007 : Reporting Configuration (part 3) - Console Reporting Links, Relational Database Concepts
- SQL Server 2012 : Running SQL Server in A Virtual Environment - IDENTIFYING CANDIDATES FOR VIRTUALIZATION
- Microsoft Dynamics CRM 4.0 : Infrastructure Design Considerations - Windows SharePoint Integration
- SQL Server 2008 R2 : Performance Monitoring Tools (part 2) - Installing and Configuring the Data Collector
- Microsoft Exchange Server 2010 : Completing Transport Server Setup (part 2) - Configuring the Transport Dumpster , Configuring Shadow Redundancy
Top 10
- Windows Server 2012 : DHCP,IPv6 and IPAM - Exploring DHCP (part 3) - Creating IPv4 DHCP Scopes
- Windows Server 2012 : DHCP,IPv6 and IPAM - Exploring DHCP (part 2) - Installing DHCP Server and Server Tools
- Windows Server 2012 : DHCP,IPv6 and IPAM - Exploring DHCP (part 1)
- Windows Server 2012 : DHCP,IPv6 and IPAM - Understanding the Components of an Enterprise Network
- Microsoft OneNote 2010 : Using the Research and Translate Tools (part 3) - Translating Text with the Mini Translator
- Microsoft OneNote 2010 : Using the Research and Translate Tools (part 2) - Translating a Word or Phrase with the Research Pane
- Microsoft OneNote 2010 : Using the Research and Translate Tools (part 1) - Setting Options for the Research Task Pane, Searching with the Research Task Pane
- Microsoft OneNote 2010 : Doing Research with Linked Notes (part 2) - Ending a Linked Notes Session, Viewing Linked Notes
- Microsoft OneNote 2010 : Doing Research with Linked Notes (part 1) - Beginning a Linked Notes Session
- Microsoft OneNote 2010 : Doing Research with Side Notes (part 3) - Moving Side Notes to Your Existing Notes
 
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro