Retrieving data from a SQL Server Database
As discussed in Retrieving Data from an Access Database, the DataReader object is one of the easiest ways to programmatically read data from a databace. The same is true for returning rows of data from a SQL Server database. A brief overview of what is required to retrieve data from a SQL Server database is as follows:
- Create a connection to the database
- Create a SQL query, with or without parameters, to access the required data
- Assign the SQL Query to a SQLCommand object
- Execute the SQLCommand to retrieve the records
Steps 1 to 3 have been covered in the posts detailing how to connect to a SQL Server Database and how to add parameters to a SQL Server query. The code snippet below assumes that you have read and understand those posts, so if you don't it's time to go back and familiarise yourself again.
'declare a DataReader
Dim dr As SqlDataReader 'For a SQL Server database
'populate the datareader with the results of the command
dr = cmd.ExecuteReader()
'create a loop to read values from the datareader
While dr.Read()
'gain access to the fields using something like below
Dim strValue As String = dr(fieldname).ToString()
End While
'close the read when done
dr.Close()
Adding Parameters to SQL Server Queries
Option 1:
Create a new Parameter object for each parameter required in the SQL query or Stored Procedure.
//Create a new command object
SqlCommand cmd = new SqlCommand();
//Create a new parameter
SqlParameter param = new SqlParameter("@parameterName", paramValue);
//add the parameter to the command
cmd.Parameters.Add(param);
Option 2:
Use the AddWithValue, to create Parameter and assign a value to a Command object.
//Create a new command object
SqlCommand cmd = new SqlCommand();
//add the parameter to the command
cmd.Parameters.AddWithValue("@parameterName", paramValue);
Connecting to the SQL Server database
Performing operations on data stored in a database is one of the most common tasks performed by developers. In fact, most modern web and desktop applications rely on data stored in databases at least to some degree. In the .NET world, SQL Server and especially SQL Server Express is almost the defacto standard database management system and even the free Express versions of the popular Visual Studio development tools support SQL Server database straight out of the box.
Of course you can use other database types, such as Access and Oracle, by simply using the libraries that are available for each specific database. Microsoft provide the System.Data.OleDB, which can be used for Access databases and third party libraries, such as the Oracle Data Provider for .NET (ODP.NET) are also available for a number of .NET framework versions.
Class Declarations
using System.Data.SqlClient;
Function Code
//Create a new connection object
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=local;Initial Catalog=TestDB;Integrated Security=True";
//Create a new command object
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
//to define dynamically created SQL
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM SAMPLE";
//to define a Stored Procedure
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_SAMPLE"; //stored procedure name
//when using a data reader you must explicitly open and close the connection
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
conn.Close();
NOTE: Most experts recommend avoiding dynamically created SQL statements, as these can cause performance issues and represents a potential security threat. Use stored procedures unless there is no other choice (and yes there is always a choice).