Retrieving data from a SQL Server Database

by Admin 6. October 2009 21:50

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:

  1. Create a connection to the database
  2. Create a SQL query, with or without parameters, to access the required data
  3. Assign the SQL Query to a SQLCommand object
  4. 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()

 

Tags: ,

SQL Server

Adding Parameters to SQL Server Queries

by CameronM 6. October 2009 21:23

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);

Tags: , , , , , ,

SQL Server | SQL Server

Connecting to the SQL Server database

by CameronM 6. October 2009 19:39

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).

Tags: , ,

SQL Server