System data sqlclient sqlexception ошибка подключения к серверу

When I experienced this error in Visual Studio,

“A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 — Could not open a connection to SQL Server)”

…it was during the execution of the following C# code, which was attempting to obtain my SQL Server data to display it in a grid. The break occurred exactly on the line that says connect.Open():

        using (var connect = Connections.mySqlConnection)
        {
            const string query = "SELECT Name, Birthdate, Narrative FROM Friends";
            using (var command = new SqlCommand(query, connect))
            {
                connect.Open();
                using (var dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        // blah
                    }
                }
            }
        }

It was inexplicable because the SQL query was very simple, I had the right connection string, and the database server was available. I decided to run the actual SQL query manually myself in SQL Management Studio and it ran just fine and yielded several records. But one thing stood out in the query results: there was some improperly encoded HTML text inside a varchar(max) type field within the Friends table (specifically, some encoded comment symbols of the sort <!-- lodged within the «Narrative» column’s data). The suspect data row looked like this:

Name    Birthdate    Narrative
====    =========    ============== 
Fred    21-Oct-79    <!--HTML Comment -->Once upon a time...

Notice the encoded HTML symbol «<«, which stood for a «<» character. Somehow that made its way into the database and my C# code could not pick it up! It failed everytime right at the connect.Open() line! After I manually edited that one row of data in the database table Friends and put in the decoded «<» character instead, everything worked! Here’s what that row should have looked like:

Name    Birthdate    Narrative
====    =========    ============== 
Fred    21-Oct-79    <!--HTML Comment -->Once upon a time...

I edited the one bad row I had by using this simple UPDATE statement below. But if you had several offending rows of encoded HTML, you might need a more elaborate UPDATE statement that uses the REPLACE function:

UPDATE Friends SET Narrative = '<!--HTML Comment -->Once upon a time...' WHERE Narrative LIKE '&lt%'

So, the moral of the story is (at least in my case), sanitize your HTML content before storing it in the database and you won’t get this cryptic SQL Server error in the first place! (Uh, properly sanitizing/decoding your HTML content is the subject of another discussion worthy of a separate StackOverflow search if you need more information!)

Ошибка соединения с бд

16.07.2013, 15:26. Показов 10522. Ответов 8

0

System.Data.SqlClient.SqlException (0x80131904):
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 — Could not open a connection to SQL Server)

System.ComponentModel.Win32Exception (0x80004005): The network path
was not found

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) > at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions
userOptions, DbConnectionInternal& connection) at
System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection
owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at
System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory,
TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1
retry) at
System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.b__0() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1
operation) at
System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action
operation) at
System.Data.Entity.Core.EntityClient.EntityConnection.Open()
ClientConnectionId:00000000-0000-0000-0000-000000000000

Mar 17, 2017 12:26:30 PM |
.NET Exception Handling: System.Data.SqlClient.SqlException

An exploration of the System.Data.SqlClient.SqlException in .NET, including a code example for connecting to and querying SQL databases.

As we continue down the beautiful path that winds through our .NET Exception Handling series, today we’ll be examining the System.Data.SqlClient.SqlException. The System.Data.SqlClient.SqlException is typically thrown when an accessed SQL Server returns a warning or error of its own.

In this article, we’ll explore where System.Data.SqlClient.SqlException resides within the .NET exception hierarchy, examine when System.Data.SqlClient.SqlExceptions most commonly appear, and see how to handle them should you encounter one yourself. So, let’s get to it!

The Technical Rundown

• All .NET exceptions are derived classes of the System.Exception base class, or derived from another inherited class therein.
• System.SystemException is inherited from the System.Exception class.
• System.Runtime.InteropServices.ExternalException is inherited from the System.SystemException class.
• System.Data.Common.DbException is inherited from the System.Runtime.InteropServices.ExternalExceptionclass.
• Finally, System.Data.SqlClient.SqlException is inherited from the System.Data.Common.DbException class.

When Should You Use It?

Since the occurrence of a System.Data.SqlClient.SqlException is directly related to a problem with the SQL server, it’s important to take a moment to understand how to connect a C# application to an SQL server, and therefore what scenarios System.Data.SqlClient.SqlExceptions might occur.

As with most anything in .NET, there are many ways to tackle the problem of database connection and usage, so we’ll just provide an example for this article, and you can apply it to your own experiences or setups. For the following code, we’re using a Microsoft Azure cloud-based SQL Server and SQL Database, so there’s no need to setup an SQL server on our local machine. Setting up an SQL Server/Database on Azure is beyond the scope of this article, but once it’s configured, we can connect to it using a standard ADO Connection String, which is just a group of key/value pairs that informs an application where our database server is located and how to connect to it. An ADOstring typically looks something like: Server=[server],1433;Initial Catalog=[catalog];...

To make use of our database, and to keep our example code a bit cleaner, we’ve opted to store our ADO string and our SQL credentials within the App.config file for our C# project. Below you can see the full App.config we’re using, with a bit of obfuscation where necessary to retain privacy. It includes the applicationSettings element, where we’ve stored our app settings related to our SQL server:

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

    <configSections>

        <sectionGroup name="applicationSettings" type="System.Configuration.ApplicationSettingsGroup, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >

            <section name="Airbrake.Data.SqlClient.SqlException.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />

        </sectionGroup>

    </configSections>

    <startup>

        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />

    </startup>

    <applicationSettings>

        <Airbrake.Data.SqlClient.SqlException.Properties.Settings>

            <setting name="SqlUserID" serializeAs="String">

                <value>[username]</value>

            </setting>

            <setting name="SqlPassword" serializeAs="String">

                <value>[password]</value>

            </setting>

            <setting name="SqlConnectionString" serializeAs="String">

                <value>Server=[server],1433;Initial Catalog=[catalog];Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;</value>

            </setting>

        </Airbrake.Data.SqlClient.SqlException.Properties.Settings>

    </applicationSettings>

</configuration>

With that in place, we’ll take a look at the example application code, which aims to perform two SQL queries (one successfully, and one unsuccessfully). The full code is below, after which we can break it down a bit more to see what’s going on:

using System.Data;

using System.Data.SqlClient;

using Utility;
namespace Airbrake.Data.SqlClient.SqlException

{

    class Program

    {

        static void Main(string[] args)

        {

            // Valid query.

            PerformQuery(@"SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName

                FROM [SalesLT].[ProductCategory] pc

                JOIN [SalesLT].[Product] p

                ON pc.productcategoryid = p.productcategoryid");



  


                       
  




            // Invalid query.

            PerformQuery(@"EXECUTE InvalidStoredProcedure");

        }
        private static void PerformQuery(string query)

        {

            try

            {

                // Generate connection string from application settings data.

                SqlConnectionStringBuilder stringBuilder = new SqlConnectionStringBuilder(Properties.Settings.Default.SqlConnectionString)

                {

                    UserID = Properties.Settings.Default.SqlUserID,

                    Password = Properties.Settings.Default.SqlPassword

                };
                // Use connection string to generate new SqlConnection.

                using (SqlConnection connection = new SqlConnection(stringBuilder.ConnectionString))

                {

                    // Open the connection.

                    connection.Open();
                    // Generate a blank command using connection, then assign CommandText.

                    SqlCommand command = new SqlCommand("", connection)

                    {

                        CommandType = CommandType.Text,

                        CommandText = query

                    };
                    // Execute command as SqlDataReader.

                    SqlDataReader reader = command.ExecuteReader();
                    // Loop through reader to evaluate returned data.

                    while (reader.Read())

                    {

                        // Output data.

                        Logging.Log($"{reader.GetString(0)}t{reader.GetString(1)}");

                    }
                    // Close reader.

                    reader.Close();
                    // Close connection.

                    connection.Close();

                }

            }

            catch (System.Data.SqlClient.SqlException exception)

            {

                // Output exception information to log.

                Logging.Log(exception);

            }

        }

    }

}
using System;

using System.Diagnostics;
namespace Utility

{

    /// <summary>

    /// Houses all logging methods for various debug outputs.

    /// </summary>

    public static class Logging

    {

        /// <summary>

        /// Outputs to <see cref="System.Diagnostics.Debug.WriteLine"/> if DEBUG mode is enabled,

        /// otherwise uses standard <see cref="Console.WriteLine"/>.

        /// </summary>

        /// <param name="value">Value to be output to log.</param>

        public static void Log(object value)

        {

            #if DEBUG

                Debug.WriteLine(value);

            #else

                Console.WriteLine(value);

            #endif

        }



  


                       
  




        /// <summary>

        /// When <see cref="Exception"/> parameter is passed, modifies the output to indicate

        /// if <see cref="Exception"/> was expected, based on passed in `expected` parameter.

        /// <para>Outputs the full <see cref="Exception"/> type and message.</para>

        /// </summary>

        /// <param name="exception">The <see cref="Exception"/> to output.</param>

        /// <param name="expected">Boolean indicating if <see cref="Exception"/> was expected.</param>

        public static void Log(Exception exception, bool expected = true)

        {

            string value = $"[{(expected ? "EXPECTED" : "UNEXPECTED")}] {exception.ToString()}: {exception.Message}";

            #if DEBUG

                Debug.WriteLine(value);

            #else

                Console.WriteLine(value);

            #endif

        }

    }

}

The Utility namespace is where our Logging class resides, which is just used as a convenience for outputting information during debugging, so we won’t go into anymore detail on that section. The meat and potatoes of our code here is in the Program.PerformQuery method:
private static void PerformQuery(string query)

{

    try

    {

        // Generate connection string from application settings data.

        SqlConnectionStringBuilder stringBuilder = new SqlConnectionStringBuilder(Properties.Settings.Default.SqlConnectionString)

        {

            UserID = Properties.Settings.Default.SqlUserID,

            Password = Properties.Settings.Default.SqlPassword

        };
        // Use connection string to generate new SqlConnection.

        using (SqlConnection connection = new SqlConnection(stringBuilder.ConnectionString))

        {

            // Open the connection.

            connection.Open();
            // Generate a blank command using connection, then assign CommandText.

            SqlCommand command = new SqlCommand("", connection)

            {

                CommandType = CommandType.Text,

                CommandText = query

            };
            // Execute command as SqlDataReader.

            SqlDataReader reader = command.ExecuteReader();
            // Loop through reader to evaluate returned data.

            while (reader.Read())

            {

                // Output data.

                Logging.Log($"{reader.GetString(0)}t{reader.GetString(1)}");

            }
            // Close reader.

            reader.Close();
            // Close connection.

            connection.Close();

        }

    }

    catch (System.Data.SqlClient.SqlException exception)

    {

        // Output exception information to log.

        Logging.Log(exception);

    }

}

The comments provide a bit of guidance, but effectively this is just one way we can create a new connection to our SQL server (using the SqlConnectionStringBuilder class), open it, pass our query parameter to the CommandText, then issue an ExecuteReader method call to make that request to the server. Once a result is returned, we loop through it, outputting the information to our log, before closing everything out. We also are checking to see if any System.Data.SqlClient.SqlExceptions occur, and catching those if necessary.

Lastly, we have two actual query strings we’re trying:

// Valid query.

PerformQuery(@"SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName

    FROM [SalesLT].[ProductCategory] pc

    JOIN [SalesLT].[Product] p

    ON pc.productcategoryid = p.productcategoryid");
// Invalid query.

PerformQuery(@"EXECUTE InvalidStoredProcedure");

The first is a basic query using the well-known AdventureWorks sample database, grabbing the first 20 Productsand their associated ProductCategory. This query works fine and the output is as expected:

Road Frames	HL Road Frame - Black, 58

Road Frames	HL Road Frame - Red, 58

Helmets	Sport-100 Helmet, Red

Helmets	Sport-100 Helmet, Black

Socks	Mountain Bike Socks, M

Socks	Mountain Bike Socks, L

Helmets	Sport-100 Helmet, Blue

Caps	AWC Logo Cap

Jerseys	Long-Sleeve Logo Jersey, S

Jerseys	Long-Sleeve Logo Jersey, M

Jerseys	Long-Sleeve Logo Jersey, L

Jerseys	Long-Sleeve Logo Jersey, XL

Road Frames	HL Road Frame - Red, 62

Road Frames	HL Road Frame - Red, 44

Road Frames	HL Road Frame - Red, 48

Road Frames	HL Road Frame - Red, 52

Road Frames	HL Road Frame - Red, 56

Road Frames	LL Road Frame - Black, 58

Road Frames	LL Road Frame - Black, 60

Road Frames	LL Road Frame - Black, 62

However, our second query explicitly makes a call to a stored procedure that doesn’t exist in our database, so we’re expecting a System.Data.SqlClient.SqlException to be raised. Sure enough, the output shows us exactly the problem:

Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll

[EXPECTED] System.Data.SqlClient.SqlException (0x80131904): Could not find stored procedure 'InvalidStoredProcedure'.

While this is just a brief glimpse and example, hopefully it illustrates just how a System.Data.SqlClient.SqlExceptionmight show up, and give some insight for your own future projects working with SQL servers.

To get the most out of your own applications and to fully manage any and all .NET Exceptions, check out the Airbrake .NET Bug Handler, offering real-time alerts and instantaneous insight into what went wrong with your .NET code, along with built-in support for a variety of popular development integrations including: JIRA, GitHub, Bitbucket, and much more.