Visual Studio Data

Visual Studio 2010 Localized Code Samples Available!

RongLu — Mon, 09 Aug 2010 06:41:39 GMT

Visual Studio 2010 Localized Code Samples for all Visual Studio supported languages are now available on the Web!

You can find the samples by clicking on Help->Samples menu directly from within Visual Studio 2010.

And then click on Visual Studio 2010 Samples link on the following page.

Or, you can directly use following links to open the code samples page:

For example, C# samples in German look like this:

Enjoy!

SQL Azure Support in Visual Studio 2010

XiangZeng — Wed, 04 Aug 2010 19:00:21 GMT

Visual Studio 2010 supports data connections to SQL Azure, query designing, data manipulation, and data binding operations in your C#/VB applications.

Connecting to a SQL Azure Database

SQL Azure can be viewed as a subset of SQL Server 2008. Therefore, when you add a SQL Azure data connection inside Visual Studio (using .NET Framework Data Provider for SQL Server, i.e. SqlClient) , the user experience is basically the same as connecting to an on-premise SQL Server 2008 database. Here’s a screenshot showing the data connection to a SQL Azure database “Northwind”:

For more information on how to get a SQL Azure database, refer to the Getting Started Guide for SQL Azure.

When you expand the SQL Azure data connection node inside Server Explorer, you may notice that some nodes may not be available compared with a normal SQL Server 2008 connection. This is because these features are not supported by SQL Azure. The Visual Studio Properties Window can show properties for SQL Azure data connection properties as well as properties for SQL Azure database objects. However, SQL Server database or database object properties not supported in SQL Azure are either hidden or disabled (grayed out).

The following is part of the SQL Azure data connection properties. Note the data connection Type and Version:

Manipulating Data

After you have made a SQL Azure database connection, you can view and edit the data of a table or view. To manipulate data for a table from Server Explorer, right click the table, and choose “Show Table Data”. For views, the context menu is “Show Results”. In the following screenshot, the data of table Categories are shown in the data grid, allowing for inserts, updates, or deletes:

Designing Queries

The Query Designer of Visual Studio 2010 fully supports SQL Azure databases. To create a new query from Server Explorer, right click the data connection node, and choose “New Query”. The Query Designer will open and you have the same experience as working with an on-premise SQL Server database.

The following is a screenshot of Query Designer in action:

Data Definition

Although Table Designer, Database Diagrams and Script Editors are currently not supported in Visual Studio, you can use Query Designer to execute some basic SQL statements. Here are the steps to create a table in your SQL Azure database:

1. From Server Explorer, right click SQL Azure data connection node, and choose “New Query”.

2. Close the Add Table dialog.

3. In the SQL Pane, type desired SQL statements ( using semicolon to separate multiple statements). For example:
CREATE TABLE TestTable01 (id int PRIMARY KEY, name VARCHAR(30))

4. Execute SQL (from context menu, menu bar, Data top menu, or Ctrl-R)

5. There will be a dialog warning that CREATE TABLE statement is not supported by Query Designer. Click Continue button, as Query Designer will then pass the raw SQL statement to the database engine.

6. You should receive a message stating “Query execution succeeded.” Refresh the Tables node in Server Explorer and the table “TestTable01” should appear.

Here’s the screenshot of the above actions:

Alternatively, you can use SQLCmd to connect to your SQL Azure database and execute queries. For example:

sqlcmd -S MyAzureServer.database.windows.net -d Northwind -U MyAdmin@ MyAzureServer -P MyPassword –i INPUT_SQL_FILE

When you write your SQL scripts, note that some T-SQL statements are not supported by SQL Azure. In addition, if a feature is deprecated in SQL Server 2008, it becomes unsupported in SQL Azure. To find out what SQL statements are supported by SQL Azure, refer to the following links in MSDN:

· Supported Transact-SQL Statements (SQL Azure Database)

· Partially Supported Transact-SQL Statements (SQL Azure Database)

· Unsupported Transact-SQL Statements (SQL Azure Database)

Data Binding

Once you are able to connect to your SQL Azure database within Visual Studio, you can treat the data connection as a normal on-premise SQL Server database connection, and bind data in your applications using DataSet, Entity Framework or other data access technologies.

Suppose you already have your SQL Azure data connection inside Server Explorer, when you add a new data source in your C#/VB project, the connection will automatically show up in Data Source Configuration Wizard. For example, the following is a screenshot of the Entity Data Model Wizard (which can be invoked through the Data Source Configuration Wizard) when there is already a SQL Azure data connection inside Server Explorer:

For more information on how to use Data Sources in your applications, check the following blog posts:

· WPF Data Binding: Creating a Master-Details form in Visual Studio 2010

· WPF Data Binding: How to Bind DataSet to WPF Designer

Enjoy your data in the cloud!

Walkthrough of creating association between Sharepoint BDC entities using Visual Studio 2010

Yanchen — Tue, 03 Aug 2010 02:45:00 GMT

Here comes our third blog in BDC serials Walkthrough of creating association between Sharepoint BDC entities using Visual Studio 2010. This time we will show how to create Association between Sharepoint BDC entities using Visual Studio 2010 and use Business Data Web Parts to display the data and the association in SharePoint page.

"Up till now, we have published two blogs in this series about SharePoint 2010 external list, Walkthrough of creating a SharePoint 2010 external list using Visual Studio 2010 Beta and Walkthrough of enabling CRUD for SharePoint 2010 external lists using Visual Studio 2010. Now you are able to create external list and add basic CRUD functions. In this blog we will show how to create Association between Sharepoint BDC entities using Visual Studio 2010 and use Business Data Web Parts to display the data and the association in SharePoint page."......

Deploy your BDC model to a specific BCS Service using Visual Studio 2010

RongLu — Fri, 30 Jul 2010 08:42:00 GMT

Please read on this blog post if you want to deploy a BDC model to the desired SharePoint BCS service or if you ever run into issues like below...

"When deploying a BDC model in Visual Studio 2010, some people may see this error message “… There is no default Business Data Connectivity Service Proxy available for the given SPServiceContext…” or similar ones. This probably means the SiteUrl feature property is not appropriately set.

SiteUrl is a SharePoint feature property that determines where a feature will be deployed. When a BCS feature is deployed, SharePoint server will look up a BCS service (if there are more than one BCS service connected to the site, it will select the default one) connected to the appointed SiteUrl. If the SiteUrl property is omitted, it assumes the value of SiteUrl is http://localhost:80. The SharePoint BDC project in VS2010 helps you customize the feature properties like SiteUrl. Let me show you how you can easily manage SiteUrl property here.."

Class Designer PowerToys for Visual Studio 2010 is Released

Lie Wang — Tue, 27 Jul 2010 03:21:53 GMT

Class Designer PowerToys for Visual Studio 2010 is released today! It provides a bunch of enhancements to Visual Studio 2010 Class Designer. Click here to download it now.

It has many cool features like pan/zoom window, floating properties window, fast navigation, etc. You could find the detailed feature list in its help document which is available on the desktop after the Class Designer PowerToys is installed.

Besides the binary, the source code is also available. You could extend the PowerToys easily.

If you are using Visual Studio 2005 or 2008, please use the below links to get the previous releases:

- Class Designer PowerToys for Visual Studio 2008

- Class Designer PowerToys for Visual Studio 2005

Here is the link to its CodePlex site.

Enjoy!

WPF-EDM: Binding to Stored Procedures

Gavin Fu — Fri, 18 Jun 2010 06:05:00 GMT

Using Stored Procedures to Convert Database Data

When building WPF database applications, you might not want to display the raw data retrieved from data tables directly. Instead, you want to convert the raw data to make it more meaningful to display. You could convert the data in your WPF applications, or you could implement the converting logic in a stored procedure and bind to it in your application. Converting data using stored procedures provides benefits like:

· Modular programming

Stored procedures are stored together with data tables in the database. They could be shared by multiple applications and could be modified independently, which makes the converting logic more maintainable.

Also, stored procedures simplify application programming. Stored procedures might operate on multiple data tables. But application programmers don’t need to care about these details, they just need to concern the inputs and outputs.

· Faster execution

Stored procedures could be parsed and optimized when they are first executed, and a complied version of the stored procedures remains in memory cache for later use, which results in much faster execution.

Combining stored procedures with data binding and rich tooling support of visual studio 2010, you could easily build applications to transform and display database data. As an example, we will implement a master-detail view application to list total count of all products that a customer orders.

Sample Application - Data Model

The data model used by the sample application is shown like below. Four data tables are used here. Each customer could have multiple orders, and each order could contain multiple products.

In this application, we don’t want to display all details of a customer’s orders. Instead, we just want to display which products a customer buys, and for each product, what quantity does the customer buys in total. For this, we need to create a stored procedure, and store it in the database. Suppose the name is CustOrderHist:

ALTER PROCEDURE CustOrderHist @CustomerID nchar(5)

SELECT ProductName, Total=SUM(Quantity)

FROM Products P, [Order Details] OD, Orders O, Customers C

WHERE C.CustomerID = @CustomerID

AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID

GROUP BY ProductName

Sample Application – Step by Step

1. Open Visual Studio 2010, create a new C# or VB WPF application named “BindToSP”.

2. Add a new EDM project item (name it Northwind.edmx). In the wizard, choose “Generate from database”, select table “Customer” and stored procedure “CustOrderHist”, and click “Finish”.

3. In the “Model Browser” window of the EDM designer, double click “CustOrderHist” to open the “Add Function Import” dialog

4. In the dialog, click “Get Column Information” to get output columns of the stored procedure, and then click “Create New Complex Type” to generate a class (say, CustOrderHist_Result) corresponding to the returned columns.

Open Northwind.Designer.cs, you will see a new function is added to NorthwindEntities class:

public ObjectResult<CustOrderHist_Result> CustOrderHist(global::System.String customerID)

Public Function CustOrderHist(customerID As Global.System.String) As ObjectResult(Of CustOrderHist_Result)

5. Open MainWindow.xaml, use “Data -> Show Data Sources” to open the Data Source Window, add a new generic object data source, and choose the complex type generated at the step above.

The Data Source Window will be like below. Both Customers and CustOrderHist_Result are bound to DataGrid by default.

6. Drag drop Customers and CustOrderHist_Result from the Data Source Window to MainWindow.xaml designer.

Two CollectionViewSource instances will be added to the window’s resources, one for Customer and one for CustOrderHist_Result:

<Window.Resources>

</Window.Resources>

And two DataGrid are created, one bound to “customerViewSource”, and the other bound to “custOrderHist_ResultViewSource”.

Also, related procedure codes are generated to load data into the CollectionViewSource instances:

private System.Data.Objects.ObjectQuery<Customer> GetCustomersQuery(NorthwindEntities northwindEntities)

{

// Auto generated code

System.Data.Objects.ObjectQuery<BindToSP.Customer> customersQuery = northwindEntities.Customers;

// Returns an ObjectQuery.

return customersQuery;

}

private void Window_Loaded(object sender, RoutedEventArgs e)

{

BindToSP.NorthwindEntities northwindEntities = new BindToSP.NorthwindEntities();

// Load data into Customers. You can modify this code as needed.

System.Windows.Data.CollectionViewSource customersViewSource = ((System.Windows.Data.CollectionViewSource)(this.FindResource("customersViewSource")));

System.Data.Objects.ObjectQuery<BindToSP.Customer> customersQuery = this.GetCustomersQuery(northwindEntities);

customersViewSource.Source =

customersQuery.Execute(System.Data.Objects.MergeOption.AppendOnly);

System.Windows.Data.CollectionViewSource custOrderHist_ResultViewSource = ((System.Windows.Data.CollectionViewSource)(this.FindResource("custOrderHist_ResultViewSource")));

// Load data by setting the CollectionViewSource.Source property:

// custOrderHist_ResultViewSource.Source = [generic data source]

}

Private Function GetCustomersQuery(

ByVal NorthwindEntities As BindingToSP.NorthwindEntities)

As System.Data.Objects.ObjectQuery(Of BindingToSP.Customer)

Dim CustomersQuery As System.Data.Objects.ObjectQuery(Of BindingToSP.Customer) = NorthwindEntities.Customers

'Returns an ObjectQuery.

Return CustomersQuery

End Function

Private Sub Window_Loaded(ByVal sender As System.Object,

ByVal e As System.Windows.RoutedEventArgs) Handles MyBase.Loaded

Dim NorthwindEntities As BindingToSP.NorthwindEntities = New BindingToSP.NorthwindEntities()

'Load data into Customers. You can modify this code as needed.

Dim CustomersViewSource As System.Windows.Data.CollectionViewSource = CType(Me.FindResource("CustomersViewSource"), System.Windows.Data.CollectionViewSource)

Dim CustomersQuery As System.Data.Objects.ObjectQuery(Of BindingToSP.Customer) = Me.GetCustomersQuery(NorthwindEntities)

CustomersViewSource.Source = CustomersQuery.Execute(System.Data.Objects.MergeOption.AppendOnly)

Dim CustOrderHist_ResultViewSource As System.Windows.Data.CollectionViewSource = CType(Me.FindResource("CustOrderHist_ResultViewSource"), System.Windows.Data.CollectionViewSource)

'Load data by setting the CollectionViewSource.Source property:

'CustOrderHist_ResultViewSource.Source = [generic data source]

End Sub

7. Add a hander to the customer DataGrid’s SelectionChanged event, modify and add the code below:

private void Window_Loaded(object sender, RoutedEventArgs e)

{

this.northwindEntities = new BindToSP.NorthwindEntities();

// Load data into Customers. You can modify this code as needed.

System.Windows.Data.CollectionViewSource customersViewSource = ((System.Windows.Data.CollectionViewSource)(this.FindResource("customersViewSource")));

System.Data.Objects.ObjectQuery<BindToSP.Customer> customersQuery = this.GetCustomersQuery(northwindEntities);

customersViewSource.Source =

customersQuery.Execute(System.Data.Objects.MergeOption.AppendOnly);

System.Windows.Data.CollectionViewSource custOrderHist_ResultViewSource =

((System.Windows.Data.CollectionViewSource)(this.FindResource("custOrderHist_ResultViewSource")));

// Load data by setting the CollectionViewSource.Source property:

custOrderHist_ResultViewSource.Source = this.orderCollection;

}

private ObservableCollection<CustOrderHist_Result> orderCollection

= new ObservableCollection<CustOrderHist_Result>();

private NorthwindEntities northwindEntities;

// This is the added handler of customer DataGrid’s SelectionChanged event

private void customersDataGrid_SelectionChanged(object sender,

SelectionChangedEventArgs e)

{

Customer selectedCustomer = this.customersDataGrid.SelectedItem as Customer;

if (selectedCustomer != null)

{

orderCollection.Clear();

foreach (CustOrderHist_Result result in

this.northwindEntities.CustOrderHist(selectedCustomer.CustomerID))

{

orderCollection.Add(result);

}

Private Sub Window_Loaded(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles MyBase.Loaded

Me.NorthwindEntities = New BindingToSP.NorthwindEntities()

'Load data into Customers. You can modify this code as needed.

Dim CustomersViewSource As System.Windows.Data.CollectionViewSource = CType(Me.FindResource("CustomersViewSource"), System.Windows.Data.CollectionViewSource)

Dim CustomersQuery As System.Data.Objects.ObjectQuery(Of BindingToSP.Customer) = Me.GetCustomersQuery(NorthwindEntities)

CustomersViewSource.Source = CustomersQuery.Execute(System.Data.Objects.MergeOption.AppendOnly)

Dim CustOrderHist_ResultViewSource As System.Windows.Data.CollectionViewSource = CType(Me.FindResource("CustOrderHist_ResultViewSource"), System.Windows.Data.CollectionViewSource)

'Load data by setting the CollectionViewSource.Source property:

CustOrderHist_ResultViewSource.Source = Me.orderCollection

End Sub

Private orderCollection As ObservableCollection(Of CustOrderHist_Result) = New ObservableCollection(Of CustOrderHist_Result)

Private NorthwindEntities As NorthwindEntities

' This is the added handler of customer DataGrid's SelectionChanged event

Private Sub CustomersDataGrid_SelectionChanged(ByVal sender As System.Object, ByVal e As System.Windows.Controls.SelectionChangedEventArgs)

Dim selectedCustomer As Customer = CType(Me.CustomersDataGrid.SelectedItem, Customer)

If selectedCustomer IsNot Nothing Then

orderCollection.Clear()

For Each result As CustOrderHist_Result In Me.NorthwindEntities.CustOrderHist(selectedCustomer.CustomerID)

orderCollection.Add(result)

End If

End Sub

8. Run the application, select any customer in the customers DataGrid, the custOrderHist DataGrid will refresh to display product details of the selected customer:

Conclusion

You could notice that although four tables are involved in the scenario, only one Customer table is imported in the EDM. The stored procedure could be authored by another specialized database programmer at the database side, and the application programmer even does not have to be aware of all other three tables! This greatly reduces the complexity of the application and makes it more maintainable.

That is all. Enjoy it!

No-Build Experience Enhancement in WCF RIAServices 1.0 RTW

David Zhao2 — Tue, 01 Jun 2010 07:32:00 GMT

Introduction

In the WCF RIA Services PDC release, Live IntelliSense was first introduced to enable no-build experience for RIA Services development. More information about this feature can be found in: Live IntelliSense in WCF RIA Services for VS2010.

In the WCF RIA Services 1.0 RTW release, the feature has been extended to support data binding scenarios. If you make changes to models or services at server side and switch to client side without building the solution, you will find not only IntelliSense but also the data sources shown in the Data Source Window can automatically get updated at client side now. Below is an example to show you how this happens.

Example

First I create a new Silverlight Business Application. When I open MainPage.xaml in the client project, the Data Source Window shows up with UserRegistrationContext and RegistrationData (if it doesn’t, click Menu -> Data -> Show Data Sources):

In this application, I’d like to collect user’s gender in registration so that I can display an appropriate profile on the home page for him/her. Hence, I switch to the server project and append a new property to the end of the RegistrationData class in Models\RegistrationData.cs (C#):

/// <summary>

/// Gets and sets the gender.

/// </summary>

[Required(ErrorMessage="Gender is required.")]

[Display(Order = 7, Name = "Gender")]

public string Gender { get; set; }

Or in Models\RegistrationData.vb (VB):

''' <summary>

''' Gets and sets the gender.

''' </summary>

<Required(ErrorMessage:="Gender is required.")> _

<Display(Order:=7, Name:="Gender")> _

Public Property Gender() As String

To get the new property propagated to the client generated code and shown in the Data Source Window, I would have to build the entire solution when using the PDC release. But it’s no longer necessary with the RTW release. Instead, I switch back to the client project directly. The newly added property Gender shows up after the Data Source Window refreshed:

It looks good so far. Then I hope to display Gender after Email but before Question when dragging & dropping RegistrationData to XAML. So I go back to Models\RegistrationData.cs and change the order to 3 in the property’s DisplayAttribute. The new order is in the middle between the orders of Email and Question:

/// <summary>

/// Gets and sets the gender.

/// </summary>

[Required(ErrorMessage="Gender is required.")]

[Display(Order = 3, Name = "Gender")]

public string Gender { get; set; }

Or in Models\RegistrationData.vb (VB):

''' <summary>

''' Gets and sets the gender.

''' </summary>

<Required(ErrorMessage:="Gender is required.")> _

<Display(Order:=3, Name:="Gender")> _

Public Property Gender() As String

Again, I switch focus back to the client project without building solution. This time the Data Source Window gets refreshed and moves the Gender property up:

Besides updating the registration page with the new property, I plan to create a new client page as well to show the basic registration information for administrator only. Meanwhile I add a new query method in Services\UserRegistrationService.cs at server side to support the query on the Gender field:

public IEnumerable<RegistrationData> GetUsersByGender(string gender)

{

}

Or in Services\UserRegistrationService.vb (VB):

Public Function GetUsersByGender(ByVal gender As String) As IEnumerable(Of RegistrationData)

End Function

I left the method body blank because I wanted to implement it later on. Normally the solution can’t be built at the moment as the compiler would tell you that not all code paths return a value. However, I just want to know how the client generated code looks like now, so again I switch focus to client and check the Data Source Window:

The new query method GetUsersByGenderQuery comes out with the Data Source Window refreshing. Next, I select the new query method as default, disable Question, Answer in the Data Source Window and drag drop RegistrationData to the new page:

Conclusion

You may have noticed that I had never built the solution in the entire example! Yes, this feature can help you get rid of most of the intermediate builds which are only for propagating server changes to client. Moreover, VS is clever enough to know whether the code changes at server side would change the client generated code or not so that it can decide whether the Data Source Window should get refreshed or not when the focus is switched to client from server. This makes the development experience across the server/client projects even smoother. Now launch your VS2010 with WCF RIA Services and give it a shot!

Enjoy!