asked on

Connecting to a MSSQL Database Using ASP.NET

Hi,

I have a sample code which I am trying to put together a web page that checks the MSSQL database connection, if it can connect successfully then I would like it to display "Success" and if it cannot "Failed"

using System;
using System.Windows.Forms;
using System.Data.SqlClient; 

namespace WindowsApplication1

{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string connetionString = null;
            SqlConnection connection ;
            SqlCommand command ;
            string sql = null;
            connetionString = "Data Source=ServerName;Initial Catalog=TEST;User ID=TEST;Password=TEST";

            sql = "select max(table_catalog) as x from information_schema.tables";

            connection = new SqlConnection(connetionString);
            try

            {
                connection.Open();
                command = new SqlCommand(sql, connection);
                command.ExecuteNonQuery();
                command.Dispose();
                connection.Close();
                MessageBox.Show (" ExecuteNonQuery in SqlCommand executed !!");
            }

            catch (Exception ex)

            {
                MessageBox.Show("Can not open connection ! ");
            }
        }
    }
}

Open in new window

Any help will be appreciated.

Thanks,

introlux

lankapala

you can use, instead of
MessageBox.Show (" ExecuteNonQuery in SqlCommand executed !!");

use this
Response.write ("ExecuteNonQuery in SqlCommand executed !!")

Nitin Sontakke

Please be very specific with your question. What is happening now? Are you seeing ANY message box or none? Did you manage to debug the application?

With the following statement right at the top, I suspect that this is a Windows Forms Desktop application and not asp.net as you mentioned.

using System.Windows.Forms;

Open in new window

As such, the MessageBox should work as expected.

introlux

ASKER

So what I want to view is a web page that displays this rather than buttons.

So if the connection to MSSQL is successful to display this message on the webpage otherwise Failed message.

Thats really it.

Nitin Sontakke

It's difficult / simple depending on your understanding of the ASP.NET development platform. First, you should have taken Web Application as a project instead of the the Windows Forms Application.

These are completely different two technologies. Now, I have absolutely no idea about your skill sets so cannot comment (and unfortunately, cannot educate, either!)

May be google for a tutorial or something like that. In addition, what is the purpose of this exercise? Are you learning?

introlux

ASKER

Hi,

Yes not an expert and you are right the example above is the wrong format and should be web form. Only realised that after I posted my response.

I am coming from a server admin, so I wanted to put a custom ASPX page to display the connectivity to the database and put monitoring like Nagios to check this if SQL connectivity between A and B was to go down then this would be picked up.

Does that make more sense?

I have managed to get this sorted for the php to muse connectivity so I was hoping something on the equivalent would exist in the .NET world.

lankapala

MSSQL connection needs to be in Web.config

Nitin Sontakke

Yes not an expert and you are right the example above is the wrong format and should be web form. Only realised that after I posted my response.

Perfect.

Does that make more sense?

Well, slightly. Your intentions are clear, however, that poses more questions.
Is this expected to attended or unattended?
Does it have to have a UI (user interface) element? (If UI, then must be attended.)
Can this not be performed by something like PowerShell may be?
Connectivity is connectivity, where does asp.net comes into picture? You can (hopefully) achieve this with php too.
Does it always between A to B? What if tomorrow you need A to C or even L to M? I had rather develop just one solution for all.
Is this for your personal / quicker usage, or giving it to someone else in organisation so that you need not be bothered?
(if giving someone else then need UI and also some hosting environment, do you have it already?)

introlux

ASKER

I am now getting the following error: (Any ideas what I need to do?)

The file '/CheckConnection.aspx' has not been pre-compiled, and cannot be requested.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Web.HttpException: The file '/CheckConnection.aspx' has not been pre-compiled, and cannot be requested.

Source Error: 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace: 


[HttpException (0x80004005): The file '/CheckConnection.aspx' has not been pre-compiled, and cannot be requested.]
   System.Web.Compilation.BuildManager.GetVPathBuildResultInternal(VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile, Boolean throwIfNotFound, Boolean ensureIsUpToDate) +674
   System.Web.Compilation.BuildManager.GetVPathBuildResultWithNoAssert(HttpContext context, VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile, Boolean throwIfNotFound, Boolean ensureIsUpToDate) +142
   System.Web.Compilation.BuildManager.GetVirtualPathObjectFactory(VirtualPath virtualPath, HttpContext context, Boolean allowCrossApp, Boolean throwIfNotFound) +161
   System.Web.Compilation.BuildManager.CreateInstanceFromVirtualPath(VirtualPath virtualPath, Type requiredBaseType, HttpContext context, Boolean allowCrossApp) +47
   System.Web.UI.PageHandlerFactory.GetHandlerHelper(HttpContext context, String requestType, VirtualPath virtualPath, String physicalPath) +44
   System.Web.MaterializeHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +377
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +134

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.7.2106.0

Open in new window

introlux

ASKER

Hi,

Responding back to your previous message, this connectivity would not change therefore will remain the way it is. It needs to be asp.net because the server its hosted on does not have PHP installed on there and I do not have the authority to install PHP (Windows Server).

The use of this is, we have basic load balancers which seeks out for a keyword, we want to be in a position where we can remove a custom page, so that the load balancer is aware that the server is offline. Also checking the successful DB connection is the best way for servers that do not have static content as the load balancer that is currently in use is very basic and classic, hence the reason for this bit of work.

Thanks,

introlux

ASKER CERTIFIED SOLUTION

introlux

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

introlux

ASKER

Approach using ASP worked.