none
What is SqlConnectionStringBuilder used for? RRS feed

  • Question

  • using System;
    using System.Data.SqlClient;
    
    namespace ConsoleApp3
    {
        class Program
        {
            static void Main(string[] args)
            {
    
    
                // Create a new SqlConnectionStringBuilder and
                // initialize it with a few name/value pairs.
                SqlConnectionStringBuilder builder =
                        new SqlConnectionStringBuilder(GetConnectionString());
    
                    // The input connection string used the 
                    // Server key, but the new connection string uses
                    // the well-known Data Source key instead.
                    Console.WriteLine(builder.ConnectionString);
    
                    // Pass the SqlConnectionStringBuilder an existing 
                    // connection string, and you can retrieve and
                    // modify any of the elements.
                    builder.ConnectionString = "server=(local);user id=ab;" +
                        "password= a!Pass113;initial catalog=AdventureWorks";
    
                    // Now that the connection string has been parsed,
                    // you can work with individual items.
                    Console.WriteLine(builder.Password);
                    builder.Password = "new@1Password";
                    builder.AsynchronousProcessing = true;
    
                // You can refer to connection keys using strings, 
                // as well. When you use this technique (the default
                // Item property in Visual Basic, or the indexer in C#),
                // you can specify any synonym for the connection string key
                // name.
                builder["Server"] = ".";
                    builder["Connect Timeout"] = 1000;
                    builder["Trusted_Connection"] = true;
                    Console.WriteLine(builder.ConnectionString);
    
                    Console.WriteLine("Press Enter to finish.");
                    Console.ReadLine();
                }
    
                private static string GetConnectionString()
                {
                    // To avoid storing the connection string in your code,
                    // you can retrieve it from a configuration file. 
                    return "Server=(local);Integrated Security=SSPI;" +
                        "Initial Catalog=AdventureWorks";
                }
            
    
    
    
        }
        
    }

    Tuesday, December 10, 2019 3:26 PM

Answers

All replies

  • Just read the remarks on the page, where you took the sample from.
    Tuesday, December 10, 2019 3:32 PM
  • I try to understand how I can use SqlConnectionStringBuilder in my application, I copied code from https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnectionstringbuilder?view=netframework-4.8, but I have error. Please, explain me, what is advantage of use SqlConnectionStringBuilder compared with this:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace _06._12._2019_loginform
    {
        public partial class Form1 : Form
        {
    
    
    
            SqlConnection con = new SqlConnection();
            public Form1()
            {
                SqlConnection con = new SqlConnection(Properties.Settings.Default.connString);
                
    
                InitializeComponent();
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                SqlConnection con = new SqlConnection(Properties.Settings.Default.connString);
                con.Open();
    
                {
                }
            }
    
    
    
    
    
            private void btnOK_Click(object sender, EventArgs e)
            {
                SqlConnection con = new SqlConnection(Properties.Settings.Default.connString);
                
                con.Open();
                string userid = loginTextBox.Text;
                string password = passwordTextBox.Text;
                SqlCommand cmd = new SqlCommand("select userid,password from hawk.dbo.UserLogin where UserName='" + loginTextBox.Text + "'and Password='" + passwordTextBox.Text + "'", con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    
                    Hide();
                    Form SW = new SuperWindow();
                    SW.ShowDialog();
    
                }
                else
                {
                    MessageBox.Show("Invalid Login please check username and password");
                }
                con.Close();
            }
        }
    
    }

    Tuesday, December 10, 2019 3:36 PM
  • With a stock console app there should be zero errors for this code.

    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleApp1
    {
        class Program
        {
            static void Main(string[] args)
            {
                var builder = new SqlConnectionStringBuilder(GetConnectionString())
                {
                    ConnectionString = 
                        "server=(local);user id=ab;" +
                        "password= a!Pass113;initial catalog=AdventureWorks",
                    Password = "new@1Password",
                    AsynchronousProcessing = true,
                    ["Server"] = ".",
                    ["Connect Timeout"] = 1000,
                    ["Trusted_Connection"] = true
                };
                Console.WriteLine(builder.ConnectionString);
    
                Console.WriteLine("Press Enter to finish.");
                Console.ReadLine();
            }
            private static string GetConnectionString()
            {
                // To avoid storing the connection string in your code,
                // you can retrieve it from a configuration file. 
                return "Server=(local);Integrated Security=SSPI;" +
                       "Initial Catalog=AdventureWorks";
            }
        }
    }
    

    There are many reason to use SqlConnectionStringBuilder.

    • Less chance for errors when creating a connection string.
    • Allows editing of say a connection string under project/settings for connection strings at runtime rather than parsing the current connection string. So a common practice might be to change servers or the default catalog.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, December 10, 2019 3:50 PM
    Moderator
  • With a stock console app there should be zero errors for this code.

    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ConsoleApp1
    {
        class Program
        {
            static void Main(string[] args)
            {
                var builder = new SqlConnectionStringBuilder(GetConnectionString())
                {
                    ConnectionString = 
                        "server=(local);user id=ab;" +
                        "password= a!Pass113;initial catalog=AdventureWorks",
                    Password = "new@1Password",
                    AsynchronousProcessing = true,
                    ["Server"] = ".",
                    ["Connect Timeout"] = 1000,
                    ["Trusted_Connection"] = true
                };
                Console.WriteLine(builder.ConnectionString);
    
                Console.WriteLine("Press Enter to finish.");
                Console.ReadLine();
            }
            private static string GetConnectionString()
            {
                // To avoid storing the connection string in your code,
                // you can retrieve it from a configuration file. 
                return "Server=(local);Integrated Security=SSPI;" +
                       "Initial Catalog=AdventureWorks";
            }
        }
    }

    There are many reason to use SqlConnectionStringBuilder.

    • Less chance for errors when creating a connection string.
    • Allows editing of say a connection string under project/settings for connection strings at runtime rather than parsing the current connection string. So a common practice might be to change servers or the default catalog.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    How can I implement SqlConnectionStringBuilder to my code? I've read https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnectionstringbuilder?view=netframework-4.8, but  I better understand on practical instances
    Tuesday, December 10, 2019 3:55 PM

  • How can I implement SqlConnectionStringBuilder to my code? I've read https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnectionstringbuilder?view=netframework-4.8, but  I better understand on practical instances

    I don't have a simple example, only a code sample which uses SqlConnectionStringBuilder to change connections string from app.config back to app.config.

    https://github.com/karenpayneoregon/ModifyConnectionsWinForms/blob/master/DataConnectionLibrary/ConnectionHelper.cs#L187

    Article

    https://social.technet.microsoft.com/wiki/contents/articles/53379.c-working-with-sql-server-connection.aspx


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Decompressor Friday, December 13, 2019 1:50 PM
    Tuesday, December 10, 2019 4:28 PM
    Moderator

  • How can I implement SqlConnectionStringBuilder to my code? I've read https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnectionstringbuilder?view=netframework-4.8, but  I better understand on practical instances

    I don't have a simple example, only a code sample which uses SqlConnectionStringBuilder to change connections string from app.config back to app.config.

    https://github.com/karenpayneoregon/ModifyConnectionsWinForms/blob/master/DataConnectionLibrary/ConnectionHelper.cs#L187

    Article

    https://social.technet.microsoft.com/wiki/contents/articles/53379.c-working-with-sql-server-connection.aspx


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    How  it to fix?

    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data.SqlClient;
    using System.Diagnostics;
    using System.IO;
    using System.Linq;
    using System.Reflection;
    using System.Text;
    using System.Threading.Tasks;
    
    using static System.Configuration.ConfigurationManager;
    
    
    namespace DataConnectionLibrary
    {
        public class ConnectionHelper
        {
    
            /// <summary>
            /// Provides functionality to obtain parent application namespace
            /// </summary>
            private readonly AssemblyHelpers _assemblyHelpers = new AssemblyHelpers();
    
            /// <summary>
            /// Return all connection string in app.config include default localDb or SQLEXPRESS
            /// </summary>
            /// <returns></returns>
            public List<ProjectConnection> Connections => ConnectionStrings.Cast<ConnectionStringSettings>().Select((item) => new ProjectConnection
            {
                Name = item.Name.Replace($"{this.GetType().Namespace}.Properties.Settings.", ""),
                Provider = item.ProviderName,
                ConnectionString = item.ConnectionString
            }).ToList();
    
    
            /// <summary>
            /// Determine if there are local connections
            /// </summary>
            public bool HasConnections => Connections.Count > 1;
            /// <summary>
            /// Get local connection
            /// </summary>
            public ProjectConnection LocalConnection => Connections[0];
            /// <summary>
            /// Get first connection
            /// </summary>
            /// <remarks>
            /// Used when there is one known connection string
            /// </remarks>
            public ProjectConnection SoleConnection => Connections[1];
            /// <summary>
            /// Get properties for a single connection string
            /// </summary>
            /// <param name="pConnectionName"></param>
            /// <returns></returns>
            public ConnectionProperties Properties(string pConnectionName)
            {
                var connectionProperties = new ConnectionProperties();
    
                if (ConnectionStrings.Cast<ConnectionStringSettings>().FirstOrDefault((css) => css.Name == pConnectionName) == null)
                {
                    return connectionProperties;
                }
    
                Configuration configuration = OpenExeConfiguration(_assemblyHelpers.CallingNamespace());
                var section = (ConnectionStringsSection)configuration.GetSection("connectionStrings");
    
                try
                {
                    var currentConnectionString = section.ConnectionStrings[pConnectionName].ConnectionString;
    
                    var builder = new SqlConnectionStringBuilder
                    {
                        ConnectionString = currentConnectionString
                    };
    
                    connectionProperties = new ConnectionProperties
                    {
                        DataSource = builder.DataSource,
                        InitialCatalog = builder.InitialCatalog,
                        Authentication = builder.Authentication,
                        AttachDbFilename = builder.AttachDBFilename,
                        IntegratedSecurity = builder.IntegratedSecurity,
                        IsValid = true
                    };
    
                }
                catch (Exception)
                {
                    connectionProperties.IsValid = false;
                }
    
                return connectionProperties;
    
            }
    
            /// <summary>
            /// Change server in connection string
            /// </summary>
            /// <param name="pConnectionName">Existing connection string in app.config</param>
            /// <param name="pServerName">Replace server name currently in app.config with this server name</param>
            public bool ChangeServer(string pConnectionName, string pServerName)
            {
    
                try
                {
                    // Assert named connection string pConnectionName exists
                    if (ConnectionStrings.Cast<ConnectionStringSettings>().FirstOrDefault((css) => css.Name == pConnectionName) != null)
                    {
    
                        Configuration configuration = OpenExeConfiguration(_assemblyHelpers.CallingNamespace());
                        var section = (ConnectionStringsSection)configuration.GetSection("connectionStrings");
    
                        var currentConnectionString = section.ConnectionStrings[pConnectionName].ConnectionString;
    
                        var builder = new SqlConnectionStringBuilder
                        {
                            ConnectionString = currentConnectionString,
                            DataSource = pServerName
                        };
    
                        section.ConnectionStrings[pConnectionName].ConnectionString = builder.ConnectionString;
                        configuration.Save(ConfigurationSaveMode.Modified);
                        RefreshSection("connectionStrings");
                    }
    
                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
    
    
            }
            /// <summary>
            /// Change server and catalog in connection string
            /// </summary>
            /// <param name="pConnectionName">Existing connection string in app.config</param>
            /// <param name="pServerName">Replace server name currently in app.config with this server name</param>
            /// <param name="pCatalog">Replace current catalog with replacement catalog</param>
            public void ChangeServer(string pConnectionName, string pServerName, string pCatalog)
            {
    
                // Assert named connection string pConnectionName exists
                if (ConnectionStrings.Cast<ConnectionStringSettings>().FirstOrDefault((css) => css.Name == pConnectionName) != null)
                {
    
                    Configuration configuration = OpenExeConfiguration(_assemblyHelpers.CallingNamespace());
                    var section = (ConnectionStringsSection)configuration.GetSection("connectionStrings");
    
                    var currentConnectionString = section.ConnectionStrings[pConnectionName].ConnectionString;
    
                    var builder = new SqlConnectionStringBuilder
                    {
                        ConnectionString = currentConnectionString,
                        InitialCatalog = pCatalog,
                        DataSource = pServerName
                    };
    
                    section.ConnectionStrings[pConnectionName].ConnectionString = builder.ConnectionString;
                    configuration.Save(ConfigurationSaveMode.Modified);
                    RefreshSection("connectionStrings");
    
                }
            }
            /// <summary>
            /// Change initial catalog for specific connection string
            /// </summary>
            /// <param name="pConnectionName">Existing connection string in app.config</param>
            /// <param name="pCatalog">Replace current catalog with replacement catalog</param>
            public void ChangeInitialCatalog(string pConnectionName, string pCatalog)
            {
    
                // Assert named connection string pConnectionName exists
                if (ConnectionStrings.Cast<ConnectionStringSettings>().FirstOrDefault((css) => css.Name == pConnectionName) != null)
                {
    
                    Configuration configuration = OpenExeConfiguration(_assemblyHelpers.CallingNamespace());
                    var section = (ConnectionStringsSection)configuration.GetSection("connectionStrings");
    
                    var currentConnectionString = section.ConnectionStrings[pConnectionName].ConnectionString;
    
                    var builder = new SqlConnectionStringBuilder
                    {
                        ConnectionString = currentConnectionString,
                        InitialCatalog = pCatalog
                    };
    
                    section.ConnectionStrings[pConnectionName].ConnectionString = builder.ConnectionString;
                    configuration.Save(ConfigurationSaveMode.Modified);
                    RefreshSection("connectionStrings");
                }
            }
        }
    }
    

    Wednesday, December 11, 2019 9:14 AM
  • Error 1, add a reference from Microsoft references.

    Remaining errors, they are all in the GitHub repository.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, December 11, 2019 10:42 AM
    Moderator
  • Error 1, add a reference from Microsoft references.

    Remaining errors, they are all in the GitHub repository.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    How to add reference from Microsoft references?
    Wednesday, December 11, 2019 10:55 AM

  • Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, December 11, 2019 11:23 AM
    Moderator

  • Remaining errors, they are all in the GitHub repository.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow

           Where they are exactly? Please, tell in detail
    Wednesday, December 11, 2019 12:26 PM

  • Remaining errors, they are all in the GitHub repository.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow

           Where they are exactly? Please, tell in detail
    https://github.com/karenpayneoregon/ModifyConnectionsWinForms

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, December 11, 2019 1:09 PM
    Moderator

  • Article

    https://social.technet.microsoft.com/wiki/contents/articles/53379.c-working-with-sql-server-connection.aspx


    Why didn't you give me such articles earlier? Thank you very much.

    Friday, December 13, 2019 1:49 PM

  • Article

    https://social.technet.microsoft.com/wiki/contents/articles/53379.c-working-with-sql-server-connection.aspx


    Why didn't you give me such articles earlier? Thank you very much.

    Simply because I have hundreds of code sample and article on the web I can't remember them all.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, December 13, 2019 2:11 PM
    Moderator