Simple .Net ASPX/DB Sample

Objective

Needed a sample C# code to track .Net Application and its interaction with the database.

 

Files

  1. Database
    • MS SQL Server
      • Stored Procedure dbo.TimeDelay_hh_mm_ss;
  2. .Net
    • Web.config
    • testDB.aspx
    • testDB.cs

 

Stored Procedure dbo.TimeDelay_hh_mm_ss;

Stolen from WAITFOR (Transact-SQL) ( link )


IF OBJECT_ID('dbo.TimeDelay_hh_mm_ss','P') IS NOT NULL  
    DROP PROCEDURE dbo.TimeDelay_hh_mm_ss;  
GO  
CREATE PROCEDURE dbo.TimeDelay_hh_mm_ss   
    (  
    @DelayLength char(8)= '00:00:00'  
    )  
AS  
DECLARE @ReturnInfo varchar(255)  
IF ISDATE('2000-01-01 ' + @DelayLength + '.000') = 0  
    BEGIN  
        SELECT @ReturnInfo = 'Invalid time ' + @DelayLength   
        + ',hh:mm:ss, submitted.';  
        -- This PRINT statement is for testing, not use in production.  
        PRINT @ReturnInfo   
        RETURN(1)  
    END  
BEGIN  
    WAITFOR DELAY @DelayLength  
    SELECT @ReturnInfo = 'A total time of ' + @DelayLength + ',   
        hh:mm:ss, has elapsed! Your time is up.'  
    -- This PRINT statement is for testing, not use in production.  
    PRINT @ReturnInfo;  
END;  
GO  

Web.config


<!--l version="1.0"-->

<configuration>
	
	<connectionStrings>

		<clear />
	 
		<add name="dbConn" connectionString="Server=DBDEV;Database=DBLAB;Trusted_Connection=Yes;" >

		</add>
		
	</connectionStrings>

	
</configuration>

testDB.aspx



<%@ Page language="c#" Codefile="testDB.cs" AutoEventWireup="true" Inherits="_Default" Debug="true" %>

<title>
Simple
</title>


<form id="form1" runat="server">


<div>

		<asp:button id="btnTestDb" onclick="btnTestDbClick" runat="server" text="Test Database Connection" />
		
		</BR>
		&nbsp;&nbsp;
		
		Timeout (HH:MM:SS)
		<asp:TextBox runat="server" ID="txtTimeoutValue" text="00:01:00" >
		</asp:TextBox>
		
		<asp:button id="btnTestDbSP" onclick="btnTestDbSPClick" runat="server" text="Test Database Stored Procedure" />
		
		<asp:Label runat="server" ID="labelStatus" >
		</asp:Label>
		
		
	</div>


</form>


 

 

testDB.cs


using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
 
 public partial class _Default : System.Web.UI.Page
 {
	 
	private String strDBConnectionString = null;
	private SqlConnection connection = null;
	private String CHAR_NEWLINE = "</BR>";
	
	protected void Page_Load(object sender, EventArgs e)
	{
		
	}
	
	protected Boolean connectDB()
	{
		
		Boolean bDBConnected = false;
		
		try
		{
			
			//strDBConnectionString = ConfigurationManager.ConnectionStrings["EarthConn"].ConnectionString;
			
			if (ConfigurationManager.ConnectionStrings["dbConn"] == null)
			{

				Response.Write("ConfigurationManager.ConnectionStrings['dbConn']");
				
			}
			
			strDBConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString;
			
			if (strDBConnectionString == null)
			{
				return bDBConnected;
			}

				
			connection = new SqlConnection
							  (
								strDBConnectionString
							  );
											
			
			if (connection == null)
			{
				return bDBConnected;				
			}
				
			connection.Open();

			
			if (
					(connection.State == ConnectionState.Open)
				)			
			{
				
				labelStatus.Text += "Connection OK!"  + CHAR_NEWLINE;
				
				bDBConnected = true;
			}
			else
			{
				
				//Response.Write("No Connection!" + CHAR_NEWLINE);
				labelStatus.Text += "Connection OK!"  + CHAR_NEWLINE;				
			}
			
			
		}
		catch (Exception ex)
		{
			
			//Response.Write("No Connection!" + ex.Message + CHAR_NEWLINE);
			
			labelStatus.Text += "No Connection!" + ex.Message + CHAR_NEWLINE;
			
		}
		
		return (bDBConnected);

	}
 
	protected void btnTestDbClick
					(
						  object sender
						, EventArgs e
					)
	{
		
		Boolean bDBConnected = false;
		
		labelStatus.Text = "";
		
		bDBConnected = connectDB();
		
		if (bDBConnected)
		{
				connection.Close();
		}			
		
	}    
	
 
	protected void btnTestDbSPClick
					(
						  object sender
						, EventArgs e
					)
	{
		
		Boolean bDBConnected = false;
		String strDelayLength;
		
		try
		{
		
			labelStatus.Text = "";
		
			//strDelayLength = "00:05:00";
			strDelayLength = txtTimeoutValue.Text;
			
			bDBConnected = connectDB();
			
			// 1.  create a command object identifying the stored procedure
			SqlCommand cmd  = new SqlCommand("dbo.TimeDelay_hh_mm_ss", connection);

			// 2. set the command object so it knows to execute a stored procedure
			cmd.CommandType = CommandType.StoredProcedure;

			// 3. add parameter to command, which will be passed to the stored procedure
			cmd.Parameters.Add(new SqlParameter("@DelayLength", strDelayLength));

			labelStatus.Text += "DB SP Started @ " + DateTime.Now.ToString() + CHAR_NEWLINE;

			labelStatus.Text += "Waiting " + strDelayLength + CHAR_NEWLINE;
			
			// execute the command
			cmd.ExecuteNonQuery();
			
			labelStatus.Text += "DB SP Ended @ " + DateTime.Now.ToString() + CHAR_NEWLINE ;		
			
			if (bDBConnected)
			{
					connection.Close();
			}		

		}
		catch (Exception ex)
		{
			labelStatus.Text += ex.Message;
			throw ex;
		}		
    }
		
	
 }	

 

Output

Processing Wait 15 Seconds

testDB-ReqestWait15seconds

 

Processed Wait 15 Seconds

 

testDB-ProcessedWait15seconds

 

Diagnostics

Failed Request Tracing

Sample files from “Failed Request Tracing“.

FailedRequestTracing-fr000039

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s