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
introluxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

use this
Response.write ("ExecuteNonQuery in SqlCommand executed !!")
0
Nitin SontakkeDeveloperCommented:
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.
0
introluxAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Nitin SontakkeDeveloperCommented:
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?
0
introluxAuthor Commented:
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.
0
lankapalaCommented:
MSSQL connection needs to be in Web.config
0
Nitin SontakkeDeveloperCommented:
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?)
0
introluxAuthor Commented:
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

0
introluxAuthor Commented:
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
0
introluxAuthor Commented:
I have decided to go with an ASP page instead, tested and all seems to now be working as aspx required .dll's which was overkill for something so simple I needed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
introluxAuthor Commented:
Approach using ASP worked.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.