c# - Metro 应用程序通过 WCF 服务连接到 SQL - 错误

Question

我为 Windows 8 创建了一个新的 Metro 应用程序。

我的意图是在地铁应用程序中使用来自 WCF 数据服务的数据。 WCF 服务应连接到安装在本地计算机上的 SQL 服务器。

我已经为我的服务设置了一个接口(interface)，这样我就可以将参数传递给我希望 WCF 执行的方法，然后将结果返回给 Metro 应用程序。

我遵循了几个不同的在线教程并取得了成功，直到......

A network-related or instance-specific error occurred whileestablishing a connection to SQL Server. The server was not found orwas not accessible. Verify that the instance name is correct and thatSQL Server is configured to allow remote connections. (provider: NamedPipes Provider, error: 40 - Could not open a connection to SQL Server)

我包含了登录登陆页面和 SQL 配置页面的代码。当 Metro 应用程序启动时，它会运行一种方法来检查是否存在连接。如果未找到连接，它会显示 SQL 配置页面。

应用程序启动。 OnNavigate 到登录表单 CheckConnection() 方法返回 true。但是当我输入用户详细信息并按登录时，我只是在登录页面上的异步无效登录()方法中的以下代码行中收到错误

newCustomType = await dataServiceClientName.CheckLoginAsync(txtUsername.Text, txtPassword.Text);

如果有人理解为什么会显示此错误，我们将不胜感激。

我在下面包含了相关代码(或者我可以说是相关的代码)。

提前致谢:)

代码片段:

简化的服务接口(interface)

[ServiceContract]
public interface CTUServiceInterface
{
    // TODO: Add your service operations here
    //LoginForm
    [OperationContract]
    bool CheckConnection();
    //SQL Config
    [OperationContract]
    bool UpdateConnectionDetails(string ConnectionString);
    //LoginForm
    [OperationContract]
    LoginDetails CheckLogin(string Username, string Password);
    //LoginForm Success
    [OperationContract]
    UserGlobalDetails GetActiveUserDetails(int UserKey);
    }
    
[DataContract]
public class LoginDetails
{
    bool loginSuccess = false;
    string errorMessage = "";
    int userKey = -1;

    [DataMember]
    public bool LoginSuccess
    {
        get { return loginSuccess; }
        set { loginSuccess = value; }
    }

    [DataMember]
    public string ErrorMessage
    {
        get { return errorMessage; }
        set { errorMessage = value; }
    }

    [DataMember]
    public int UserKey
    {
        get { return userKey; }
        set { userKey = value; }
    }
}

[DataContract]
public class UserGlobalDetails
{
    string firstName = "";
    string lastName = "";
    int departmentKey = -1;
    bool manager = false;

    [DataMember]
    public string FirstName
    {
        get { return firstName; }
        set { firstName = value; }
    }

    [DataMember]
    public string LastName
    {
        get { return lastName; }
        set { lastName = value; }
    }

    [DataMember]
    public int DepartmentKey
    {
        get { return departmentKey; }
        set { departmentKey = value; }
    }

    [DataMember]
    public bool Manager
    {
        get { return manager; }
        set { manager = value; }
    }
}

简化服务代码

public class CTUService : CTUServiceInterface
{
    private static SqlConnection localConnection = new SqlConnection("Data Source=PC-VIRTUAL;Initial Catalog=CarTraUnl;Integrated Security=True");

    //LoginForm
    public bool CheckConnection()
    {
        bool result = true;
        try
        {
            localConnection.Open();
            localConnection.Close();
        }
        catch(Exception ex)
        {
            result = false;
            throw ex;
        }
        return result;
    }


    //SQL Config
    public bool UpdateConnectionDetails(string ConnectionString)
    {
        localConnection = new SqlConnection(ConnectionString);
        return CheckConnection();
    }


    //LoginForm
    public LoginDetails CheckLogin(string Username, string Password)
    {
        //Initilize the LoginDetails Object to return with results
        LoginDetails localDetails = new LoginDetails();
        //Initilize the localDataTable to hold sql results
        DataTable localDataTable = new DataTable("localDataTable");
        //Setup a SqlDataAdapter and get info from the 'UserGlobal' Table
        localConnection.Open();
        SqlDataAdapter localDataAdapter = new SqlDataAdapter(
            "SELECT [Password],[Key]" +
            "FROM [CarTraUnl].[dbo].[UserGlobal]" +
            "WHERE [Username] = '" + Username + "'"
            , localConnection);
        localConnection.Close();
        //Fill localDataTable with information from the UserGlobal Table
        localDataAdapter.Fill(localDataTable);
        //Set loginStatus equal to the number of passwords found for the given username
        int loginStatus = localDataTable.Rows.Count;
        //If no passwords are found, there was no username like the given one
        if (loginStatus == 0)
        {
            localDetails.ErrorMessage = "Invalid Username";
        }
        //If one password was found, check if it matches the given password
        else if (loginStatus == 1 && localDataTable.Rows[0][0].ToString() == Password)
        {
            localDetails.LoginSuccess = true;
            localDetails.ErrorMessage = "";
            localDetails.UserKey = int.Parse(localDataTable.Rows[0][1].ToString());
        }
        //If one password is found, but it doesn't match show the error
        else if (loginStatus == 1 && localDataTable.Rows[0][0].ToString() != Password)
        {
            localDetails.ErrorMessage = "Invalid Password";
        }
        //If multiple passwords are found, there are duplicate usernames
        else if (loginStatus > 1)
        {
            localDetails.ErrorMessage = "Duplicate Usernames";
        }
        return localDetails;
    }


    //LoginForm Success
    public UserGlobalDetails GetActiveUserDetails(int UserKey)
    {
        //Initilize UserGlobalDetails object to return later
        UserGlobalDetails localUserGlobalDetails = new UserGlobalDetails();
        //Initilize a DataTable to hold our sql results
        DataTable localDataTable = new DataTable("localDataTable");
        //Setup a SqlDataAdapter and get info from the 'UserGlobal' Table
        SqlDataAdapter localDataAdapter = new SqlDataAdapter(
            "SELECT [FirstName],[LastName],[DepartmentKey],[Manager]" +
            "FROM [CarTraUnl].[dbo].[UserGlobal]" +
            "WHERE [Key] = '" + UserKey + "'"
            , localConnection);
        //Fill localDataTable with information from the 'UserGlobal' Table
        localDataAdapter.Fill(localDataTable);

        //Configure the UserGlobalDetails object 'localUserGlobalDetails' with the retrived results
        localUserGlobalDetails.FirstName = localDataTable.Rows[0][0].ToString();
        localUserGlobalDetails.LastName = localDataTable.Rows[0][1].ToString();
        localUserGlobalDetails.DepartmentKey = int.Parse(localDataTable.Rows[0][2].ToString());
        localUserGlobalDetails.Manager = bool.Parse(localDataTable.Rows[0][3].ToString());

        //return the results
        return localUserGlobalDetails;
    }
   }

登录.xaml.cs

public sealed partial class MainPage : Page
{
    public MainPage()
    {
        this.InitializeComponent();
    }

    protected override void OnNavigatedTo(NavigationEventArgs e)
    {
        lblError.Text = "";
    }

    private async void CheckConnection()
    {
        CTUServiceReference.CTUServiceInterfaceClient dataServiceClientName = new CTUServiceReference.CTUServiceInterfaceClient();
        bool result = await dataServiceClientName.CheckConnectionAsync();
        if (result == false)
        {
            this.Frame.Navigate(typeof(SqlConfig));
        }
    }

    private void btnLogin_Click(object sender, RoutedEventArgs e)
    {
        Login();
    }

    private async void Login()
    {
        btnLogin.IsEnabled = false;
        CTUServiceReference.CTUServiceInterfaceClient dataServiceClientName = new CTUServiceReference.CTUServiceInterfaceClient();
        CTUServiceReference.LoginDetails newCustomType = new CTUServiceReference.LoginDetails();
        newCustomType = await dataServiceClientName.CheckLoginAsync(txtUsername.Text, txtPassword.Text);
        CTUServiceReference.UserGlobalDetails currentActiveUserDetails = new CTUServiceReference.UserGlobalDetails();
        currentActiveUserDetails = await dataServiceClientName.GetActiveUserDetailsAsync(newCustomType.UserKey);
        //The globalSettings.cs file is not included, but it just holds persistent application data
        globalSettings.currentUserKey = newCustomType.UserKey;
        globalSettings.firstName = currentActiveUserDetails.FirstName;
        globalSettings.lastName = currentActiveUserDetails.LastName;
        globalSettings.departmentKey = currentActiveUserDetails.DepartmentKey;
        globalSettings.manager = currentActiveUserDetails.Manager;

        if (newCustomType.LoginSuccess)
        {
            //This page is where we go when login is successful
            this.Frame.Navigate(typeof(FormSelector));
        }
        else
        {
            lblError.Text = newCustomType.ErrorMessage;
            btnLogin.IsEnabled = true;
        }
    }

SQLConfig.xaml.cs

        private void btnSqlConfigSave_Click(object sender, RoutedEventArgs e)
    {
        saveConfig();
    }

    private async void saveConfig()
    {
        btnSave.IsEnabled = false;
        string connectionstring = "";
        if (rbAutomatic.IsChecked == true)
        {
            //Integrated Secturity
            connectionstring = "Data Source=" + txtServer.Text + ";Initial Catalog= " + txtDB.Text + ";Integrated Security=True";
        }
        else
        {
            //Standard Authentication with username and password
            connectionstring = "Data Source=" + txtServer.Text + ";Initial Catalog= " + txtDB.Text + ";User Id=" + txtUser.Text + ";Password=" + txtPass.Text;
        }
        CTUServiceReference.CTUServiceInterfaceClient dataServiceClientName = new CTUServiceReference.CTUServiceInterfaceClient();
        bool result = await dataServiceClientName.UpdateConnectionDetailsAsync(connectionstring);
        if (result)
        {
            this.Frame.GoBack();
            return;
        }
        else
        {
            lblError.Text = "Config error";
            btnSave.IsEnabled = true;
        }
    }

版本信息

Visual Studio

• Microsoft Visual Studio 旗舰版 2012
• 版本 11.0.50727.1 RTMREL
• Microsoft .NET Framework 版本 4.5.50709
• 已安装版本:Ultimate

SQL服务器

• Microsoft SQL Server 管理工作室 - 11.0.2100.60
• Microsoft Analysis Services 客户端工具 - 11.0.2100.60
• Microsoft 数据访问组件 (MDAC) - 6.2.9200.16384
• 微软 MSXML - 3.0 6.0
• Microsoft .NET 框架 - 4.0.30319.18051
• 操作系统 - 6.2.9200

Answer 1

这个错误可能有多种原因，但它意味着它的意思......是，你的服务无法访问:

"Data Source=" + txtServer.Text + ";

这很可能不是您的代码中的问题，而是 SQLServer 配置问题。

我建议您首先尝试在 ODBC 调用中使用 connectionString，看看是否可以从本地和远程访问它。

您的服务似乎试图通过命名管道进行连接，请在 SQLServer 配置管理器中检查该协议(protocol)是否已针对您的特定实例启动。

同时尝试启用 TCP/IP 协议(protocol)并检查它是否在正确的端口 (1433) 上监听。

也可能是您没有针对“命名实例”。即:

"Data Source=MYSERVER"

代替

"Data Source=MYSERVER\MyInstance"

希望这对您有所帮助!