Beginners Reading Notes database articles (a)

FREE Diabetes Recipes eBook! Click here to redeem.

No matter what knowledge base the most important, everything from the basics, otherwise it will be in the air. Here is what I see “. Net programming Inside” database chapter study notes.
. Net framework for the operation of the database is mainly done through ADO.NET, and ADO.NET database access is through the process known as data provider (data provider) software module for the.
. Net framework 1.0 version has two data providers:
1, SQL Server. NET provider is the Microsoft SQL Server database interface, unmanaged providers do not need any help.
2, OLE DB. NET provider is through the OLE DB provider to access the database interface.
OLE DB.NET provider which allows us to call from managed code through the existing OLE DB provider to use them:
1, SQLOLEDB provider is the interface with the SQL Server database
2, MSDAORA provider is the interface with the Oracle database
3, Microsoft.jet, OLEDB.4.0 provider it is through the Microsoft Jet database engine-driven interface to the database
If the application uses the Microsoft SQL 7.0 or higher, use the SQL Server. NET provider. It is faster than the OLE DB provider. Access the database when it is managed code without leaving the area, on the contrary, OLE DB. NET providers to use. NET Framework Platform Invoke mechanism to call unmanaged OLE DB Provider; if the application using the Microsoft SQL 6.5 or a previous version, select OLE DB.NET provider; if the application using a non-SQL Server database, such as Oracle 8i, select the OLE DB.NET provider.

Here’s how SQL Server 7.0, use the SQL Server. NET Provider or OLE DB. NET provides an instance of the program code for database operations
Use the SQL Server. NET providers:
using System.Data.SqlClient
…
SqlConnection conn = new SqlConnection (“server = localhost; database = pubs; uid = sa; pwd =”);
try
{
conn.open ();
SqlCommand cmd = new SqlCommand (“select * from titles”, conn);
SqlDataReader reader = cmd.ExecuteReader ();
while (reader.read ())
{
Console.WriteLine (reader ["title"]);
}
catch (SqlException ex)
{
Console.WriteLine (ex.Message);
}
finally
{
conn.close ();
}
}

Using OLE DB. NET providers:
using System.Data.OleDb;
…
OleDbConnection conn = new OleDbConnection (“provider = sqloledb; server = localhost; database = pubs; uid = sa; pwd =”);
try
{
conn.open ();
OleDbCommand cmd = new OleDbCommand (“select * from titles”, conn);
OleDbDataReader reader = cmd.ExecuteReader ();
while (reader.read ())
{
Console.WriteLine (reader ["title"]);
}
catch (OleDbException ex)
{
Console.WriteLine (ex.Message);
}
finally
{
conn.close ();
}
}

Can see that in addition to the class name and connection string other than the operating between the two providers that no other sql server 7 different shows for different types of ADO.NET database provides a common API, the API details Some will differ, depending on whether we use the managed provider.

1 connection
SqlConnection conn = new SqlConnection (“server = localhost; database = pubs; uid = sa; pwd =”);
This is a SqlConnection constructor with parameters, the parameters can actually be a connection conn property ConnectionString, in which the format of the string to the right
When you open the connection string will be verified, if not by will generate an exception.
server = localhost can be written server = (local) or Data Source = (local); database parameter can be written Initial Catalog, uid can be written as user id, pwd can be written password.
Oledb provider for the connection is similar: OleDbConnection conn = new OleDbConnection (“provider = sqloledb; server = localhost; database = pubs; uid = sa; pwd =”);
Provider parameter identification and database interaction for Ole Db Provider – This is the SQLOLEDB, which is Microsoft’s OLE DB for SQL Server provider. The provision of process change into MSDARA will change the target Oracle database.
Create a SqlConnection or OleDbConnection object and not to provide a connection string to open a physical connection to the database, you must call the object’s Open method to do that. Open connection with the Open is best to use Close to close.
If you can not establish a connection to the database, Open method throws an exception, and open the connection in the database to perform the operation if it fails, will throw an exception, an exception should be caught.
Therefore, the general operation of the database code generally:
SqlConnection conn = new SqlConnection (“server = localhost; database = pubs; uid = sa; pwd =”);
try
{
conn.open ();
/ / TODO: Use Connection
}
catch (SqlException ex) / / we need to care about the properties SqlException
{
/ / TODO: handle exceptions
}
finally
{
conn.close ();
}
}
On the OLE DB. NET provides the equivalent program code is as follows:
OleDbConnection conn = new SqlConnection (“server = localhost; database = pubs; uid = sa; pwd =”);
try
{
conn.open ();
/ / TODO: Use Connection
}
catch (OleDbException ex)
{
/ / TODO: handle exceptions
}
finally
{
conn.close ();
}
}

2. Use the command (SqlCommand class or OleDbCommand)
Using the ExecuteNonQuery method, this method is used to perform INSERT, UPDATE, DELETE, and other SQL command does not return a value (such as CREATE DATABASE and CREATE TABLE command)
This method returns the number of rows affected by the command. To use the ExecuteNonQuery to create a new database named MyDatabase, simply change the command object’s command-oriented “create database MyDatabase”, then use the create table and insert commands to create tables and insert data.
If the ExecuteNonQuery fails, or OleDbException SqlException object will produce an exception. Note: The update command with invalid fields and primary key constraint violation of the rules of insert command will cause an exception, but there is no record to update and delete commands for the goal not enough as the error, but ExecuteNonQuery
Back o.

Using the ExecuteScalar method, which executes a SQL command and returns the result set of the first column of the first row, to the neglect of the other columns and rows. It is most commonly used to execute Sql the count, avg, min, max and the sum so set functions, because these functions return a single row single column result set.
Worthy of our attention is ExecuteScalar method returns a Object type, so we put it in use when the results returned by the need to cast the type. If the conversion is not correct,. Net framework will lead to InvalidCastException exception.
This method also has a more common usage is retrieved from the database BLOB (binary large objects). See the following code:

MemoryStream stream = new MemoryStream ();
SqlConnection conn = new SqlConnection (“server = localhost; database = pubs; uid = sa; pwd =”);
try
{
conn.Open ();
SqlCommand cmd = new SqlCommand (“select logo from pub_info where pub_info = ’0763 ‘”, conn);
byte [] blob = (byte []) cmd.ExecuteScalar ();
stream.Write (blob, 0, blob.Length);
Bitmap bitmap = new Bitmap (stream);
bitmap.Dispose ();
}
catch (SqlException ex)
{
/ / TODO: handle exceptions
}
finally
{
stream.Close ();
conn.Close ();
}

This creates a bitmap.
How to easily write code that blob into the database:
SqlConnection conn = new SqlConnection (“server = localhost; database = pubs; uid = sa; pwd =”);
try
{
conn.Open ();
SqlCommand cmd = new SqlCommand (“insert into pub_info (pub_id, logo) values (’9937 ‘, @ logo)”, conn) / / using the parameters of the command
cmd.Parameters (“@ logo”, blob);
cmd.ExecuteNonQuery ();
}
catch (…)
{
…
}
finally
{
conn.Close ();
}
Blob is a separate variable definition and initialization, the following is read from the file logo.jpg Image:
FileStream stream = new FileStream (“logo.jpg”, FileMode.Open);
Byte [] blob = new byte [stream.Length];
stream.Read (blob, 0, (int) stream.Length);
stream.Close ();

Using the ExecuteReader method, there is only one purpose, as quickly as possible on the database query and get results, and returns a DataReader object. It is a fast database query result enumeration mechanism
Is read-only, forward-only. DataRead generated object is a starting point to the empty set of item data record, and must use the Read () method moves the pointer to display data.
Such as:
SqlConnection conn = new SqlConnection (“server = localhost; database = pubs; uid = sa; pwd =”);
try
{
conn.open ();
SqlCommand cmd = new SqlCommand (“select * from titles”, conn);
SqlDataReader reader = cmd.ExecuteReader ();
while (reader.read ()) / / / read the last data later reader.read () return value to false will exit the loop
{
Console.WriteLine (reader ["title"]); / / / You can also use the index reader [0] said that the first column of this row of data
}
catch (SqlException ex)
{
Console.WriteLine (ex.Message);
}
finally
{
conn.close ();
}
}
But also query the database using DataReader does not need to know in advance prior to its architecture, you can use this object to get the schema information. DataReader object’s properties using the function FieldCount to get the number of columns in the row, then use the method GetName (int i) returns the index i of the column name. You can also use GetSchemaTable () method to get the frame information, the method returns a DataTable object.
int index = DataReader.GetOrdinal (“property name”) to return the property name corresponding to the index.
GetDecimal (index) get the row index is the column index of the property value.
It should be noted that the DataReader object must be connected in the state of the database is to run, so can not appear before the query conn.Close (), and if you use a command to create a DataReader object later, want to use that command
Object unless things, you must create the DataReader object is closed Reader.close (), and then perform other operations.
You can also use DataReader closing the underlying connection, code: reader = cmd.ExecuteReader (CommandBehavior.CloseConnection); at this time must reader.Close () on the finally block to prevent the leaking connection throws an exception.