American Grimoire

SQL Server 2000 is a pesky animal when it comes to making a copy of a database. Here’s the code I use. Use the name of your database files of course. You shouldn’t need to change any code at all after the 17th line or so. Cheers!

USE master
GO

– the original database (use ‘SET @DB = NULL’ to disable backup)
DECLARE @DB varchar(200)
SET @DB = ‘COIL’

– the backup filename
DECLARE @BackupFile varchar(2000)
SET @BackupFile = ‘d:\db_backups\coil_db_200709070200.bak’

– the new database name
DECLARE @TestDB varchar(200)
SET @TestDB = ‘COILDEV’

– the new database files without .mdf/.ldf
DECLARE @RestoreFile varchar(2000)
SET @RestoreFile = ‘d:\db_backups\coil_db_200709070200′
– ****************************************************************
–                    no change below this line
– ****************************************************************
DECLARE @query varchar(2000)

DECLARE @DataFile varchar(2000)
SET @DataFile = @RestoreFile + ‘.mdf’

DECLARE @LogFile varchar(2000)
SET @LogFile = @RestoreFile + ‘.ldf’

IF @DB IS NOT NULL
BEGIN
    SET @query = ‘BACKUP DATABASE ‘ + @DB + ‘ TO DISK = ‘ + QUOTENAME(@BackupFile, ””)
    EXEC (@query)
END

– RESTORE FILELISTONLY FROM DISK = ‘C:\temp\backup.dat’
– RESTORE HEADERONLY FROM DISK = ‘C:\temp\backup.dat’
– RESTORE LABELONLY FROM DISK = ‘C:\temp\backup.dat’
– RESTORE VERIFYONLY FROM DISK = ‘C:\temp\backup.dat’

IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB)
BEGIN
    SET @query = ‘DROP DATABASE ‘ + @TestDB
    EXEC (@query)
END

RESTORE HEADERONLY FROM DISK = @BackupFile
DECLARE @File int
SET @File = @@ROWCOUNT

DECLARE @Data varchar(500)
DECLARE @Log varchar(500)

SET @query = ‘RESTORE FILELISTONLY FROM DISK = ‘ + QUOTENAME(@BackupFile , ””)

CREATE TABLE #restoretemp
(
 LogicalName varchar(500),
 PhysicalName varchar(500),
 type varchar(10),
 FilegroupName varchar(200),
 size int,
 maxsize bigint
)
INSERT #restoretemp EXEC (@query)

SELECT @Data = LogicalName FROM #restoretemp WHERE type = ‘D’
SELECT @Log = LogicalName FROM #restoretemp WHERE type = ‘L’

PRINT @Data
PRINT @Log

TRUNCATE TABLE #restoretemp
DROP TABLE #restoretemp

IF @File > 0
BEGIN
    SET @query = ‘RESTORE DATABASE ‘ + @TestDB + ‘ FROM DISK = ‘ + QUOTENAME(@BackupFile, ””) +
        ‘ WITH MOVE ‘ + QUOTENAME(@Data, ””) + ‘ TO ‘ + QUOTENAME(@DataFile, ””) + ‘, MOVE ‘ +
        QUOTENAME(@Log, ””) + ‘ TO ‘ + QUOTENAME(@LogFile, ””) + ‘, FILE = ‘ + CONVERT(varchar, @File)
    EXEC (@query)
END

To make a long story short, we have several legacy DTS packages running on SQL Server that are executed via a batch file or a command script in SQL Agent. The code normally looks something like this

DTSRun /~Z0xDECE772DF6021A7ED260B31A2A37454F9614F6426C26B75B35481216F12946BB2ED19 DC1E116ECD0C39E279544DAEE48E088FCE1CD35D0A1A99E11530ED82E9E97FF95CD4ECB7AD5D56F66CA8CC060E3 E08B99BF3478B3DF4ADFD1912C097B77C9C2C44419F78FC5C7B6E8A269A9840E5387B48AB10106F2AECEDBE59C1DB 35A7811F81D04ABF01F5E67C148778028B165D51BDF88DEE3CBF8F7582B6CA554D826B9BB

Unfortunately I had no way of knowing exactly which DTS package the above code was referring to! Fortunately, there’s a way. Here’s how.

1) Go to the DOS command prompt and enter in your DTSRun code just like above but add the switches /!X /!C at the end. It will look like this:

DTSRun /~Z0xDECE772DF6021A7ED260B31A2A37454F9614F6426C26B75B35481216F12946BB2ED19 DC1E116ECD0C39E279544DAEE48E088FCE1CD35D0A1A99E11530ED82E9E97FF95CD4ECB7AD5D56F66CA8CC060E3 E08B99BF3478B3DF4ADFD1912C097B77C9C2C44419F78FC5C7B6E8A269A9840E5387B48AB10106F2AECEDBE59C1DB 35A7811F81D04ABF01F5E67C148778028B165D51BDF88DEE3CBF8F7582B6CA554D826B9BB
/!X /!C

2) Hit “Enter” and let it run. After it finishes, right-click and select “paste” in the DOS command window and the info will appear. It will come out in this format:

DTSRun /S "<server>\<instance>" /U "<uid>" /P "<pwd>" /N "<package name>" /!X /!C

Enjoy!

OK, I’ve encountered this issue enough in my work that I’ve decided to post about it. Let’s say you use the “export to excel” code I provided in an earlier post but it keeps bombing out with various errors. These errors are normally a result of having the gridview and “export” button inside of an AJAX.NET update panel. TIP: Try placing the button outside the update panel. Errors are also generated sometimes when master pages are used. Regardless, here’s an alternative method I use to export to excel which WORKS with master pages.

1) Open a blank website in Visual Studio and create a gridview that pulls data from a table using a sqldatasource. Drop a button somewhere and call it “btnExport”. Easy-peasy.

2) In the code behind, use this code.

protected void btnExport_Click(object sender, EventArgs e)
{
Export();
}

protected void Export()
{
if (this.Gridview1.Rows.Count > 65535)
{
//lblMessage.Text = “Export to excel is not allowed due to excessive number of rows”;
return;
}

string filename = String.Format(“Results_{0}_{1}.xls”, DateTime.Today.Month.ToString(), DateTime.Today.Year.ToString());

Response.Clear();
Response.ClearHeaders();
Response.AddHeader(“Content-Disposition”, “attachment;filename=” + filename);
Response.Charset = “”;

// SetCacheability is frequently used in export functions, but here it doesn’t seem to make a difference so I took it out
//Response.Cache.SetCacheability(System.Web.HttpCacheability.NoCache);

Response.ContentType = “application/vnd.xls”;

System.IO.StringWriter stringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);

// Replace all gridview controls with literals
ClearControls(this.Gridview1);

System.Web.UI.HtmlControls.HtmlForm form = new System.Web.UI.HtmlControls.HtmlForm();
Controls.Add(form);
form.Controls.Add(this.Gridview1);
form.RenderControl(htmlWriter);

Response.Write(stringWriter.ToString());
Response.End();
}

private void ClearControls(Control control)
{
for (int i = control.Controls.Count – 1; i >= 0; i–)
{
ClearControls(control.Controls[i]);
}

if (!(control is TableCell))
{
if (control.GetType().GetProperty(“SelectedItem”) != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);

try
{
literal.Text =(string)control.GetType().GetProperty(“SelectedItem”).GetValue(control, null);
}
catch
{ }
control.Parent.Controls.Remove(control);
}
else if (control.GetType().GetProperty(“Text”) != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
literal.Text =(string)control.GetType().GetProperty(“Text”).GetValue(control, null);
control.Parent.Controls.Remove(control);
}
}
return;
}

Here’s a very cool, easy way to get someone’s windows login in your ASP.NET page.

Imports System.Security
………
………
and

Dim authUserName As String
Dim aspUserName As String
authUserName = User.Identity.Name
aspUserName = Principal.WindowsIdentity.GetCurrent.Name
Label1.Text = “You are: ” & authUserName
Label2.Text = “This page runs as: ” & aspUserName

1) Start IIS
2) Expand your server and its default Web site, right-click the “your
project” site, and then click Properties.
3) On the Directory Security tab in the WindowsSite Properties dialog box,
click the Edit button in the “Anonymous access and authentication control”
section.
4) Click to clear the Anonymous access check box, verify that the Integrated
Windows authentication check box is selected, and then click OK.
5) Click OK to close the “your project” Properties dialog box.
6) Switch back to Visual Studio, and then run the project. Confirm that the
page is displayed with the following message:

You are: “Your Windows user name”
This page runs as: DomainOrServer\ASPNET

Booyah!

If you have the requirement to create a Windows form that tinkers with Outlook, then look no further! Here’s a basic example project that moves emails from one location to another.

1.) Create a new project

2.) Drag and drop a label (“label1″) and a button (“button1″) onto Form1

3.) Go to the button1 Click event by doubleclicking on it

4.) In the Solution Explorer (on the far right), right-click on “References,” then “Add References.” At this point a pop-up should appear. Ensure you’re in the .NET tab and scroll down until you find “Microsoft.Office.Interop.Outlook.” Your version shouldn’t matter, but I’m using 12.0.0.0. Select it and hit OK.

5.) Add the following “using” code at the appropriate spot up top:
using Microsoft.Office.Interop.Outlook;

6.) Then, go back to the button Click event and add this code:
Microsoft.Office.Interop.Outlook.Application oOutlook;
Microsoft.Office.Interop.Outlook.NameSpace oNs;
Microsoft.Office.Interop.Outlook.MAPIFolder oFldr;
Microsoft.Office.Interop.Outlook.MAPIFolder oDestFldr;

oOutlook = new Microsoft.Office.Interop.Outlook.Application();
oNs = oOutlook.GetNamespace(“MAPI”);

7.) Next, right below the above code we add more code to reference the originating email box and the destination email box. Obviously your email box will have a different name.
oFldr = oNs.Folders["Mailbox - Anderson, Roy Mr"].Folders["Inbox"];
oDestFldr = oNs.Folders["Mailbox - Anderson, Roy Mr"].Folders["test"];

8.) Now we add some code to check if there’s email in the mailbox, and display a message if so:
if (oFldr.Items.Count > 0)
{
this.label1.Text = oFldr.Items.Count.ToString() + ” emails detected.”;
this.Refresh();
}

9.) Run the application and click button1. Voila! But this isn’t good enough. We want to actually move the emails, remember? Here’s some code to handle that. Add this inside the “if” loop above:
//process normal emails
string filter = “[MessageClass] = \”IPM.Note\”";
ProcessEmails(oFldr, oDestFldr, filter);

//process encrypted emails
filter = “[MessageClass] = \”IPM.Note.SMIME\”";
ProcessEmails(oFldr, oDestFldr, filter);

Note: there are more email types than the two referenced above, but that’s an article for another day.

10.) Now for the heart of this: the “ProcessEmails” function.
private void ProcessEmails(MAPIFolder oFldr, MAPIFolder oDestFldr, string filter)
{
Microsoft.Office.Interop.Outlook.Items oTestItems = oFldr.Items.Restrict(filter);
//cycle through each email
for (int i = oTestItems.Count; i > 0; i–)
{
this.label1.Text = “Cycling through email ” + i.ToString() + ” of ” + oTestItems.Count.ToString();
this.Refresh();

MailItem oMessage = (MailItem)oTestItems[i];

//move email
oMessage.Move(oDestFldr);
}
}
At this point you should have a fully-operational email moving program. Note that in the ProcessEmails function there’s so much more you can do. You can insert a record of each email into a datatable, you can strip out and parse attachments. The sky’s the limit! Ask if you have any questions.

Let’s say you want to have a customized Javascript alert pop up when something happens on your page. Here’s an example:

1.) Create a button on your ASPX page called “Button1″

2.) In it’s button Click event, type this: PopUpBox(“this is where you’d place a customized message”)

3.) Now for the actual function. Place this code in your code-behind:

Sub PopUpBox(ByVal x As String)
Dim NewScript As String
NewScript = “<script language=JavaScript>”
NewScript = NewScript & “alert(‘” & x & “‘);”
NewScript = NewScript & “</script” & “>”
If (Not ClientScript.IsClientScriptBlockRegistered(“alert”)) Then
ClientScript.RegisterClientScriptBlock(Me.GetType(), “alert”, NewScript)
End If
End Sub

Voila! You’re done. Now, in your codebehind, you can call the PopUpBox sub from anywhere and send it whatever message you’d like.

(Courtesy of a coworker. Thanks Mike!) This is written in VBScript for a Classic ASP page, but the concept is easily portable to ASP.NET.

Since I’m going to be including a generic header page on our new site, I needed a way to specify the page title without simply having a list of titles and checking for the right one. So, I decided to use the file name, which has the added benefit of making the file names make sense when you’re trying to find the right one. Getting the page title to Display In A Title Case was more difficult than I thought since I couldn’t find a function to do it for me. So, I decided to just beat the string into submission:
 
Dim title, t, pagetitle

‘Strip off the folder name
title = Mid(Replace(Request.ServerVariables(“SCRIPT_NAME”),”_”,” “),14,Len(Request.ServerVariables(“SCRIPT_NAME”))-17)

‘Split the text into an array
t = Split(title)

‘For each word in the title, take the first letter and make it uppercase, then append the rest of the word and a space ‘ However, if the word is “breakbulk”, make the first and sixth letters uppercase (since the boss wants it displayed as “BreakBulk”)

For Each x in t
If x = “breakbulk” Then
pagetitle = pagetitle & Ucase(Left(x,1)) & Mid(x,2,4) & Ucase(Mid(x,6,1)) & Right(x,3) & ” ”
Else
pagetitle = pagetitle & Ucase(Left(x,1)) & Right(x,Len(x)-1) & ” ”
End If
Next

In a rush? Need to export data from an ASP.NET gridview? Here’s your solution.

1.) Create a button on your ASPX page called “btnExport”

2.) In the page directive (on your ASPX page) enter this: EnableEventValidation=”false”. This not always strictly necessary, but it does help resolve a couple potential issues.

3.) Insert this code into the button’s on Click function:

Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lbtnExportToExcel.Click

If GridView1.Rows.Count.ToString + 1 < 65536 Then
GridView1.AllowPaging = “False”
GridView1.DataBind()

Dim tw As New IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
Dim frm As HtmlForm = New HtmlForm()

Response.ContentType = “application/vnd.ms-excel”
Response.AddHeader(“content-disposition”, “attachment;filename=export.xls”)
Response.Charset = “”
EnableViewState = False
Controls.Add(frm)
frm.Controls.Add(GridView1)
frm.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
GridView1.AllowPaging = “True”
GridView1.DataBind()
Else

End If
End Sub

****And for you C#’ers****

protected void btnExport_Click(object sender, EventArgs e)
{
if (GridView1.Rows.Count + 1 < 65536)
{
GridView1.AllowPaging = false;
GridView1.DataBind();

System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
HtmlForm frm = new HtmlForm();

Response.ContentType = “application/vnd.ms-excel”;
Response.AddHeader(“content-disposition”, “attachment;filename=export.xls”);
Response.Charset = “”;
EnableViewState = false;
Controls.Add(frm);
frm.Controls.Add(GridView1);
frm.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
GridView1.AllowPaging = true;
GridView1.DataBind();
}
else
{

}
}

The top function removes duplicate records in SQL, the 2nd removes duplicates in T-SQL. Note that both require you to have an integer “uid” field. Besides that, both should be self-explanatory. If not, shoot me an email.

DELETE FROM table1 t1
WHERE t1.uid >
(
SELECT
MIN(t2.uid)
FROM table1 t2
WHERE
t1.field1 = t2.field1 AND
t1.field2 = t2.field2
)

DELETE FROM table1
WHERE uid IN
(
SELECT t1.uid
FROM table1 t1,
table1 t2
WHERE
t1.field1 = t2.field1 AND
t1.field2 = t2.field2 AND
t1.uid < t2.uid
)

Here’s a couple methods for customizing your dates in gridviews. The first utilizes a TemplateField, the second a normal databound field. I like the 2nd best because it allows for easiest customization of whatever date you need.

<asp:TemplateField HeaderText=”Invoice” SortExpression=”CommDate”>
<ItemTemplate>
<asp:Label runat=”server” ID=”txtCommDate” Width=”70px” Text=’<%# eval(“CommDate”,”{0:MM/dd/yyyy}”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>

 
<asp:BoundField DataField=”CommDate” HeaderText=”Invoice” SortExpression=”CommDate” DataFormatString=”{0:d}” HtmlEncode=”false” />

Here’s numerous examples of how to deal with timeouts in .NET (but remember, ensure your time outs aren’t the result of un-optimized SQL queries).

In an App.Config file:
<connectionStrings>
<add name=”BLAH.Properties.Settings.BLAHConnectionString” connectionString=”Data Source=160.160.16.160;Initial Catalog=BLAH;Integrated Security=True;Connect Timeout=99999″ providerName=”System.Data.SqlClient” />
</connectionStrings>
 
In a Web.Config file:
<connectionStrings>
<add name=”BLAHConnectionString” connectionString=”Data Source=160.160.16.160;Initial Catalog=BLAH;Integrated Security=True;timeout=99999;” providerName=”System.Data.SqlClient”/>
</connectionStrings>

In the auto-generated code of an XSD file: This method is more advanced. Make a backup prior. You’ve been warned.
1.) Left-click the plus sign next to your XSD file. Find the one that has “Designer” in it’s name and open it up.

2.) Do a CTRL-F and search for “this._commandCollection[" (without the quotes). What you're looking for is something like this:
this._commandCollection[0].Connection = this.Connection;

You’ll find several of these. Sometimes there will be a “1″ or “2″, and so on, in the index position. After the connection line, add this code:
this._commandCollection[0].CommandTimeout = 99999;

So now your code will look like this:

this._commandCollection[0].Connection = this.Connection;
this._commandCollection[0].CommandTimeout = 99999;

Do this under every Connection line you find.

In the code-behind:
this.MyTableAdapter.Adapter.SelectCommand.CommandTimeout = 99999;
this.MyTableAdapter.Fill(this.MyDataSet.BLAH);

In the web.config:
<httpRuntime maxRequestLength=”1048576″ executionTimeout=”99999″ />

In the web.config: (note, kicking up your session timeout beyond 30 mins is generally considered to be a horribly bad idea. It leads to slooowness.)
<sessionState timeout=”99999″ />

In the code-behind:
SqlCommand myCommand = new SqlCommand ( );
myCommand.CommandText = “SELECT * FROM nw_Categories ORDER BY CategoryID”;
myCommand.CommandTimeout = 99999;

Note: the last one is placed into the SQLdatasource’s “Selecting” event.

Hey all, 

If you’re a newbie coder, you’re probably wondering what “overloading” a class is. I wrote this primer up for a long-distance associate who’s cutting his teeth on classes.  His name (no surprise) is ”Tom.” ;) Block out about 15 mins and do the below if you’re clueless about overloaded classes.

1.) Create a new web site in Visual Studio.

2.) Right-click on your solution, go to “Add New Item” and create a new class (in should go in the app_code directory). Call your new class “Tom.” It should look just like this:

Imports Microsoft.VisualBasic

Public Class Tom

End Class

So yeah, you got class now.

3.) Now hop over to your webpage’s codebehind (Default.vb) and go into your page_load (accessible via one of the two dropdowns up top) and type “Tom.” Note the period at the end of “Tom.” That’ll give you an intellisense dropdown. Those two auto-generated functions you see are what every new object has by default. So, yes, you JUST created a “Tom” object! Woohoo! Your page_load should look like this:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Tom.

End Sub

4.) But we don’t want Tom to just have two default functions. That’s lame. Let’s give Tom some hands, feet, and brains functions! Your Tom class should now look like this:

Imports Microsoft.VisualBasic

Public Class Tom

Shared Sub hands()

End Sub

Shared Sub feet()

End Sub

Shared Sub brain()

End Sub

End Class

Save the file then hop back to step 3 again and re-do! You’ll see that when you re-type “Tom.” you get FIVE functions in your dropdown! Tom now has hands, feet, and brains! *sniffle* They grow up so fast…

5.) But Tom’s not finished. Those hands, feet, and brains don’t do shit! Our Tom is weak! Frail! We should add some processing or something in the those Subs so that they actually DO something…but hell with that. You get the idea, you can add processing if you so desire. Let’s move on to overloading!

6.) You can kick off the Tom “hands” function in the page_load just by typing “Tom.hands()” and whatever processing you have in the “hands” Sub will kick off when the page loads. Duh. There’s absolutely no need to send in a variable to the “hands” Sub. “Hands” gets along just fine without variables. BUT let’s say we want to have the option to send “hands” different variables. So we just dupicate “hands” like so:

Imports Microsoft.VisualBasic

Public Class Tom

Shared Sub hands()

End Sub

Shared Sub hands(ByVal ball As String)

End Sub

Shared Sub hands(ByVal ball As String, ByVal bat As String)

End Sub

Shared Sub hands(ByVal ball As String, ByVal bat As String, ByVal glove As String)

End Sub

Shared Sub feet()

End Sub

Shared Sub brain()

End Sub

End Class

Now, go back to the page_load in Default.vb and type “Tom.hands(”  After you type the parantheses you’ll see that the “hands” Sub now has FOUR possible ways to call it. You can just kick it off. Or you can send in different variables.

That is “overloading.” Basically, you’re duplicating a Function or Sub in a class BUT changing the variables. That’s all folks. Email if you have any questions. Another way to think of it is to use the phrase: “same function, different variables.”

P.S.—I know what you’re probably thinking: why not just create a Sub with a completely different name if you’re going to be sending in different variables? Why call multiple Subs the same name? This is done primarily for simplicity’s sake because the code in those same-named Subs accomplishes the same thing.