C# SqlConnection Example: Using, SqlCommand

Use SqlConnection from System.Data.SqlClient to connect to a database.

SqlConnection. The SqlConnection class handles database connections. It initiates a connection to your SQL database. This class is best used in a using resource acquisition statement.

More details. We call Open to query the database with SqlCommand. With System.Data.SqlClient classes, we often use many together (like SqlParameter, SqlDataReader).SqlCommand

First example. We use SqlConnection in a "using" statement. The SqlConnection has a constructor that requires a string reference pointing to the connection string character data.

Tip: This connection string is often generated for you by the dialogs in Visual Studio, and is sometimes provided by a host.

And: You must include the SqlConnection code before you can perform a database query.

C# program that uses SqlConnection using System; using System.Data.SqlClient; class Program { static void Main() { // // First access the connection string. // ... This may be autogenerated in Visual Studio. // string connectionString = ConsoleApplication1.Properties.Settings.Default.ConnectionString; // // In a using statement, acquire the SqlConnection as a resource. // using (SqlConnection con = new SqlConnection(connectionString)) { // // Open the SqlConnection. // con.Open(); // // This code uses an SqlCommand based on the SqlConnection. // using (SqlCommand command = new SqlCommand("SELECT TOP 2 * FROM Dogs1", con)) using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Console.WriteLine("{0} {1} {2}", reader.GetInt32(0), reader.GetString(1), reader.GetString(2)); } } } } } Output 57 Koko Shar Pei 130 Fido Bullmastiff

Program notes. This program will not work unless you change the connection string reference to point to a correct one in your environment.

Add new data source: To create a connection string to a database, go to the Visual Studio Data menu and select Add New Data Source.

Also: The program assumes the name of an SQL table that will not be present in most databases.

Using statement. The purpose of the using statement is to provide a simpler way to specify when the unmanaged resource is needed by your program, and when it is no longer needed.

Internally: The language can transform the using statement into a try-finally statement that calls the Dispose method.

Using

Open method call. The using statement creates a read-only variable of type SqlConnection. You need to call Open() on the SqlConnection instance before using it in an SqlCommand.

And: The SqlConnection is passed as the parameter to the SqlCommand. In this way we specify that the SqlCommand "uses" the SqlConnection.

Tutorial. For the example to work correctly, it would need to find the specified SQL table. The specified connection string must be correct.SqlClient

Using. In the annotated C# language specification, we are advised to use the "using" statement when creating any object that implements the interface IDisposable.

And: Even if the interface does nothing, it is safest to always call it if it exists.

Note: Many examples of SqlConnection and SqlCommand do not reliably use the using statement.

SqlConnection: Microsoft Docs

A summary. We looked at database code. We stressed the proper usage of the SqlConnection class and the resource acquisition pattern.

Correct use. The SqlConnection is required for correctly using other SQL objects such as SqlCommand and SqlDataReader. These objects work together.SqlDataReader

The Dev Codes

.Net

.NET Array Dictionary List String 2D Async DataTable Dates DateTime Enum File For Foreach Format IEnumerable If IndexOf Lambda LINQ Parse Path Process Property Regex Replace Sort Split Static StringBuilder Substring Switch Tuple

Java

Core Array ArrayList HashMap String 2D Cast Character Console Deque Duplicates File For Format HashSet If IndexOf Lambda Math ParseInt Process Random Regex Replace Sort Split StringBuilder Substring Switch Vector While

TheDeveloperBlog.com

C# SqlConnection Example: Using, SqlCommand

Related Links:

.Net

Java

Related Links