Подключение к базе данных

Приветствую всех сегодня мы рассмотрим подключения к базам данных от разных поставщиков.

Для создания подключения к источнику данных в ADO.NET существует специальный объект Connection. В зависимости от выбранного источника данных этот объект может
называться по-разному. Для создания подключения к базам данных MS SQL Server следует использовать объект SqlConnection, который находится в пространстве имен System.Data.SqlClient

Пространство имен System.Data.SqlClient является поставщиком данных .NET FrameWork для источников данных MS SQL Server

Для работы с объектом SqlConnection ему нужно предоставить строку соединения, которая указывает каким образом нужно подключиться к источнику данных. Строка соединения-строка, состоящая из пар имя — значение, содержащая сведения об инициализации, передаваемые в виде параметра от приложения к источнику данных. Синтаксис строки соединения зависит от выбранного источника данных.

Строка подключения:

Формат строки соединения является списком разделенных точкой с запятой пар «параметр -значение». Знак равенства (=) соединяет каждый параметр с его значением.

Основные параметры строки подключения к MS SQL Server БД:

Data Source – указывает имя экземпляра SQL Server, к которому нужно подключиться
Initial Catalog – параметр, указывающий на имя базы данных на сервере, к которой нужно подключиться
Integrated Security – позволяет использовать для подключения к серверу данные учетной записи Windows или имя входа SQL Server.
User Id — позволяет указать имя входа SQL Server для подключения к серверу
Password — пароль имени входа SQL Server

Варианты подключения:

  string conStr = @"Data Source=.\SQLEXPRESS;Initial Catalog=MyDB; Integrated Security=True";               
  string conStr = @"Data Source=(local)\SQLEXPRESS;Initial Catalog=MyDB; Integrated Security=True"; 
  string conStr = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=MyDB; Integrated Security=True";

string conStr = @"Data Source=.\SQLEXPRESS;Initial Catalog=MyDB; Integrated Security=True";

string conStr = @"Data Source=(local)\SQLEXPRESS;Initial Catalog=MyDB; Integrated Security=True";

string conStr = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=MyDB; Integrated Security=True";

Вышеприведенных примерах подключения можно прочитать следующим образом:
Следую инструкциям строки подключения следует найти на локальном компьютере экземпляр SQL Server с именем SQLEXPRESS, поискать каталог MyDB и попытаться получить доступ к источнику данных через доверительное подключение, используя для этого вашу учетную запись Microsoft Windows

После создания строки подключения можно создавать экземпляр SqlConnection.

SqlConnection connection = new SqlConnection(conStr);

1	SqlConnection connection = new SqlConnection(conStr);

Так же для создания объекта SqlConnection можно пользоваться конструктором без параметров. В этом случае строку подключения для объекта SqlConnection можно будет задать с помощью свойства ConnectionString.

Открытие и закрытие соединения

Для открытия соединения с сервером у объекта SqlConnection есть метод Open(), а для закрытия – метод Close();

 connection.Open(); // открытие физического подключения к источнику данных 
 connection.Close(); //закрытие физического соединения с источником данных

1 2	connection.Open(); // открытие физического подключения к источнику данных connection.Close(); //закрытие физического соединения с источником данных

Создавать экземпляр SqlConnection целесообразно в блоке using. При выходе из блока, using на экземпляре созданного SqlConnection будет вызван метод Dispose, который автоматически закроет соединение с источником данных.

Важно! При повторном открытии незакрытого соединения будет сгенерированна исключительная ситуация.

Объект SqlConnectionStringBuilder

Объект SqlConnectionStringBuilder Обеспечивает удобный способ создания и управления содержимым строк подключения. Построитель строк подключения предоставляет строго типизированные свойства, соответствующие известным парам ключ/значение, которые разрешены SQL Server, кроме этого SqlConnectionStringBuilder выполняет проверки на наличие недопустимых пар ключ/значение.

Безопасность строк подключения

Не используйте конкатенацию строк для при создании приложений, в которых пользователю предоставляется возможность изменять параметры строки подключения.
Для этих целей следует использовать объект SqlConnectionStringBuilder, в противном случае пользователь сможет изменить структуру строки подключение, что приведет к неправильной работе приложения или же нарушения безопасности приложения.

Пример

 SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
 
 connectionStringBuilder.DataSource = @".\SQLEXPRESS";       // используйте конструктор строк подключения для 
 connectionStringBuilder.InitialCatalog = "MyDB";          // предотвращения изменения пользователем структуры строки подключения
 connectionStringBuilder.UserID = userNameTextBox.Text;      
 connectionStringBuilder.Password = passwordTextBox.Text;  

using (SqlConnection connection = new SqlConnection(connectionStringBuilder.ConnectionString))
            {
                try
                {
                    connection.Open();
                    MessageBox.Show("Connection opened to " + connection.Database);

                }
                catch (Exception exception)
                {
                    MessageBox.Show(exception.Message);
                }

            }

SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();

connectionStringBuilder.DataSource = @".\SQLEXPRESS"; // используйте конструктор строк подключения для

connectionStringBuilder.InitialCatalog = "MyDB"; // предотвращения изменения пользователем структуры строки подключения

connectionStringBuilder.UserID = userNameTextBox.Text;

connectionStringBuilder.Password = passwordTextBox.Text;

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

{

try

{

connection.Open();

MessageBox.Show("Connection opened to " + connection.Database);

}

catch (Exception exception)

{

MessageBox.Show(exception.Message);

}

Пул соединений

Соединение с сервером базы данных обычно состоит из нескольких длительных шагов. На практике большинство приложений использует только одно или несколько различных конфигураций соединений. Это означает, что во время выполнения приложения многие идентичные соединения будут повторно открываться и закрываться. Чтобы сократить стоимость открытия соединения в ADO.NET, используется технология оптимизации, называемая connection pooling.

Пул соединений снижает количество открытий новых соединений. Пул управляет соединениями с помощью поддержания набора активных соединений для каждой конфигурации данного соединения. Каждый раз, когда пользователь вызывает метод Open в соединении, организатор пулов ищет в пуле доступное соединение. Если соединение пула доступно, вместо открытия нового соединения он возвращает его участнику.

Важно! В пул могут помещаться только соединения с одинаковой конфигурацией. ADO.NET поддерживает несколько пулов одновременно, по одному для каждой конфигурации.

static void Main(string[] args)
        {
            string conStr = @"Data Source=.\SQLEXPRESS;Initial Catalog=ShopDB; Integrated Security=true; Pooling = true"; // включение или отключение пула для этого подключения

            DateTime start = DateTime.Now; 

            for (int i = 0; i < 1000; i++)
            {
                SqlConnection connection = new SqlConnection(conStr);
                connection.Open();    // при включенном пуле физическое соединение не создается, а берется из пула соединений
                connection.Close();   // при включенном пуле физическое соединение не разрывается, а помещается в пул
            }

            TimeSpan stop = DateTime.Now - start;

            Console.WriteLine(stop.TotalSeconds);
        }

static void Main(string[] args)

{

string conStr = @"Data Source=.\SQLEXPRESS;Initial Catalog=ShopDB; Integrated Security=true; Pooling = true"; // включение или отключение пула для этого подключения

DateTime start = DateTime.Now;

for (int i = 0; i < 1000; i++)

{

SqlConnection connection = new SqlConnection(conStr);

connection.Open(); // при включенном пуле физическое соединение не создается, а берется из пула соединений

connection.Close(); // при включенном пуле физическое соединение не разрывается, а помещается в пул

}

TimeSpan stop = DateTime.Now - start;

Console.WriteLine(stop.TotalSeconds);

}

Использование файлов конфигурации

Использование файлов конфигурации для хранения строк подключения позволяет изолировать информацию о подключении от остальной части приложения.

  static void Main(string[] args)
        {
            // Объект ConnectionStringSettings представляет собой отдельную строку подключения в разделе строк подключения 
            // конфигурационного файла
            var setting = new ConnectionStringSettings
            {
                Name = "MyConnectionString1",     //имя строки подключения в конфигурационном файле
                ConnectionString = @"Data Source=.\SQLEXPRESS;
                                     Initial Catalog=MyDB;
                                     Integrated Security=True;"
            };

            Configuration config;  // Объект Config представляет конфигурационный файл
            config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);  // Объект ConfigurationManager предоставляет доступ к файлам конфигурации
            config.ConnectionStrings.ConnectionStrings.Add(setting);
            config.Save();

            Console.WriteLine("Строка подключения записана в конфигурационный файл.");

            // Получение строки подключения.
            Console.WriteLine(ConfigurationManager.ConnectionStrings["MyConnectionString1"].ConnectionString);
        }

static void Main(string[] args)

{

// Объект ConnectionStringSettings представляет собой отдельную строку подключения в разделе строк подключения

// конфигурационного файла

var setting = new ConnectionStringSettings

{

Name = "MyConnectionString1", //имя строки подключения в конфигурационном файле

ConnectionString = @"Data Source=.\SQLEXPRESS;

Initial Catalog=MyDB;

Integrated Security=True;"

};

Configuration config; // Объект Config представляет конфигурационный файл

config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None); // Объект ConfigurationManager предоставляет доступ к файлам конфигурации

config.ConnectionStrings.ConnectionStrings.Add(setting);

config.Save();

Console.WriteLine("Строка подключения записана в конфигурационный файл.");

// Получение строки подключения.

Console.WriteLine(ConfigurationManager.ConnectionStrings["MyConnectionString1"].ConnectionString);

}

Пример шифрования конфигурационного файла:

 static void Main(string[] args)
        {
            Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            config.ConnectionStrings.ConnectionStrings.Add(new ConnectionStringSettings("COnnectionStr1", "SomeConnectionString"));
            config.Save();

            ConnectionStringsSection section = config.GetSection("connectionStrings") as ConnectionStringsSection;

            if (section.SectionInformation.IsProtected)
            {
                // Расшифровать секцию
                section.SectionInformation.UnprotectSection();
            }
            else
            {
                // Зашифровать секцию.
                section.SectionInformation.ProtectSection(
                    "DataProtectionConfigurationProvider");
            }

            // Сохранить файл конфигурации.
            config.Save();

            // Проверка шифрования
            Console.WriteLine("Protected={0}", section.SectionInformation.IsProtected);

            Console.WriteLine(ConfigurationManager.ConnectionStrings["COnnectionStr1"].ConnectionString);
        }

static void Main(string[] args)

{

Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);

config.ConnectionStrings.ConnectionStrings.Add(new ConnectionStringSettings("COnnectionStr1", "SomeConnectionString"));

config.Save();

ConnectionStringsSection section = config.GetSection("connectionStrings") as ConnectionStringsSection;

if (section.SectionInformation.IsProtected)

{

// Расшифровать секцию

section.SectionInformation.UnprotectSection();

}

else

{

// Зашифровать секцию.

section.SectionInformation.ProtectSection(

"DataProtectionConfigurationProvider");

}

// Сохранить файл конфигурации.

config.Save();

// Проверка шифрования

Console.WriteLine("Protected={0}", section.SectionInformation.IsProtected);

Console.WriteLine(ConfigurationManager.ConnectionStrings["COnnectionStr1"].ConnectionString);

}

Пример подключения используя событие и блок using, который позволяет не заботиться о физическом соединении с источником данных.

  static void connection_StateChange(object sender, System.Data.StateChangeEventArgs e)
        {
            SqlConnection connection = sender as SqlConnection;

            Console.WriteLine();

            Console.WriteLine                 //вывод информации о соединении и его состоянии
                (
                "Connection to" + Environment.NewLine +
                "Data Source: " + connection.DataSource + Environment.NewLine +
                "Database: " + connection.Database + Environment.NewLine +
                "State: " + connection.State
                );
        }

        static void Main(string[] args)
        {
            string conStr = @"Data Source=.\SQLEXPRESS;DataBase=MyDB; Integrated Security=True"; 
            // для указания базы данных, к котрой нужно подключиться кроме параметра Initial Catalog можно пользоваться параметром DataBase, 

            using (SqlConnection connection = new SqlConnection(conStr)) 
            // при использовании объекта SqlConnection  в блоке using можно не заботиться о закрытии физического соединения с источником данных,
            // даже если в блоке using генерируется исключительная ситуация
            {
                connection.StateChange += connection_StateChange; // событие, вызываемое при изменении состояния соединения

                try
                {
                    connection.Open();
                    //throw new Exception("error"); // раскомментировать
                }
                catch (Exception e)
                {
                    Console.WriteLine();
                    Console.ForegroundColor = ConsoleColor.Red;
                    Console.WriteLine(e.Message);
                    Console.ForegroundColor = ConsoleColor.Gray;
                }
            } // connection.Dispose();
        }

static void connection_StateChange(object sender, System.Data.StateChangeEventArgs e)

{

SqlConnection connection = sender as SqlConnection;

Console.WriteLine();

Console.WriteLine //вывод информации о соединении и его состоянии

(

"Connection to" + Environment.NewLine +

"Data Source: " + connection.DataSource + Environment.NewLine +

"Database: " + connection.Database + Environment.NewLine +

"State: " + connection.State

);

}

static void Main(string[] args)

{

string conStr = @"Data Source=.\SQLEXPRESS;DataBase=MyDB; Integrated Security=True";

// для указания базы данных, к котрой нужно подключиться кроме параметра Initial Catalog можно пользоваться параметром DataBase,

using (SqlConnection connection = new SqlConnection(conStr))

// при использовании объекта SqlConnection в блоке using можно не заботиться о закрытии физического соединения с источником данных,

// даже если в блоке using генерируется исключительная ситуация

{

connection.StateChange += connection_StateChange; // событие, вызываемое при изменении состояния соединения

try

{

connection.Open();

//throw new Exception("error"); // раскомментировать

}

catch (Exception e)

{

Console.WriteLine();

Console.ForegroundColor = ConsoleColor.Red;

Console.WriteLine(e.Message);

Console.ForegroundColor = ConsoleColor.Gray;

}

} // connection.Dispose();

}

Пример подключения к базе данных Microsoft Access .mdb

       // Подключение к Microsoft Access 
        private static void ConnectToAccessDB()
        {
            var conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source= D:\Access.mdb");

            try
            {
                conn.Open();
                Console.WriteLine("Connection to .mdb(AccessDB) file opened successfully");
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e.Message);
            }
            finally
            {
                conn.Close();
                Console.WriteLine("Connection closed");
            }
        }

// Подключение к Microsoft Access

private static void ConnectToAccessDB()

{

var conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source= D:\Access.mdb");

try

{

conn.Open();

Console.WriteLine("Connection to .mdb(AccessDB) file opened successfully");

}

catch (Exception e)

{

Console.WriteLine("Error: " + e.Message);

}

finally

{

conn.Close();

Console.WriteLine("Connection closed");

}

Пример подключения к базе данных Microsoft Access .mdb с паролем

       // Подключение к Microsoft Access 
        private static void ConnectToAccessDB()
        {
             var conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Users\Andrey\Documents\Access.mdb; Jet OLEDB:Database Password=123456");

            try
            {
                conn.Open();
                Console.WriteLine("Connection to .mdb(AccessDB) file opened successfully");
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e.Message);
            }
            finally
            {
                conn.Close();
                Console.WriteLine("Connection closed");
            }
        }

// Подключение к Microsoft Access

private static void ConnectToAccessDB()

{

var conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Users\Andrey\Documents\Access.mdb; Jet OLEDB:Database Password=123456");

try

{

conn.Open();

Console.WriteLine("Connection to .mdb(AccessDB) file opened successfully");

}

catch (Exception e)

{

Console.WriteLine("Error: " + e.Message);

}

finally

{

conn.Close();

Console.WriteLine("Connection closed");

}

Пример подключения к базе данных Microsoft Access .accdb

// Подключение к Microsoft Access 
        private static void ConnectToAccessDB2()
        {
               var conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\SQL\nookery.accdb");
               conn.Open();
            
            try
            {
                conn.Open();
                MessageBox.Show("Connection to .mdb(AccessDB) file opened successfully");
            }
            catch (Exception e)
            {
                MessageBox.Show("Error: " + e.Message);
            }
            finally
            {
                conn.Close();
                MessageBox.Show("Connection closed");
            }

            
        }

// Подключение к Microsoft Access

private static void ConnectToAccessDB2()

{

var conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\SQL\nookery.accdb");

conn.Open();

try

{

conn.Open();

MessageBox.Show("Connection to .mdb(AccessDB) file opened successfully");

}

catch (Exception e)

{

MessageBox.Show("Error: " + e.Message);

}

finally

{

conn.Close();

MessageBox.Show("Connection closed");

}

Пример подключения к базе данных Microsoft Excel .xls

 // Подключение Microsoft Excel
        private static void ConnectToExcelDB()
        {
            var conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\ADO.NET\DATA\Excel.xls; Extended Properties=""Excel 8.0""");

            try
            {
                conn.Open();
                Console.WriteLine("Connection to .xls(ExcelDB) file opened successfully");
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e.Message);
            }
            finally
            {
                conn.Close();
                Console.WriteLine("Connection closed");
            }
        }

// Подключение Microsoft Excel

private static void ConnectToExcelDB()

{

var conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\ADO.NET\DATA\Excel.xls; Extended Properties=""Excel 8.0""");

try

{

conn.Open();

Console.WriteLine("Connection to .xls(ExcelDB) file opened successfully");

}

catch (Exception e)

{

Console.WriteLine("Error: " + e.Message);

}

finally

{

conn.Close();

Console.WriteLine("Connection closed");

}

Строка подключения:

Открытие и закрытие соединения

Объект SqlConnectionStringBuilder

Безопасность строк подключения

Пул соединений

Использование файлов конфигурации

Добавить комментарий Отменить ответ