jump to navigation

Wix 2009/03/18

Posted by onlineall in CSharp.
add a comment

In finally get CruiseControl.Net set up to not only automate our build process, but notify us of any breaking changes on checkin, I discovered some issues in using the standard setup and deployment projects. Not wanting to spend a lot of time on it, I decided to look into Wix and see how it could help me streamline things.

It turns out Wix is a whole lot more work. Instead of a simple point and click interface, I’m presented with hand editing the XML file. Boo.

There are a few decent open source projects to give me a GUI interface so I can get my point and click back, but they turned out to either be for Wix 2 or they had some serious bugs in them.

In the end I used Dark to script my MSI file. While this worked out ok, Wix 3 wouldn’t compile the script without me making some changes. I dug through the script it created and I started to wonder how I was going to automate this. The trick is really not to automate it. The trick is to set up your script once and then leave it alone. Modifications are only made when things change in your build. That’s fine, but what about setting it up for the first time? You have to create a GUID for each component (read, every file if you want the files updateable) that gets installed. This can be time consuming if you have quite a few ancilary files that need to be shipped with the release.

Well, I automated that part. I created myself a small console application that will take a folder and a output file name as arguments. The program will traverse this folder and create an include script. It treats every file, directory, and directory of files as individual components.

In case anyone wants to do this but doesn’t want to take the time to write all the code, here it is.
view plaincopy to clipboardprint?

1. using System;
2. using System.Collections.Generic;
3. using System.Linq;
4. using System.Text;
5. using System.IO;
6. using System.Xml;
7. using System.Xml.XPath;
8. using System.Reflection;
9.
10. namespace CreateIncludeScript
11. {
12. class Program
13. {
14. static int Main(string[] args)
15. {
16. if (args.Length 2)
17. {
18. return (-1);
19. }
20. string folder = args[0];
21. string outputfile = args[1];
22. XmlDocument xmlDoc = new XmlDocument();
23. XmlNode root = xmlDoc.AppendChild(xmlDoc.CreateElement(“Include”, “http://schemas.microsoft.com/wix/2006/wi”));
24.
25. XmlNode dir = root.AppendChild(xmlDoc.CreateElement(“Directory”, “http://schemas.microsoft.com/wix/2006/wi”));
26. SetAttr(xmlDoc, dir, “Id”, “TARGETDIR”);
27. SetAttr(xmlDoc, dir, “Name”, “SourceDir”);
28.
29. ProcessDirectory(xmlDoc, dir, folder);
30.
31. XmlNode feat = root.AppendChild(xmlDoc.CreateElement(“Feature”, “http://schemas.microsoft.com/wix/2006/wi”));
32. SetAttr(xmlDoc, feat, “Id”, “DefaultFeature”);
33. SetAttr(xmlDoc, feat, “Level”, “1″);
34. SetAttr(xmlDoc, feat, “ConfigurableDirectory”, “TARGETDIR”);
35. ProcessFeatures(xmlDoc, feat, dir);
36.
37. xmlDoc.Save(outputfile);
38. return (0);
39. }
40.
41. static void SetAttr(XmlDocument doc, XmlNode node, string name, string value)
42. {
43. XmlAttribute attr = node.Attributes.Append(doc.CreateAttribute(name));
44. attr.Value = value;
45. }
46.
47. static void ProcessDirectory(XmlDocument xmlDoc, XmlNode target, string directory)
48. {
49. string[] files = Directory.GetFiles(directory);
50. string[] dirs = Directory.GetDirectories(directory);
51.
52. foreach (string file in files)
53. {
54. FileInfo finfo = new FileInfo(file);
55.
56. XmlNode comp = target.AppendChild(xmlDoc.CreateElement(“Component”, “http://schemas.microsoft.com/wix/2006/wi”));
57. string guid = System.Guid.NewGuid().ToString().ToUpper();
58. string id = “C_” + guid.Replace(“-”, “”);
59. SetAttr(xmlDoc, comp, “Id”, id);
60. SetAttr(xmlDoc, comp, “Guid”, “{” + guid + “}”);
61.
62. XmlNode fnode = comp.AppendChild(xmlDoc.CreateElement(“File”, “http://schemas.microsoft.com/wix/2006/wi”));
63. SetAttr(xmlDoc, fnode, “Id”, “F_” + guid.Replace(“-”,””));
64. SetAttr(xmlDoc, fnode, “Name”, finfo.Name);
65. SetAttr(xmlDoc, fnode, “KeyPath”, “yes”);
66. SetAttr(xmlDoc, fnode, “DiskId”, “1″);
67. SetAttr(xmlDoc, fnode, “Source”, finfo.FullName);
68.
69. if (finfo.Extension.ToLower() == “.dll” || finfo.Extension.ToLower() == “.exe”)
70. {
71. if (IsDotNetAssembly(finfo.FullName))
72. {
73. SetAttr(xmlDoc, fnode, “Assembly”, “.net”);
74. SetAttr(xmlDoc, fnode, “AssemblyManifest”, “F_” + guid.Replace(“-”, “”));
75. SetAttr(xmlDoc, fnode, “AssemblyApplication”, “F_” + guid.Replace(“-”, “”));
76. }
77. }
78. }
79.
80. foreach (string dir in dirs)
81. {
82. DirectoryInfo dinfo = new DirectoryInfo(dir);
83.
84. XmlNode dnode = target.AppendChild(xmlDoc.CreateElement(“Directory”, “http://schemas.microsoft.com/wix/2006/wi”));
85. SetAttr(xmlDoc, dnode, “Id”, dinfo.Name.Replace(“-”,””));
86. SetAttr(xmlDoc, dnode, “Name”, dinfo.Name);
87.
88. ProcessDirectory(xmlDoc, dnode, dinfo.FullName);
89. }
90. }
91.
92. static void ProcessFeatures(XmlDocument xmlDoc, XmlNode target, XmlNode root)
93. {
94. XmlNamespaceManager xmlmgr = new XmlNamespaceManager(xmlDoc.NameTable);
95. xmlmgr.AddNamespace(“wi”, “http://schemas.microsoft.com/wix/2006/wi”);
96. XmlNodeList comps = root.SelectNodes(“descendant::wi:Component”, xmlmgr);
97.
98. foreach (XmlNode node in comps)
99. {
100. XmlNode compfeat = target.AppendChild(xmlDoc.CreateElement(“ComponentRef”, “http://schemas.microsoft.com/wix/2006/wi”));
101. SetAttr(xmlDoc, compfeat, “Id”, node.Attributes.GetNamedItem(“Id”).Value);
102. XmlNode file = node.SelectSingleNode(“wi:File”, xmlmgr);
103. if (file.Attributes.GetNamedItem(“Assembly”) != null)
104. {
105. SetAttr(xmlDoc, compfeat, “Primary”, “yes”);
106. }
107. }
108. }
109.
110. static bool IsDotNetAssembly(string fileName)
111. {
112. using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
113. {
114. try
115. {
116. using (BinaryReader binReader = new BinaryReader(fs))
117. {
118. try
119. {
120. fs.Position = 0x3C; //PE Header start offset
121. uint headerOffset = binReader.ReadUInt32();
122. fs.Position = headerOffset + 0×18;
123. UInt16 magicNumber = binReader.ReadUInt16();
124. int dictionaryOffset;
125. switch (magicNumber)
126. {
127. case 0x010B: dictionaryOffset = 0×60; break;
128. case 0x020B: dictionaryOffset = 0×70; break;
129. default:
130. throw new Exception(“Invalid Image Format”);
131. }
132.
133. //position to RVA 15
134. fs.Position = headerOffset + 0×18 + dictionaryOffset + 0×70;
135.
136. //Read the value
137. uint rva15value = binReader.ReadUInt32();
138. return (rva15value != 0);
139. }
140. finally
141. {
142. binReader.Close();
143. }
144. }
145. }
146. finally
147. {
148. fs.Close();
149. }
150. }
151. }
152. }
153. }

XML Parsing in .Net 2009/03/18

Posted by onlineall in CSharp.
add a comment

While working on wxDesktop, which was my first attempt at parsing data from an XML source, I tried to find the best way to read and parse out certain data from an XML document. Before I even really got to the meat of it, I had just had it in my head that I was going to use a DataSet. Why would I do that, you ask? Well, when that’s all you know how to do, that’s usually what you go with. I have never dealt with the XML namespace, mostly because I haven’t had to. Now that I have, I know I will never use the crappy DataSet class for this ever again.

Take the following XML document.

Sample set name
5
4
6
1
20
54
10
19
17
15

Second Sample set name
10
12
14
2
4
6
8
16
18
20

When you read this document in using the DataSet.ReadXML function like this:

private void readXMLDataSet(string FileName)
{
DataSet ds = new DataSet();
ds.ReadXml(“test.xml”);
}

The DataSet will contain 3 tables. It will contain the tables called data, value, and otherdata. Not really what you would think would be in there… It sort of makes sense, except for the fact that all value columns are then lumped into one table and then linked via an ID. While this isn’t too bad, and it would probably be usable, it’s not ideal, nor fun to use.

Now take this piece of code:

private void readXMLNodes(string FileName)
{
System.Xml.XmlDocument xd = new System.Xml.XmlDocument();
xd.Load(“test.xml”);

System.Xml.XmlNode node = xd.SelectSingleNode(“/test/data”);
System.Xml.XmlNode nameNode = node.SelectSingleNode(“name”);
System.Xml.XmlNodeList valueList = node.SelectNodes(“value”);
}

This uses the XmlDocument, XmlNode, and XmlNodeList classes and allows you to easily parse out certain parts of the XML document to find exactly what you need. nameNode.InnerText will show “Sample set name” and the valueList will contain every value in the data section. So valueList[0].InnerText will be 5.

Now, you could shove this information into a correctly laid out DataSet, but why bother? Now I see the value in creating a class just to parse certain XML documents. You can set the class up so that it will match the structure as it should be logically laid out and use the class to just access the nodes as needed through functions.

How VB.Net can screw up a newbie without them knowing it. 2009/03/18

Posted by onlineall in VB.Net.
add a comment

Let me preface this by saying I have no hate for VB.Net. When used properly, it is just as good as C#. However, VB as a whole has gotten a bad rep because generally the people that flock to it are the ones with no formal programming experience. Thus, they don’t really always know what they are doing. I’ve seen it a lot. I will also admit that I have done a lot of these horrendous things that I should have never done. In fact, this post is about something I did that turned out way bad when we fast forward a few years later (yes, it took that long for me to find this problem.)

Here is the problem and the solution I came up with over two years ago. We have a very large MDI application that has a lot of different forms. We didn’t want users opening up multiple copies of those forms, plus we wanted a neat way to bring certain forms to the foreground if they were already open.

My simple and what I thought was ingenious solution was to create a few functions that just check to see if the form is open… Something like this:

Public Function IsFormOpen(ByVal oFormName As Form) As Boolean
Dim bAlreadyShown As Boolean = False
Dim obj As Form
For Each obj In Me.MdiChildren
If obj.Name = oFormName.Name Then
bAlreadyShown = True
End If
Next
Return bAlreadyShown
End Function

When I wrote that and it worked, it made sense to me that it would just pass in the type and I could get the name of the type and it would match and the world would be happy…

Now fast forward 2 years and we are getting some complaints of the application/whole computer being slow after running for a while. This smelled of memory leaks (which we have had lots of in the past). So I started doing my memory leak testing and now I am seeing all of our forms still being held open when they should be closed!

So turning off just my code debugging and putting a breakpoint in InitializeComponent() told me a big huge story. A story of how VB “helps” a developer by just doing things for them without them knowing. It seems that before going into the IsFormOpen function call, VB creates an object of that form type and passes in that object. That object never gets destroyed, either. Dispose on that object only gets called when the application exits. Nice, huh? So every time we open a form, we actually are creating two and only opening one. One simple change fixed it:

Public Function IsFormOpen(ByVal oFormName As System.Type) As Boolean
Dim bAlreadyShown As Boolean = False
Dim obj As Form
For Each obj In Me.MdiChildren
If obj.Name = oFormName.Name Then
bAlreadyShown = True
End If
Next
Return bAlreadyShown
End Function

Incidentally, C# wouldn’t allow the first function to happen. It won’t even compile in any way, shape, or form (at least in the way I was trying to use it). It tells me the obvious, I was trying to use a Type as a variable. VB “helps” you with that…

ASP.Net MVC versus Web Forms 2009/03/16

Posted by onlineall in ASP.Net.
add a comment

We are going to be converting a fairly good sized project over to ASP.Net soon. The process of planning has already started. I have been investigating our options and trying to decide what would work best for us.

I checked out ASP.Net MVC first because that seems to be all the rage right now. I can definitely see why people are excited. I had a fully functioning site up and running in no time. It’s definitely a great framework and introduces a lot of very nice features. I love the testability it introduces into the whole equation, even the view.

What I don’t like, is the fact that it doesn’t support 3rd party controls. With the exception of Telerik and possibly Infragistics, others are SOL right now until the component vendors get their components up to speed. I know Jeff Handley had posted a few articles about extending the base controls, but I’m talking about some serious controls, like Dev Express’s grids and asp editors. I know there are options, but we will never ditch Dev Express. They have been too good to us. When I can e-mail the CTO with an off the wall management question and have him respond within 8 hours, and submit a message to a general e-mail with some questions about their plans and get a phone call from them within 8 hours, that’s not only a company that makes a great product, but a company that cares (shameless plug for you guys Julian and Mehul).

So I started to investigate other options. When searching for some silverlight info, one of my dudes discovered Visual WebGui. Visual WebGui was pretty slick. You drag and drop onto what looks like a Windows Form and where you drop it is where it renders when viewed through IE or Firefox. They also had a small tutorial on how to take a regular Windows forms application and quickly convert it to a full blown ASP.Net web application. This would have been awesome, except we don’t use Windows Forms controls for anything, not even our forms. Everything is from Dev Express, so it doesn’t convert so hotly. I did some more research and you can get those 3rd party controls to work, but it’s not the greatest or easiest to work with. Their quick solution would eventually turn into a code nightmare. So much for that.

Looks like it’s time to dig out the new routing engine in ASP.Net and start wiring up some of my own code. I really wanted to use MVC, but if the UI is going to suck, our users will absolutely hate us.

Disposing of memory leaks. 2009/03/16

Posted by onlineall in VB.Net.
add a comment

One of the biggest causes of memory leaks that I find from time to time is from forgetting to dispose of some SQLConnection object. Even if the connection is closed, the SQLConnection object seems to stay alive forever, which keeps any form objects alive as well.

This happens in VB a lot if you forget to call the Dispose method of the SQLConnection object or if you don’t use the Using keyword. While cleaning up code, the best way to get in the habit of doing it, seems to be changing everything into a Using statement so I get in the habit of using it…
Instead of:
view plaincopy to clipboardprint?

1. Dim myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable”)
2. Dim myConnection As New SqlClient.SqlConnection(ConnectionString)
3. myCommand.Connection = myConnection
4. myConnection.Open()
5. Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
6. myConnection.Close()
7. myConnection.Dispose()
8. myCommand.Dispose()

Dim myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable”)
Dim myConnection As New SqlClient.SqlConnection(ConnectionString)
myCommand.Connection = myConnection
myConnection.Open()
Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
myConnection.Close()
myConnection.Dispose()
myCommand.Dispose()

Do this:
view plaincopy to clipboardprint?

1. Using myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable”)
2. Using myConnection As New SqlClient.SqlConnection(ConnectionString)
3. myCommand.Connection = myConnection
4. myConnection.Open()
5. Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
6. End Using
7. End Using

Using myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable”)
Using myConnection As New SqlClient.SqlConnection(ConnectionString)
myCommand.Connection = myConnection
myConnection.Open()
Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
End Using
End Using

Since the IDisposable interface on the SQLConnection object will automatically close open connections, there is no need to close it… However, if you decide to do this:
view plaincopy to clipboardprint?

1. Using myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable” _
2. , New SqlClient.SqlConnection(ConnectionString))
3. myCommand.Connection.Open()
4. Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
5. myCommand.Connection.Close()
6. End Using

Using myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable” _
, New SqlClient.SqlConnection(ConnectionString))
myCommand.Connection.Open()
Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
myCommand.Connection.Close()
End Using

Make sure you close the connection first… Although, I don’t recommend doing it that way, because the IDisposable interface of the SQLCommand object does not call the IDisposable interface of the SqlConnection object that it holds. I’ve read that all the SqlConnection object’s IDisposable interface does is close an open connection, I wouldn’t trust that completely and dispose of it properly.

TableAdapter Connection Strings 2009/03/16

Posted by onlineall in VB.Net.
add a comment

I don’t know how a lot of people handle it, but one thing that always bugged me about table adapters and datasets is the way it handles it’s connection strings. It starts out innocent enough. A new dataset is created with it’s associated table adapter. The connection string is saved to the application settings file and that property is saved in the dataset. Running it on the development machine (or on the same network) is no big deal and just works. But what if you send that application to someone else that has their own SQL Server?

Did anyone at Microsoft actually use this scenario in a production environment? What were they thinking?

In order to run that in a production environment you have to set the connection string in the app.config file. While this may be fine for some people, what about the people like me that do not want my users to access that database? I don’t want them to have that username and password… While the chances of a normal user loading SQL Server Management Studio and logging in are slim, it’s still possible and it’s definitely possible if a user purposely wants to get out of having to do work that day.

What are the options? Well, one option is to use the encryption to encrypt the settings in app.config. For me, this option is not ideal. Reports of those settings getting corrupted are quite high, plus you have to deal with the loading and saving of those settings, which isn’t all that easy to do.

The other option is to take the route I was taking for a while… I had a function that would build me a connection string. Then I could: TableAdapter.Connection.ConnectionString = myLibrary.ConnectionStringFunction()

This was great, until that day came where I was in a hurry and added a few more tables to a form but forgot to set the ConnectionStrings. Whoops.

So I needed a solution that would stop me from having to set those ConnectionString properties, keep my connection string out of the app.config, and be easy to use (i.e. Just Works).

I started out by just giving all my datasets the same connection string. Then on application startup, I tried to change that one application setting. Hmm.. It seems those ConnectionString properties are set to friend and are read only.

Upon further investigation, it seems that there are some events that fire, such as SettingsLoaded. This event fires when the app.config is read and all the settings are loaded. When this event fires, it fires inside the MySettings class. This should allow that property to be changed.
view plaincopy to clipboardprint?

1. Private Sub MySettings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded
2. Me.Item(“MyAppConnectionString”) = MyLibrary.BuildConnectionString()
3. End Sub

Private Sub MySettings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded
Me.Item(“MyAppConnectionString”) = MyLibrary.BuildConnectionString()
End Sub

This will set the MyAppConnectionString setting to the proper connection string. Now, all table adapters will have an up to date connection string.

So what happens if you want to change the connection string later while the application is still running? Well, there is no way to do that. So it’s time to come up with a way to trick it into updating that property.

In looking at the MySettings class, there is another event called PropertyChanged. We can use this event if we create another setting that can be updated anywhere in the application. First, we create a new string setting that has a User scope (I called mine ConnectionString). This will allow the application to update the setting at any time.

Next, we need to create a function that will update that property with our connection string.
view plaincopy to clipboardprint?

1. Public Shared Sub ChangeConnectionString()
2. My.Settings.ConnectionString = BuildConnectionString()
3. End Sub

Public Shared Sub ChangeConnectionString()
My.Settings.ConnectionString = BuildConnectionString()
End Sub

Now we can change the events in the MySettings class to look like this.
view plaincopy to clipboardprint?

1. Private Sub MySettings_PropertyChanged(ByVal sender As Object, ByVal e As System.ComponentModel.PropertyChangedEventArgs) Handles Me.PropertyChanged
2. If e.PropertyName = “ConnectionString” Then
3. Me.Item(“MyAppConnectionString”) = My.Settings.ConnectionString
4. End If
5. End Sub
6.
7. Private Sub MySettings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded
8. MyLibrary.ChangeConnectionString()
9. End Sub
10. End Class

Private Sub MySettings_PropertyChanged(ByVal sender As Object, ByVal e As System.ComponentModel.PropertyChangedEventArgs) Handles Me.PropertyChanged
If e.PropertyName = “ConnectionString” Then
Me.Item(“MyAppConnectionString”) = My.Settings.ConnectionString
End If
End Sub

Private Sub MySettings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded
MyLibrary.ChangeConnectionString()
End Sub
End Class

Now, every time that ChangeConnectionString() is called, the MyAppConnectionString will be updated. The ChangeConnectionString procedure can be changed so that it can accept a string parameter that is the actual connection string. Then you can build a Connection String anywhere and just pass it to that procedure.

Disposing of memory leaks. 2009/03/16

Posted by onlineall in .Net Code.
add a comment

One of the biggest causes of memory leaks that I find from time to time is from forgetting to dispose of some SQLConnection object. Even if the connection is closed, the SQLConnection object seems to stay alive forever, which keeps any form objects alive as well.

This happens in VB a lot if you forget to call the Dispose method of the SQLConnection object or if you don’t use the Using keyword. While cleaning up code, the best way to get in the habit of doing it, seems to be changing everything into a Using statement so I get in the habit of using it…
Instead of:
view plaincopy to clipboardprint?

1. Dim myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable”)
2. Dim myConnection As New SqlClient.SqlConnection(ConnectionString)
3. myCommand.Connection = myConnection
4. myConnection.Open()
5. Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
6. myConnection.Close()
7. myConnection.Dispose()
8. myCommand.Dispose()

Dim myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable”)
Dim myConnection As New SqlClient.SqlConnection(ConnectionString)
myCommand.Connection = myConnection
myConnection.Open()
Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
myConnection.Close()
myConnection.Dispose()
myCommand.Dispose()

Do this:
view plaincopy to clipboardprint?

1. Using myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable”)
2. Using myConnection As New SqlClient.SqlConnection(ConnectionString)
3. myCommand.Connection = myConnection
4. myConnection.Open()
5. Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
6. End Using
7. End Using

Using myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable”)
Using myConnection As New SqlClient.SqlConnection(ConnectionString)
myCommand.Connection = myConnection
myConnection.Open()
Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
End Using
End Using

Since the IDisposable interface on the SQLConnection object will automatically close open connections, there is no need to close it… However, if you decide to do this:
view plaincopy to clipboardprint?

1. Using myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable” _
2. , New SqlClient.SqlConnection(ConnectionString))
3. myCommand.Connection.Open()
4. Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
5. myCommand.Connection.Close()
6. End Using

Using myCommand As New SqlClient.SqlCommand(“Select Count(*) from MyTable” _
, New SqlClient.SqlConnection(ConnectionString))
myCommand.Connection.Open()
Dim iCount As Integer = CInt(myCommand.ExecuteScalar())
myCommand.Connection.Close()
End Using

Make sure you close the connection first… Although, I don’t recommend doing it that way, because the IDisposable interface of the SQLCommand object does not call the IDisposable interface of the SqlConnection object that it holds. I’ve read that all the SqlConnection object’s IDisposable interface does is close an open connection, I wouldn’t trust that completely and dispose of it properly.

Windows Installer – SQL Server Installation Issues and a Solution. 2009/03/16

Posted by onlineall in .Net Code.
add a comment

Here is the problem in a nutshell: You have a custom client application that uses SQL Server 2005 Express. The server installation is a little bit to be desired. First, you have to install SQL Server Express, then you have to configure the options (unless you did command line options), then you have to execute the scripts to create your database, tables, and possibly populate it with some data. Or conversely, execute scripts or install and use the management console to attach a database. Messy, at best.

So what if you want to have a single installation program that will install all the prerequisites (.Net), then install SQL Server 2005 with your custom options (instance name for example), and then have it execute scripts and fill it with data? After working at it for hours, I came to the conclusion that you could be out of luck. Until I did some reconfiguring.

First things first, you need to get SQL Server Express to install properly. I struggled with this for a while, until I had the bright idea of using a bootstrapper… But can’t we use the one that comes with the Visual Studio Package and Deployment Wizard? Yes you can!

Go into your Visual Studio 8 folder and find the SDK/v2.0/Bootstrapper/Packages folder. Make a copy of the SqlExpress folder and name it something like, MyAppSqlExpress. Inside that folder, there is a product XML file. Edit that product.xml file and change the product code. Mine was Microsoft.Sql.Server.Express.1.0 and I changed it to MyAppName.Microsoft.Sql.Server.Express.1.0 … Just change the MyAppName to the app name of the product it goes with. You will also see a En folder for the English installation. The package.xml file is the one you want to edit in that folder. The first item to edit, is the arguments line. This passes arguments to the SqlExpr32.exe file (the setup for Sql Express). The arguments are the same as any other SQL Express install… So change it to be something like this:
view plaincopy to clipboardprint?

1. Arguments=’-q /norebootchk /qb reboot=ReallySuppress addlocal=all
2. INSTANCENAME=MyApp SECURITYMODE=SQL SAPWD=MyStrongPassword
3. DISABLENETWORKPROTOCOLS=0 SQLAUTOSTART=1′

Arguments=’-q /norebootchk /qb reboot=ReallySuppress addlocal=all
INSTANCENAME=MyApp SECURITYMODE=SQL SAPWD=MyStrongPassword
DISABLENETWORKPROTOCOLS=0 SQLAUTOSTART=1′

This will do a silent installation of SQL Server Express using all protocols, a custom instance name (MyApp), turn on the SQL security mode and specify a strong SA password.

Next, there is the strings block at the bottom. Edit the StringName “DisplayName” to look like this:
view plaincopy to clipboardprint?

1. MyApp SQL Server 2005 Express Edition

MyApp SQL Server 2005 Express Edition

Save it, and go into your setup and deployment project you created, go to the project properties, then to the prerequisites, and in the list you should now see your custom install of SQL Server Express!

This does work, I just tested it out today.

So that solves problem #1. What about the problem of executing scripts? That seems to be the easy part… From my earlier post you can incorporate that code into a custom action.

Create a new project, and select Installer Class from the templates. This creates a new installer class for you to augment.
view plaincopy to clipboardprint?

1. Private Sub ExecuteSQL(ByVal SQL As String, _
2. ByVal ConnectionString As String)
3.
4. Dim srv As New Server(New ServerConnection( _
5. New SqlClient.SqlConnection(ConnectionString)))
6.
7. srv.ConnectionContext.ExecuteNonQuery(SQL)
8. srv.ConnectionContext.Disconnect()
9. End Sub
10.
11. Private Sub BulkLoad(ByVal ConnectionString As String, _
12. ByVal TableName As String)
13.
14. Dim bulk As New SqlClient.SqlBulkCopy(ConnectionString, _
15. SqlClient.SqlBulkCopyOptions.KeepIdentity)
16.
17. Dim ds As New DataSet
18. Dim path As String = My.Application.Info.DirectoryPath
19. ds.ReadXmlSchema(path & “\” & TableName & “.xsd”)
20. ds.ReadXml(path & “\” & TableName & “.xml”)
21. bulk.BulkCopyTimeout = 0
22. bulk.DestinationTableName = TableName
23. bulk.WriteToServer(ds.Tables(TableName))
24. bulk.Close()
25. End Sub
26.
27. Public Overrides Sub Install(ByVal stateSaver As _
28. System.Collections.IDictionary)
29.
30. MyBase.Install(stateSaver)
31. Dim ConnBuilder As New SqlClient.SqlConnectionStringBuilder()
32. ConnBuilder.UserID = “sa”
33. ConnBuilder.Password = “MyStrongPassword”
34. ConnBuilder.DataSource = “localhost\MyApp”
35. ConnBuilder.InitialCatalog = “master”
36. ExecuteSQL(My.Resources.CreateDatabaseSQL, _
37. ConnBuilder.ConnectionString())
38.
39. ConnBuilder.InitialCatalog = “MyDatabase”
40. BulkLoad(ConnBuilder.ConnectionString(), “MyTable”)
41. End Sub

Private Sub ExecuteSQL(ByVal SQL As String, _
ByVal ConnectionString As String)

Dim srv As New Server(New ServerConnection( _
New SqlClient.SqlConnection(ConnectionString)))

srv.ConnectionContext.ExecuteNonQuery(SQL)
srv.ConnectionContext.Disconnect()
End Sub

Private Sub BulkLoad(ByVal ConnectionString As String, _
ByVal TableName As String)

Dim bulk As New SqlClient.SqlBulkCopy(ConnectionString, _
SqlClient.SqlBulkCopyOptions.KeepIdentity)

Dim ds As New DataSet
Dim path As String = My.Application.Info.DirectoryPath
ds.ReadXmlSchema(path & “\” & TableName & “.xsd”)
ds.ReadXml(path & “\” & TableName & “.xml”)
bulk.BulkCopyTimeout = 0
bulk.DestinationTableName = TableName
bulk.WriteToServer(ds.Tables(TableName))
bulk.Close()
End Sub

Public Overrides Sub Install(ByVal stateSaver As _
System.Collections.IDictionary)

MyBase.Install(stateSaver)
Dim ConnBuilder As New SqlClient.SqlConnectionStringBuilder()
ConnBuilder.UserID = “sa”
ConnBuilder.Password = “MyStrongPassword”
ConnBuilder.DataSource = “localhost\MyApp”
ConnBuilder.InitialCatalog = “master”
ExecuteSQL(My.Resources.CreateDatabaseSQL, _
ConnBuilder.ConnectionString())

ConnBuilder.InitialCatalog = “MyDatabase”
BulkLoad(ConnBuilder.ConnectionString(), “MyTable”)
End Sub

In this example, I just stored the data files (an XML data file and an XSD schema file) in the setup project, which gets installed along with the custom installer DLL. The create database SQL stuff was all saved as a store resource in the installer class project. I did this so that some of the structure was hidden, which may or may not be an issue for others.

Now, add the custom action to a setup project and you are finished!

Microsoft.SqlServer.Smo, where have you been all my life? 2009/03/16

Posted by onlineall in .Net Code.
add a comment

I have had a need, for quite some time, to have a comprehensive SQL Server tool where I could execute scripts, import/export tables, and generally just run a few queries. In the past, I have been using the free SQL Management Studio Express edition, which works just fine. The problem with this utility, is using it at customer installations. The install requires administrator privileges, which work for most places, but make it impossible to install at others. So, the need to have a small utility that I can run along side my application is pretty great.

I started out just using the SQLClient name space. While it worked well for simple queries, trying to run a comprehensive database update script just killed it. While it would probably work, I would have to parse the script first, stripping out all the GO statements and breaking them up into separate scripts. That worked fine until I ran into the first transaction… Not good either, apparently.

Feeling dead in the water, I did some searching and came across a name space I had not known existed. It’s Microsoft.SqlServer.Smo. It is the replacement for Nmo and has quite a few really nice features.

One such feature is the ability to use .ExecuteNonQuery() without having to break apart or parse existing update scripts, including ones with built in transaction support!

It’s pretty simple to use and combined with the SqlClient name space, I was able to create a pretty comprehensive solution for my needs.

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

Dim conn As New ServerConnection(SQLAuthInfo.GetSQLConnection())
Dim srv As New Server(conn)
dropdown.Items.Clear()
For i As Integer = 0 To srv.Databases.Count – 1
dropdown.Items.Add(srv.Databases.Item(i).Name)
Next
conn.Disconnect()

That’s as simple as it gets to iterate through all the databases on the server. The SQLAuthInfo is a class I designed that will just create a SqlConnection object based on preset values.

Need to execute a script? No problem…

Dim conn As New ServerConnection(SQLAuthInfo.GetSQLConnection())
Dim srv As New Server(conn)
Dim cmd As String = ScriptMemoEdit.Text

srv.ConnectionContext.ExecuteNonQuery(cmd, ExecutionTypes.ContinueOnError)

conn.Disconnect()

It didn’t take me long to use many of the features… For import and export, I just used a select query to get all the data from one table and saved it into a dataset. Then I used the dataset’s WriteXML and WriteXMLSchema functions to save it to files. Then with the import function, I just read in the schema and data into a dataset and used the SqlBulkCopy object to save it to the target database table. Pretty easy stuff. This also gives me some new options for our server installation program I need to fix (i.e. create).

XML Parsing in .Net 2009/03/16

Posted by onlineall in .Net Code.
add a comment

While working on wxDesktop, which was my first attempt at parsing data from an XML source, I tried to find the best way to read and parse out certain data from an XML document. Before I even really got to the meat of it, I had just had it in my head that I was going to use a DataSet. Why would I do that, you ask? Well, when that’s all you know how to do, that’s usually what you go with. I have never dealt with the XML namespace, mostly because I haven’t had to. Now that I have, I know I will never use the crappy DataSet class for this ever again.

Take the following XML document.

Sample set name
5
4
6
1
20
54
10
19
17
15

Second Sample set name
10
12
14
2
4
6
8
16
18
20

When you read this document in using the DataSet.ReadXML function like this:

private void readXMLDataSet(string FileName)
{
DataSet ds = new DataSet();
ds.ReadXml(“test.xml”);
}

The DataSet will contain 3 tables. It will contain the tables called data, value, and otherdata. Not really what you would think would be in there… It sort of makes sense, except for the fact that all value columns are then lumped into one table and then linked via an ID. While this isn’t too bad, and it would probably be usable, it’s not ideal, nor fun to use.

Now take this piece of code:

private void readXMLNodes(string FileName)
{
System.Xml.XmlDocument xd = new System.Xml.XmlDocument();
xd.Load(“test.xml”);

System.Xml.XmlNode node = xd.SelectSingleNode(“/test/data”);
System.Xml.XmlNode nameNode = node.SelectSingleNode(“name”);
System.Xml.XmlNodeList valueList = node.SelectNodes(“value”);
}

This uses the XmlDocument, XmlNode, and XmlNodeList classes and allows you to easily parse out certain parts of the XML document to find exactly what you need. nameNode.InnerText will show “Sample set name” and the valueList will contain every value in the data section. So valueList[0].InnerText will be 5.

Now, you could shove this information into a correctly laid out DataSet, but why bother? Now I see the value in creating a class just to parse certain XML documents. You can set the class up so that it will match the structure as it should be logically laid out and use the class to just access the nodes as needed through functions.

Follow

Get every new post delivered to your Inbox.