Setup and Deployment Packages in Visual Studio 2008
Setup and deployment projects are frequently used by developers looking for a user friendly means to install their custom application on end users’ computers. The result of creating a setup and deployment project is a portable and easy to use MSI installer package for your software. To create a bare minimum setup and deployment package for a Visual Studio application, follow these steps (written specifically for Visual Studio 2008).
1) Right click on your Visual Studio solution in the Solution Explorer panel in VS. Select “Add -> New Project…”
2) In the available project listing, select “Setup Project”. Give it a name and location and click OK.
3) At this point, Visual Studio will display the file system on the target machine in your main development panel. Open the Application Folder here. Right click on the blank file system and select “Add -> Project Output…”
4) Select the project that you’re creating this package for in the drop down listing. Then select the “Primary Output” option. Leave the rest of the settings as is and click OK.
5) You should see the primary output as well as any necessary dll files listed in the application folder in the development window. This is good. From here, right click on the setup and deployment project name in the solution explorer and select “Build”.
6) Now you’re done! The MSI and setup installation files for your custom application will be residing in the output folder that you specified when initially creating the package.
Intalling the AJAX toolkit for Visual Studio 2008
AJAX is a wonderful tool for dynamic web development. Samples of the tools included with the toolkit can be found here:
http://www.asp.net/ajax/ajaxcontroltoolkit/samples/
To install the latest edition of the AJAX toolkit for Visual Studio (in this case, Visual Studio 2008). Take the following steps.
1) Download the toolkit at http://www.asp.net/ajaxlibrary/download.ashx
2) Extract the .ZIP file downloaded anywhere on your hard drive.
3) Open Microsoft Visual Studio 2008. Right click on an empty space in the toolkit window on the left side of the screen. Select “Add Tab”.
4) Give the tab a name. Right click under the named tab and select “Choose Items…”. Once the .COM object window appears, click the “Browse” button.
5) Navigate to the folder where you extracted the AJAX toolkit .ZIP file. Select the AjaxControlToolkit.dll file there. Click OK to add the toolkit – and you’re done!
Using CASE statements in T-SQL
One of the more useful and flexible expressions in T-SQL is the CASE statement. Have you ever been asked to write a report that returns data in an unnatural form, or with slight alterations to the stored fields? Using a SELECT CASE can usually fit your needs without altering database table structure. Lets go through a couple of examples.
Customer A wants a report of all of their vendors and total dollar sums for each type of item purchased. You have a table in your database listing all orders which have fields for the vendor, the type of order, and the billing amount. Writing a query like the following will return the requested data.
SELECT Vendor, SUM(Type_A$), SUM(Type_B$)
FROM (SELECT Vendor, CASE WHEN Type = ‘A’ THEN Billing$ ELSE 0.0 END AS TypeA_$, CASE WHEN Type = ‘B’ THEN Billing$ ELSE 0.0 END AS TypeB_$ FROM Orders) AS VMatrix
GROUP BY Vendor
Customer B wants a report that determines and displays a category for orders made (category being a field type that is not stored in the database).
SELECT Vendor, Order,
CASE
WHEN Billing$ < 10.0 THEN ‘Cheap’ WHEN Billing$ >= 10.0 AND Billing$ < 100.0 THEN ‘Average’
ELSE ‘Expensive’
END AS BillingCategory
FROM Orders
Search T-SQL stored procedures containing text string.
I’ve written thousands of stored procedures that I barely remember. So, as you can imagine, sometimes I’d like to search an entire database for a specific text string. Here’s a simple SQL query that will accomplish this:
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE ‘%searchtext%’ AND OBJECTPROPERTY(id, ‘IsProcedure’) = 1
GROUP BY OBJECT_NAME(id)
I’ve used this with success in management studio for SQL 2008, SQL 2005, and through query analyzer in SQL 2000.
Add or edit SQL 2008 DSN connections in VB.NET
Some programs created in Visual Studio 2008 require a SQL Server 2008 DSN connection to do various things like access databases, run Crystal Reports, etc. When deploying a program that needs a particular SQL 2008 DSN, it’s a general pain to have to manually create a new SQL Native Client 10.0 DSN on every computer that the program is deployed too. Wouldn’t you want to detect the presence of the needed SQL DSN (which we’ll call SQLDSN), create it if it doesn’t exist, and update it to include the needed server and database name if it does? This can be done using API calls. The first thing that you need to do is include the following line of code in the module, class, or form that will be adding or editing your DSN connection.
Private Declare Function SQLConfigDataSource Lib “ODBCCP32.DLL” (ByVal hwndParent As Integer, ByVal fRequest As Integer, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Integer
This essentially includes an entry point to the SQLConfigDataSource function in ODBCCP32.DLL. Go to the MSDN definition of SQLConfigDataSource for more information about this function. Now – I have utilized this function first by creating my DSN attributes as follows.
Dim attr As String = “SERVER=” & server & Chr(0) & “DSN=SQLDSN” & Chr(0) & “DESCRIPTION=SQL 2008 DSN” & Chr(0) & “DATABASE=” & database & Chr(0)
Then I call the SQLConfigDataSource function with the following syntax to add the DSN.
SQLConfigDataSource(0, 4, “SQL Server Native Client 10.0″, attr)
And with the following syntax to edit the DSN.
SQLConfigDataSource(0, 5, “SQL Server Native Client 10.0″, attr)
The second parameter indicates what type of datasource the connection is (user or system), and also the action being taken (add, edit, or remove). Adding or editing a SQL Server 2008 DSN is as simple as this in VB.NET.
ISNULL vs. COALESE in T-SQL
As many SQL developers know, COALESCE and ISNULL can be used to achieve the same results. They both essentially check if a value is NULL and return something else if it is. Both functions are widely used from earlier versions of SQL to SQL Sever 2008 development. For anyone needing to brush up on their syntax, here are their MSDN definitions.
COALESCE
ISNULL
Some of the main advantages of using COALESCE over ISNULL are
On the flip-side, some of the advantages of using ISNULL are
Fixing the SQL 2000 MMC snap-in error
Earlier today, I was experiencing a SQL 2000 management console error. Unfortunately I have to maintain a SQL Server 2000 database in addition to developing a SQL Server 2008 database. The error stated “MMC could not create the snap-in”. This prevented me from viewing merge and transactional replication status for my SQL 2000 servers. This error can be resolved by installing Microsoft’s SQL 2005 backwards compatibility msi package. This sounds ridiculous, as any SQL 2005 package should have no effect on a SQL 2000 installation – but it works. This package can be found here:
http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
Access deleted datatable rows in VB.NET
Earlier today, I was working on a VB.NET project in Visual Studio 2008 where I really needed to access fields from user deleted data-table rows before accepting changes. Here’s the full story that led me to researching that action. I have a form with a DataTable that contains the result of a rather simple SQL SELECT query. This DataTable is mapped to a DataGridView control on my form. I want the user to be able to add and delete rows (among other things) without making changes to my database until the confirm their actions in bulk. Because of this, I need to access my uniqueidentifier guid field for each deleted row upon saving changes. Here’s essentially how I’m accomplishing this.
For Each dr As DataRow In dt.GetChanges.Rows
If dr.RowState = DataRowState.Deleted Then
Delete(dr(“ID”, DataRowVersion.Original))
End If
Next
You can access all changed rows in a datatable through the table’s GetChanges row collection. Each row in that collection has a “state” which indicates what action was performed on that row. In this case, I’m only interested in rows that were deleted – so I compare each row’s state to DataRowState.Deleted. The tricky part is accessing the deleted row’s data. The conventional means of accessing datarow fields (row.Item(“FieldName”)) does not work in this case. You’ll get an error stating “Deleted row information cannot be accessed through the row”. You need to use the row field’s overloaded function and pass in DataRowVersion.Original as a parameter (row(“ID”, DataRowVersion.Original)). This will access the deleted row’s original data. Once your program calls the AcceptChanges method on the DataTable, however, these rows will no longer be available.
Indexes in SQL Server 2008
As many experienced database developers know, table indexes are essential for optimal performance when working with large sets of data. In fact, a program that retrieves data from a large database can be ground to a halt if no database indexing is done. Thankfully, working with indexes are somewhat easier in SQL Server 2008 than they were in SQL Server 2000. First, you’ll want to analyze your database tables and any commonly used SELECT queries that are called by your program. One thing to keep in mind about indexes is that too many of them create overhead that will slow down UPDATE, INSERT, and DELETE statements. So – you want to be careful about how many indexes to create per table. Some of the best practices with a T-SQL index are the following.
Consult the Database Engine Tuning Advisor. This is an analytical program that comes with SQL Server 2008. Save some of your program’s more commonly used SELECT queries as .sql files. Then run those .sql files against your database via Database Engine Tuning Advisor. It’ll come up with some pretty good index and stats suggestions. It’s wise to listen to what it suggests in many cases (although it’ll often suggest too many indexes).
In your SELECT queries, analyze your WHERE clause and JOIN statements. The more commonly used fields in those two places deserve to be indexed. These should be in non-clustered indexes.
Make sure that your primary keys are indexed. Every table should have a unique primary key. SQL Server 2005/2008 will often generate indexes for those primary keys. In the event that those indexes are missing, create a clustered index on all primary key fields.
CTRL+L is your friend. When designing a query through SQL Server Management Studio, check it’s execution plan. A lot of times this will show you a spot in your query that’s really bottlenecking the overall execution of the command.
This should serve as guidance for index building.There are a lot of optimization options when it comes to SQL indexes, but I believe that these 4 tips will always lead you on the right path. If anybody has any other index strategies, I’d love to hear it in the comments section.
How to SEO Any WordPress Blog
Since this is a WordPress blog about software and web development, I thought it was only appropriate that the first post be about search engine optimization (SEO) specifically for WordPress blogs. With the popularity of WordPress, there are a number of blog topics out there relating to blog SEO. Some of these have great tips, some are outdated, and some are flat out overkill. Of the tips that I have followed – I’ve found the following to be most successful.
- Permalinks – The majority of your search engine traffic will point directly to specific posts or subpages within your blog. Having posts and subpages with search engine friendly URLs is extremely important. Google, Yahoo, Bing, et cetera do not think highly of a post with a URL like www.yourblog.com/post.php?p=548. Go to your permalink settings and change them to a custom structure that includes the post name (/%postname%/ is a pretty safe bet).
- Title Tag – You should have a keyword driven title tag, HOWEVER it should be proper English. A good structure is “Blog Title | Keyword driven description of your blog”. Make sure whatever niche that your driving towards is referenced in the title in a format that makes sense. Do you make cheesecakes? “Rachel’s Oven | New York style gourmet cheesecakes” would be an appropriate SEO title.
- Sitemap – Google really does care about this. Download and activate the “Google XML Sitemaps” plugin and it will do all of the dirty work for you.
- Robots.txt – You don’t really want pointless comment feeds and other non-SEO nonsense to be indexed. Create a robots.txt file in your root directory that looks something like this:
User-agent: *
Disallow: /cgi-bin
Disallow: /wp-admin
Disallow: /wp-includes
Disallow: /wp-content/plugins
Disallow: /wp-content/cache
Disallow: /wp-content/themes
Disallow: /trackback
Disallow: /feed
Disallow: /comments
Disallow: /category/*/*
Disallow: */trackback
Disallow: */feed
Disallow: */comments
Disallow: /*?*
Disallow: /*?
Allow: /wp-content/uploads# Google Image
User-agent: Googlebot-Image
Disallow:
Allow: /*# Google AdSense
User-agent: Mediapartners-Google*
Disallow:
Allow: /*# digg mirror
User-agent: duggmirror
Disallow: /Sitemap: http://www.yourblog.com/sitemap.xml
- Cache – Website performance can really kill your search engine ranking. If it takes the googlebot a long time to retrieve any of your pages, it will penalize you. Install and activate the “W3 Total Cache” plugin. It’s much simpler than the “WP Super Cache” and it accomplishes the same level of site performance improvement.
- Webhost – Choose your webhost wisely. Their performance issues can also damage your search engine ranking. This is why I would advise anyone to stay away from Dreamhost. There are several faster alternatives out there (maybe this will be it’s own topic later).
- Simplicity – Keep your blogs simple. This may be the most important aspect of WordPress SEO. If you download and use a custom theme – analyze its files thoroughly. I’ve seen themes that have footer.php files that are encoded so that people don’t delete the blog creator’s name. Some of these footer.php files are over 500kb in size. That’s absolutely ridiculous. If you don’t know how to trim a theme’s footer.php file, choose another theme. It’s not worth the performance downgrade.
Those are what I’ve found to be the most important aspects to consider when optimizing your WP blog for search engines. If anybody has any additional advice, feel free to comment on this article. I’ll update the list above as time goes on to prevent it from becoming outdated.
Filed Under SEO