SQL connection string in web service c#

Question

Hi I'm having a problem finding the correct connection statement for my web-service to an sql-server database. I'm trying to retrieve data from my database to check a users login details.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace BTC_Service
{
    public class UseDatabase
    {
        SqlConnection sqlConn;

        internal Boolean Connect()
        {
            try
            {
                sqlConn = new SqlConnection(@"Integrated Security=true; Initial Catalog=BTCFS_DataBase; Data Source=.\SQLEXPRESS;");
                sqlConn.Open();
                return true;
            }
            catch (SqlException ex)
            {
                return false;
            }
        }

        internal void DisconnectDatabase()
        {
            sqlConn.Close();
        }

        internal Boolean ExecuteCommand(String query)
        {
            try
            {
                SqlCommand cmd = sqlConn.CreateCommand();
                cmd.CommandText = query;
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (SqlException ex)
            {
                return false;
            }
        }

        internal SqlDataReader ExecuteQuery(String query)
        {
            try
            {
                SqlCommand cmd = sqlConn.CreateCommand();
                cmd.CommandText = query;
                return cmd.ExecuteReader();
            }
            catch (SqlException ex)
            {
                return null;
            }
         }
    }
}

The database is created with sql-server 2008 and the path for it is:

C:\BTCFS_DataBase\db_BTDC_data.mdf

and the log file

C:\BTCFS_DataBase\db_BTDC_log.ldf

There is no password for the database and the code is as follows:

USE master
GO

create database db_BTCFC 
ON PRIMARY
(
    NAME =  'db_BTCFC_Data',
    FILENAME = 'c:\BTCFS_DataBase\db_BTDC_data.mdf',
    SIZE = 5MB,
    FILEGROWTH = 10%    
)
LOG ON
(
    NAME = 'db_BTFC_log',
    FILENAME = 'c:\BTCFS_DataBase\db_BTDC_log.ldf',
    SIZE = 5MB,
    FILEGROWTH = 10%
)
GO

Is there any suggestion to what I am doing wrong? Should I add the database to visual studio in a specific way? Or am i creating my database in the wrong way?

Thank you in advance.

FarligOpptreden · Accepted Answer · 2014-08-11 11:07:02Z

1

The fact that you are connected using Integrated Security, means that your local user account on Windows should be authenticated on the SQL server instance which is hosted locally on your machine (evident by the "." in the Data Source, which refers to your local machine). It might be that the setup of your SQL server instance doesn't accommodate windows authentication. Check that your configuration allows for "mixed mode" authentication, i.e. either Windows authentication or username/password authentication...

answered Aug 11, 2014 at 11:07

FarligOpptreden

5,04324 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wolfish Over a year ago

You may find this a useful link to your answer: connectionstrings.com/sql-server-2008

Kobus · Accepted Answer · 2014-08-11 13:02:29Z

1

I found this statement to be more effective than the previous one:

sqlConn = new SqlConnection(@"Integrated Security=SSPI; Initial Catalog=BTCFS_DataBase; Data Source=localhost");

Thanks @Wolfish for the link.

answered Aug 11, 2014 at 13:02

Kobus

292 silver badges5 bronze badges

1 Comment

Wolfish Over a year ago

You may wish to verify that this works from an external source, unless the db resides on your web server and the XML has the attribute Runat:="server" or similar. If the code runs in an instance of the site within the browser, you've got a major design flaw anyway :)

Collectives™ on Stack Overflow

SQL connection string in web service c#

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related