C# SqlDataReader: GetInt32, GetString

Use SqlDataReader from System.Data.SqlClient to read in data from a database.

SqlDataReader reads database rows one-by-one. It reads in forward order from an SQL database. The SqlDataReader type can be used in a loop to read multiple rows from an SQL database. It provides good performance and strong typing.

Example. Preliminary steps are required before using SqlDataReader on a database table. These steps require configuration on your part. You must target the correct database with a custom connection string and also target the proper table.

Tip: To begin, you can create a new SqlConnection and open it. We do this with the SqlConnection constructor.

Constructor Using

Then: You can create a new SqlCommand and call its ExecuteReader method, assigning the reference it returns to an SqlDataReader.

C# program that uses SqlDataReader with SqlClient using System; using System.Data.SqlClient; class Program { static void Main() { // // You need to access the project's connection string here. // string connectionString = ConsoleApplication1.Properties.Settings.Default.ConnectionString; // // Create new SqlConnection object. // using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // // Create new SqlCommand object. // using (SqlCommand command = new SqlCommand("SELECT * FROM Dogs1", connection)) { // // Invoke ExecuteReader method. // SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { int weight = reader.GetInt32(0); // Weight int string name = reader.GetString(1); // Name string string breed = reader.GetString(2); // Breed string // // Write the values read from the database to the screen. // Console.WriteLine("Weight = {0}, Name = {1}, Breed = {2}", weight, name, breed); } } } } } Output Weight = 57, Name = Koko, Breed = Shar Pei Weight = 130, Name = Fido, Breed = Bullmastiff Weight = 93, Name = Alex, Breed = Anatolian Shepherd Dog Weight = 25, Name = Charles, Breed = Cavalier King Charles Spaniel Weight = 7, Name = Candy, Breed = Yorkshire Terrier

Access connection string. Main() first accesses a project-specific ConnectionString. Your project will have its own ConnectionString, which can be generated through the Add Data Source menu item in Visual Studio.

Tip: You must always create a new SqlConnection before using the SqlDataReader code here.

SqlConnection

Note: SqlConnection objects use an advanced optimization called connection pooling.

So: Creating these new objects will probably not have a highly adverse affect on overall program performance.

Next, you must create a new SqlCommand object with an SQL text query as its first parameter, and the SqlConnection object reference as its second parameter. This program does not use a stored procedure, which may reduce performance.

Note: The text "SELECT * FROM Dogs1" simply selects all rows from a table called Dogs1 in the database.

Finally, the program creates a new SqlDataReader object from the result of the ExecuteReader() method. The while-loop continues iterating through its loop body as long as the Read() method does not return false.

Tip: This makes it possible to query the SqlDataReader for integers, strings and other types with the GetInt32 and GetString methods.

SqlDataAdapter. A more object-oriented approach to database table reading can be achieved by using DataTables. You can directly populate a DataTable with the data from an SQL database table using the SqlDataAdapter class.SqlDataAdapter

Warning: This approach is slower if you are dealing with vast amounts of data, because all of it must be stored in memory at once.

Summary. We saw the SqlDataReader class. It provides an excellent way to query rows one-by-one from your database tables. It does not require the usage of a DataTable, which can improve performance and decrease memory usage in certain cases.SqlClient

Tip: Using SqlDataReader is an easy way to print all rows from a table. It is efficient and worth knowing.

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# SqlDataReader: GetInt32, GetString

Related Links:

.Net

Java

Related Links