Search results matching tag 'Code Samples'

How to: Delete rows from a table using a join on a second table

Jan Schreuder — Wed, 22 Oct 2008 08:43:00 GMT

In my current project, we have a tables where the primary key consists of two values. Based on the result of a query, we needed to delete a few rows from that table. The query returned the two key values and we needed to join that information in the delete statement. It's not rocket science, but it did cost us some searching before we had a solution. To make sure I don't forget, this small blog shows how we did it.

First of all, when you need to delete information from one table based on information from a second, then this is the way to go:

DELETE t1

FROM MyTable1 AS t1

inner join MYTABLE2 t2

    ON t1.KeyValue1= t2.KeyValue1 and t1.KeyValue2 = t2.KeyValue2

As you can see, you simply add the join and specify the key values for that join. However, we needed the result from a query. To do that, you can use the following solution:

DELETE t1

FROM MyTable1 AS t1

inner join (SELECT KeyValue1, KeyValue2 from MyTable2) t2

    ON t1.KeyValue1 = t2.KeyValue1 and t1.KeyValue2 = t2.KeyValue2

I simplified the query, because the original query is a lot more complex than this one. Again, it's not difficult, but could be useful if you run into a similar situation

How to - Create a missing designer.cs file

Jan Schreuder — Fri, 17 Oct 2008 09:49:00 GMT

I was running into this just now and found a quick way to solve it, so I just posted it for future reference.

I have an ASP.Net web application project in .Net 2.0 which was migrated from .Net 1.1 some time ago. I noticed that a few aspx pages did not have a designer.cs file. All controls on those pages were listed as protected variable in the main code behind page, like this example:

/// <summary>

/// An example label

/// </summary>

protected System.Web.UI.WebControls.Label Label2;

And it annoyed me. It was not consistent with the new pages in the application, and most other aspx pages that were also there during migration did have the designer.cs file. So how to solve this, in order to get a more consistent use of ASP.Net. I followed the following steps to do this:

First step - Make sure the latest version of the project is on your machine.
Second step is to remove the protected variables for the controls from the normal code behind cs file.
Now right-Click on the project and select 'Convert to Web Application'

The missing designer.cs is now created and added to the project. The protected variables, which were removed from the main code behind class are now in the partial class in the newly created designer.cs. The class in the original code behind class is also changed to a partial class.

If you forget step 2 and still want to remove the protected variables from your code behind class, then follow the following steps:

Remove the protected variables for the controls from the normal code behind cs file.
Open the ASPX file and make sure you view it in Source (HTML) mode
Select the entire HTML mark up using CTRL-A
Now press CTRL-K followed by CTRL-F

The HTML mark-up will be re-aligned and the designer.cs file will be recreated, including all the protected variables.

How to: Use OleDb to import text files (tab, csv, custom)

Jan Schreuder — Tue, 15 Jul 2008 11:52:00 GMT

Introduction

I have been browsing the web for a good and simple class to handle delimited file imports. My current assignment has an import option that needs to deal with that. However, the current implementation (using StreamReader) is not good enough. It doesn't handle all the exceptions you encounter with delimited files. I found a number of examples on the internet, but none of them really suited my needs. What I really missed was a simple example that I could extend so that it would suite my needs. So, being the developer that I am, I created my own class to import delimited files. After this was completed, I though I'd share it as an example to others.

Using StreamReader

The easiest way to process delimited files is to use a StreamReader object. You then simply open the file, read each line and then use the split method to get the various column values. For example:

public void ImportDelimitedFile(string filename, string delimiter)

    using (StreamReader file = new StreamReader(filename))

        string line;

        while ((line = file.ReadLine()) != null)

            
            if (line.Trim().Length > 0)

                string[] columns = line.Split(delimiter, StringSplitOptions.None);

                // Add code to process the columns

In a lot of cases this works just fine, but there are limitations to this scenario.

It's difficult to split a line into columns. For example, when you use Comma Separated File (CSV) it is well possible that a comma is in one of the columns. Using a simple string.Split is therefor not an option
When you only need certain columns or lines, you will need to scan all of them and handle all lines and filter what you need
It's not possible to return to a previous line

Using the Jet Engine

The above mentioned problems are eliminated when you use the Jet engine. The following code shows how a CSV file can be processed.

public void ImportCsvFile(string filename)

    FileInfo file = new FileInfo(filename);

    using (OleDbConnection con = new OleDbConnection(

        "Provider=Microsoft.Jet.OLEDB.4.0;Data
            Source=\"" + file.DirectoryName + "\";Extended Properties='text;HDR=Yes;FMT=Delimited(,)';"))

        using (OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM [{0}]", file.Name), con))

            con.Open();

            
            // Using a DataReader to process the data

            
            using (OleDbDataReader reader = cmd.ExecuteReader())

                while (reader.Read())

                    // Process the current reader entry...

            
            // Using a DataTable to process the data

            
            using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))

                DataTable tbl = new DataTable("MyTable");

                adp.Fill(tbl);

                foreach (DataRow row in tbl.Rows)

                {                   // Process the current row...

As you can see in the example, once you have the Command object, you have the option of using anything a command object will allow you to do. You could process the file using a DataReader object, create a DataTable object containing the data or even add a where clause to the CommandText of your Command object to specifiy better which data is to be imported.

Helper Class

Using this, and the information provided in this Microsoft Article, I created a small class that allows you to import delimited files. The class is very basic, but can easily be extended to suit your specific needs. This class will solve the most important issues when you're going to use Jet as your import engine.

Some things you need to consider when you are importing delimited files, be it with this class or using custom code:

The Jet engine makes assumptions about the content of the file. This can result in incorrect imports. For example, it might think a column contains date values. But in fact, you're file should treat the columns as a string. In these cases, you should create a Schema.Ini file that describes the type of value for each column. The class creates a Schema.Ini file before it opens the delimited file, but only to specify what the delimiter is. You may want to change this to use pre-defined ini files that describe your input file. Details on the Schema.Ini file can be found here .
The class uses an OleDbDataReader to read each line in the import file. But it is easily replaced with the option of adding the data into a DataSet or DataTable object. It's also possible to use SqlBulkCopy to instantly insert all the data into a SQL server database.
The above mentioned Microsoft Article is the best starting point for this type of import. You might want to read that before you start building imports for delimited files, even if this class is usefull to you. The article provides interesting background information and links to various Microsoft resources with more details and information.
The helper class uses an event to allow you to handle the information being read. You can, of course, also provide an overridable method.

Valuable resources

The information I used to to build this class was found on the Internet. I used the following resources:

Disclaimer

The code presented in the helper class is not an all-purpose import solution. It's just a basic class to help you build your own import class. If you need other import types, or a way to influence the content of the default Schema.ini file, you will need to do that your self. If you find any problems, please feel free to point them out to me.

You can download the file here

This article is also published on CodeProject.com.

Keep track of data changes using a SQL trigger

Jan Schreuder — Fri, 08 Feb 2008 09:26:00 GMT

In my current project, we calculate the status of tasks that a user needs to do and store the status in a table. This happens on a daily basis. Once every week, we calculate which items are overdue and inform the user by email to take appropriate action. However, sometimes our users complain that they receive such an email and that there aren't any tasks that require immediate action.

We know that this happens, but since we calculate the status once per day and when the user logs on to the application, we cannot really see why this happens. What we needed was a way to see when a tasks moves to another state. We decided to create a history table which contains the information in our status table and update that history table using a trigger. But the examples we found in Books on Line and other resources on the web just didn't give us the information we needed.

So I called my good friend Patrick Wellink who I know to be a SQL guru. He had written a generic trigger which can be used to audit changes and posted this on SqlServerCentral.Com. The script looks like this:

CREATE TRIGGER TRG_##YOUR_TABLE##

ON [DBO].[##YOUR_TABLE##]

FOR DELETE,INSERT,UPDATE

AS

-- JUST CHANGE ##YOUR_TABLE## INTO YOUR OWN TABLENAME TO MAKE IT WORK

DECLARE @ACT CHAR(6)

DECLARE @DEL BIT

DECLARE @INS BIT

DECLARE @SQLSTRING VARCHAR(2000)

SET @DEL = 0

SET @INS = 0

IF EXISTS (SELECT TOP 1 1 FROM DELETED) SET @DEL=1

IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @INS = 1

IF @INS = 1 AND @DEL = 1 SET @ACT = 'UPDATE'

IF @INS = 1 AND @DEL = 0 SET @ACT = 'INSERT'

IF @DEL = 1 AND @INS = 0 SET @ACT = 'DELETE'

IF @INS = 0 AND @DEL = 0 RETURN

IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[AUDIT_##YOUR_TABLE##]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)

BEGIN

    -- CREATE A MEMORY TABLE CONTAINING THE FIELDS AND TYPES OF THE TABLE

    DECLARE @MEMTABLE TABLE

        ID INT IDENTITY

        ,COLUMNAME SYSNAME

        ,TYPENAME VARCHAR(20)

    -- INSERT THE COLUMNAMES AND THE DATATYPES

    INSERT @MEMTABLE

        (COLUMNAME,TYPENAME)

        SELECT NAME,TYPE_NAME(XTYPE)

        FROM SYSCOLUMNS

        WHERE ID = OBJECT_ID('[DBO].[##YOUR_TABLE##]')

        ORDER BY COLID

    DECLARE @CUR INTEGER

    DECLARE @MAX INTEGER

    DECLARE @SQLSTR AS VARCHAR(8000)

    DECLARE @CURCOL SYSNAME

    DECLARE @COLTYPE AS VARCHAR(10)

    -- SETUP VARIABLES

    SET @SQLSTR = ''

    SET @CUR=1

    SELECT @MAX = MAX(ID) FROM @MEMTABLE

    -- LOOP EVEY FIELD

    WHILE @CUR <= @MAX

    BEGIN

        -- GET VALUES FROM THE MEMTABLE

        SELECT @CURCOL = COLUMNAME,@COLTYPE = TYPENAME FROM @MEMTABLE WHERE ID = @CUR

        IF @COLTYPE = 'INT' OR @COLTYPE = 'BIGINT' OR @COLTYPE='UNIQUEIDENTIFIER'

            -- WE DO WANT TO COPY INT/BIGINT/UNIQUEIDENTIFIER FIELDS BUT IF THEY ARE AN

            -- IDENTITY OR A ROWGUIDCOLUMN WE DO NOT WANT TO COPY THAT ATTRIBUTES

            SET @SQLSTR = @SQLSTR + ' CAST('+@CURCOL + ' AS '+@COLTYPE+') AS [' + @CURCOL +'] '

        ELSE

            -- ANOTHER FIELD DO NOTHING JUST COPY IT AS IT IS

            SET @SQLSTR = @SQLSTR + ' '+@CURCOL + ' AS [' + @CURCOL +'] '

        IF @CUR <= @MAX - 1 SET @SQLSTR=@SQLSTR + ','

        SET @CUR = @CUR + 1

END

    -- ADD THE AUDIT FIELDS

    SET @SQLSTR = @SQLSTR +',CAST(''      '' AS CHAR(6)) AS TRG_ACTION,CAST(GETDATE() AS DATETIME) AS TRG_DATE'

    -- SET UP THE SELECT FOR CREATING THE AUDIT TABLE

    SET @SQLSTR = 'SELECT TOP 0 ' + @SQLSTR + ' INTO [DBO].[AUDIT_##YOUR_TABLE##] FROM [DBO].[##YOUR_TABLE##]'

    EXEC(@SQLSTR)

END

IF @ACT = 'INSERT' INSERT [DBO].[AUDIT_##YOUR_TABLE##] SELECT *,'INSERT' ,GETDATE() FROM INSERTED

IF @ACT = 'DELETE' INSERT [DBO].[AUDIT_##YOUR_TABLE##] SELECT *,'DELETE' ,GETDATE() FROM DELETED

IF @ACT = 'UPDATE' INSERT [DBO].[AUDIT_##YOUR_TABLE##] SELECT *,'UPDATE' ,GETDATE() FROM INSERTED

It does almost everything we needed. It copies the information to a new table when something happens to the row in the original table. Thanks Patrick for helping us with this script.

We modified the code for the update section, because we only wanted to store the old information in the AUDIT table when certain fields are modified:

INSERT [DBO].[AUDIT_##YOUR_TABLE##]

SELECT DELETED.*,GETDATE()

FROM INSERTED

INNER JOIN DELETED ON DELETED.StatusID = INSERTED.StatusID

WHERE (INSERTED.Status <> DELETED.STATUS)

OR (INSERTED.CountAlerts <> DELETED.CountAlerts)

OR (INSERTED.AlertViewed <> DELETED.AlertViewed)

This way, we get the information as it was before the update and only when the fields that we want to monitor are changed. Works great!

How to: Quickly check if data exists in your SQL table

Jan Schreuder — Fri, 21 Dec 2007 07:22:00 GMT

I was confronted with a slow performing stored procedure in SQL server this week. The procedure was to retrieve one row from a table. But before that row could be retrieved, an update procedure was called to make sure all rows in that table had the correct status. That update procedure first checked the number of rows in a table to see if there were rows to be be updated. It used a piece of SQL similar to this:

CREATE PROCEDURE dbo.spMyProcedure

AS

   SET NOCOUNT ON

   IF (SELECT count(UserID) from UserTable) > 0

   BEGIN

       -- Process the items in the table

END

GO

This will obviously work. But on a table that contains more than 150,000 rows this can take some time. But why count the number of rows if all we need to know if there is at least one row present. I changed the IF statement a little and gave the procedure a new name so that I could compare both:

CREATE PROCEDURE dbo.spMyProcedureNew

AS

   SET NOCOUNT ON

   IF EXISTS(SELECT top 1 UserID from UserTable)

   BEGIN

       -- Process the items in the view

END

GO

I then called both with the Execution plan on and found this result:

A logical conclusion, just checking if there is one row in the table is much faster than checking of the number of records is more than zero. When you compare the subtree cost of the old procedure (0.726) with the subtree cost of the new procedure (0.00641), you can see a performance gain of a factor 113 . But how does SqlProfiler look at both queries? Well, here's that result:

As you can see, the duration of the old procedure averages at 34.4 and the new procedure averages at 12.2. The new proceduer is still a factor 2.8 faster than the old procedure. But you'll also need to look at the difference in CPU time (0 for the new procedure and 31 for the old procedure) and the number of reads (19 for the new compared to 709 for the old procedure) to conclude that checking for at least one row is a lot more efficient than counting the number of rows.

How to: Check if the specified URL really exists

Jan Schreuder — Wed, 12 Dec 2007 09:18:00 GMT

I think a lot of people need to do this, and we do in our project. We created a method that will check if URLs specified in our database (a couple of hundred) actually exist. Our support team uses the result of that check to update incorrect URLs in our application. I extracted the method and removed some project specific stuff to show it here:

/// <summary>

/// Checks the status of the specified URL.

/// </summary>

/// <param name="url">The URL that needs to be checked</param>

/// <param name="statusText">The status text associated with the specified URL</param>

/// <returns>An Integer value which is the result of the check action</returns>

public int CheckUrl(string url, out string statusText)

    int status = -1;

    statusText = "Unknown status";

try

        ServerXMLHTTP30Class http = new ServerXMLHTTP30Class();

        http.setTimeouts(5000, 5000, 5000, 5000);

        http.open("HEAD", url, Missing.Value, Missing.Value, Missing.Value);

        http.send(Missing.Value);

        status = http.status;

        // Status 200 specifies the http.send was successful

        if (status != 200)

            statusText = http.statusText;

        else

            statusText = "OK";

    catch (COMException com)

        statusText = com.Message;

    return status;

For most URLs, this method works fine for us. There are some cases where the method fails. For example, some urls (for intranet sites) require a user with sufficient access rights to be logged on. One problem kind of worries us. The http.Send method sometimes throws the following exception:

System.Runtime.InteropServices.COMException (0x80072EE2):

   Exception from HRESULT: 0x80072EE2

   at MSXML2.ServerXMLHTTP30Class.send(Object varBody)

I'm wondering if someone out there can tell me why, or maybe someone knows of a better (and faster) way to check if a URL exists.

Crystal Reports for Visual Studio 2005 deployment trouble

Jan Schreuder — Mon, 10 Dec 2007 12:53:00 GMT

I just spent 4 hours of figuring out why the reports we developed using Crystal Reports would not show the graphs after being deployed to our production server. We checked everything from access rights to proper installation of Crystal reports on the server. Below are a few things you may want to check when you need to deploy a .Net 2.0 web application that integrates Crystal Reports:

Deploy Crystal components

First, and rather obvious, is to make sure the correct version of Crystal components are installed on the server. If you have the Visual Studio SDK installed, you can look for CRRedist2005_x86.msi, which is in this folder C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\CrystalReports on your machine. Running that installation on the server will make sure all components required for Crystal Reports are properly installed on your machine.

If you have a setup project for your web application, you can add a merge module to that project. Crystal Reports provides four merge modules for download at this location. Depending on your needs, you may need to add any combination of these to your setup project. Which module is required for your application can be found in this document on the Business Objects support site.

Web.Config settings

In order for a Crystal Reports viewer to work, the following settings must be added to the appSettings section in the web.Config file:

<add key="CrystalImageCleaner-AutoStart" value="true"/>

<add key="CrystalImageCleaner-Sleep" value="60000"/>

<add key="CrystalImageCleaner-Age" value="120000"/>

This will ensure that the temporary images for Crystal are removed when they are no longer required. The following setting must be added the the httpHandlers section.

<add verb="GET" path="CrystalImageHandler.aspx" type="CrystalDecisions.Web.CrystalImageHandler,

    CrystalDecisions.Web, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>

This is required, because otherwise all you will see is an empty box with a red cross in it.

aspnet_client files

Last, but certainly not least, check the aspnet_client folder in the root folder of your web site. This folder should contain a folder with the name CrystalReportWebFormViewer3. That folder must exist in the following path: system_web\2_0_50727. If that folder is not there, copy it from your local machine. It should be in the folder C:\WINNT\Microsoft.NET\Framework\v2.0.50727\ASP.NETClientFiles.

.Net Zip library

Jan Schreuder — Wed, 31 Oct 2007 11:17:00 GMT

The MSDN blogs are a really great source for all sorts of information. I browse the main blog page regularly, just to get a feeling of what is happening on the Microsoft side of things. This morning I found a link to a Codeplex project which encapsulates the System.IO.Compression.DeflateStream class to allow you to read and write zip files.

The project can be found here: http://www.codeplex.com/DotNetZip.

Another "What the F**K" moment

Jan Schreuder — Tue, 02 Oct 2007 09:40:00 GMT

I tried compiling my application just now and got this error message from the compiler:

Preparing resources...

Updating references...

Performing main compilation...

error CS0583: Internal Compiler Error (0xc0000005 at address 77FCD43E): likely culprit is 'PARSE'.

An internal error has occurred in the compiler. To work around this problem, try simplifying or

changing the program near the locations listed below. Locations at the top of the list are closer

to the point at which the internal error occurred.

c:\inetpub\wwwroot\Intra\Hierarchy.aspx.cs: error CS0586: Internal Compiler Error: stage 'PARSE'

error CS0587: Internal Compiler Error: stage 'PARSE'

error CS0587: Internal Compiler Error: stage 'BEGIN'

Excuse me?!?!?! The last time I ever received errors like this was in 1996, while building applications for good-old MS-Dos. The Microsoft C compiler in some cases could not parse the code and presented you with a "Too complex" error. The only way around this was to re-order the code. But in the .Net age I never came across this error. Until now that is.

And what's even stranger is that a rebuild of the app solved the problem. I'm lost. Any one out there has any experience with this error?

What the F**K is the 'Grady' operator

Jan Schreuder — Mon, 01 Oct 2007 13:31:00 GMT

The web application I'm working on logs errors in a log file using Log4Net. Nothing new, loads of applications do that. But I was surprised when I found the following error message:

System.Data.SyntaxErrorException: Syntax error: Missing operand after 'grady' operator.

Now what in heavens name is a 'Grady' operator. I had no clue. Google only knows Grady from the book "Object-Oriented Analysis And Design With Applications" and other OO related work. So no solution to be found there. So what could it be?

I looked into the log file again and checked if the exception was raised more than once. I found more interesting 'operators': 'Toole', 'Amore','Sullivan'. Wait a minute there all names. I checked the stack traces for these exceptions and then it dawned on me. The error was raised when the user clicked a filter button. The web page where the error was raised showed a user list. Apparently, the user was looking for someone named O'Toole or O'Sullivan. I checked the code and found this:

if (txtUserFilter.Text != String.Empty)

    dataTable.DefaultView.RowFilter = string.Format("NAME like '%{0}%'", txtUserFilter.Text);

This will work fine of course, provided the text string does not contain any single quotes. The following solved my problem:

if(txtUserFilter.Text != String.Empty)

    dataTable.DefaultView.RowFilter = string.Format("NAME like '%{0}%'", txtUserFilter.Text.Replace("'", "''"));