Improve SQL Server Connection Performance In ADO.NET

This little tip will boost the speed of SQL Server connection using ADO.NET. I was using ADO.NET to connect to an SQL Server database on a local network. I noticed that the connection speed seems off. It was slow. I did some research and Googled it and found one property in ADO.NET connection string that can help improve the performance.

Recently, I was using ADO.NET to connect to an SQL Server database on a local network. I noticed that the connection speed seemed off. It was slow. I did some research and googled it and found one property in ADO.NET connection string that can help improve the performance of our database connection, i.e., the "Network Library=DBNMPNTW;' 

 

This loads a DLL protocol that enhances the speed to the connection on a local network. In my case, we had a 100MB internet connection and the performance improved close to 50 percent.

 

Actual C# code

public static SqlConnection GetConnection(in string cStrConn)
       {
           try
           {
               var oSql = new SqlConnection(cStrConn);
               oSql.Open();  // HIGH DELAY
               return oSql;
           }
           catch
           {
               return null;
           }
       }

Sample connection

Network Library=DBNMPNTW;Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;
Password=myPassword;

Fixed open

public static SqlConnection GetConnection(in string cStrConn)
        {
            try
            {
                var oSql = new SqlConnection($"Network Library=DBNMPNTW;{cStrConn}");
                oSql.Open();
                return oSql;
            }
            catch
            {
                return null;
            }
        }

A sample of how to use it:

try
      {

          using (var oCnn = ConfiguracoesDBT.GetConnection("Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername; Password=myPassword;  "))
          {
              if (oCnn is null) yield break;
              ds = ConfiguracoesDBT.GetDataTable(cSql.ToString(), oCnn);
          }
      }
      catch { yield break; }

The network library is the real magic. It handles the communication with SQL Server instance and makes it faster.

 

See more information here.

 

Info about the library can be seen here - https://www.win7dll.info/dbnmpntw_dll.html 

 

Conclusion

 

This DLL protocol makes the database connection faster (in my case, 50 percent faster).

 

CAUTION

 

Use it only on a local network. This is not recommended for Web apps or remote databases.