Reading Notes for beginners (Part 1)

no matter what knowledge you learn, the foundation is the most important. Everything starts from the foundation. Otherwise, it will only be a castle in the air. The following are my reading notes on the database of. Net Program Design Technology insider.
. net Framework mainly relies on ADO for database operations. net, while ADO. NET database access is implemented through the software module called data provider.
.. NET Framework Version 1.0 has two data providers:
1, SQL server . net provider. It is an interface for Microsoft SQL Server databases and does not require any help from unmanaged providers.
2, ole db. Net provides an interface for accessing the database through the ole db Provider.
where ole db. net providers allow us to use existing ole db providers by calling them from the hosted Code :
1. sqloledb provides interfaces with SQL Server databases.
2, msdaora provides interfaces with Oracle databases.
3. Microsoft. provided by jet and oledb.4.0, it is an interface with a database driven by the Microsoft Jet Database Engine
If the application uses Microsoft SQL 7.0 or a later version, use SQL Server.. net provider. It is faster than the ole db Provider. It does not leave the field of hosting code when accessing the database. On the contrary, ole db. net provider.. NET Framework's platform invoke mechanism calls the unmanaged ole db Provider. If the application uses Microsoft SQL 6.5 or a previous version, select OLE DB.. net provider. If the application uses a non-SQL Server database, such as Oracle 8i, select OLE DB.. net provider.

The following describes how to operate SQL Server 7.0 using SQL Server. net provider or ole db. net provider database operation instance Code
use SQL Server. net provider :
using system. data. sqlclient
...
sqlconnection conn = new sqlconnection ("Server = localhost; database = pubs; uid = sa; Pwd = '');
try

{< br>
Conn. open ();
sqlcommand cmd = new sqlcommand ("select * from titles", Conn);
sqldatareader reader = cmd. executereader ();
while (reader. read ()

{< br>
console. writeline (Reader ["title"]);

}< br>
catch (sqlexception ex)
{
console. writeline (ex. message);

}< br>
finally

{< br>
Conn. close ();

}

Use the ole db. net provider:

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

}

}

Apart from the class name and connection string, there is no difference between the two providers in Operating SQL Server 7, indicating ADO. NET provides a common API for different types of databases. The details of this API are somewhat different, depending on the managed provider we use.

1. Connection

Sqlconnection conn = new sqlconnection ("Server = localhost; database = pubs; uid = sa; Pwd = '');

This is a constructor with parameters for sqlconnection. The parameter can actually be a connectionstring attribute connecting to conn. The format of this string must be correct.

This string is verified when the connection is opened. If the connection fails, an exception is thrown.

Server = localhost can be written as server = (local) or data source = (local); database parameters can be written as initial catalog, uid can be written as user ID, and PWD can be written as password.

Oledbconnection conn = new oledbconnection ("provider = sqloledb; server = localhost; database = pubs; uid = sa; Pwd = '');

The provider parameter identifies the ole db provider used to interact with the database-sqloledb, Which is Microsoft's ole db provider for SQL Server. Change the supplied process to msdara and change the target to the Oracle database.

You have created a sqlconnection or oledbconnection object and provided a connection string without opening a physical connection to the database. You must call the open method of the object to do this. It is best to close the connection opened with open.

If you cannot establish a connection to the database, the open method will cause an exception. If you fail to open the connection to the database, an exception will also be thrown. You should capture the exception.

Therefore, the database operation code is generally:

Sqlconnection conn = new sqlconnection ("Server = localhost; database = pubs; uid = sa; Pwd = '');

Try

{

Conn. open ();

// Todo: Use the connection

}

Catch (sqlexception ex) // we need to consider the attributes of sqlexception.

{

// Todo: Exception Handling

}

Finally

{

Conn. Close ();

}

}

The equivalent code for the ole db. net provider is as follows:

Oledbconnection conn = new sqlconnection ("Server = localhost; database = pubs; uid = sa; Pwd = '');

Try

{

Conn. open ();

// Todo: Use the connection

}

Catch (oledbexception ex)

{

// Todo: Exception Handling

}

Finally

{

Conn. Close ();

}

}

2. Use commands (Class: sqlcommand or oledbcommand)

Executenonquery is used to execute insert, update, delete, and other SQL commands without return values (such as the CREATE DATABASE AND CREATE TABLE commands)

This method returns the number of lines affected by the command. To use executenonquery to create a new database named mydatabase, you only need to change the command object command to "create database mydatabase", and then use the create table and insert commands to create tables and insert data.

If the executenonquery fails, sqlexception or oledbexception object will also be generated. Note: The update command with invalid fields and the INSERT command that violates the primary key constraint rules may cause an exception, but the update and delete commands with non-existent records as the target are not enough errors, but executenonquery

Returns O.

Using the executescalar method, this method executes an SQL command and returns the first row of the first column of the result set, ignoring other columns and rows. It is most commonly used to execute SQL functions such as Count, AVG, Min, Max, and sum, because these functions all return single row Single Column result sets.

It is worth noting that the executescalar method returns an object type, so we need to forcibly convert the returned results to the required type when using it. If the conversion is incorrect, the. NET Framework will cause an invalidcastexception.

This method also has a common usage of retrieving blob from the database (Binary Large Object). 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 = '000000'", Conn );
byte [] blob = (byte []) cmd. executescalar ();
stream. write (blob, 0, blob. length);
Bitmap bitmap = new Bitmap (Stream);
bitmap. dispose ();

}< br>
catch (sqlexception ex)

{< br>
// todo: exception Handling

}< br>
finally

{< br>
stream. close ();
Conn. close ();

}

In this way, a bitmap is created.

Write the code to write 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 ('000000', @ logo)", Conn) // use parameterized commands

Cmd. parameters ("@ logo", blob );

Cmd. executenonquery ();

}

Catch (...)

{

...

}

Finally

{

Conn. Close ();

}

The variable blobis a standalone and optimized object. The image is read from the logo.jpg file:

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, this method has only one purpose. You can query the database as quickly as possible and obtain the result. A datareader object is returned. It is a mechanism for quickly enumerating database query results,

It is read-only and only inbound. The generated dataread object is an empty item data that begins to point to the record set. You must use the read () method to move the pointer to display the data.

For example:

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 record and then reader. Read () returns false to exit the loop.

{

Console. writeline (Reader ["title"]); // you can also use index reader [0] to indicate the data in the first column of this row.

}

Catch (sqlexception ex)

{

Console. writeline (ex. Message );

}

Finally

{

Conn. Close ();

}

}

In addition, you do not need to know its architecture before using datareader to query the database. You can use this object to obtain the architecture information. Use the attribute fieldcount of the datareader object to obtain the number of columns in the row, and then use the getname (int I) method to return the name of the column indexed as I. You can also use the getschematable () method to obtain the schema information. This method returns a datatable object.

Int Index = datareader. getordinal ("attribute name") to return the index corresponding to this attribute name.

Getdecimal (INDEX) obtains the value of the attribute column whose index value is index.

It is worth noting that the datareader object can only run when the database is connected, so conn cannot appear before the query statement. close (), and if you use a command to create a datareader object

The datareader object reader. Close () must be closed before other operations.

You can also use datareader to close the underlying connection. The code is reader = cmd. executereader (commandbehavior. closeconnection). At this time, you must put reader. Close () in the Finally block to prevent exceptions caused by connection leakage.

Sorry, I'm a little confused.

Why can't I insert code ??