ADO.NET - ADO.NET Connection
Lớp ADO.NET SqlConnection
Để tương tác với database, bạn phải có một kết nối tới nó. Kết nối giúp xác định database server, database name, user name, password, và các tham số cần thiết để kết nối tới database. Một đối tượng connection được dùng bởi đối tượng command vì thế chúng sẽ biết database nào để thực thi lệnh.
Connection không ngắt kết nối. Do đó, chúng ta phải ngắt kết nối.
SqlConnection
public sealed class SqlConnection : System.Data.Common.DbConnection, ICloneable, IDisposable
Phương thức thiết lập SqlConnection
| Constructors | Description |
|---|---|
| SqlConnection() | It is used to initializes a new instance of the SqlConnection class. |
| SqlConnection(String)0 | It is used to initialize a new instance of the SqlConnection class and takes connection string as an argument. |
| SqlConnection(String, SqlCredential) | It is used to initialize a new instance of the SqlConnection class that takes two parameters. First is connection string and second is sql credentials. |
Các phương thức SqlConnection
| Method | Description |
|---|---|
| BeginTransaction() | It is used to start a database transaction. |
| ChangeDatabase(String) | It is used to change the current database for an open SqlConnection. |
| ChangePassword(String, String) | It changes the SQL Server password for the user indicated in the connection string. |
| Close() | It is used to close the connection to the database. |
| CreateCommand() | It enlists in the specified transaction as a distributed transaction. |
| GetSchema() | It returns schema information for the data source of this SqlConnection. |
| Open() | It is used to open a database connection. |
| ResetStatistics() | It resets all values if statistics gathering is enabled. |
SqlConnection Example
Trong ví dụ sau chúng ta kết nối với CSDL Student. Code C# sau:
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
}
Using block is used to close the connection automatically. We don't need to call close () method explicitly, using block do this for ours implicitly when the code exits the block.
// Program.cs
using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
class Program
{
static void Main(string[] args)
{
new Program().Connecting();
}
public void Connecting()
{
using (
// Creating Connection
SqlConnection con = new SqlConnection("data source=.; database=student; integrated security=SSPI")
)
{
con.Open();
Console.WriteLine("Connection Established Successfully");
}
}
}
}
Output:
What, if we don't use using block.
If we don't use using block to create connection, we have to close connection explicitly. In the following example, we are using try-block instead of using block.
// Program.cs
using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
class Program
{
static void Main(string[] args)
{
new Program().Connecting();
}
public void Connecting()
{
SqlConnection con = null;
try
{
// Creating Connection
con = new SqlConnection("data source=.; database=student; integrated security=SSPI");
con.Open();
Console.WriteLine("Connection Established Successfully");
}
catch (Exception e)
{
Console.WriteLine("OOPs, something went wrong.\n"+e);
}
finally
{ // Closing the connection
con.Close();
}
}
}
}
Output:
