MisterClay

Pushing my SharePoint Skills

2007-01-06T10:42:00Z

I can't remember the last time I posted anything here. Well, I guess that could be 6 months ago, when I posted a topic regarding how to access SQL Instances using SQLDMO and .NET. That was the last time I visited this all-filipino online community of developers.

Just a little update about me, I'm still working here in Singapore and for less than a year, I resigned from my previous mobile company as their wasted .NET developer and got an opportunity with Citibank Singapore. It's a big decision I made. In this country where there are a lot of .NET developers, competition is very stiff. Companies are offering lower rates since the number of resources they can get with .NET skills compared to Mainframe skills is really enormous.

I have to continue a skill that I think a rare one - MS SharePoint. This has been my skill when I worked with Accenture Manila Delivery Centre and I think I have to concentrate on this area with total conviction. I'm still in the area of .NET Development, but on a more exciting one - web part development (see MSN.com for web part samples). Besides, the pay is pretty higher than ordinary ASP .NET and General .NET rates.

The next time I'll be posting a topic here, most likely it's about SharePoint Development. Kampai to everyone!

List All SQL Servers using SQLDMO

2006-06-08T22:30:00Z

When I was still in college, one of my professors (Oracle) in PUP and one of the Systems Analyst of PUPILS asked to list all SQL Servers within LAN as part of their functionality in a system, to manually setup the server and save the settings in an INI file. He then introduced me to the SQLDMO. We're still using VB6.0 then.

The last time I used this library is when I created a DTS job scheduler for the eTouch V2 when I was still working with Soluziona Philippines. That time, it was done and used in ASP .NET.

This is also to answer sir cvega's posted comment to Keith's Article regarding how to programatically list all sql servers in your network. Please see the code below...

private void btnRetrieve_Click(object sender, System.EventArgs e)

{

SQLDMO.NameList sqlList = null;

SQLDMO.Application sqlApp = null;

lstServers.Items.Clear();

try

{

sqlApp = new SQLDMO.Application();

sqlList = sqlApp.ListAvailableSQLServers();

for(int ctr = 1; ctr <= sqlList.Count; ctr++)

{

lstServers.Items.Add(sqlList.Item(ctr));

}

catch(Exception ex)

{

MessageBox.Show(ex.Message);

}

finally

{

if(sqlList != null)

sqlList = null;

if(sqlApp != null)

sqlApp = null;

}

VB .NET (Based using code formatter)

Private Sub btnRetrieve_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Dim sqlList As SQLDMO.NameList = Nothing

Dim sqlApp As SQLDMO.Application = Nothing

lstServers.Items.Clear()

Try

sqlApp = New SQLDMO.Application

sqlList = sqlApp.ListAvailableSQLServers

Dim ctr As Integer = 1

While ctr <= sqlList.Count

lstServers.Items.Add(sqlList.Item(ctr))

System.Math.Min(System.Threading.Interlocked.Increment(ctr), ctr - 1)

End While

Catch ex As Exception

MessageBox.Show(ex.Message)

Finally

If Not (sqlList Is Nothing) Then

sqlList = Nothing

End If

If Not (sqlApp Is Nothing) Then

sqlApp = Nothing

End If

End Try

End Sub

Attached also is the working sample done using C#. Notes though, SQLDMO should be referenced and the DLL can be found in C:\Program Files\Microsoft SQL Server\80\Tools\Binn.

Executing DTS Package in ASP .NET

2006-05-31T08:36:00Z

It’s been a very long instance since the last time I posted an article here in my blog for a lot of reasons most programmers can comprehend, and one is the busyness @ work, then doing some music-related stuffs, church activities, online game priority, etc. etc. etc. Well, I have decided to post the topic I promised to Keith since I am so idle here in the office and all of my superiors are in an out-of-the office meeting.

Anyway, I already had experiences with DTS before working with Accenture Manila but too basic to consider. I’ve been using it for almost 3 years now and when I worked with Soluziona Philippines with their AstraZeneca’s eTouchV2 project, one of the challenges that my project manager gave to me is he wanted to imitate the look and functionality of the MSSQL’s DTS job scheduler, just like this one:

There were two scenarios given: Extraction of Data can be done manually (with just one button, data will be extracted from the database and dump the data in a text file and in an Excel file with formats in case users wanted to generate ad hoc reports prior to scheduled one) and Extraction of Data based on the indicated schedule of extraction. Data extraction functionality of the project was created first prior to the development of the interface so adjusting the MSSQL scripts was discouraged.

Given this functionality, these are the main challenges:

(1) Treat the whole ASP .NET web page as if the user is using a desktop application. This was met using JavaScript, validation were done on the client side. The only instance that the page will post back is when the schedule is submitted.

(2) Modify the DTS job schedule thru the ASP .NET web page. – using MSSQL SQLDMO.dll

(3) Execute the DTS to generate on-demand report (not following the schedule).

This article will just tackle on how to execute DTS from the ASP .NET application.

There are some ways on how to execute DTS Packages in your .NET code. The simplest I guess is using the LoadFromSQLServer() method. This can be obtained using the dtspkg.dll that can be found in C:\Program Files\Microsoft SQL Server\80\Tools\Binn\ folder path by default.

            using System;
      using System.Runtime.InteropServices;
      using DTS;

    namespace DTS
    {
        class ExecPkg
        {
            [MTAThread]
            static void Main(string[] args)
            {
                try
                {
                    Package2Class package = new Package2Class();
                    object pVarPersistStgOfHost = null;

                    package.LoadFromSQLServer(
                        "(local)\\dev",
                        null,
                        null,
                        DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,
                        null,
                        null,
                        null,
                        "Hello DTS",
                        ref pVarPersistStgOfHost);

                    package.Execute();
                    package.UnInitialize();

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(package);
                    package = null;
                }
                catch(System.Runtime.InteropServices.COMException e)
                {
                    //handle error
                }
            }
        }
    }

But before you take note of that code snippet, please be reminded that it only works with Console and Windows applications. This is not applicable with ASP .NET because I tried that approach so many times long long time ago. So, is there any other way? One way is thru the use of xp_cmdshell of MSSQL as indicated in this link à http://www.windowsitpro.com/Article/ArticleID/14277/14277.html. Honestly, I don’t have any idea what are the drawbacks of using either of the techniques. Performance wise, I think using SQLDMO should be the least to be considered. At the same time, unless called by some instances that you really have to use it in .NET for adventure or flexibility sake, it’s too tedious when coding in .NET. Like what I did for the job scheduling, the only self-perceived way is to use it. Otherwise, I will not be using it either if there is still other way.

To begin with, let’s create a DTS Package first. The simplest DTS. Open your MSSQL Server EM to start the adventure. First things first, consider a database to be used. Create a dummy database or as personal discretion can be implied, you can use existing database and create a table called TEMP with just one column, TEMP_COL of type nvarchar(50). Let’s say, I created the table under the Northwind database. Our objective will be, any entry in the ASP .NET must be inserted to the TEMP database following this process:

ASP .NET à SP à XP_CMDSHELL à DTS à TEMP TABLE

1. String Entry from ASP .NET to be passed to Stored Procedure à for instance, “DEMO_DTS” string value

2. Using XP_CMDSHELL within the stored procedure, pass the parameter value to DTS (passing value to DTS Global Variable) and execute the DTS using DTSRUN utility.

3. DTS inserts the value “DEMO_DTS” to the TEMP table

I know that this sample is too silly because it’s too impractical to use XP_CMDSHELL and DTS just to insert a record to the TEMP. We can even do it in .NET without using SP (Stored Procedure). But this is the simplest way to demonstrate the whole process on how to execute DTS Packages from .NET so I guess it’s justifiable J. Now,

(1) Go to your desired server and go to the Data Transformation Services folder.

(2) Right click on it, select New Package

(3) Place the following task objects:

1. ActiveX Script (if prompted, define any name to distinguish the script object)

2. MSSQL Connection object (configure the connection property)

3. Execute SQL Task (Place “SQLROBOT” as the name, select the MSSQL Connection object as the existing connection and indicate the SQL statement. For instance:

INSERT INTO TEMP VALUES('@VAR')

The @VAR value will just serve as a temporary key value and will be replaced dynamically via ActiveX Script Task object.

(4) Select the ActiveX Script object and (using CTRL+Left Click) the Execute SQL Task object.

(5) Go to Workflow > On Success. This is to define the process flow and direct the process in case the script was executed successfully.

(6) To test if the package will produce a positive result, go to Package > Execute. Check the TEMP table is the value @VAR is inserted.

(7) Right click on the design stage, select Package Properties. In this window, you can define DTS description and global variables.

(8) Go to the Global Variables tab, identify one variable. Let’s say, TEMP_VAR then click OK.

(9) Double-click the ActiveX Script Task Object and replace:

'************************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
Main = DTSTaskExecResult_Success
End Function

With this one:

'************************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
     On Error Resume Next
     Dim objTask
     Dim strValue

     strValue = DTSGlobalVariables("TEMP_VAR").Value
     Set objTask = DTSGlobalVariables.Parent.Tasks(2)
     objTask.CustomTask.SQLStatement = Replace(objTask.CustomTask.SQLStatement, "@VAR", strValue)
     Set objTask = nothing

     If Err.Count = 0 Then
                 Main = DTSTaskExecResult_Success
     Else
                 Main = DTSTaskExecResult_Failure
     End If
End Function

If you know how to use VBScript (or VB in general), then this would not be a burden to you. If you are comfortable on how to use Jscript, then it is still possible. To describe the code:

On Error Resume Next

Old-school VB error handling. J

     Dim objTask
     Dim strValue

     VBScript variable declaration has no type implementation.

strValue = DTSGlobalVariables("TEMP_VAR").Value

DTSGlobalVariables object is part of the GlobalVariables collection that can be used to retrieve Global Variables and it’s value defined in the package. TEMP_VAR is the name of the variable that we defined previously. This variable will contain the value from the stored procedure and thru this line, the value will be retrieved and strValue variable will get the value as depicted.

Set objTask = DTSGlobalVariables.Parent.Tasks(2)

DTSGlobalVariables.Parent is used to reference any other objects in the DTS object model hierarchy. Using this object, you can ulitize it to create Connections, Tasks, Steps, etc. We referred the Tasks collection to return the Task object on index of 2, returning a Task type value.

objTask.CustomTask.SQLStatement = Replace(objTask.CustomTask.SQLStatement, "@VAR", strValue)

CustomTask.SQLStatement is a get-and-set property for the SQL Statement. For this sample, get SQL Statement will return the INSERT statement that we defined recently. Replace string function is used to change the @VAR fixed value that we defined to the string value that will be passed from the ASP .NET page.

Set objTask = nothing

     If Err.Count = 0 Then
                 Main = DTSTaskExecResult_Success
     Else
                 Main = DTSTaskExecResult_Failure
     End If

DTSTaskExecResult_Success and it’s counterpart are DTS ActiveX Script Constants.

(10) Save the Package as “TEMP_DTS”. Your DTS Package would mostly look like this one:

Now, let’s go to the Stored Procedure. Using your Query Analyzer, select the appropriate database and start creating a stored procedure with one parameter that will accept the string value from ASP .NET. Let say, SPR_ExecutePackage is the name of the stored procedure.

CREATE PROCEDURE SPR_ExecutePackage(
@tempvar nvarchar(200))
AS
BEGIN

END

Declare a variable of type nvarchar that will hold the DTSRUN command string and another variable of type integer that will receive the returned value of the XP_XMDSHELL execution.

DECLARE @rc int
DECLARE @TSQL nvarchar(4000)

Using DTSRUN utility, assign the DTSRUN command to @TSQL variable. DTRUN utility is used to execute packages created in the Data Transformation Services (DTS) section of your server. For more information regarding this utility, refer to this link à http://doc.ddart.net/mssql/sql2000/html/coprompt/cp_dtsrun_95kp.htm.

SET @TSQL = 'dtsrun /S"(local)" /U"sa" /P"sa" /N"TEMP_DTS" /A TEMP_VAR:8=”' + @tempvar + '”'

Given the above statement, /S is for the sername name, /U is for the user id, /P is for the password, /N is for the DTS Package name, /A denotes DTS global variable name, :8 indicates the data type (please see the abovementioned link for the complete list of data types supprted). In the case of more than one global variables, just use the same format (/A).

After setting the dtsrun command in string format, pass the command string to the XP_CMDSHELL just like you’re passing a command string using the EXEC command, catch the returned value and perform some error handling.

EXEC @rc = master.dbo.xp_cmdshell @TSQL

To sum it up, this should be the structure of your stored procedure:

CREATE PROCEDURE SPR_ExecutePackage(
            @tempvar nvarchar(50))
AS
BEGIN
            DECLARE @rc int
            DECLARE @TSQL nvarchar(4000)

SET @TSQL = 'dtsrun /S"(local)" /U"sa" /P"sa" /N"TEMP_DTS" /A TEMP_VAR:8=”' + @tempvar + '”'
--USE /E for trusted connection and remove /U and /P parameters

EXEC @rc = master.dbo.xp_cmdshell @TSQL

            IF @rc <> 0
               BEGIN
                        RAISERROR('Error executing DTS. Please contact your system administrator.', 16, 1)
                        SELECT @rc
               END
            ELSE
               BEGIN
                        SELECT 0
               END
END

Run the stored procedure and try to test if your stored procedure will work.

EXEC SPR_ExecutePackage ‘Testing lng’

Now, let’s proceed with ASP .NET. Create a new ASP .NET web application. Using your default start-up page, add 1 textbox, 1 button and 1 label controls and name them txtSample, btnSample, lblSample respectively. Set the Text property of the textbox and label to null and “Execute” for the button. Begin the coding in the Click event of the button.

VB .NET
-------------

    Imports System.Data
    Imports System.Data.SqlClient

    .......

    Private Sub btnSample_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSample.Click
        Dim sqlConn As SqlConnection = Nothing
        Dim sqlComm As SqlCommand = Nothing

        Try
            sqlConn = New SqlConnection("User ID=test;Initial Catalog=Northwind;Data Source=(local);Password=test")
            sqlConn.Open()

            sqlComm = New SqlCommand("SPR_ExecutePackage", sqlConn)
            sqlComm.CommandType = CommandType.StoredProcedure
            sqlComm.Parameters.Add(New SqlParameter("@tempvar", txtSample.Text))

            Dim res As String = sqlComm.ExecuteScalar()
            If res = "0" Then
                lblSample.Text = "Execution of DTS Package succeeded"
            Else
                lblSample.Text = "Execution of DTS Package Failed" & "<p>" & res & "</p>"
            End If
        Catch ex As Exception
            lblSample.Text = ex.Message
        Finally
            If Not sqlComm Is Nothing Then
                sqlComm.Dispose()
                sqlComm = Nothing
            End If

            If Not sqlConn Is Nothing Then
                sqlConn.Close()
                sqlConn.Dispose()
                sqlConn = Nothing
            End If
        End Try
    End Sub

C#
-------------

       using System.Data;
    using System.Data.SqlClient;

    .......

    private void btnSample_Click(object sender, System.EventArgs e)
    {
        SqlConnection sqlConn = null;
        SqlCommand sqlComm = null;

        try
        {
            sqlConn = new SqlConnection("User ID=test;Initial Catalog=Northwind;Data Source=(local);Password=test");
            sqlConn.Open();
            sqlComm = new SqlCommand("SPR_ExecutePackage", sqlConn);
            sqlComm.CommandType = CommandType.StoredProcedure;
            sqlComm.Parameters.Add(new SqlParameter("@tempvar", txtSample.Text));
            string res = sqlComm.ExecuteScalar();
            if (res == "0")
            {
                lblSample.Text = "Execution of DTS Package succeeded";
            }
            else
            {
                lblSample.Text = "Execution of DTS Package Failed" + "<p>" + res + "</p>";
            }
        }
        catch (Exception ex)
        {
            lblSample.Text = ex.Message;
        }
        finally
        {
            if (!(sqlComm == null))
            {
                sqlComm.Dispose();
                sqlComm = null;
            }

            if (!(sqlConn == null))
            {
                sqlConn.Close();
                sqlConn.Dispose();
                sqlConn = null;
            }
        }
    }

As can be observed, the code above depicts on how we normally execute stored procedures in .NET. Of course, connection string will vary. Also, on how we catch the exception to be thrown.

That’s it. If you have any comments, suggestions, or additional info that you want to add, feel free to post it.

P.S. @Keith – Sa wakas pre, naka-compose ako ulit ng article.

DevPinoy Advertisement

2006-01-30T08:53:00Z

I'm just trying to kill some time here at the office. The other day, I asked Keith to create something that we can use for the advertisement of our community. Need some comments guys. Badly needed, most especially the <can't think of catchy line> part. I'll be using the image as part of Keith's plan to campaign the site in PUP.

I'll try to create another image tomorrow, if time will permit.

P.S.
I took that picture during my vacation in Surigao. It was not taken else where (e.g. google image).

Why should Keith give me the Code Complete Book?

2006-01-19T06:40:00Z

I'm not really a good writer, so it means that there is a big chance that nobody will not be impressed with the way I want to convey my thoughts to answer the aforementioned question. But I guess hoping for luck this time is not bad.

Why should Keith give me one of the copies of the Code Complete Book? [:D]

Keith knows how much my fascination is for programming books ever since our college days.
It dismays me not to post my answer to the question Keith had posted in order to grab a fresh copy of .NET 2005 and SQL Server 2005. Opportunity was missed once, I guess that will be the very least one. [:)]
I want to make sure that my future projects' quality uncompromised, developed quickly but less problems and I know this book will definitely help me acquire that goal.
I've been coding for years but I always rely my coding standards based on the company software guidelines. I want to know more outside company-specific standards and I want to know them with this book.

That's all. Back to work... God bless!

Attributes Property of ASP .NET

2005-09-14T02:04:00Z

Are you tired of thinking ways on how to minimize the number of PostBacks your web page could take when clicking or pressing a server control? Are you looking for an alternative way to show an alert message box when a user clicked a button without refreshing the page and without using the RegisterClientScriptBlock() and RegisterStartupScript() methods?

For those programmers who prefer JavaScript when performing validation, control property modifications without postbacks or dynamically changing page and control properties without reloading the page and for client-based interaction only, the Attributes property will be your ultimate partner.

Almost all webcontrols constitute the Attributes property. Attributes property is a collection of name or value pairs rendered to the client as Attributes (just like a client-script). It contains union of declaratively set Attributes that do not correspond to properties or events of the control and those that are set programmatically.

For instance, a server control button can be attributed to a button_click(Object o, EventArgs e) event. Yes, this can be very useful when performing transaction-based procedures (e.g. submitting data records or credentials, server-side validations, etc) but sometimes, it's not an a-Ok for some developers when your webpage refreshes every time you click the button whereas your button only needs to show an alert message or perform a client-side validation.

Normally, a server button for instance, is coded in this manner:

<asp:button id="btnCreateUser" style="Z-INDEX: 101; LEFT: 16px; POSITION: absolute; TOP: 8px" Width="184px" Font-Size="8pt" Font-Names="Tahoma" BorderStyle="Groove" runat="server" Text="Create Users" Enabled="False"></asp:button>

Each property such as style, cssclass, text, even the events (onmouseover, onclick, onblur, etc) is considered an attribute of the control. For the client-side script lovers, rendering event to a button is thru adding event attribute within the HTML code of the control (onClick="alert('This is a test message');", thru event-function association (btnAlert.Click = function myFunctionForClick(){//codes...};), or thru the addAttributes() method of JavaScript. Whatever the approach you will be using, the question is how can you integrate JavaScript codes and new attributes to a control at runtime thru code behind?

Still with the button control named btnCreateUser, in case you want to alert the user without a postback, you can call the Add() method of the Attributes property of the control where you want to apply the new property. Example:

//on form load
btnCreateUser.Attributes.Add("onclick", "alert('This is a test message'); return false;");

Return false? For what? This is to tell the browser that the procedure will never perform a postback or will not reload after the execution of the statement. In case you want to call a JavaScript function, just replace the alert() statement with the function name. What if you want to call the client-side script but after that, you still want to call the event handler associated for the button server control? Replace the return false; with return true. But be reminded that the client script will be performed first before the event...

Using the same property, you can also change the appearance or event of a server control. For example:

//on form load
//changing the border width of the button control width postback
btnCreateUser.Attributes.Add("Test", "Sample Only");

//calling a javascript function
btnCreateUser.Attributes.Add("onClick", "ValidateValues(); return false;");

//changing the font weight on mouse over
btnCreateUser.Attributes.Add("onmouseover", "this.style.font-weight='bold';");

//In case you have an HTMLTable running on server
//and you want to hide the table without page postback
tblUsers.Attributes.Add("style", "display:none");

This property can be very useful also when you want to create your own validation procedures and you don't want to rely much on the required field validator controls of .NET. In this case, you have to create your own scripts... customized but tedious.. [:D]