Web Technologies

BLOG for Web Technologies

Freewares, Free E-Books Download, SEO, Tips, Tricks, Tweaks, Latest News, .Net, PHP, ASP, ASP.Net, CSP, MS SQL Server, MySQL, Database
earnptr.com
Thursday, September 18, 2008
Calling Stored Procedures from ASP.NET and VB.NET
Let us see how can we create and call Stored procedures, in a .NET Environment, i.e Visual Studio.We use .NET 2.0 and Visual Studio 2005 for all the examples.

Creating Stored Procedures

Writing stored procedures has never been easy as Microsoft has almost integrated SQL Server with Visual Studio 2005. In the past most of the developers has wondered can’t we have a good editor for creating stored procedures. One of the main advantage of creating procedures in Visual Studio is it creates the basic stub for you and further more, it has inbuilt syntax checking which makes the job easier for us.

In order to create a stored procedure from Visual Studio, first you need to create a data connection from the Server Explorer and follow the below steps.

Step 1: Open Visual Studio 2005.

Step 2: Create a VB.NET / C# Windows / Web Application Project.

Step 3: Open the Server Explorer by Selecting View -> Server Explorer

Step 4: Create a Data Connection to your server you can do this by Right Clicking on the Data Connection Tree and Selecting “Add New Connection”.

Step 5: It will Prompt for the Provider Type you can select .NET SQL Server Provider as it gives more performance.

Step 6: After giving all the credentials once the connection is active expand the database that you are having.

Step 7: Expand the Stored Procedure Tree.

Step 8: To Create a New Procedure Right Click and Select “Add New Procedure”.

Step 9: The IDE will give you a Stub where you can replace the Name of the Procedure and Arguments.

Those who are familiar with Visual Studio IDE would love to create procedures here rather then doing it in Query Analyzer or in SQL Enterprise Manager, though it doesn’t provide any fancy auto complete drop downs its still the best I believe to create stored procedures.

TIP: The Maximum number of parameters in a stored procedure is 2100.

Calling Stored Procedure

Hope everyone have used SQLCommand / OLEDB Command objects in .NET. Here we can call stored procedures in two different forms, one without using parameter objects which is not recommended for conventional development environments, the other one is the familiar model of using Parameters.

In the first method you can call the procedure using Exec command followed by the procedure name and the list of parameters, which doesn’t need any parameters.

Example:

Dim SQLCon As New SqlClient.SqlConnection
SQLCon.ConnectionString = "Data Source=Server;User ID=User;Password=Password;"
SQLCon.Open()

Calling Stored Procedures with Exec command

SQLCmd.CommandText = "Exec SelectRecords 'Test', 'Test', 'Test'"
SQLCmd.Connection = SQLCon 'Active Connection

The second most conventional method of calling stored procedures is to use the parameter objects and get the return values using them. In this method we need to set the “SQLCommandType” to “StoredProcedure” remember you need to set this explicitly as the the default type for SQLCommand is SQLQuery”.

Here is an example to call a simple stored procedure.

Example - I (A Stored Procedure Returns Single Value)

In order to get XML Results from the Stored Procedure you need to first ensure that your stored procedure is returning a valid XML. This can be achieved using FOR XML [AUTO | RAW | EXPLICIT] clause in the select statements. You can format XML using EXPLICIT Keyword, you need to alter your Query accordingly

'Set up Connection object and Connection String for a SQL Client
Dim SQLCon As New SqlClient.SqlConnection
SQLCon.ConnectionString = "Data Source=Server;User ID=User;Password=Password;"
SQLCon.Open()

SQLCmd.CommandText = "SelectRecords" ' Stored Procedure to Call
SQLCmd.CommandType = CommandType.StoredProcedure 'Setup Command Type
SQLCmd.Connection = SQLCon 'Active Connection

The procedure can be called by adding Parameters in at least two different methods, the simplest way to add parameters and respective values is using

SQLCmd.Parameters.AddWithValue("S_Mobile", "Test")
SQLCmd.Parameters.AddWithValue("S_Mesg", "Test")
SQLCmd.Parameters.AddWithValue("LastMsgID", "")

In this above method, you doesn’t necessarily know the actually data type that you had in your procedure and all parameters are validated according to the type declared in your procedure but only thing is all the validations will occur in SQL and not in your client code.

We still need to declare the last parameter as Output and we need to do that explicitly as the default type is Input. So here we are going to declare the last parameter as Output by

SQLCmd.Parameters("LastMsgID").Direction = ParameterDirection.Outputfs

If you want to declare parameters properly then you need to use the below method to add all the parameters with its data type, direction. Also if you are using stored procedures to update all the rows in a dataset then you need to declare parameters in the below fashion and give SouceColumn value as the Column name in the DataTable.

SQLCmd.Parameters.Add(New SqlClient.SqlParameter("S_Mobile", SqlDbType.VarChar, 10, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, "91000000000"))

SQLCmd.Parameters.Add(New SqlClient.SqlParameter("S_Mesg", SqlDbType.VarChar, 160, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, "Calling Stored Procedures from VB.NET"))

SQLCmd.Parameters.Add(New SqlClient.SqlParameter("LastMsgID", SqlDbType.BigInt, 5, ParameterDirection.Output, False, 5, 0, "", DataRowVersion.Current, 0))

' The Above Procedure has two input parameters and one output parameter you can notice the same in the “Parameter Direction”
SQLCmd.ExecuteNonQuery() 'We are executing the procedure here by calling Execute Non Query.

MsgBox(SQLCmd.Parameters("LastMsgID").Value) 'You can have the returned value from the stored procedure from this statement. Its all similar to ASP / VB as the only difference is the program structure.

Example - II (Stored Procedure to get Table Result Set)

In order to get the result sets from the stored procedure, the best way is to use a DataReader to get the results. In this example we are getting the results from the Stored Procedure and filling the same in a DataTable.

Here we need to additionally declare a SQLDataReader and DataTable

Dim SQLDBDataReader As SqlClient.SqlDataReader
Dim SQLDataTable As New DataTable

SQLCmd.CommandText = "GetAuthors"
SQLCmd.CommandType = CommandType.StoredProcedure
SQLCmd.Connection = SQLCon
SQLCmd.Parameters.Add(New SqlClient.SqlParameter("AuthorName", SqlDbType.VarChar, 100, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, "Y%")) SQLDBDataReader = SQLCmd.ExecuteReader() SQLDataTable.Columns.Add("AuthorName", GetType(Int32), "") SQLDataTable.Columns.Add("AuthorLocation", GetType(String), "")

Dim FieldValues(1) As Object 'A Temporary Variable to retrieve all columns in a row and fill them in Object array

While (SQLDBDataReader.Read)
SQLDBDataReader.GetValues(FieldValues)
SQLDataTable.Rows.Add(FieldValues)

End While

Example - II (Calling Simple Stored Procedure to get XML Result Set)

In order to get XML Results from the Stored Procedure you need to first ensure that your stored procedure is returning a valid XML. This can be achieved using FOR XML [AUTO | RAW | EXPLICIT] clause in the select statements. You can format XML using EXPLICIT Keyword, you need to alter your Query accordingly.

CREATE PROCEDURE GetRecordsXML (@AuthorName varchar(100))
AS

Select Author_ID, Author_Name, Author_Location Where Author_Name LIKE @AuthorName from Authors FOR XML AUTO

RETURN

When you use the above procedure you can get XML Results with TableName as Element and Fields as Attributes

Dim SQLXMLReader As Xml.XmlReader

SQLCmd.CommandText = "GetAuthorsXML"
SQLCmd.CommandType = CommandType.StoredProcedure
SQLCmd.Connection = SQLCon
SQLCmd.Parameters.Add(New SqlClient.SqlParameter("AuthorName", SqlDbType.VarChar, 100, ParameterDirection.Input, False, 30, 0, "", DataRowVersion.Current, "Y%"))
SQLDBDataReader = SQLCmd.ExecuteReader()

SQLXMLReader = SQLCmd.ExecuteXmlReader()
While (SQLXMLReader.Read)
MsgBox(SQLXMLReader.ReadOuterXml)
End While

You can further process this XML or write XSL to display results in a formatted manner. But in order to get formatted XML Results, we need to use EXPLICIT case.

Labels: , , ,

posted by WebTeks @ 5:53 AM   0 comments
JavaScript - Cross Browser Modal Dialog Box
The following JavaScript code snippet demonstrates how to create a wide variety of modal dialog boxes. The first block of JavaScript code can be put into a generic .js script file. The second JavaScript block contains a couple of wrapper samples for creating a yes, no, cancel option as well as a yes, no, maybe option to demonstrate that you can create an unlimited number of customized dialogs. You'll probably want to create a few standard options and put them in your own generic .js script file. It is pretty easy to force the window to be modal. We'll attack the issue in both the window.opener and the modal dialog window itself. In the window.opener, we'll use the window.setInterval() method to repeatedly check to see if our globally defined winmodal window is open. If so, we'll set its focus by using window.focus(). To avoid any extra window minimization of the modal dialog window, we'll call window.focus() in the dialog window body tag's onblur() event. When we launch the modal dialog window, we'll also include the name of the method we want called when an option is selected by the user and the window is closed. Notice how I opted to use eval() to dynamically fire the passed in JavaScript method when the window is closed to handle the business logic when the user selects a response. Unlike typical modal dialogs in windows, you have to separate out the code the launches the window and the code that reacts to the response into two separate JavaScript functions.I believe you'll find this to be an effective way to create modal dialog windows. In fact, it even handles the user clicking on non IE windows and then attempting to return to the window.opener or if the user tries to close the browser window by right clicking on it while in a minimized state. I hope you found this little snippet helpful.

Modal Dialog Box Sample Code

<html>
<script language="JavaScript">

var ModalDialogWindow;
var ModalDialogInterval;
var ModalDialog = new Object;

ModalDialog.value = '';
ModalDialog.eventhandler = '';


function ModalDialogMaintainFocus()
{
try
{
if (ModalDialogWindow.closed)
{
window.clearInterval(ModalDialogInterval);
eval(ModalDialog.eventhandler);
return;
}
ModalDialogWindow.focus();
}
catch (everything) { }
}

function ModalDialogRemoveWatch()
{
ModalDialog.value = '';
ModalDialog.eventhandler = '';
}

function ModalDialogShow(Title,BodyText,Buttons,EventHandler)
{

ModalDialogRemoveWatch();
ModalDialog.eventhandler = EventHandler;

var args='width=350,height=125,left=325,top=300,toolbar=0,';
args+='location=0,status=0,menubar=0,scrollbars=1,resizable=0';

ModalDialogWindow=window.open("","",args);
ModalDialogWindow.document.open();
ModalDialogWindow.document.write('<html>');
ModalDialogWindow.document.write('<head>');
ModalDialogWindow.document.write('<title>' + Title + '</title>');
ModalDialogWindow.document.write('<script' language="JavaScript">');
ModalDialogWindow.document.write('function CloseForm(Response) ');
ModalDialogWindow.document.write('{ ');
ModalDialogWindow.document.write(' window.opener.ModalDialog.value = Response; ');
ModalDialogWindow.document.write(' window.close(); ');
ModalDialogWindow.document.write('} ');
ModalDialogWindow.document.write('</script' + '>');
ModalDialogWindow.document.write('</head>');
ModalDialogWindow.document.write('<body onblur="window.focus();">');
ModalDialogWindow.document.write('<table border="0" width="95%" align="center" cellspacing="0" cellpadding="2">');
ModalDialogWindow.document.write('<tr><td align="left">' + BodyText + '</td></tr>');
ModalDialogWindow.document.write('<tr><td align="left"><br /></td></tr>');
ModalDialogWindow.document.write('<tr><td align="center">' + Buttons + '</td></tr>');
ModalDialogWindow.document.write('</body>');
ModalDialogWindow.document.write('</html>');
ModalDialogWindow.document.close();
ModalDialogWindow.focus();
ModalDialogInterval = window.setInterval("ModalDialogMaintainFocus()",5);

}

</script>

<script language="JavaScript">


function YesNoCancel(BodyText,EventHandler)
{
var Buttons='';
Buttons = '<a href="javascript:CloseForm(">Yes</a> ';
Buttons += '<a href="javascript:CloseForm(">No</a> ';
Buttons += '<a href="javascript:CloseForm(">Cancel</a> ';
ModalDialogShow("Dialog",BodyText,Buttons,EventHandler);
}

function YesNoMaybe(BodyText,EventHandler)
{
var Buttons='';
Buttons = '<a href="javascript:CloseForm(">Yes</a> ';
Buttons += '<a href="javascript:CloseForm(">No</a> ';
Buttons += '<a href="javascript:CloseForm(">Maybe</a> ';
ModalDialogShow("Dialog",BodyText,Buttons,EventHandler);
}

function YesNoCancelReturnMethod()
{
document.getElementById('modalreturn1').value = ModalDialog.value;
ModalDialogRemoveWatch();
}

function YesNoMaybeReturnMethod()
{
document.getElementById('modalreturn2').value = ModalDialog.value;
ModalDialogRemoveWatch();
}

</script>

<body>

<table border="1" cellpadding="2" cellspacing="2" align="center" width="60%">
<tr><td align="left"></td></tr>
<tr><td align="left"></td></tr>
<tr><td align="left"></td></tr>
<tr>
<td align="left"><a href="javascript:YesNoCancel('Yes, no, or cancel me',
'YesNoCancelReturnMethod()');">Show Modal #1</a>
1. <input type="text" id="modalreturn1" name="modalreturn1" value="''"></td>
</tr>
<tr>
<td align="left"><a href="javascript:YesNoMaybe('Yes, no, or maybe me',
'YesNoMaybeReturnMethod()');">Show Modal #2</a>
2. <input type="text" id="modalreturn2" name="modalreturn2" value="''"></td>
</tr>

</table>

</body>
</html>


Labels: ,

posted by WebTeks @ 5:53 AM   0 comments
Wednesday, September 10, 2008
11 Ebooks on Web Development, AJAX, ASP.NET, C++, C#, and XSLT
Here is a Google Groups post that contains links to 11 freely available ebooks covering Web Development and Programming, AJAX, ASP.NET, C++ Programming, Microsoft Visual Studio, Visual C Sharp (C#), and XSLT.

To access the download link for the ebooks on rapdishare.de, click on the Free button at the bottom of the rapidshare page, wait about 30 seconds, then enter the 3 character code and click on the download button. (You will need to wait 1 hour between large downloads.). To uncompress .rar files you can use 7-Zip, available here: www.7-zip.com/download.html.

Labels: , , , , ,

posted by WebTeks @ 5:26 AM   0 comments
Previous Post
Archives
Links
Template by

Free Blogger Templates

BLOGGER

Subscribe in NewsGator Online Subscribe in Rojo Add to Google Add to netvibes Subscribe in Bloglines Web Developement Blogs - BlogCatalog Blog Directory Blogarama - The Blog Directory Blog Directory & Search engine Computers Blogs - Blog Top Sites Top Computers blogs