Test SQL connection from C# ASP WEB application

Question

I am a newbie to C# programming, and am trying to get a REST API working. For some reason it is not connecting from iOS, and I wanted to test the SQL connection in order to troubleshoot the connection from that point first. How can I go about testing it? I tried to figure it out, but my understanding of C# is still quite limited.

Here is the code below:

Web.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <system.web>
    <compilation debug="true" targetFramework="4.5" />
    <httpRuntime targetFramework="4.5" />
  </system.web>
  <connectionStrings>
    <add name="conString" connectionstring="Data Source=10.0.0.1;Initial Catalog=DBName;Password=Password;User ID=UserID;Integrated Security=True;" providername="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

IServiceAPI.CS

using System.Data;

namespace RESTWebAPI
{
    // This interface declare the methods need to be implement.
    public interface IServiceAPI
    {
        void CreateNewAccount(string username, string password);
        DataTable Getmembers(string username);
        bool UserAuthentication(string username, string passsword);
    }
}

ServiceAPI.CS

using System;
using System.Data;
using System.Data.SqlClient;
using Web.config;

namespace RESTWebAPI
{
        public static bool IsServerConnected(string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    return true;
                }
                catch (SqlException)
                {
                    return false;
                }
            }
        }

    public class ServiceAPI : IServiceAPI
    {
        SqlConnection dbConnection;

        public ServiceAPI()
        {
            dbConnection = DBConnect.getConnection();
        }

        public void CreateNewAccount(string username, string password)
        {
            if (dbConnection.State.ToString() == "Closed")
            {
                dbConnection.Open();
            }
            string query = "INSERT INTO members VALUES ('" + username + "','" + password + "');";
            SqlCommand command = new SqlCommand(query, dbConnection);
            command.ExecuteNonQuery();
            dbConnection.Close();
        }

        public DataTable Getmembers(string username)
        {
            DataTable membersTable = new DataTable();
            membersTable.Columns.Add(new DataColumn("username", typeof(String)));

            if (dbConnection.State.ToString() == "Closed")
            {
                dbConnection.Open();
            }

            string query = "SELECT username FROM members WHERE username='" + username + "';";
            SqlCommand command = new SqlCommand(query, dbConnection);
            SqlDataReader reader = command.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    membersTable.Rows.Add(reader["username"]);
                }
            }
            reader.Close();
            dbConnection.Close();
            return membersTable;
        }

        public bool UserAuthentication(string username, string passsword)
        {
            bool auth = false;

            if (dbConnection.State.ToString() == "Closed")
            {
                dbConnection.Open();
            }
            string query = "SELECT id FROM members WHERE username='" + username + "' AND password='" + passsword + "';";

            SqlCommand command = new SqlCommand(query, dbConnection);
            SqlDataReader reader = command.ExecuteReader();

            if (reader.HasRows)
            {
                auth = true;
            }
            reader.Close();
            dbConnection.Close();
            return auth;
        }
    }
}

DBConnect.cs

using System.Configuration;
using System.Data.SqlClient;

namespace RESTWebAPI
{
    // This class is used to connect to sql server database
    public class DBConnect
    {
        private static SqlConnection NewCon;
        private static string conStr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;

        public static SqlConnection getConnection()
        {
            NewCon = new SqlConnection(conStr);
            return NewCon;
        }

        public DBConnect()
        {
        }
    }
}

Handler1.ashx.cs

using JsonServices;
using JsonServices.Web;

namespace RESTWebAPI
{
    public class Handler1 : JsonHandler
    {
        public Handler1()
        {
            this.service.Name = "RESTWebAPI";
            this.service.Description = "JSON API for mobile application";
            InterfaceConfiguration IConfig = new InterfaceConfiguration("RestAPI", typeof(IServiceAPI), typeof(ServiceAPI));
            this.service.Interfaces.Add(IConfig);

        }
    }
}

catch (SqlException) {return false;} You know the point of catching exceptions? To handle them, either by trying something else or logging the problem. But you aren't doing either of those, so you don't know what the error is. And since you don't know what the error is, you can't tell us. For now, just remove the try/catch and see what the error message is, then update your question with that information. — mason
– mason, Commented Feb 9, 2015 at 19:06
I have cleaned up your code to remove the multitude of empty lines. Please remove this nothing code when posting to Stack Overflow for the benefit of those trying to help you. — DLeh
– DLeh, Commented Feb 9, 2015 at 19:08
My apologies for the poor formatting. I appreciate your help. — J.J.
– J.J., Commented Feb 9, 2015 at 19:41

void · Accepted Answer · 2015-02-09 19:34:49Z

your IsServerConnected method is static, Note: if connection be open and you try to open it then for sure you'll get an exception, I mean for an open connection connection.Open(); raises an exception, so you need a finally block to close the connection after opening it, if the IsServerConnected method is only for checking the connection:

public static bool IsServerConnected(string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    return true;
                }
                catch (SqlException)
                {
                    return false;
                }
                finally
                {
                    try
                     {
                         connection.Close();
                     }
                     catch (Exception ex)
                     {
                     }
                }
            }
        }

Collectives™ on Stack Overflow

Test SQL connection from C# ASP WEB application

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related