May 2010 - Posts
There are some scenarios that require building a connection string at runtime. For example, if you are building a database utility or if your application allows working with multiple databases. In these cases, you may want to ask the user for the basic parts of the connection and not require entry of a syntactically correct connection string.
Here is an example of a Windows Forms dialog for entry of connection information:
(Thanks to my friend Robin for creating this nice user interface.)
After the user makes the appropriate selections, you can use the entered values to create a connection string.
The xxxConnectionStringBuilder classes in System.Data can help you with this. Use the SqlConnectionStringBuilder class if you are accessing SQL Server or the OdbcConnectionStringBuilder class if you are referencing a Microsoft Access or other Odbc database.
This example uses the SqlConnectionStringBuilder. But you can readily replace it with OdbcConnectionStringBuilder when necessary.
The following example defines a shared/static function that builds a connection string from the controls on the form shown above.
Be sure to set a reference to System.Data.SqlClient.
In C#:
private static string CreateConnectionString()
{
SqlConnectionStringBuilder sqlBuilder =
new SqlConnectionStringBuilder();
if (!string.IsNullOrWhiteSpace(ServerNameTextBox.Text)) {
sqlBuilder.DataSource = ServerNameTextBox.Text;
if (!string.IsNullOrWhiteSpace(DatabaseNameComboBox.Text) {
sqlBuilder.InitialCatalog = DatabaseNameComboBox.Text;
}
sqlBuilder.IntegratedSecurity =
AuthenticationComboBox.SelectedValue ==
AuthenticationType.Windows ? true : false;
// For SQL Server authentication, need a user Id and password
if (sqlBuilder.IntegratedSecurity == false) {
sqlBuilder.UserID = UserIdTextBox.Text;
sqlBuilder.Password = PasswordTextBox.Text;
}
}
return sqlBuilder.ConnectionString;
}
In VB:
Private Shared Function CreateConnectionString() As String
Dim sqlBuilder As New SqlConnectionStringBuilder
If Not String.IsNullOrWhiteSpace(ServerNameTextBox.Text) Then
sqlBuilder.DataSource = ServerNameTextBox.Text
If Not String.IsNullOrWhiteSpace(DatabaseNameComboBox.Text) Then
sqlBuilder.InitialCatalog = DatabaseNameComboBox.Text
End If
sqlBuilder.IntegratedSecurity = If
(AuthenticationComboBox.SelectedValue =
AuthenticationType.Windows, True, False)
' For SQL Server authentication, need a user Id and password
If sqlBuilder.IntegratedSecurity = False Then
sqlBuilder.UserID = UserIdTextBox.Text
sqlBuilder.Password = PasswordTextBox.Text
End If
End If
Return sqlBuilder.ConnectionString
End Function
NOTE: The above code uses the implicit line continuation feature that is new in VB 10 (VS 2010). If you have an older version of VB, you will need to add the line continuation character ( _ ) where necessary.
The CreateConnectionString function shown above creates a new instance of the SqlConnectionStringBuilder. It then assigns the properties of the SqlConnectionStringBuilder from the data entry fields. The ConnectionString property then contains the appropriate connection string.
You can then use the returned connection string to connect to the selected database.
Enjoy!
With the new multi-monitor support, an underused Visual Studio menu option just became much more useful. You can use the Window | New Window option to view a second code window with the same code file. The result looks like this:
You can then drag one of the code windows so they are side by side or drag one to your other monitor. This allows you to view two different parts of your code file at the same time.
HOWEVER, the Window | New Window option is disabled when working with Visual Basic code files. The feature was put in, but not fully tested. So it was disabled.
If you really want to use this feature in VB.NET, you can remove the registry entry that disables it.
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\VisualStudio\10.0\Languages\Language Services\Basic]
"Single Code Window Only"=dword:00000001
USE AT YOUR OWN RISK!
I submitted a Connect Issue to have this put in by default. You can vote for it here:
https://connect.microsoft.com/VisualStudio/feedback/details/559243/enable-the-windows-new-window-feature-for-visual-basic
Enjoy!
We are lucky to have two of Redmond's finest down here in Silicon Valley for a full day .NET 4.0 and VS 2010 event on May 15, 2010. Click here for more information or to register.
I am happy to be providing one of the talks at the event. I am giving the "What's New in VB 10" talk. VB and C# continue to get closer together in terms of feature set, so that means more feature parity.
With parity in mind, VB 10 has the following new features:
My talk covers these items and plenty more. If you want the sample code for the talk, you can find it here.
Enjoy!
If you are doing any type of statistical analysis, you probably need to calculate mean, median and mode. There are lots of places on the Web you can find the calculations. This post is different than most in that it uses LINQ and Lambda expressions.
Mean is the statistical average of a set of numbers. This one is easy with LINQ because of the Average function.
In C#:
int[] numbers = { 4, 4, 4, 4, 3, 2, 2, 2, 1 };
double mean = numbers.Average();
Debug.WriteLine(("Mean: " + mean));
In VB:
Dim numbers() As Integer = {4, 4, 4, 4, 3, 2, 2, 2, 1}
Dim mean As Double = numbers.Average()
Debug.WriteLine("Mean: " & mean)
The result is:
Mean: 2.88888888888889
This code uses the Average extension method on the IEnumerable class to calculate the mean, or average, of the numbers.
Median is the middle number of a set of numbers. If there is an even number of entries, it is the average of the two middle numbers.
In C#:
int[] numbers = { 4, 4, 4, 4, 3, 2, 2, 2, 1 };
int numberCount = numbers.Count();
int halfIndex = numbers.Count()/2;
var sortedNumbers = numbers.OrderBy(n=>n);
double median;
if ((numberCount % 2) == 0)
{
median = ((sortedNumbers.ElementAt(halfIndex) +
sortedNumbers.ElementAt((halfIndex - 1)))/ 2);
} else {
median = sortedNumbers.ElementAt(halfIndex);
}
Debug.WriteLine(("Median is: " + median));
In VB:
Dim numbers() As Integer = {4, 4, 4, 4, 3, 2, 2, 2, 1}
Dim numberCount As Integer = numbers.Count
Dim halfIndex As Integer = numbers.Count \ 2
Dim sortedNumbers = numbers.OrderBy(Function(n) n)
Dim median As Double
If (numberCount Mod 2 = 0) Then
median = (sortedNumbers.ElementAt(halfIndex) +
sortedNumbers.ElementAt(halfIndex - 1)) / 2
Else
median = sortedNumbers.ElementAt(halfIndex)
End If
Debug.WriteLine("Median is: " & median)
The result is:
Median is: 3
This code first counts the numbers and divides the count by 2 to find the middle of the list. Note that the VB code uses the backslash (\) to perform an integer division where the C# code uses a forward slash (/) for the division.
It then sorts the numbers in order using the OrderBy extension method and a Lambda expression that simply orders by the numbers.
The last step is to get the element at the middle (if odd) or the average of the two middle elements (if even). The result is the median.
Mode is the number that occurs the largest number of times.
In C#:
int[] numbers = { 4, 4, 4, 4, 3, 2, 2, 2, 1 };
var mode = numbers.GroupBy(n=> n).
OrderByDescending(g=> g.Count()).
Select(g => g.Key).FirstOrDefault();
Debug.WriteLine(("Mode is: " + mode));
In VB:
Dim numbers() As Integer = {4, 4, 4, 4, 3, 2, 2, 2, 1}
Dim mode = numbers.GroupBy(Function(n) n).
OrderByDescending(Function(g) g.Count).
Select(Function(g) g.Key).FirstOrDefault
Debug.WriteLine("Mode is: " & mode)
The result is:
Mode is: 4
This code uses the GroupBy extension method on IEnumerable to group the numbers by number. It then orders them by the count and selects the first one. This provides the number that occurs the most times.
Use these techniques whenever you need to calculate the mean, median, or mode.
Enjoy!
Most of the cool extension methods that you can use with Lambda expressions, such as Where, FirstOrDefault, and Zip, are extensions of the generic IEnumerable<T> class. Interestingly, the .NET multi-dimensional array does not implement the generic IEnumerable<T> class. So you don't have direct access to any of the Linq extension methods.
The reason for this is that a single-dimension array is implemented differently from a multi-dimension array in .NET. This stackoverflow post provides more detail on this issue.
The purpose of this post is to provide a way to work with extension methods on multi-dimensions arrays. The trick is to use Cast.
This example defines a 3x3 array with three student's scores on three exams. It then averages the scores.
In C#:
int[,] multiArray = {{70, 88, 90}, {98, 100, 96}, {88, 94, 95}};
var averageScore = multiArray.Cast<int>().Average();
In VB:
Dim multiArray(,) As Integer = {{70, 88, 90}, {98, 100, 96},
{88, 94, 95}}
Dim averageScore = multiArray.Cast(Of Integer).Average
The first line of code defines the 3 x 3 array.
The second line of code uses the Cast operator to cast the array to a generic IEnumerable(Of Integer). This basically flattens the array into a single set of integers.
Once you have a generic IEnumerable, you can use its associated extension methods, including Average. The result is an average of 91.
Use this technique any time you have to work with multi-dimensional arrays.
Enjoy!
One of the new features in C# 4 and VB 10 (.NET 4.0) is the Zip extension method on IEnumerable. This method allows you to merge two lists such as two arrays or two generic List<T>.
The Zip extension method merges the two sequences by matching up each item in one sequence with another item in another sequence.
Starting with a simple example, say you have an array of numbers 0-5. And you also have an array of strings that contain the number names zero-five. You want to merge these into a single array in the form: number (name), e.g. 0 (zero), 1 (one), and so on.
In C#:
int[] numberArray = {0, 1, 2, 3, 4, 5};
string[] nameArray = {"zero", "one", "two",
"three", "four", "five"};
var combinedArray = numberArray.Zip(nameArray,
(number, name) => number.ToString() + " (" +
name + ")").ToArray();
Array.ForEach(combinedArray, s=> Console.WriteLine(s));
In VB:
Dim numberArray() As Integer = {0, 1, 2, 3, 4, 5}
Dim nameArray() As String = {"zero", "one", "two",
"three", "four", "five"}
Dim combinedArray = numberArray.Zip(nameArray,
Function(number, name) number.ToString & " (" &
name & ")").ToArray
Array.ForEach(combinedArray, Sub(s) Console.WriteLine(s))
The first two lines of code declare the two arrays: one with a set of numbers and the other with a set of names.
The Zip extension method combines the two arrays. Use the Zip method on either of the arrays and set the first parameter of the Zip method to the other array. The second parameter of the Zip method is a Lambda expression that details the desired result. In this case, the merged array contains a string concatenation of the number and name.
The parameters to the Lambda expression, number and name in this example, represent a value from each of the two arrays. The first parameter maps to the array defined to the left of the Zip method and the second parameter maps to the array defined as the first parameter to the Zip method.
(For more technical detail on how the Zip method works, see Bart De Smet's blog post.)
The last line of code uses the ForEach extension method to display each value in the merged array to the console.
The result is:
0 (zero)
1 (one)
2 (two)
3 (three)
4 (four)
5 (five)
Instead of returning a simple array, you can return an array of anonymous types. An anonymous type is like a class that you create dynamically that has no name, hence the reason it is called an anonymous type.
Using the same arrays as in the prior example, this next example returns an array of anonymous types where each item has a number and name.
In C#:
var nums = numberArray.Zip(nameArray,
(number, name) => new { number, name});
foreach (var item in nums)
Console.WriteLine(item);
In VB:
Dim nums = numberArray.Zip(nameArray,
Function(number, name) New With {number, name})
For Each item In nums
Console.WriteLine(item)
Next
This code starts with the same two arrays, but this time it creates an anonymous type. The new keyword defines the anonymous type and the list of items in the curly braces sets the properties of the anonymous type.
The result is:
{ number = 0, name = zero }
{ number = 1, name = one }
{ number = 2, name = two }
{ number = 3, name = three }
{ number = 4, name = four }
{ number = 5, name = five }
Now consider a more business-oriented example. Say you have a list of customers (like the one in this prior post) and a list of invoices (like the one in this prior post). You want to merge the lists into a single list so you can bind it to a grid.
In C#:
var listToBind = custList.Zip(invoiceList,
(cust, inv) => new {
LastName = cust.LastName,
FirstName = cust.FirstName,
InvoiceAmount = inv.InvoiceAmount,
InvoiceDate = inv.InvoiceDate});
dataGridView1.DataSource = listToBind.ToList();
In VB:
Dim listToBind = custList.Zip(invoiceList,
Function(cust, inv) New With {
.LastName = cust.LastName,
.FirstName = cust.FirstName,
.InvoiceAmount = inv.InvoiceAmount,
.InvoiceDate = inv.InvoiceDate})
DataGridView1.DataSource = listToBind.ToList
This code uses the Zip extension method to combine the customer list and the invoice list. It uses the new keyword to create an anonymous type with properties from both lists. The result can be bound to a grid, such as the DataGridView.
The result is as follows:
You can use filtering and sorting to change the contents of the resulting list. For example, the following code only displays invoices with amounts > 100.
In C#:
var listToBind = custList.Zip(invoiceList,
(cust, inv) => new {
LastName = cust.LastName,
FirstName = cust.FirstName,
InvoiceAmount = inv.InvoiceAmount,
InvoiceDate = inv.InvoiceDate}).
Where(c => c.InvoiceAmount>100);
dataGridView1.DataSource = listToBind.ToList();
In VB:
Dim listToBind = custList.Zip(invoiceList,
Function(cust, inv) New With {
.LastName = cust.LastName,
.FirstName = cust.FirstName,
.InvoiceAmount = inv.InvoiceAmount,
.InvoiceDate = inv.InvoiceDate}).
Where(Function(c) c.InvoiceAmount > 100)
DataGridView1.DataSource = listToBind.ToList
Use the Zip extension method any time you need to combine lists or build a new list from existing lists. This is especially useful for binding to grids.
Enjoy!
There are often cases where you need to group on multiple properties and potentially sum on others. This post details how to group and sum on multiple properties using lambda expressions.
This example uses an Invoice class, since that provides many opportunities for summing. First, here is a basic Invoice class.
In C#:
class Invoice
{
public DateTime InvoiceDate { get; set; }
public int InvoiceType { get; set; }
public decimal InvoiceAmount { get; set; }
public int NumberOfItems { get; set; }
}
In VB:
Public Class Invoice
Public Property InvoiceDate As DateTime
Public Property InvoiceType As Integer
Public Property InvoiceAmount As Decimal
Public Property NumberOfItems As Integer
End Class
Normally, you would populate the Invoice class from data in a database or other data store. But to keep this example simple, the values for the invoices are hard-coded.
In C#:
List<Invoice> invoiceList = new List<Invoice>();
invoiceList = new List<Invoice>
{new Invoice()
{
InvoiceDate=new DateTime(2010,4,30),
InvoiceType = 1,
InvoiceAmount = 150,
NumberOfItems = 8},
new Invoice()
{
InvoiceDate=new DateTime(2010,4,29),
InvoiceType = 2,
InvoiceAmount = 215,
NumberOfItems = 7},
new Invoice()
{
InvoiceDate=new DateTime(2010,4,30),
InvoiceType = 1,
InvoiceAmount = 50,
NumberOfItems = 2},
new Invoice()
{
InvoiceDate=new DateTime(2010,4,29),
InvoiceType = 2,
InvoiceAmount = 550,
NumberOfItems = 5}};
In VB:
Dim invoiceList As List(Of Invoice)
invoiceList = New List(Of Invoice) From
{New Invoice With
{
.InvoiceDate = New DateTime(2010, 4, 30),
.InvoiceType = 1,
.InvoiceAmount = 150,
.NumberOfItems = 8},
New Invoice With
{
.InvoiceDate = New DateTime(2010, 4, 29),
.InvoiceType = 2,
.InvoiceAmount = 215,
.NumberOfItems = 7},
New Invoice With
{
.InvoiceDate = New DateTime(2010, 4, 30),
.InvoiceType = 1,
.InvoiceAmount = 50,
.NumberOfItems = 2},
New Invoice With
{
.InvoiceDate = New DateTime(2010, 4, 29),
.InvoiceType = 2,
.InvoiceAmount = 550,
.NumberOfItems = 5}}
This code creates two invoices of type 1 that are dated 4/30/2010 and two invoices of type 2 that are dated 4/29/2010.
Now for the fun part. This example groups on both the InvoiceDate and the InvoiceType properties. The totals accumulate the invoice amount and the number of items. This allows the code to provide totals based both on the date and type.
In C#:
var query = invoiceList
.GroupBy(g => new { g.InvoiceDate,
g.InvoiceType })
.Select(group => new {
InvoiceDate = group.Key.InvoiceDate,
InvoiceType = group.Key.InvoiceType,
TotalAmount = group.Sum(a=>a.InvoiceAmount),
TotalCount = group.Sum(c=>c.NumberOfItems)});
In VB:
Dim query = invoiceList.
GroupBy(Function(g) New With {Key g.InvoiceDate,
Key g.InvoiceType}).
Select(Function(group) New With {
.InvoiceDate = group.Key.InvoiceDate,
.InvoiceType = group.Key.InvoiceType,
.TotalAmount = group.Sum(Function(a) a.InvoiceAmount),
.TotalCount = group.Sum(Function(c) c.NumberOfItems)})
This code starts with a GroupBy clause with a Lambda expression. The Lambda expression (indicated with the => syntax in C# and the Function keyword in VB) uses the new keyword to create a new anonymous type with two properties: InvoiceDate and InvoiceType. This technique allows the code to group on both properties. More properties can be included here if you need to group on more than two properties.
Notice the Key modifier on the two properties in the Lambda expression for the VB example. This ensures that the anonymous type is immutable, providing read-only values. This is not necessary in the C# code because C# only supports immutable anonymous types.
The next part of the above code is a Select clause with another Lambda expression. The Lambda expression uses the new keyword to create an anonymous type with four properties: the invoice date and type from the grouping and the two totals. Each total uses a Sum clause with a Lambda expression defining the property to sum.
The group.Key syntax provides the key values from the grouping, which in this case are the properties from our first anonymous type.
The group.Sum syntax provides a sum on a particular property in the original list.
To view the results of the query, the code can display the items.
In C#:
foreach (var item in query)
{
Console.WriteLine("Invoice Date: {0} ({1}) TotalAmount: {2} TotalCount: {3}",
item.InvoiceDate.ToShortDateString(),
item.InvoiceType,
item.TotalAmount,
item.TotalCount);
}
In VB:
For Each item In query
Console.WriteLine("Invoice Date: {0} ({1}) TotalAmount: {2} TotalCount: {3}",
item.InvoiceDate.ToShortDateString(),
item.InvoiceType,
item.TotalAmount,
item.TotalCount)
Next
The result is as follows:
Invoice Date: 4/30/2010 (1) TotalAmount: 200 TotalCount: 10
Invoice Date: 4/29/2010 (2) TotalAmount: 765 TotalCount: 12
Use this technique any time you need to group or sum information in your business objects.
Enjoy!
The new Visual Studio 2010 Database Project, as described in this prior post, makes it easy to work with scripts for an existing database or build new scripts directly within Visual Studio. This post details how to add stored procedure scripts to a Database project
Start by adding a VS 2010 Database project to your solution following the steps in this prior post. Then follow the steps below to add stored procedure scripts to your project.
1. Right-click on the Stored Procedures node for the Database project in Solution Explorer:
2. Select Add | Stored Procedure
This opens the Add New Item dialog:
3. Enter the name of the new stored procedure and click the Add button.
The stored procedure template then appears in the code editor and you can enter the stored procedure information.
One of the really nice new features in Visual Studio 2010 is full Intellisense when building the stored procedures:
NOTE: To get full Intellisense on the available table fields, add the From clause first. Without the From clause, Visual Studio does not know how to resolve the field list and provide appropriate Intellisense.
NOTE: Intellisense would sometimes stop working for no reason I could figure out. Closing the script and reopening it often helped get Intellisense working again.
When you are ready to apply the stored procedure script to your development database, you can deploy the script as detailed in this post.
But if you want to simply validate the stored procedure syntax before deployment, use the Validate SQL Syntax icon in the toolbar OR right-click on the code editor and select Validate SQL Syntax. In either case, you will be asked to connect to your database. The syntax is then checked and any errors are defined in the Messages tab at the bottom of the code window.
You can also execute the script, writing the stored procedure to the database, directly from the code editor. Select the Execute SQL icon in the toolbar OR right-click on the code editor and select Execute SQL. You will be asked to connect to your database before the script is executed and the stored procedure is created in the defined database.
NOTE: Because the stored procedure scripts in the Database project are always Create Procedure scripts, you can not execute the script again once the stored procedure is created. The work around for this is to change "Create Procedure" to "Alter Procedure" in the script, execute the script, and change the script back to "Create Procedure".
You can also execute any SQL statements within the script by selecting the statements. Then right-click and select Execute SQL. The script is then executed and the results are displayed in the Results tab.
Use the techniques detailed in this post any time you want to add a stored procedure script to your Database project or work with a stored procedure script in your project.
Enjoy!
The new Visual Studio 2010 Database Project, as described in this prior post, makes it easy to manage the scripts for your database from within Visual Studio. This includes building the schema scripts from the individual database object scripts and deploying the scripts back to the database.
One of the ways that the new VS 2010 Database project is significantly different from the Database project available in prior versions of Visual Studio is the build and deployment process.
The old VS 2003/2005/2008 Database project was just a container for your scripts. You could right-click on the database in Server Explorer to generate the script to the project. When you made changes to the script, you could right-click on the script and select to apply the script to the database. But you had to manually perform these tasks.
And with the old style Database projects, it was easy to forget to apply your script changes to the database. It also was not easy to keep everything in synchronization. For example, if you later made a table change, it is easy to forget to adjust the related stored procedures as well.
Instead of being a simple repository, the new 2010 Database project is alive. The scripts in your Database project define your database just like the files in your code projects define your application. And the build and deployment process can apply the changes to your actual database.
This post details how to use the VS 2010 Database project and deploy your scripts back to your database.
Start by adding a VS 2010 Database project to your solution following the steps in this prior post. Then write the scripts into your project from an existing database as detailed in this prior post. Or write your own scripts in the project.
After you've made any changes to those scripts, you will want to deploy them back to the database.
To build the Database project, right-click on the Database project and select Build. OR you can use the Build menu.
When you build the Database project, Visual Studio validates your scripts and writes the database schema to a file with a .dbschema extension.
To deploy the scripts to your database, right-click on the Database project and select Deploy. Or you can use the Build menu.
When you deploy the Database project, Visual Studio builds a deployment script.
You can then run that one script to apply all of the information from your database object scripts to the development database.
But if you want to ensure that your development database is always up to date with the scripts in your Database project, you can set your Database project properties so that any changes are automatically deployed to the development database when you run your application (or build the solution).
Double-click on the Properties node of the Database project in Solution Explorer to open the Database project properties. Select the Deploy tab:
Set the Deploy action to "Create a deployment script (.sql) and deploy to the database". Then use the Edit button to the right of the Target connection (cut off in the above screen shot) to set the connection to your development database. Once you set these project settings, they will remain set unless you change them.
Each time you deploy the Database project, using the menu options or by just running the application within Visual Studio, Visual Studio will immediately apply all script changes directly to the database.
In this example, I added a table script for an Invoice table. When I ran the application, Visual Studio built and deployed the Database project scripts, creating the Invoice table in the database.
Use these features of the Database project any time you want to keep the master copy of your database schema defined in your scripts and ensure that the schema is in sync every time you run your application within Visual Studio.
Enjoy!
The new Visual Studio 2010 Database Project, as described in this prior post, makes it easy to work with scripts for an existing database or build new scripts directly within Visual Studio. This post details how to add table scripts to a Database project
Start by adding a VS 2010 Database project to your solution following the steps in this prior post. Then follow the steps below to add tables to your project.
1. Right click on the Tables node for the Database project in Solution Explorer.
2. Select Add | Table.
This opens the Add New Item dialog:
3. Enter the name of the new table and click the Add button.
The table script template then appears in the code editor and you can enter the information for the table.
Notice that there is no visual editor available here as there is in Server Explorer. But if you know SQL, it is quick to enter the columns in the table.
When you are ready to apply the table script to your development database, you can deploy the script as detailed in this post.
Use this technique any time you want to add a table script to your project.
Enjoy!
The new Visual Studio 2010 Database Project, as described in this prior post, makes it easy to work with scripts for an existing database directly within Visual Studio.
You can automatically build scripts for your existing tables, stored procedures, indexes, constraints, and so on. You can then check these scripts into and out of source code control. You can edit the scripts to change the table schemas or stored procedures. You can then build the project to validate the scripts and then deploy the scripts back to the database.
Start by adding a VS 2010 Database project to your solution following the steps in this prior post. Then follow the steps below to create the scripts in the project from an existing database.
1. Right-click on the Database project name in Solution Explorer and select Import Database Objects and Settings.
The Import Database Wizard is displayed.
2. Select the database connection to the existing database, any desired properties, and click on the Start button to start the wizard.
The database objects are then scripted directly into your Database project:
3. Click on the Finish button to finish.
View the Database project in Solution Explorer to see your added database objects.
Notice that this process added the three tables and three stored procedures from my database.
You can then check these files in and out of source code control just like any other project files. You can double-click on any script to edit it.
When you are ready to apply the changes to your development database, you can deploy the scripts as detailed in this post.
NOTE: If your database has been around a while, it is possible that some of the stored procedures may have gotten out of synchronization with the tables. For example, if your DBA renamed the CustomerInfo table to Customer, it is possible that your associated stored procedures were not all modified to match. Unless your code calls all of the stored procedures, you may not have noticed the problem. Since the Database project is "alive", once you follow this process to create the associated scripts into your Database project, any mismatches are defined as application warnings:
This helps you ensure that your stored procedures and tables are always in synchronization.
If you want these types of warnings to be treated as application errors and prevent execution of the application from within Visual Studio, you can change these warnings to errors. Double-click on the Properties under the Database project in Solution Explorer and click on the Build tab. Then check the "Treat Warnings as Errors" checkbox. Your warnings will immediately become application errors.
Use the techniques detailed here any time you are working with an existing database and want to add the scripts to your solution.
Enjoy!
Visual Studio 2010 has a new Database Project. The scripts in this new Database project define your database just like the files in your code projects define your application.
The prior Database project (the one under the "Other Projects" node in VS 2003/2005/2008 as detailed in this prior post), was simply a storage container for your database scripts.
The new Database project is alive. Like the old Database project, it retains your database scripts, but it also has Intellisense, build-time validation, and automatic deployment features. It allows you to keep the definition of the database in your scripts and ensure that the scripts are deployed to your development database.
This is the first in a series of posts on using the new Database project. This post provides the steps for adding a Database Project to any solution in either VB or C#.
1. Right-click on your solution and select Add | New Project. Or select File | New Project from the main menu.
The Add New Project dialog is then displayed.
2. Open the Database node in the Installed Templates panel on the left and select SQL Server.
3. Select SQL Server 2008 Database Project from the middle panel, enter a name for the project, and click OK.
NOTE: If you have SQL Server 2005, you can select the SQL Server 2005 Database Project instead.
Visual Studio then creates the new project and adds it to your Solution Explorer:
4. Use the Database project to write the scripts for a new database or manage the scripts for an existing database.
For example, use the Tables node to manage your table scripts and the Programmability | Stored Procedures node to manage your stored procedure scripts:
These posts provide details on using this new Database project:
Enjoy!