Tuldok Lambat's Tech Blog

Backup Your SQL Data Into A Script

2006-03-08T21:58:00.000+08:00

The objective: Backup your SQL Server data into a script.

Tools of sorts can do this trivial task easy. One of the tools that can is MyGeneration. I'm not here to talk about this nifty tool though. I'm here to show you a nice TSQL script that can do that. Lo and behold.

SET NOCOUNT ON

-- create a temporary table to hold the table info of the current db
DECLARE @tables TABLE (
seq int identity(1,1)
,id int
,name varchar(100))

INSERT INTO @tables
SELECT s.id, s.name
FROM sysobjects s
WHERE s.xtype='U'
AND s.status > 0
ORDER BY s.name

-- create a temporary table to hold the columns of the table being process
DECLARE @columns TABLE (
name varchar(100))

-- drop #temp table if it exists, this may happen if the running this sp previously was cancelled
-- or returns an exception
IF object_id('tempdb..#temp', 'U') IS NOT NULL
DROP TABLE #temp

-- create a temporary table to hold the constructed INSERT commands
CREATE TABLE #temp (
seq int identity(1,1)
,cmd varchar(8000))

-- holds the sequence numbers
DECLARE @tabseq int
DECLARE @cmdseq int

-- holds table info
DECLARE @tablename varchar(100)
DECLARE @tableid int

-- usually "dbo"
DECLARE @user_name varchar(100)
SET @user_name = user_name()

-- holds the SELECT statement that's ran against the table to create the INSERT commands (@cmd)
DECLARE @select varchar(8000)
-- holds the INSERT command
DECLARE @cmd varchar(8000)
-- holds the number of INSERT command for the table in process
DECLARE @cmd_count int
-- holds the field part of the constructed INSERT command
DECLARE @fields varchar(8000)
-- holds the VALUES part of the constructed INSERT command
DECLARE @values varchar(8000)

-- START SCRIPT CONSTRUCTION
PRINT 'SET NOCOUNT ON'
PRINT ''
PRINT 'USE ' + db_name()
PRINT ''

-- process each table
WHILE EXISTS(SELECT TOP 1 * FROM @tables)
BEGIN
-- reset these values for each table
SET @fields = ''
SET @select = ''
SET @values = ''

SELECT TOP 1
@tabseq = seq,
@tableid = id,
@tablename = name
FROM @tables

-- fetch the column info for the current table
INSERT INTO @columns
SELECT name
FROM syscolumns
WHERE id = @tableid
ORDER BY colid

-- construct @fields and @values string for each columns,
-- this have an effect of looping through the @columns table
UPDATE @columns
SET @fields = @fields + name + ', '
,@values = @values + 'ISNULL('''''''' + CAST(' + name + ' AS varchar(8000)) + '''''''',''NULL'') + '', '' + '

SET @select = 'SELECT ''INSERT INTO ' + @user_name + '.' + @tablename+ ' (' + LEFT(@fields, LEN(@fields) - 1) + ') VALUES ('' + ' + LEFT(@values, LEN(@values) - 9) + ' + '')'' FROM ' + @tablename

INSERT INTO #temp EXEC (@select)

-- if @@ROWCOUNT return zero that means the SELECT statement above did not return any data
-- which means the table is empty and therefore need not be scripted
IF @@ROWCOUNT > 0
BEGIN
-- disable constraints before inserting data
PRINT '-- Data population script for ' + @tablename + ' table.'
PRINT 'SET IDENTITY_INSERT ' + @tablename + ' ON'
PRINT 'ALTER TABLE ' + @tablename + ' NOCHECK CONSTRAINT ALL'
PRINT ''

SELECT @cmd_count = COUNT(*) FROM #temp

-- construct INSERTS
PRINT '-- Data population script for ' + @tablename + ' table (' + CAST(@cmd_count AS varchar(100)) + ' records).'
WHILE EXISTS(SELECT TOP 1 * FROM #temp)
BEGIN
SELECT TOP 1 @cmdseq = seq, @cmd = cmd FROM #temp

PRINT @cmd

DELETE #temp WHERE seq = @cmdseq
END

-- enable constraints back
PRINT ''
PRINT 'ALTER TABLE ' + @tablename + ' CHECK CONSTRAINT ALL'
PRINT 'SET IDENTITY_INSERT ' + @tablename + ' OFF'
PRINT ''
END

DELETE #temp
-- clear @columns table
DELETE @columns
DELETE @tables WHERE seq = @tabseq
END

PRINT 'SET NOCOUNT OFF'

-- END SCRIPT CONSTRUCTION

DROP TABLE #temp

Now, there's not much to say about this script. Comments are there to do just that. Just run this inside Query Analyzer within a database context and you're on. The result can be seen inside the Message pane that you could copy and paste into a file or just click on the Query > Results To File menu to have Query Analyzer prompt you a file where the result would be directly save when you run the script (see Figures 1 & 2)

Figure 1.

Figure 2.

Be forewarned though, this script has limits to what data type can be scripted, for one, it cannot do image data types and the likes, don't ask me what "the likes" are I haven't got time to test them =) all but it works for most common data types i.e. int, varchar, text, char, and all their mutations. It also only works for SQL Server 7.0 and later.

Cheers!

ERRATA:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1053&lngWId=5

I just found out that the script posted here does not work for tables without an identity so I corrected that version to conditionally "SET IDENTITY INSERT...". This second version also tries to include table with images although not populating that column.

Setting An External Application's Opacity

2005-12-15T12:23:00.000+08:00

Opacity is nothing new in the .NET world, in fact it's an inherent property of WinForms although in actuality it's really an intrinsic property of Win2k's (and later Windows OS’es) window forms. In my opinion, the opacity property is one of the features of windows form that is not use most often but at the same time also one of the properties occasionally seek in an application i.e wouldn't be nice to have your IM console a bit translucent or your media player set invisibly enough so that everyone won't notice? It’s wickedly evil yet simple to do.

The enabling technology

As far as I know, the .NET framework (v2.0) as it stands today do not have inherent capability to subclass external applications and mess with its window and its style, which I think is just logical lest it affects its portability. Leaving us no better choice than to use good old Windows API. The following table describes the API functions and constants/ flags we’re going to utilize for our demo application.

Please refer to your MSDN for more info regarding the above functions.

Now that we’ve define the functions to use. It’s time to put them into code. The snippet below is the content of the APIHelper class contained in the sample.

class APIHelper
{
public static int GWL_EXSTYLE = -20;
public static int WS_EX_LAYERED = 0x80000;
public static int LWA_ALPHA = 0x2;
[DllImport("user32")]
public static extern int WindowFromPoint(int x, int y);
[DllImport("user32")]
public static extern int SetLayeredWindowAttributes(int hWnd, byte crey, byte alpha, int dwFlags);
[DllImport("user32")]
public static extern int SetWindowLong(int hWnd, int nIndex, int dwNewLong);
[DllImport("user32")]
public static extern int GetWindowLong(int hWnd, int nIndex);
[DllImport("user32")]
public static extern int GetParent (int hWnd);
[DllImport("user32")]
public static extern int GetParentOwner(int hWnd);
}

Practically, the function SetLayeredWindowAttributes is the only function that enables setting the opacity of a window, the functions SetWindowLong and GetWindowLong are just there as auxillary to its working. While the functions GetParent and WindowFromPoint are there because of the demo application’s objective, that is – to enable the user to change the opacity of an external window (obtained by the GetParent) by dragging a pointer to it (WindowFromPoint identifies the window pointed to).

A brief explanation

Before we delve on the technical details of the demo, I’d like you to bear this in mind, “Everything in Windows is a window”. True, Windows wouldn’t be called as such if its not. The textboxes, the labels, the buttons, the forms and all the rest of the controls (except for, I think, the image controls) are all windows, that is, they have what you call a “windows handle” (hWnd or handle by any other name). A window handle uniquely identifies each window (or should I say control in higher level parlance), and this is what we need to peruse the above functions.

In particular, I’d like to discuss the following snippet that made use of the GetParent. This snippet is located under the MouseUp event, the trigger that indicates that we’re through selecting a window.

lastWnd = APIHelper.WindowFromPoint(MousePosition.X, MousePosition.Y);
while (APIHelper.GetParent(lastWnd) > 0)
lastWnd = APIHelper.GetParent(lastWnd);

As you can see, the snippet retrieves the window handle of the “window” pointed to by mouse position. Now this can just be any other controls present on a form window, which we won’t need to deal with since what we’re interested is the form. And for us to get a handle from it is to walk the parent-child hierarchy until we find a window (/control) that don’t have a parent – the topmost window or the form window.

Now that we have grabbed a window handle, it’s time to do our thing. The TrackBar control in our demo app will be use to adjust the window’s opacity thru its scroll event.

int oldWinStyle = APIHelper.GetWindowLong(lastWnd, APIHelper.GWL_EXSTYLE);
APIHelper.SetWindowLong(lastWnd, APIHelper.GWL_EXSTYLE,
trackBar1.Value < 255 ? oldWinStyle APIHelper.WS_EX_LAYERED :
oldWinStyle & ~APIHelper.WS_EX_LAYERED);
APIHelper.SetLayeredWindowAttributes(lastWnd, 0, (byte)trackBar1.Value, APIHelper.LWA_ALPHA);

In pseudo terms, the snippet simply says, get the window’s previous style OR that to WS_EX_LAYERED to enable its opacity (or layering) and when done set its LWA_ALPHA attribute to the value indicated on the TrackBar. Easy is it? Below are the snapshots of the demo app in action.

Figure 1 & 2: Before and after applying the transparency using the demo application.

Figure 3: Applying transparency to a set of windows using yet another demo application included in the download.

Conclusion

The opacity feature of Win2K forms really is cool and a bit tweaking of the demo app would surely make it a nice-to-have tool in everyone’s desktop.

References:
How to create layered Windows in Visual Basic
Using Windows

Downloads:
http://www.megaupload.com/?d=P8CO97WY (Framework 2.0)
http://www.megaupload.com/?d=RPSTEM8T (Framework 1.1)
Most Recent Version (Framework 1.1)

Ugly-Printing: Reducing Your Page Content Size

2005-11-29T12:58:00.000+08:00

Previously we talked about the idea of compressing the ViewState and the idea of persisting it on the server to reduce the content size of your .aspx page. In addition to those ideas I’ll be discussing with you yet another way of speeding up your .aspx web pages.

Pretty-printing…

The developers of ASP.NET had put lots of effort in formatting the HTML source of our .aspx page and did so pretty well, just right click your browser and select View Source to see what I mean (Figure 1). These were all well and good for those who know how and care to peek under the covers of your web pages but does it really matter to the average user? For all we know, the average user care less if the underlying HTML source is pretty-printed or not. What matters to them, besides your content of course, is the speed at how it delivers its content in front of them.

Figure 1. Pretty printed HTML source.

The Filter property

The HttpResponse class exposes a Filter property that you could peruse to filter (and modify) the contents of the resulting HTML. This is just what we need to remove the extraneous character (mostly whitespace characters like carriage-returns and tabs) that ASP.NET deliberately put in for pretty-printing, in addition we could also extend our filtering scheme to include removing HTML comment, script comments, spaces between operators (like a + b when a+b will suffice) and etc. for further reduction. You can also try obfuscating your scripts using this scheme. Think of the endless possibilities.

Digression

Staying young and beautiful almost always has a tradeoff. You’ll spend lots of effort and time to maintain one’s beauty, just asks my missus and she’ll tell you. So why stay young and die early in the process (because you worry often that you’re getting older) when you can get old earlier and live longer.

Ugly-printing…

The digression above was just rhetorical but it holds true in computer processing. Agile method proponents always wants to keep everything simple because they acknowledge that most user care less if the system is aesthetically pleasing as long as it work and gets the job done. Most likely such system will be used longer than a system that is aesthetically pleasing yet doesn’t work at all. What I’m really saying is, why pretty-print when we can ugly-print for better user experience.

Using the HttpResponse.Filter and setting up our filter mechanism is not complicated but it’s not trivial either. What we need is a class that derives from the abstract Stream class with the required members implemented (see your MSDN documentation), especially the Write method, where the actual filtering process takes places.

public class UglyPrint : Stream
.
.
public override void Write(byte[] buffer, int offset, int count)
{
byte[] data = new byte[count];
Buffer.BlockCopy(buffer, offset, data, 0, count);
string inputstring = Encoding.ASCII.GetString(data);

inputstring = Regex.Replace(inputstring, @">[\s\S]*?<", new MatchEvaluator(Evaluate));

data = Encoding.ASCII.GetBytes(inputstring);
strSink.Write(data, 0, inputstring.Length);
}

protected string Evaluate(Match m)
{
string ms = m.ToString();
ms = Regex.Replace(ms, @"\r\n\s*", "");
return ms;
}
.
.
}

Here I made use of the Regular Expression class (Regex) to find the pattern “>[\s\S]*?<”, the pattern basically meant, all white spaces and/or non-white space between the “>” and “<” symbol. If we found such pattern, it will call the Evaluate function and further search the matched string with symbols “\r\n\s*” which meant all carriage-returns followed by zero or more white spaces and replaces them with an empty string.

Now, that we have setup the filtering class. It’s now time to use in our page. Just add the following snippet in the Page_Load event.

this.Response.Filter = new UglyPrint(this.Response.Filter);

or inherit from the ZipPage class (included in the example), like so:

public partial class DynamicNodes_aspx : ZipPage
{
.
.
}

This System.Web.UI.Page derived ZipPage class implements the UglyPrint class and the classes from the ViewState Compression and Persistence blog.

That’s it and you’re done. Run the page and try viewing its source in the browser (Figure 2).

Figure 2. After ugly-printing the page.

Now, that’s what I call a mess. It’s ugly but it’s faster and because it’s hard to read you are well off from other prying eyes. Also from the snapshot below (Figure 3) you’ll see how much the page was reduced from 17,881 to 16,861. This may seem not much but that’s just because our filtering scheme is simple and did not strip of white spaces inside the scripts. But like I mentioned earlier we could still extend the filtering scheme and make it more robust.

Figure 3. Shows the size difference before and after ugly-printing the page.

Conclusion

Think of the combine effect of compressing the ViewState or persisting it in the server with this idea of ugly-printing and you’ll get an idea of how much less your .aspx page now weigh. You need not worry how long this overhead takes because it depends on the complication of your filtering scheme but nonetheless a lighter page means a faster delivery.

Note:
Please take note that the above example leaves lot to be done. The filtering logic in the code above is simple and just serves as a proof-of-concept.

Downloads: http://www.megaupload.com/?d=2HHKPJ8J

ViewState Compression and Persistence

2005-11-23T19:26:00.000+08:00

For most web developers, one of the breakthroughs that the .net framework brought them is the ViewState. That enabling technology that save/restore page state between postback. But this cool stuff carries with it an overhead. Enabled by default and for the most part ignored during development, the ViewState may bloat to a size that may affect performance especially during page load.

So what do we do?

We can turn viewstate off all together. A not so good idea because that would mean throwing an extra programming effort for you to take care of the page state. Although a nicer idea would be to turn viewstate off for only the controls that need don't need it. But this won't entirely solve the problem of a bloated viewstate.

So what are the other options?

Other option I've seen on the net entails compressing the viewstate. I've tried it and it actually reduced the size of my viewstate by more than a half! Below is my port of it in .NET 2.0 (FYI, .NET 2.0 now comes with a compression namespace but you can always opt to use 3rd party products like ShapNZipLib, ComponentOne.Zip, etc).

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.IO.Compression;

public class GZip
{
public static byte[] Compress(byte[] b)
{
MemoryStream ms = new MemoryStream();
GZipStream zs = new GZipStream(ms, CompressionMode.Compress, true);
zs.Write(b, 0, b.Length);
zs.Close();
return ms.ToArray();
}

public static byte[] Decompress(byte[] b)
{
MemoryStream ms = new MemoryStream();
GZipStream zs = new GZipStream(new MemoryStream(b), CompressionMode.Decompress, true);
byte[] buffer = new byte[4096];
int size;
while (true)
{
size = zs.Read(buffer, 0, buffer.Length);
if (size > 0) ms.Write(buffer, 0, size);
else break;
}
zs.Close();
return ms.ToArray();
}
}

public class Deflate
{
public static byte[] Compress(byte[] b)
{
MemoryStream ms = new MemoryStream();
DeflateStream zs = new DeflateStream(ms, CompressionMode.Compress, true);
zs.Write(b, 0, b.Length);
zs.Close();
return ms.ToArray();
}

public static byte[] Decompress(byte[] b)
{
MemoryStream ms = new MemoryStream();
DeflateStream zs = new DeflateStream(new MemoryStream(b), CompressionMode.Decompress, true);
byte[] buffer = new byte[4096];
int size;
while (true)
{
size = zs.Read(buffer, 0, buffer.Length);
if (size > 0) ms.Write(buffer, 0, size);
else break;
}
zs.Close();
return ms.ToArray();
}
}

//Please see code snippet below on how to use these classes.

Is that it?

The buck doesn't stop here. I've read Dino Esposito's Programming Microsoft ASP.NET (ISBN:0735619034) and in one of the advance topics in his book he mentioned saving the ViewState in a file at the server. I was having second thoughts on this idea at first. Since this would mean lots of files and disk read/write activities in the server but then I thought of the advantages (and it outweighs the dis in some ways). First, security-wise, the ViewState won't get manipulated. Second, at least you have control of the server's resources and you can scale it up all you want rather than upgrading all of the client workstation. Third, I can't think of anything but I'm sure with this, I'll be saving the client some load up time =). See how it's implemented below:

using System;
using System.Collections.Generic;
using System.Text;
using System.Web.UI;
using System.IO;

public class ZipPage : Page
{

protected override void OnPreLoad(EventArgs e)
{
this.Session["_storeViewStateInServer"] = false;
base.OnPreLoad(e);
}

public bool StoreViewStateInServer
{
get
{
return (bool)this.Session["_storeViewStateInServer"];
}
set
{
this.Session["_storeViewStateInServer"] = value;
}
}

protected override object LoadPageStateFromPersistenceMedium()
{
LosFormatter f = new LosFormatter();
string vstate;
if (StoreViewStateInServer)
{
StreamReader sr = new StreamReader(GetFileName());
vstate = sr.ReadToEnd();
sr.Close();
}
else
{
vstate = this.Request.Form["__ZIPSTATE"];
byte[] b = Convert.FromBase64String(vstate);
b = Deflate.Decompress(b);
vstate = Convert.ToBase64String(b);
}

return f.Deserialize(vstate);
}

protected override void SavePageStateToPersistenceMedium(object state)
{
LosFormatter f = new LosFormatter();
if (StoreViewStateInServer)
{
StreamWriter sw = new StreamWriter(GetFileName());
f.Serialize(sw, state);
sw.Close();
}
else
{
StringWriter sw = new StringWriter();
f.Serialize(sw, state);
byte[] b = Convert.FromBase64String(sw.ToString());
b = Deflate.Compress(b);
ClientScript.RegisterHiddenField("__ZIPSTATE", Convert.ToBase64String(b));
}
}

private string GetFileName()
{
string url = Request.ServerVariables["Path_Info"];
url = url.Replace("/", "_");

// Place the file in a temp folder (with write permissions)
string fileName = "{0}/{1}_{2}.viewstate";
fileName = String.Format(fileName, "Temp", Session.SessionID, url);
return Server.MapPath(fileName);
}

}

Now you'll just have to inherit from this class and you're on the go. By default the ViewState won't be save on the server, I'll let you set it up by setting the StoreViewStateInServer property. Note that opting to save the ViewState in the server won't compress it since it doesn't matter anyway. Also please don't forget to give ASPNET machine account proper rights to the folder you'll be writing those temp tables or else it won't be able to persist the ViewState and exceptions will occur.

Conclusion

The two solutions cited to reduce the load up time of your aspx page are just one of the many things you can do the tweak you web app performance. Next blog I'll be showing you how to compress the HTML rendered on the page by removing the extra characters inserted by ASP.NET during page rendering.

Welcome!

2005-11-22T18:17:00.000+08:00

Welcome to my very first technical blog. This is where I'll be sharing my experiences in the information technology field.