- Posted by justin on February 21, 2007
I have used these tutorials quite a bit and have found them very useful trying to come up to speed on the new data source controls in net 2.0.
original post at: http://feeds.feedburner.com/~r/ScottOnWriting/~3/93860920/11475.aspx
Working with Data in ASP.NET 2.0 tutorials have been updated to include the four newest tutorials, which illustrate working with database data directly from an ASP.NET web page. The previous 46 tutorials (as well as all tutorials following these four) looked at working with data through a layered architecture. But for one-off projects or prototyping, it may be preferable to use a SqlDataSource and avoid building the architecture. These four new tutorials illustrate various facets of working with the SqlDataSource control:
- Querying Data with the SqlDataSource Control [VB | C#]
- Using Parameterized Queries with the SqlDataSource [VB | C#]
- Inserting, Updating, and Deleting Data with the SqlDataSource [VB | C#]
- Implementing Optimistic Concurrency with the SqlDataSource [VB | C#]
Like the previous tutorials in the series, all tutorials are available in C# and VB, include the complete code download as a self-extracting ZIP, and are available in PDF format. Also, the layout of the tutorial homepage has been revamped and a new style has been applied for the individual tutorials.
There are more tutorials to be released in the upcoming weeks, including tutorials on working with binary data (images, PDFs, etc.), caching, and much more!
- Posted by justin on February 21, 2007
Passing lists to SQL Server 2005 with XML Parameters
Overview
SQL Server 2005's XML capabilities make it a easier to pass lists to SQL Server procedures.
Background
I recently needed to write a stored procedure which took a list of ID's as a parameter. That's one of those things that seems like it would be really simple, but isn't. You'd think you could just pass in a comma delimited string of id's: @ids = '3,5,7,8' and use something like 'SELECT * FROM Products WHERE ID IN (@ids)'. Nope, it doesn't work. I still remember my surprise when I ran into that six or seven years ago.
There are a huge variety of workarounds for this issue - see Erland's comprehensive list ranging form SQL Server 6.5 to 2000. I've used several of these, and while they worked I never liked them. Probably the best method is to just use a SPLIT table valued function which splits your string and returns a table. It's clean, but all of your procedures depend on the existence of that function.
It was also possible to use OPENXML in SQL Server 2000. The syntax was obviously put together by C++ programmers (you have to prepare a document and work with an integer handle, which feels a lot like a pointer), and there were some limitations to be aware of, but it pretty much worked.
This time around, I decided to try this with SQL Server 2005's XML capabilities and see if it was any easier. It is.
Getting started with SQL Server 2005's XML Syntax
XML variables in SQL Server 2005 make it easy to "shred" XML strings into relational data. The main new methods you'll need to use are value() and nodes() which allow us to select values from XML documents.
DECLARE @productIds xml
SET @productIds ='<Products><id>3</id><id>6</id><id>15</id></Products>'
SELECT
ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID)
Which gives us the following three rows:
3
6
15
Alright, just show me how to pass a list in a procedure parameter already!
Here's a proc which takes a single XML parameter. We first declare a table variable (@Products) and load the XML values into it. Once that's done, we can join against the @Products table as if it were any other table in the database.
CREATE PROCEDURE SelectByIdList(@productIds xml) AS
DECLARE @Products TABLE (ID int)
INSERT INTO @Products (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID)
SELECT * FROM
Products
INNER JOIN
@Products p
ON Products.ProductID = p.ID
Now we can call it as follows:
EXEC SelectByIdList @productIds='<Products><id>3</id><id>6</id><id>15</id></Products>'
Which gives us the following:
| ProductID |
ProductName |
SupplierID |
CategoryID |
QuantityPerUnit |
UnitPrice |
UnitsInStock |
UnitsOnOrder |
ReorderLevel |
Discontinued |
ID |
| 3 |
Aniseed Syrup |
1 |
2 |
12 - 550 ml bottles |
10 |
13 |
100 |
25 |
0 |
3 |
| 6 |
Grandma's Boysenberry Spread |
3 |
2 |
12 - 8 oz jars |
25 |
120 |
0 |
25 |
0 |
6 |
| 15 |
Genen Shouyu |
6 |
2 |
24 - 250 ml bottles |
15.5 |
39 |
0 |
5 |
0 |
15 |
In order to use this, you'll need to an XML string with your ID's. In our application, Steve was handling the application code, and I talked him into doing this via quick and dirty string concatenation. His method worked great:
public static string BuildXmlString(string xmlRootName, string[] values)
{
StringBuilder xmlString = new StringBuilder();
xmlString.AppendFormat("<{0}>", xmlRootName);
for (int i = 0; i < values.Length; i++)
{
xmlString.AppendFormat("<value>{0}</value>", values[i]);
}
xmlString.AppendFormat("</{0}>", xmlRootName);
return xmlString.ToString();
}
What's next?
This is a very simple use of XML in SQL Server. You can pass complex XML documents containing business objects to insert and update in your relational tables, for instance. If you're going to do that with a large amount of data, have a look at Ayende's clever use of SqlBulkCopy to handle that more efficiently.
- Posted by justin on February 21, 2007
Good post on how to use nant to update the version information in the assemblyinfo.cs the way that Microsoft does it.
Versioning the Microsoft way... with NAnt
I was inspired by a recent blog entry by Jeff Atwood here about how Microsoft versions their products and how the build number is significant. I thought it would be good to post a walkthrough of how to build your own versioning system ala Microsoft but using NAnt. I'm sure some budding geek out there could convert this to MSBuild, but you know my love of that tool so NAnt it is.
First off, NAnt has a great facility for generating that AssemblyInfo.cs file that every project has. It's the asminfo task and basically looks like this:
<?xml version="1.0"?>
<project name="Test" default="UpdateAssemblyInfo">
<target name="UpdateAssemblyInfo">
<asminfo output="AssemblyInfo.cs" language="CSharp">
<imports>
<import namespace="System.Reflection" />
<import namespace="System.Runtime.InteropServices" />
</imports>
<attributes>
<attribute type="AssemblyTitleAttribute" value="ClassLibrary1" />
<attribute type="AssemblyDescriptionAttribute" value="" />
<attribute type="AssemblyConfigurationAttribute" value="" />
<attribute type="AssemblyCompanyAttribute" value="" />
<attribute type="AssemblyProductAttribute" value="ClassLibrary1" />
<attribute type="AssemblyCopyrightAttribute" value="Copyright (c) 2007" />
<attribute type="AssemblyTrademarkAttribute" value="" />
<attribute type="AssemblyCultureAttribute" value="" />
<attribute type="ComVisibleAttribute" value="false" />
<attribute type="GuidAttribute" value="f98c8021-fbf1-44ff-a484-946152cefdb8" />
<attribute type="AssemblyVersionAttribute" value="1.0.0.0" />
<attribute type="AssemblyFileVersionAttribute" value="1.0.0.0" />
</attributes>
</asminfo>
</target>
</project>
This will product a default AssemblyInfo.cs file that looks like this:
using System.Reflection;
using System.Runtime.InteropServices;
//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated by a tool.
// Runtime Version:2.0.50727.42
//
// Changes to this file may cause incorrect behavior and will be lost if
// the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
[assembly: AssemblyTitleAttribute("ClassLibrary1")][assembly: AssemblyDescriptionAttribute("")][assembly: AssemblyConfigurationAttribute("")][assembly: AssemblyCompanyAttribute("")][assembly: AssemblyProductAttribute("ClassLibrary1")][assembly: AssemblyCopyrightAttribute("Copyright (c) 2007")][assembly: AssemblyTrademarkAttribute("")][assembly: AssemblyCultureAttribute("")][assembly: ComVisibleAttribute(false)]
[assembly: GuidAttribute("f98c8021-fbf1-44ff-a484-946152cefdb8")][assembly: AssemblyVersionAttribute("1.0.0.0")][assembly: AssemblyFileVersionAttribute("1.0.0.0")]Notice however a few things. First is the Guid. We had to hard code that which might be okay, but lets dig into NAnt scripting by replacing it with a real Guid. NAnt also has the ability to let you write embedded code (C#, VB.NET, etc.) via the <script> task, so let's write a small task to do that. We'll just have it generate a new Guid and set a new custom property in the NAnt script that we'll use in our asminfo task. Create a property in the NAnt script to hold our Guid:
<property name="project.guid" value="f98c8021-fbf1-44ff-a484-946152cefdb8" />
Then use that property in our GuidAttribute:
<attribute type="GuidAttribute" value="${project.guid}" />Finally here's the task to generate a Guid via NAnt (make the default UpdateAssemblyInfo task dependent on this one):
<target name="CreateUniqueGuid">
<script language="C#">
<code>
<![CDATA[
public static void ScriptMain(Project project) { project.Properties["project.guid"] = Guid.NewGuid().ToString();
}
]]>
</code>
</script>
</target>
Great. We now have a NAnt script that will generate a new version file with a unique Guid everytime. Next we want to tackle the versioning issue.
As described by Jensen Harris here, the Microsoft Office scheme is pretty simple:
- Take the year in which a project started. For Office "12", that was 2003.
- Call January of that year "Month 1."
- The first two digits of the build number are the number of months since "Month 1."
- The last two digits are the day of that month.
Using this we'll need to setup a couple of properties. One is to hold the year the project starts, the other is the build version we want to set:
<property name="project.year" value="2003" />
<property name="build.version" value="1.0.0.0" />
Now we could write a lot of NAnt code as there are functions to manipulate dates, but it's much easier using the <script> task and some C#. Here's the NAnt task to generate the build number using the Microsoft Office approach:
<target name="GenerateBuildNumber">
<script language="C#">
<imports>
<import name="System.Globalization" />
<import name="System.Threading" />
</imports>
<code>
<![CDATA[
public static void ScriptMain(Project project) { Version version = new Version(project.Properties["build.version"]);
int major = version.Major;
int minor = version.Minor;
int build = version.Build;
int revision = version.Revision;
int startYear = Convert.ToInt32(project.Properties["project.year"]);
DateTime start = new DateTime(startYear, 1, 1);
Calendar calendar = Thread.CurrentThread.CurrentCulture.Calendar;
int months = ((calendar.GetYear(DateTime.Today)
- calendar.GetYear(start)) * 12)
+ calendar.GetMonth(DateTime.Today)
- calendar.GetMonth(start);
int day = DateTime.Now.Day;
build = (months * 100) + day;
version = new Version(major, minor, build, revision);
project.Properties["build.version"] = version.ToString();
}
]]>
</code>
</script>
We get the version in the NAnt script as a starter (since we're only replacing the build number) and then assign values to it (they're read-only in .NET). Then this is written back out to the property as a string.
If this is run today (February 17, 2007) it's been 49 months since the start of 2003 and today is the 17th day. So the build number is 4917.
Here's the finaly output from this NAnt script:
using System.Reflection;
using System.Runtime.InteropServices;
//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated by a tool.
// Runtime Version:2.0.50727.42
//
// Changes to this file may cause incorrect behavior and will be lost if
// the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
[assembly: AssemblyTitleAttribute("ClassLibrary1")][assembly: AssemblyDescriptionAttribute("")][assembly: AssemblyConfigurationAttribute("")][assembly: AssemblyCompanyAttribute("")][assembly: AssemblyProductAttribute("ClassLibrary1")][assembly: AssemblyCopyrightAttribute("Copyright (c) 2007")][assembly: AssemblyTrademarkAttribute("")][assembly: AssemblyCultureAttribute("")][assembly: ComVisibleAttribute(false)]
[assembly: GuidAttribute("a6e7ff79-63ba-443f-8bc3-0c4b43f43ffe")][assembly: AssemblyVersionAttribute("1.0.4917.0")][assembly: AssemblyFileVersionAttribute("1.0.4917.0")]
And here's the full NAnt script:
1 <?xml version="1.0"?>
2 <project name="Test" default="UpdateAssemblyInfo">
3
4 <property name="project.guid" value="f98c8021-fbf1-44ff-a484-946152cefdb8" />
5 <property name="project.year" value="2003" />
6 <property name="build.version" value="1.0.0.0" />
7
8 <target name="UpdateAssemblyInfo" depends="CreateUniqueGuid, GenerateBuildNumber">
9 <asminfo output="AssemblyInfo.cs" language="CSharp">
10 <imports>
11 <import namespace="System.Reflection" />
12 <import namespace="System.Runtime.InteropServices" />
13 </imports>
14 <attributes>
15 <attribute type="AssemblyTitleAttribute" value="ClassLibrary1" />
16 <attribute type="AssemblyDescriptionAttribute" value="" />
17 <attribute type="AssemblyConfigurationAttribute" value="" />
18 <attribute type="AssemblyCompanyAttribute" value="" />
19 <attribute type="AssemblyProductAttribute" value="ClassLibrary1" />
20 <attribute type="AssemblyCopyrightAttribute" value="Copyright (c) 2007" />
21 <attribute type="AssemblyTrademarkAttribute" value="" />
22 <attribute type="AssemblyCultureAttribute" value="" />
23
24 <attribute type="ComVisibleAttribute" value="false" />
25
26 <attribute type="GuidAttribute" value="${project.guid}" /> 27
28 <attribute type="AssemblyVersionAttribute" value="${build.version}" /> 29 <attribute type="AssemblyFileVersionAttribute" value="${build.version}" /> 30 </attributes>
31 </asminfo>
32 </target>
33
34 <target name="CreateUniqueGuid">
35 <script language="C#">
36 <code>
37 <![CDATA[
38 public static void ScriptMain(Project project) { 39 project.Properties["project.guid"] = Guid.NewGuid().ToString();
40 }
41 ]]>
42 </code>
43 </script>
44 </target>
45
46 <target name="GenerateBuildNumber">
47 <script language="C#">
48 <imports>
49 <import name="System.Globalization" />
50 <import name="System.Threading" />
51 </imports>
52 <code>
53 <![CDATA[
54 public static void ScriptMain(Project project) { 55 Version version = new Version(project.Properties["build.version"]);
56 int major = version.Major;
57 int minor = version.Minor;
58 int build = version.Build;
59 int revision = version.Revision;
60
61 int startYear = Convert.ToInt32(project.Properties["project.year"]);
62 DateTime start = new DateTime(startYear, 1, 1);
63 Calendar calendar = Thread.CurrentThread.CurrentCulture.Calendar;
64 int months = ((calendar.GetYear(DateTime.Today)
65 - calendar.GetYear(start)) * 12)
66 + calendar.GetMonth(DateTime.Today)
67 - calendar.GetMonth(start);
68 int day = DateTime.Now.Day;
69 build = (months * 100) + day;
70
71 version = new Version(major, minor, build, revision);
72 project.Properties["build.version"] = version.ToString();
73 }
74 ]]>
75 </code>
76 </script>
77 </target>
78
79 </project>
Enjoy!
