Exercise 4: Creating a Visual Web Part that is Integrated with SQL Azure

Task 1 – Creating a new Visual Web Part that connects to the StoreInformation SQL Azure database

1. Open Visual Studio 2010. Start > All Programs > Microsoft Visual Studio 2010 > Visual Studio 2010. Run as Administrator.
2. Create a new Visual Web Part project. File > New Project > SharePoint > Empty SharePoint Project > Provide a Name > Deploy as Farm Solution > Finish.
3. Right-click the newly created project and add a new visual web part. Add > New Item > Visual Web Part. Provide a name for the web part (e.g. SQLAzureVisualWebPart).
4. Right-click the .ascx file (e.g. SQLAzureVisualWebPart.ascx) and select View Designer. Click Source.

5. Add the bolded code below to the ascx file. Double-click the linkbutton to add an event to the code behind.

   ASP.NET

   <%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>

   FakePre-45385d484c0c4a7cb7bde31cd2fd65ed-c3402a7cf52f40ac9940e2cf54d67751FakePre-42a404134b8642c59d4cd7a75f75d1de-75e117621e734b4a86844601d060910eFakePre-23d3714ddc264acaa64af8bf6277cae6-262579edec524d5e914c761626b2eb0cFakePre-7f521c1865f44cefbe107d6e33f5cb29-de9c42afea0e4ba790839c4d5a362942FakePre-bfe0dba81ae44724b6987387794a7f57-c81f0e8dd6f840d283cccd8bd8ade5bbFakePre-e8fec9f5a349444b852fa502e1b98a7a-1abed22c1dcf493ea39203390c772b2dFakePre-5e57ad0b6f53467eb5799eba405d7973-8bf3ca0872b34057b2eb26fb1f740da9<style type="text/css"> .style2 { font-family: Calibri; font-size: small; color: #000066; } </style> <table> <tr> <td> <span class="style2"> <strong>Store Information</strong></span> </td> </tr> <tr> <td> <asp:GridView ID="datagrdStoreData" runat="server" BackColor="White" style="font-family: Calibri; font-size: small"> <AlternatingRowStyle BackColor="#99CCFF" ForeColor="Black" /> <HeaderStyle BackColor="#0099CC" ForeColor="White" /> </asp:GridView> </td> </tr> <tr> <td> <asp:LinkButton ID="lnkbtnGetStoreInformation" runat="server" style="font-family: Calibri; font-size: small" onclick="lnkbtnGetStoreInformation_Click">Get Store Data</asp:LinkButton> </td> </tr> </table>

6. Add the following bolded code to the code-behind. This will use a SQL Data Connection string to load and bind the data to a data-grid.

   C#

   using System;

   FakePre-dc638947b19b438baeec1b154065e4d9-cc6053730d934bb093694eeb07ccf4b1FakePre-6a8a873ad6864529921257db3e63b5f3-cd3bd89812d34441bf1f93940bf56999FakePre-ed79909041d141b4a3f9758754b1b072-6b5be0b54fd9464595994422235c86d8using System.Data; using System.Data.SqlClient; string queryString = "SELECT * from Customers.dbo.StoreInformation;"; DataSet azureDataset = new DataSet(); protected void lnkbtnGetStoreInformation_Click(object sender, EventArgs e) { string connectionString = "Server=tcp:SERVER.database.windows.net;Database=Customers;User ID=USER@SERVER;Password=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); datagrdStoreData.DataSource = azureDataset; datagrdStoreData.DataBind(); } }FakePre-ab74f0f591ba42bfac13f8139dbfe561-ac84e719ab714639907dcaf4c6a994c3FakePre-8ffd1a199bfe47658b3c253bc6fa73f8-b81b334aa9af456bb099a490c382aaf7FakePre-b7e44bd3f4094262b415d2f5d99a3044-774041b042024855b0e9ab13d21bc369FakePre-bb7331b9b1d6462f929e740cc74f9d48-058e076f59074fe2889bf53210a1c214FakePre-7861091f4fc3443e98582d0a09276315-40a3a7436e2f4ea0b805bc4ac072b344using System.Data; using System.Data.SqlClient; string queryString = "SELECT * from Customers.dbo.StoreInformation;"; DataSet azureDataset = new DataSet(); protected void lnkbtnGetStoreInformation_Click(object sender, EventArgs e) { string connectionString = "Server=tcp:SERVER.database.windows.net;Database=Customers;User ID=USER@SERVER;Password=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); datagrdStoreData.DataSource = azureDataset; datagrdStoreData.DataBind(); } }FakePre-e9adbac9f657438f907fa865397f9b91-f15ce7dca6054724822723436e107907FakePre-4d71c697dbcf423e9bd1298b26edfe54-07d15b38ea5d4ed6bda2c00d4aba4d21FakePre-94d9ffa460af4acda2138a0713ed4fa4-aefc95ffd8ea4caf8ea71ca45b3edb9eFakePre-bbeb650ae8544da1b6a47d716ae1f927-02cec5d473974e30bfcbee9d5fd5b233FakePre-2e0c23fee53c4e41accf392281f107e5-016e973e7f3c46859038c2648e940596using System.Data; using System.Data.SqlClient; string queryString = "SELECT * from Customers.dbo.StoreInformation;"; DataSet azureDataset = new DataSet(); protected void lnkbtnGetStoreInformation_Click(object sender, EventArgs e) { string connectionString = "Server=tcp:SERVER.database.windows.net;Database=Customers;User ID=USER@SERVER;Password=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); datagrdStoreData.DataSource = azureDataset; datagrdStoreData.DataBind(); } }FakePre-72c434f7c1514557a2fbd07cc161cc55-322b31c01654464eb844307ff6589853FakePre-666e52a12b6c4a37b91b5bfbed1ac967-ba752f292ee248f2bd21a3530ca11d6dFakePre-5ea4356c463d4846aa1a27b944985c4f-2992f06a5d354206893defcd15835365FakePre-aaee5fd2fbaa40978a15de14076e202e-09a0798ad15f4277a08ae5be96938075

7. Amend the Title and Description properties of the .webpart file, as is bolded below.

   XML

   <?xml version="1.0" encoding="utf-8"?>

   FakePre-55645858792b4d7d9a95a2a252abad36-e213226813aa4301a18c0e104cfec208FakePre-c60297ebff544b7cad85ce99d2f7f59d-c1b00cd544774bae99c68a7a1e016bffFakePre-388d371e46d54c2aa030f1c03f10e8a6-181a99d4a43d449f80f37f822550f431FakePre-faa13dbada98457897276ac2658e9add-4444c3ff390245a191437fd341dd9586FakePre-532e1eb0625a4cdea5767870cc6b034f-16041027a6a34334828b92764e0d9548FakePre-843d1bbebe9a46c1b877e7a11c74efac-3b66f7f2d2414ddfaadeff2ca241c6e4FakePre-30bf30614ef0435c9abe711b9e571ba0-b28217135a804386966aaa9d54589f3aFakePre-3e19bc67ba44447988d7333d802b5bcb-7e333aac9aa444f1a51920a468b26561 <property name="Title" type="string">SQL Azure Visual Web Part</property> <property name="Description" type="string">A visual web part that loads SQL Azure data using a SQL Connection string.</property>FakePre-d2fa47a1f7194fa9b5de5b7a3592c69a-3df0419d185c4ce68044ef8760972adcFakePre-566316c6587349f39292094ac2c45735-6caff436d2154fd2be1643d62cb3f841FakePre-0d9d57ac634d44c9b0c8f74ab424ecf0-22da9aff073541f1bb1edf1301e3e869FakePre-543a892d59104139a217cb564bca6785-748a9f99d42341f8849233ad5d474323

8. Deploy and test the web part. Right-click the project > Deploy.
9. Navigate to your SharePoint site and add the new web part. Site Actions > Edit Page > Insert > Web Part > Custom. Select your new visual web part and click Add.

10. When the visual web part loads, click the Get Store Data linkbutton to retrieve the data from SQL Azure.

    Figure 21

    SQL Azure Visual Web Part