12.1 Introduction
The .NET Framework uses ADO.NET to access databases. ADO.NET is a set of
managed classes within the .NET Framework. It operates on a disconnected data
access model. When an application requests data a new database connection is
created and destroyed when the request is completed. A disconnected data access
model is frugal with resources, which is desirable.
One important advantage of ADO.NET is that information is stored and transferred
in XML.
ADO.NET allows access to many different databases. There are two basic types of
connections. SQLClient is used for Microsoft’s SQL Server and OLEDB is used for
all other database formats.
In order to use SQL with SQL Server or SQL Server Express the following namespace
must be specified.
using System.Data.SqlClient;
In order to use OLEDB the following namespace must be specified.
using System.Data.OleDb;
When designing a database, the correct datatypes must be chosen. Note the following.
• Choosing an incorect datatype can degrade performance, because of data conversion.
• Choosing too small a datatype cannot meet the system need and at some point
your system may become useless.
• Choosing too large a datatype can waste space and increase capital expenses.
156
12.1. INTRODUCTION 157
Accessing a database requires that a .NET Data Provider is set up. A Data Provider
consists of
• Connection
• Command
• DataReader
• DataAdapter
A Dataset can be understood as a virtual database table that resides in RAM. A
DataSet can contain a set of tables with all the metadata necessary to represent
the structure of the original database. It can be manipulated and updated independantly
of the database. A DataSet is not always required.
The Connection creates the actual connection to the database. The Connection
object contains all the information needed to open the connection, for example the
userid and password. A Connection String is required to connect to a database.
The easiest way to find the correct connection string for a DBMS is to search the
Web. A good site to start with is http://www.connectionstrings.com.
In order to open and close a database connection the Connection methods Open()
and Close() are used respectively.
The Command executes an instruction against the database. For example, this
could be a SQL query or a stored procedure. There are three options for executing
a Command.
1. ExecuteReader: Used for accessing data. Can return multiple rows. Readonly,
forward-only.
2. ExecuteScalar: Used to retrieve a value from a single field.
3. ExecuteNonQuery: Used for data manipulation, such as Delete, Update and
Insert.
A DataReader is used for fast and efficient forward-reading, read-only database
access. It returns individual rows directly to the application. The data is not cached.
In the case that the data is read-only and rarely updated, then a DataAdapter can
be used. A DataAdapter handles retrieving and updating data. It is a middle-layer
between the database (Data Provider) and the disconnected DataSet. It decouples
the DataSet from the database and allows a single DataSet to represent more than
one database. The data adapter fills a DataSet object when reading the data and
writes in a single batch when writing changes back to the data base.
158 CHAPTER 12. ACCESSING DATABASES
An important control is the SqlDataSource. It allows connections to most relational
databases. The default provider is for Microsoft SQL Server, but providers
for other databases are provided. For example, for Oracle.
An important concept is that of Data-Bound controls. They provide a link
between the data source controls and the user. Any data source control can be
selected and linked to any data-bound controls.
12.2 Examples
The following program shows how to connect to a Microsoft SQL Server 2005 or
Sql Express database and retrieve some basic information about the server. Care
must be taken is creating an appropriate connection string (CON STRING in these
examples). Knowledge of SQL is required.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace csDB
{
class OpenSQLDB
{
public const string CON_STRING = "Data Source=mySqlDB;Initial
Catalog=dbTest;User ID=JoeUser;Password=User1234";
static void Main(string[] args)
{
Console.WriteLine("Connecting to db\n");
SqlConnection connection = new SqlConnection(CON_STRING);
connection.Open();
Console.WriteLine("Server Version: {0}",
connection.ServerVersion);
Console.WriteLine("Database: {0}",
connection.Database);
Console.WriteLine("Data Source: {0}",
connection.DataSource);
Console.WriteLine("Stats Enabled: {0}",
connection.StatisticsEnabled);
connection.Close();
} // end Main
} // end class
} // end namespace
12.2. EXAMPLES 159
Embedding the user ID and password, as in the above code, is not a good idea; a
change in the database means the code will have to be modified and recompiled. In
addition, it is not secure. ASP.NET solves this problem by using the web.config file.
The following is an extract from a web.config file.
value="server=mySqlDB.newdb,net;initial
catalog=dbTest;user id=JoeUser;pwd=User1234;"/>
...
<\configuration>
The following line of code fetches the connection string from web.config.
string CON_STRING=ConfigurationSettings.Appsettings["DBConnect"];
The basic method of retrieving data using a Data Reader is as follows. A standard
SQL query is used and submitted to the database via a SqlCommand object. In the
case of data retrieval the SELECT statement is used.
SqlCommand cmDB = new SqlCommand();
cmDB.Connection = connection ;
cmDB.CommandText = "SELECT * FROM tblCustomer";
SqlDataReader myReader = cmDB.ExecuteReader();
while(myReader.Read())
{
// Do something with the data
// In the following code, the myReader[0]
// accesses the FIRST field of data in the
// the selected record. myReader[1] then
// accesses the SECOND field.
// ---------------------------------------
// string strTmp = myReader[0].ToString() +
// " " + myReader[1].ToString();
}
myReader.Close();
A wildcard search can be achieved as follows. The assumption is that a TextBox
called txtSearch is used to input the search parameters. A Trim() is used to remove
whitespace.
160 CHAPTER 12. ACCESSING DATABASES
string strSearch =
"SELECT * FROM tblCustomer where fldCustName LIKE ’" +
TextBox1.Text.Trim() + "%’"; // SQL uses a % for wildcard
myReader = cmDB.ExecuteReader();
The following code shows how to access particular fields using a Data Reader.
// Access SQL EXPRESS db and retrieve data and put field
// values into variables
string CON_STRING =
"Data Source=ejd\\sqlexpress;" +
"Initial Catalog=dbCustomer;Integrated Security=True";
string strSQL =
"SELECT * FROM tblCustomer WHERE" +
" fldCustSurname = ’Hardy’ AND fldCustName = ’Frank’";
int id; string strName, strSurname; float fltCred, fltBal;
SqlConnection connection = new SqlConnection(CON_STRING);
connection.Open();
SqlCommand cmDB = new SqlCommand();
cmDB.Connection = connection;
cmDB.CommandText = strSQL;
SqlDataReader rdr = cmDB.ExecuteReader();
while (rdr.Read())
{
id = (int) rdr["fldCustomerID"];
strName = (string) rdr["fldCustName"];
// etc etc
TextBox1.Text = id + " " + strName;
}
rdr.Close();
connection.Close();
The following code shows how to insert a row into a database, using a SQL INSERT
command.
String CON_STRING
= @"Data Source=mname\sqlexpress;" +
"Initial Catalog=dbCustomer" +
";Integrated Security=True";
SqlConnection connection = new SqlConnection(CON_STRING);
12.2. EXAMPLES 161
connection.Open();
String MyString
= @"INSERT INTO tblCustomer(fldCustName, fldCustSurname)" +
"VALUES(’Angus’, ’MacBeth’)";
SqlCommand MyCmd = new SqlCommand(MyString, connection);
MyCmd.ExecuteScalar();
connection.Close();
It is likely that the C# programmer will have a need to extract some data from a
TextBox, either on a Windows or Web Form, and insert that value into a table. In
the case of wanting to insert the values from two TextBoxes called txtFirstName
and txtSurname, you would have the following statement in C#.
String MyString
= @"INSERT INTO tblCustomer(fldCustName, fldCustSurname)"
+ "VALUES(’"
+ txtFirstName.Text.Trim()
+ "’, ’"
+ txtSurname.Text.Trim()
+ "’)";
The following code shows how to edit a row in a database, using a SQL UPDATE
command.
string CON_STRING =
@"Data Source=mname\sqlexpress;" +
"Initial Catalog=dbCustomer;Integrated Security=True";
SqlConnection connection = new SqlConnection(CON_STRING);
connection.Open();
String MyString =
"UPDATE tblCustomer SET fldCustCred = ’1000’ " +
"WHERE fldCustName = ’Angus’";
SqlCommand MyCmd = new SqlCommand(MyString, connection);
MyCmd.ExecuteNonQuery();
connection.Close();
The following code shows how to delete data in a database, using a SQL DELETE
command. This program uses a TRY... CATCH block for exception handling.
string CON_STRING =
"Data Source=mname\\sqlexpress;" +
"Initial Catalog=dbCustomer;" +
"Integrated Security=True";
162 CHAPTER 12. ACCESSING DATABASES
SqlConnection connection = new SqlConnection(CON_STRING);
try
{
connection.Open();
String MyString = @"DELETE FROM tblCustomer " +
"WHERE fldCustName = ’Greg’";
SqlCommand MyCmd = new SqlCommand(MyString, connection);
MyCmd.ExecuteScalar();
}
catch (SqlException sqlexp)
{
// SqlException catches SQL specific exceptions
// Use it when accessing SQL databases
}
catch (Exception ex)
{
TextBox1.Text = ex.Message;
}
finally
{
// The following code checks if the database is still open
// If it is, then it is closed
if(connection.State == ConnectionState.Open) connection.Close();
}
No comments:
Post a Comment