Wednesday, October 31, 2012

tsql program to kill connection sleep more than 10 min

DECLARE @v_spid INT
DECLARE c_Users CURSOR
   FAST_FORWARD FOR
   SELECT SPID
   FROM master..sysprocesses (NOLOCK)
   WHERE spid>50
   AND status='sleeping'
   AND DATEDIFF(mi,last_batch,GETDATE())>=10
   AND spid<>@@spid

OPEN c_Users
FETCH NEXT FROM c_Users INTO @v_spid
WHILE (@@FETCH_STATUS=0)
BEGIN
  PRINT 'KILLing '+CONVERT(VARCHAR,@v_spid)+'...'
  EXEC('KILL '+@v_spid)
  FETCH NEXT FROM c_Users INTO @v_spid
END

CLOSE c_Users
DEALLOCATE c_Users

TSQL CHANGE SPECIFIC CHARACTERS ENTIRE OF DATABASE


SELECT REPLACE(REPLACE('variable','ي','ی'),'ك','ک');

--- OR

Set Nocount on
                Declare @t Table (Id int identity(1,1) primary key,
                                ColumnName varchar(100),TableName varchar(100),SchemaName varchar(100))

                Declare @Id int,
                                                @ColumnName varchar(100),
                                                @TableName varchar(100),
                                                @SchemaName varchar(100),
                                                @Sql nvarchar(max)

                Insert Into @t(ColumnName,TableName,SchemaName)
                Select sys.all_columns.name, sys.objects.name,sys.schemas.name
                from sys.all_columns
                                Inner Join sys.types on sys.types.user_type_id= sys.all_columns.user_type_id
                                Inner Join sys.objects on sys.objects.object_id=sys.all_columns.object_id
                                Inner Join sys.schemas on sys.schemas.schema_id=sys.objects.schema_id
                where sys.types.system_type_id in (231,239)
                                and sys.objects.type='U'

                While Exists(Select * from @t)
                Begin
                                Select Top 1 @Id=Id,@ColumnName=ColumnName,@TableName=TableName,@SchemaName=SchemaName
                                From @t

                                Print '['+@SchemaName+N'].['+@TableName+N'].['+@ColumnName+N']'

                                Set @sql=N'
                BEGIN TRY
                                Update ['+@SchemaName+N'].['+@TableName+N']
                                Set ['+@ColumnName+N']=Replace(['+@ColumnName+N'],N''ی'',N''ي'')
                                Where ['+@ColumnName+N'] Like N''%ی%''

                                Update ['+@SchemaName+N'].['+@TableName+N']
                                Set ['+@ColumnName+N']=Replace(['+@ColumnName+N'],N''ک'',N''ك'')
                                Where ['+@ColumnName+N'] Like N''%ک%''
                END TRY
                BEGIN CATCH
          Print ''Error on update ['+@SchemaName+N'].['+@TableName+N'].['+@ColumnName+N']''
                END CATCH
                                '

                                --print @sql
                                Exec (@Sql)
                                Delete @t
                                Where Id=@Id
                End

Client Callbacks in ASP.NET 2.0


Client Callbacks in ASP.NET 2.0

Introduction

Client callbacks is an interesting feature of ASP.NET which allows calling server code from client-side JavaScript through XmlHTTP. In this article, I will discuss client callback first, and then will compare its benefits with AJAX.
Using AJAX in ASP.NET 1.1 is quite a tedious task. First, you need to copy the AJAX DLL into the bin folder, then register it as an HttpHandler in Web.Config. Things do not end here because every page your are planning to use AJAX needs to be registered as well by calling AJAXUtility.RegisterForAJAXType (this.GetType ()). And, then comes the writing of the actual client and server-side code.
In ASP.NET 2.0, the Runtime takes this responsibility of registering the HTTP handler, page types etc., and simplifies the process of client script callbacks, so in this article, I will discuss the details of it.
ASP.NET introduced a new interface named ICallBackEventHandler. A page needs to implement this interface in order to support client callbacks. It has two methods:
  • RaiseCallbackEvent: Processes a callback event that targets a control. This method is the execution point when the server code is called from the client script.
  • GetCallbackResult: Returns the results of a callback event that targets a control. The result is usually returned by RaiseCallBackEvent, and stored in some class member.

Client callbacks in action

Let's take a look at a very basic example of client callbacks in which clicking of a client side button triggers server code which returns a simple text message for display in the textbox at the client side.

Implementing ICallbackEventHandler


public partial class _Default : System.Web.UI.Page, ICallbackEventHandler
{
    string callbackResult;
    protected void Page_Load(object sender, EventArgs e)
    {
        // will discuss this later
    }
    public void RaiseCallbackEvent(string eventArgument)
    {
        // perform some real operation
        callbackResult = "DotNET Rocks!";
    }
    public string GetCallbackResult()
    {
        return callbackResult;
    }
}
The above code is quite simple. RaiseCallbackEvent performs some operations and stores the result in the callbackresult variable, which is returned by the GetCallbackResult method. The next step is to have some JavaScript code which will trigger this callback.
<script type="text/javascript">
function GetMessageFromServer()
{
    UseCallBack();
}
function JSCallback(TextBox1, context)
{
    // when callback finishes execution this method will called
    document.forms[0].TextBox1.value = TextBox1;
}
</script>
GetMessageFromServer will trigger the server code using the UseCallback method. This is a dynamic method generated from the code-behind; more on this later. JSCallback is the client method which is called when the server callback completes its execution.
So, what I did here is just copy the server message in the textbox.
Clicking the following HTML button will invoke the method:
<input type="button" id="Button1" runat="server" value="Get Message"
       onclick="GetMessageFromServer()"/>
Now comes the final and the most important step of using client callbacks. This step is actually responsible for emitting the JavaScript which will call the Framework method to make the actual XmlHttp. Normally, this code fragment comes in the Page_Load event.
protected void Page_Load(object sender, EventArgs e)
{
   // get reference of call back method named JSCallback
   string cbref = Page.ClientScript.GetCallbackEventReference(this,
                   "arg", "JSCallback", "context");
   // Generate JS method trigger callback
   string cbScr = string.Format("function UseCallBack(arg," +
                                " context) {{ {0}; }} ", cbref);
   Page.ClientScript.RegisterClientScriptBlock(this.GetType(),
                     "UseCallBack", cbScr, true);
}
The above C# code generates the following JavaScript at run time:
function UseCallBack(arg, context)
{
   WebForm_DoCallback('__Page',arg,JSCallback,context,null,false);
}
This actually calls the XmlHttp behind the scenes, which will ultimately result in a callback of the server code. So that’s it. After the server code finishes its execution, the Framework will call the specified JavaScript method to notify the completion of the callback, in this case, the function JSCallback(TextBox1, context).

Internals of Client Callbacks

You might be wondering where the XmlHttp object is and how the framework calls the server callback behind the scene. Well, if you look at the generated HTML for the page, you will see an interesting script declaration:
<script src=http://www.codeproject.com/<AppName>/WebResource.axd?d=v...&amp;t=63...0
       type="text/javascript"></script>
If you open the above URL in the browser, you will see the complete definition of library functions and objects responsible for server calls and client notifications.
Another interesting thing in the previous code is the ClientScript property of the Page class. This property represents the instance of the ClientScriptManager class which contains all the concerns of client side scripting and contains lots of methods useful in client-side scripting.
GetCallbackEventReference is one of the methods which returns a reference to a client-side function that, when invoked, initiates a client callback to a server-side event.

Page Cycle in Client Callback Scripts

One of the interesting and unique features of client callback scripts is that when calling a server callback, the ASPX page loads into memory and executes normal page cycle events such as Page_Init, Page_Load etc. This is where client callback runs away from AJAX because in AJAX, an ASPX page is not loaded in memory, so you cannot access ViewState or the page controls. But this is not the case in client callbacks.
However, in client callbacks, a partial page cycle executes, and events such as Pre_Render, Render don’t fire, and it’s logical enough because we don’t want the complete page to refresh. The following diagrams will describe the page life cycle in both scenarios
Normal ASP.NET 2.0 Page Cycle


Partial Page Cycle in Client Callbacks

Client Callbacks with Parameters

You can also pass parameters to the server code; however, only one. Passing a parameter is very simple. All you need to do is pass some information to the dynamically generated method which will automatically pass it to the server:
function GetMessageFromServer()
{
   var parameter = “some paramter”;
   UseCallBack(parameter, "");
}
public void RaiseCallbackEvent(string eventArgument)
{
   // process eventargument
   callbackResult = “some result”;
}

Client Callbacks vs. AJAX

Following are some benefits of client callbacks over AJAX.
  • Since it’s undertaken by the Framework, implementation of client callbacks are consistent compared to AJAX which has various implementations and lacks standardization.
  • Client callback resultant execution of page cycle means the server callback can access ViewState and form controls.
  • No need to register the HttpHandlers in the configuration files.
  • In client callbacks, the developer doesn’t need to write code into JavaScript. Instead, they can use a dynamic and less error prone approach of calling Page.ClientScript.GetCallbackEventReference.

Conclusion

Client callbacks is one of the exciting features in ASP.NET 2.0 which allows calls to server code asynchronously through XmlHttp, which is also known as AJAX. A client callback script provides certain advantages such as access to the ViewState and the Forms collection of an ASPX page which is not there in AJAX.
Your feedback and comments are always welcome.

References:

  • A First Look at ASP.NET v. 2.0, Addison Wesley, ISBN 0-321-22896-0
  • Professional ASP.NET 2.0, Wrox Press, ISBN 10: 0-7645-7610-0


T-SQL Cursor


T-SQL Cursor

Here is a T-SQL cursor example code created for looping selecting a list of email addresses for emailing.
The select query in the definition of the example t-sql cursor returns the example record set that will be used for emailing purposes.
After we declare and open the example sql cursor, by fetch next method in the sample cursor source rows, we will loop and send email in each loop with in the record set.

To send email to an email address from MS SQL Server, we will use sp_send_dbmail SQL Server mailing enhancement by defining a email profile and setting SMTP properties for the related SQL Server DBMail profile.

Let's first create a sql database table which will hold the sample email data for our t-sql cursor example.
GO
CREATE TABLE EmailList (
  id smallint identity(1,1),
  email varchar(500),
  name nvarchar(500),
  emailsent bit default 0,
  sentdate datetime
)
Now we are ready to populate our sql email table with sample data.
In our sql cursor example we will loop for each row in this table and send email to email addresses that are not sent email before.
Within t-sql code in our t-sql cursor example, we will update rows that are sent email.
INSERT INTO EmailList (email, name) VALUES ('emailaddress1@test.com', N'Darth Vader')
INSERT INTO EmailList (email, name) VALUES ('emailaddress2@test.com', N'Bill Gates')
INSERT INTO EmailList (email, name) VALUES ('emailaddress3@test.com', N'Katy Perry')
And now let's code t-sql script for our example t-sql cursor.
CREATE PROC SendEmailCursor

AS

-- eMail Variables --
DECLARE @email_subject nvarchar(1000)
DECLARE @email_body nvarchar(max)

SET @email_body = N'Welcome to our Community'
SET @email_body = N'<html><body>Dear {0},<br />We''re glad to see you.</body></html>'
-- eMail Variables (END) --

-- Cursor Variables --
DECLARE @Id smallint;
DECLARE @email varchar(500);
DECLARE @name nvarchar(500);
-- Cursor Variables (END) --

DECLARE @pbody nvarchar(max)

------------------ CURSOR eMail --------------------
DECLARE eMailCursor CURSOR FAST_FORWARD FOR
SELECT
  id, email, name
FROM EmailList
WHERE emailsent = 0

OPEN eMailCursor

FETCH NEXT FROM eMailCursor INTO @Id, @email, @name

WHILE @@FETCH_STATUS = 0
BEGIN
  ---
  SET @pbody = REPLACE(@email_body, '{0}', @name)

  EXEC msdb.dbo.sp_send_dbmail
    @profile_name = N'TBS',
    @recipients = @email,
    @subject = @email_subject,
    @body = @pbody,
    @body_format = 'HTML'

  UPDATE EmailList SET emailsent = 1, sentdate = GetDate() WHERE id = @Id
  ---
  FETCH NEXT FROM eMailCursor INTO @Id, @email, @name
END

CLOSE eMailCursor
DEALLOCATE eMailCursor
------------------ CURSOR eMail (END) --------------------

GO
Now we can end our cursor example tutorial by calling the stored procedure we have code the sql cursor in.
If the DBMail profile settings are correct, you will soon get emails sent through the sql cursor example we have declared and execured above.
EXEC SendEmailCursor

Cursor Types

T-SQL cursors are generally bad approach and are often misused. In today’s world of correlated subqueries, CTE’s, recursive CTE’s, ranking and windowing functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE) and other tools, you have really few moments where cursor is better solution. On the other hand, there are still scenarios where cursor are best and most efficient solution.

If you think that holy moment of cursor occurred, it’s good to know which type of cursor you should use. It’s good to know what cursor features you need and choose a proper type of cursor. Or you’re most probably wasting server resources. I tried to summarize important aspects which you need consider when using cursors to prevent such situation.
Here is important settings from standard cursor declaration syntax:
DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
Here is fast human description of cursor options:
[ LOCAL | GLOBAL ] – Use LOCAL, cursor will live only in scope of batch. GLOBAL cursor lives in whole connection, I can’t imagine many scenarios where you really need it.
[ FORWARD_ONLY | SCROLL ] – Use FORWARD_ONLY, cursor will enable only sequential forward only reading. SCROLL cursor enables to move freely forward and backward which require more resources.
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] – Use FAST_FORWARD, it is the combination of READ_ONLY and FORWARD_ONLY cursor. FAST_FORWARD cursor enables only forward and read only movement which makes it most efficient compared to the other types. STATIC cursor loops over copy of data, not over original data hence doesn’t support any updates. Creating copy of data is very resource-intensive. KEYSET cursor requires unique key identifying rows, these keys are copied in tempdb when cursor is opened. It loops then based on those keys. You must have very good reason to use KEYSET cursor. DYNAMIC cursor immediately reflects any changes made to underlying tables.
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] -READ_ONLY is good but doesn’t support any updates. SCROLL_LOCKS option tells cursor to lock every read row to ensure that updates through cursor will succeed.
And few more important points :
  • start with LOCAL FAST_FORWARD cursor and then change it if you need something special
  • use minimal count of rows and columns within cursor
  • think about what you’re doing inside cursor, try to avoid complex SELECT queries inside
  • try to avoid STATIC and KEYSET cursors, they work with tempdb which is another overhead
  • don’t forget to CLOSE and DEALLOCATE cursor regardless of its scope settings

Tuesday, October 30, 2012

Converting a generic list into a JSON string and then handling it in JavaScript


Converting a generic list into a JSON string and then handling it in JavaScript
We all know that JSON (JavaScript Object Notation) is very useful for manipulating strings on client side with JavaScript, and its performance is very good over various browsers. In this article, I will describe how we can easily convert a C# Generic list into a JSON string with the help of the JavaScript Serializer class, and how we can get this string into JavaScript using the ASP.NET ScriptManager by calling a Web Service with JavaScript. I manipulate the string in JavaScript to generate custom HTML.
I will develop a simple ASP.NET web application which contains the Web Service, and the web page which will contain the JavaScript code to produce custom HTML at the client side with JSON.
To create a Generic List of a custom type, we need an Info class which contains some properties. I have created the class UserInfo which contains the properties called UserId and UserName. Shown below is the code for that:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Experiment
{
    public class UserInfo
    {
        public int UserId { get; set; }
        public string UserName { get; set; }
    }
}

Like we have done with the class, let's create a Web Service which will create a Generic List of the above class, and then we will create some data for the List using a for loop. After creating a Generic List with data, we will use the JavaScriptSerializer class to serialize the Listand convert it into a JSON string. Here is the code for that:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
 
namespace Experiment.WebService
{
    /// <summary>
    /// Summary description for WsApplicationUser
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script,
    // using ASP.NET AJAX, uncomment the following line. 
    [System.Web.Script.Services.ScriptService]
    public class WsApplicationUser : System.Web.Services.WebService
    {
 
        [WebMethod]
        public string GetUserList()
        {
            List<UserInfo> userList = new List<UserInfo>();
            for (int i = 1; i <= 5; i++)
            {
                UserInfo userInfo = new UserInfo();
                userInfo.UserId = i;
                userInfo.UserName = 
                  string.Format("{0}{1}", "J", i.ToString());
                userList.Add(userInfo);
 
            }
            System.Web.Script.Serialization.JavaScriptSerializer jSearializer = 
                   new System.Web.Script.Serialization.JavaScriptSerializer();
            return jSearializer.Serialize(userList);
        }
    }
}

Note: Here, we must have theSystem.Web.Script.Services.ScriptService attribute call the Web Service from JavaScript.
Now, as we have done with the Web Service, let's create the HTML part to consume the custom HTML generated by the JavaScript. First of all, we need to add a service reference to the Web Service which we need to use on the client side through JavaScript. And we will also create a div'divUserList' which will contain the custom HTML generated via JavaScript. So our HTML code in the WebForm will look like this:
<form id="form1" runat="server">
<asp:ScriptManager ID="myScirptManger" runat="Server">
    <Services>
        <asp:ServiceReference 
           Path="~/WebService/WsApplicationUser.asmx" />
    </Services>
</asp:ScriptManager>
 
<div id="divUserList">
</div>
</form>

Now that we have created a Web Service class, let's create a JavaScript function 'GetUserList' which will call the Web Service from the JavaScript, like shown:
function GetUserList()
{
    Experiment.WebService.WsApplicationUser.GetUserList(
                ReuqestCompleteCallback, RequestFailedCallback);
}

As you can see, in the above code, I have implemented two callback functions in JavaScript: ReuqestCompleteCallback andRequestFailedCallback. Here, the ReuqestCompleteCallbackfunction will contain code for converting a JSON string into custom HTML, and the RequestFailedCallback function will contain code for handling the Web Service and other errors if any errors occur. Shown below is the code:
function ReuqestCompleteCallback(result)
{
    result = eval(result);
    CreateUserListTable(result);
}
 
function RequestFailedCallback(error)
{
    var stackTrace = error.get_stackTrace();
    var message = error.get_message();
    var statusCode = error.get_statusCode();
    var exceptionType = error.get_exceptionType();
    var timedout = error.get_timedOut();
 
    // Display the error.
    var divResult = document.getElementById("divUserList");
    divResult.innerHTML = "Stack Trace: " + stackTrace + "<br/>" +
        "Service Error: " + message + "<br/>" +
        "Status Code: " + statusCode + "<br/>" +
        "Exception Type: " + exceptionType + "<br/>" +
        "Timedout: " + timedout;
}
 
function CreateUserListTable(userList)
{
    var tablestring = '<table ><tr><td>UsreID" + 
        "</td><td>UserName</td></tr>';
 
    for (var i = 0, len = userList.length; i < len; ++i) 
    {
        tablestring=tablestring + "<tr>";
        tablestring=tablestring + "<td>" + 
                    userList[i].UserId + "</td>";
        tablestring=tablestring + "<td>" + 
                    userList[i].UserName + "</td>";
        tablestring=tablestring + "</tr>";
    }
    tablestring = tablestring + "</table>";
    var divResult = document.getElementById("divUserList");
    divResult.innerHTML = tablestring;
}

As you can see, in the above code, ReuqestCompleteCallback will parse a string with the JavaScript EVal function which will convert the JSON string into enumerable form. After that, it will pass that result variable to another function called CreateUserListTable, which create an HTML table string by iterating with a for loop. Then it will put that table string in the inner HTML of the container div.
The same way, if any error occurs, RequestFailedCallback will handle the Web Service error and will print all the error information in the div's inner HTML.
Now we need to call the GetUserList function on the page. We have to add JavaScript code like shown below:
window.onload=GetUserList();

As you can see, it's very easy to convert a C# generic list into custom HTML with help of JSON. This was a very basic example. You can extend this example and can create a JavaScript client side grid with paging and sorting and other features. The possibilities are unlimited. Hope your liked my effort.
Reference :

ASP.NET web method call using AJAX (jQuery)

This article is about how to call a server side function (web method) from client side (aspx page) using AJAX(provided by Jquery).

Its interesting and easy to implement. As always- lets go directly to an example and see
 how to implement it...




In this example we will create a webmethod which will return a message to client side

1) Create an ASP.NET Application.

2) Add a new page 'WebForm1.aspx' and make it the start up page.

3) In WebForm1.aspx include jQuery file as given below. If you do not have jQuery file, you can
 download the latest files from http://jquery.com/


<script src="Scripts/jquery-1.4.1.js" type="text/javascript"></script>

4) In code behind page (WebForm1.aspx.cs) create a webmethod to return some data as given below.
 Make sure to add the attribute [WebMethod()] to the function. You have to include the namespace
 System.Web.Services. (using System.Web.Services;)


[WebMethod()]
public static string GetData(int userid)
{
    /*You can do database operations here if required*/
    return "my userid is" + userid.ToString();
}


5) Add script tags and include the function to call the web method. Pass the parameter
 (in this case 'userid') to web method as JSON object


function asyncServerCall(userid) {
    jQuery.ajax({
 url: 'WebForm1.aspx/GetData',
 type: "POST",
 data: "{'userid':" + userid + "}",
 contentType: "application/json; charset=utf-8",
 dataType: "json",
 success: function (data) {
     alert(data.d);
 }

    });
}


6) Add button on aspx page and call the ajax function on click event.


<input type="button" value="click me" onclick="asyncServerCall(1);" />

7) DONE! Run the app and click the button, you can see that the webmethod is 
called and data is returned.

Thursday, October 11, 2012

How to ping a device with Java


public class Pinger {
// byte[] addr1 = new byte[]{(byte)192,(byte)168,(byte)2,(byte)5};

public static long testDBConn(byte[] addr1, int port, int timeoutMs) {
//pass in a byte array with the ipv4 address, the port & the max time
out required
long start = -1; //default check value
long end = -1; //default check value
long total = -1; // default for bad connection

//make an unbound socket
Socket theSock = new Socket();

try {
InetAddress addr = InetAddress.getByAddress(addr1);

SocketAddress sockaddr = new InetSocketAddress(addr, port);

// Create the socket with a timeout
//when a timeout occurs, we will get timout exp.
//also time our connection this gets very close to the real time
start = System.currentTimeMillis();
theSock.connect(sockaddr, timeoutMs);
end = System.currentTimeMillis();
} catch (UnknownHostException e) {
start = -1;
end = -1;
} catch (SocketTimeoutException e) {
start = -1;
end = -1;
} catch (IOException e) {
start = -1;
end = -1;
} finally {
if (theSock != null) {
try {
theSock.close();
} catch (IOException e) {
}
}

if ((start != -1) && (end != -1)) {
total = end - start;
}
}

return total; //returns -1 if timeout
}
}


public static void main(String[] args) {

// byte[] addr1 = new
byte[]{(byte)192,(byte)168,(byte)2,(byte)5};
int port = 1521;
int timeoutMs = 2000; // 2 seconds
long value = testDBConn(addr1, port, timeoutMs);
System.out.println(value);
}