Monday, May 26, 2008

SharpZip Library .net

I have come across an interesting article for using SharpZip library for zip operation required in .net. I hope you will find it interesting.

http://codecruncher.blogsome.com/2007/01/04/37/

Still SharpZip is not available for .net 3.5. So guyz wait for this.

Sample Code Word 11.0 Object Library

Just add the reference of Microsoft Word 11.0 Object library and play with the following code by putting in your method:


Microsoft.Office.Interop.Word.Document doc = new Microsoft.Office.Interop.Word.Document();

ApplicationClass myWordApp = new ApplicationClass(); // our application


object nothing = System.Reflection.Missing.Value; // our 'void' value

object filename = "C:/TestDoc/TestDot.dot"; // our word template

object destination = "C:/TestDoc/MyNewDocument.doc"; // our target filename

object notTrue = false; // our boolean false


myWordApp.Visible = false;

doc = this.myWordApp.Documents.Add(ref filename, ref nothing, ref nothing, ref nothing);


doc.WebPagePreview();


doc.SaveAs(

ref destination,

ref nothing,

ref nothing,

ref nothing,

ref nothing,

ref nothing,

ref nothing,

ref nothing,

ref nothing,

ref nothing,

ref nothing,

ref nothing,

ref nothing,

ref nothing,

ref nothing,

ref nothing);

Office Programming

Microsoft Office document creation and update is one of the hot topic in any project. Because organizations require them very often. So it is the duty of every software engineer to have a expetise on those technologies which are related to this requirement.

I recommend reading about these.

Visual Studio Tools for Office.
Microsoft Word Object Library
Microsoft Excel Object Library

Just search these on the google and read the wonderful information on the internet about these.

BCC in VSTO for outlook

It is interesting to know that MailItem.BCC can be assigned any value but it does not seem to work. There is an alternative approach for this which I used. It is as follows:

Outlook.Recipient rcp = ((Outlook.MailItem)Item).Recipients.Add("shujaatned@gmail.com");
rcp.Type = 3;
((Outlook.MailItem)Item).Recipients.ResolveAll();


Here Outlook.Recipient.Type = 3 means that this a BCC recipient.

This took me all day to find out. But I dont know why MailItem.BCC does not work.

Friday, May 2, 2008

DDL trigger for auditing DDL statments running on SQL Server

Today I would discuss the DDL triggers available in SQL Server 2005. Like Oracle these are executed when any DDL statement is executed. The SQL statements can be logged in this trigger.

For keeping a record, it is better to create a table.

CREATE TABLE [dbo].[DDL_OBJECT_VERSION](
[EVENT_TYPE] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[POST_TIME] [datetime] NULL,
[LOGIN_NAME] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[USERNAME] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DATABASE_NAME] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[SCHEMA_NAME] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[OBJECTNAME] [nchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OBJECT_TYPE] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[SQL_TEXT] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SERVER_NAME] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]

The context information can be obtained by EVENTDATA() function availble in DDL trigger. The format of XML returned by this function is as follows:


CREATE_TABLE
2005-07-30T10:48:52.537
55
TEST-SHUJAAT
shujaat\Administrator
dbo
AdventureWorks
Person
Address
TABLE

ANSI_NULL_DEFAULT="ON"
ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON"
ENCRYPTED="FALSE" />
CREATE TABLE [Person].[Address](
[AddressID] [int]
IDENTITY (1, 1)
NOT FOR REPLICATION NOT NULL,
[AddressLine1] [nvarchar](60) NOT NULL,
[AddressLine2] [nvarchar](60) NULL,
[City] [nvarchar](30) NOT NULL,
[StateProvinceID] [int] NOT NULL,
[PostalCode] [nvarchar](15) NOT NULL,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
[DF_Address_rowguid] DEFAULT (NEWID()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT
[DF_Address_ModifiedDate] DEFAULT (GETDATE())
) ON [PRIMARY];




You can access invidual information with XQuery support available in SQL Server. The example code of DDL trigger is as follows:

CREATE TRIGGER [DDL_TRIG_OBJ_VERSIONS]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON
DECLARE @data XML;
SET @data = EVENTDATA();
insert INTO [DBO].[DDL_OBJECT_VERSION](
EVENT_TYPE,
POST_TIME,
SERVER_NAME,
LOGIN_NAME,
USERNAME,
DATABASE_NAME,
SCHEMA_NAME,
OBJECTNAME,
OBJECT_TYPE,
SQL_TEXT
)
VALUES(
@data.value('(/EVENT_INSTANCE/EventType)[1]','sysname'),
GETDATE(),
@data.value('(/EVENT_INSTANCE/ServerName)[1]','sysname'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]','sysname'),
@data.value('(/EVENT_INSTANCE/UserName)[1]','sysname'),
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]','sysname'),
@data.value('(/EVENT_INSTANCE/SchemaName)[1]','sysname'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]','sysname'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]','sysname'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','VARCHAR(max)')
);