Ado. NET Getting Started Tutorial (vi) talking about command object and data retrieval

Summary

So far, I believe you have a strong understanding of how to connect with external data sources and how to improve connectivity performance. The connection object as the main pioneer of ADO, has built a solid bridge for the user to interact with the database. Its life is ordinary and great, always do their best to build a user to the database of a flat road. The command object seems to be more dazzling than the connection object. Command object is always busy in the world of ADO, it is like a diplomat, for users to communicate all the operational database information.

Directory

• Get ready
• What is a command object?
• Several properties that must be mastered
• Several methods that must be mastered
• How do I create a command object?
• Select the appropriate execution command
• Summarize

1. Preparation

The quickest and best way to learn knowledge is to combine theory with practice. In order to help you better understand and master the Command object, I have also prepared a lot of examples of practice. I hope that everyone can be well prepared, so that the words do not rush in practice. Here are a few things you need to prepare:

(1) Make sure that your computer is equipped with a SQL Server 2005/2008 database server. If the SQL Server server is not installed, click here to download SQL Server EXPRESS R2.

(2) Create a database named Db_mydemo.

Use master;
GO
CREATE DATABASE Db_mydemo
On
(
  Name = Mydemo_data,/* Specifies the logical name of the file */
  FILENAME = ' D:\mydemo_dat.mdf ',/* Physical file name */
  Size = 10,/* Specifies the file size, unit mb*/
  MAXSIZE = 50,/* file maximum value, Unit mb*/
  FileGrowth = 5/  * Auto Increment */
)
LOG on
(
   Name = Mydemo_log,
   FILENAME = ' D:\mydemo_log.ldf ',
   SIZE = 5MB,
   MAXSIZE = 25MB,
   FileGrowth = 5MB
)
GO

(3) Create Customer table Tb_selcustomer.

Use Db_mydemo;
GO
CREATE TABLE Tb_selcustomer
(
   ID INT IDENTITY (PRIMARY) key,/*id, PRIMARY key */
   Name varchar () not NULL,/* Name */
   Sex char (1) Default (' 0 '),/* Gender: 0 is male, 1 is female, default is 0*/
   CustomerType char (1) Default (' 0 '),/* Customer Type: 0 for normal user, 1 for VIP user, default to 0*/
   Phone varchar (12),/* Contact telephone */
   Email varchar (50),/* Email */
   Contactaddress varchar (200),/* Contact address */
   Lat float,/* location dimension for display on map */
   Lng float,/* location longitude, for map display */
   Postalcode varchar (10),/* ZIP/Postal Code */
   Remark varchar (50)/* Notes */
)

All right, congratulations! Finally the preparation was done. Below, let us come together to uncover the veil of command objects!

2. What is a command object?

We know that the main purpose of ADO is to provide consistent access to external data sources. and access to data source data, there are no additions and deletions and other operations. Although the connection object is already connected to an external data source, it is dedicated and does not provide any action on the external data source. At the moment of entanglement, the command object was born. it encapsulates all operations on an external data source (including SQL statements and stored procedures such as add, delete, check, change, and so on) and returns the appropriate results after the execution is complete. As with connection objects, for different data sources, ADO. NET provides different command objects. Specifically, it can be divided into the following command object.

. NET Data provider	corresponding Command object
. NET Framework Data Provider for OLE DB	OleDbCommand Object
. NET Framework data Provider for SQL Server	SqlCommand object
. NET Framework Data Provider for ODBC	OdbcCommand Object
. NET Framework Data Provider for Oracle	OracleCommand Object

Regardless of the command object, it inherits from the DbCommand class. Like the DbConnection class, the DbCommand class is also an abstract base class, cannot be instantiated, and expects derived classes (corresponding to specific. NET Data Provider (the command Class) to implement the method. The DbCommand class defines the basic methods and basic properties of a well-established database operation, which is structured as follows:

    IDbCommand, IDisposable

From the above we can know that it inherits the component class as well as the IDbCommand interface and the IDisposable interface.

3. Several properties that must be mastered

CommandText: Gets or sets the text command to execute on the data source. The default value is an empty string.

CommandType: the command type, indicating or specifying how the CommandText property is interpreted. The value of the CommandType property is an enumeration type that defines the structure as follows:

public enum CommandType    
{Text      = 1,    //sql text command. Default ）          
        StoredProcedure = 4,    the//name of the stored procedure.          
        TableDirect =    The name of the table//.  
}

It is important to note that when you set CommandType to StoredProcedure, you should set the CommandText property to the name of the stored procedure. The command executes this stored procedure when one of the Execute methods is called.

Connection: Sets or gets the connection to the data source.

Parameters: A parameter that binds an SQL statement or stored procedure. It is important to have an indispensable object in a parameterized query.

tranction: Gets or sets the transaction in which the. NET Framework Data provider's Command object is executed.

4. Several methods that must be mastered

ExecuteNonQuery: performs an operation that does not return a data row and returns data of type int.

Note: for UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is-1.

ExecuteReader: executes the query and returns a DataReader object.
ExecuteScalar: executes the query and returns the first column of the first row in the query result set (object type). If the first column of the first row in the result set is not found, a null reference is returned.

5. How do I create a command object?

Before you create a command object, you need to be clear about two things:(1) What do you want to do? (2) Which data source do you want to work with? understand these two things, everything is well done. We can construct an SQL statement from a string string, or specify a connected data source through the connection object. So how do we give this information to the Command object? In general, there are two ways of doing this:

(1) through the constructor function. the code is as follows:

String strSQL = "SELECT * from Tb_selcustomer";
SqlCommand cmd = new SqlCommand (strSQL, conn);

(2) through the Command object's properties. the code is as follows:

SqlCommand cmd = new SqlCommand ();
Cmd. Connection = conn;
Cmd.commandtext = strSQL;

tip: The above two instances are relative to SQL Server, and if you access other data sources, you should choose a different command object. The specific reference #2 what is the table in the Command object.

6. Select the appropriate execution command

The Command object provides a wealth of execution commands, which can be referenced by several methods that #4 must master . There are pros and cons, Comandante object since the provision of a variety of execution orders, we in the actual development will have a choice, choose the appropriate execution order. In fact, the user's operation on the data source is crud-s(Create, Update, Delete, Select) operations. Below I will explore how to choose the appropriate execution commands in different scenarios.

(1) Scenario One: Perform crud operations, do not return data rows, return the number of rows affected (optional)

When we add, delete, update, or manipulate data-definition statements (such as creating table structures with Create tables) on Rows (records) of a data table, the database actually does not return rows of data, returning only an integer that contains information that affects the number of rows. In general, when performing non-query operations, we need to call the Excutenonquery method. Or, let's look at an example first! We insert a row of records in the Tb_selcustomer table with the following code:

Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Text;
Using system.data;//must introduce
Using system.data.sqlclient;//must introduce

Namespace Command
{
Class Program
{
static void Main (string[] args)
{
String connsql = @ "Data source=.\sqlexpress; Initial Catalog=db_mydemo; Integrated SECURITY=SSPI ";//Construct connection string
SqlConnectionStringBuilder connstr = new SqlConnectionStringBuilder (connsql);

using (SqlConnection conn = new SqlConnection (connstr.connectionstring))
{
Splicing SQL statements
StringBuilder strSQL = new StringBuilder ();
Strsql.append ("INSERT into Tb_selcustomer");
Strsql.append ("values");
Strsql.append ("' Liuhao ', ' 0 ', ' 0 ', ' 13822223333 ', ' [email protected] ', ' Guangdong province Shenzhen Baoan District ', 12.234556, 34.222234, ' 422900 ', ' (Note information ') ");

Console.WriteLine ("Output sql:\n{0}", strsql.tostring ());

To create a Command object
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = conn;
Cmd.commandtype = CommandType.Text;
Cmd.commandtext = Strsql.tostring ();

Try
{
Conn. Open ();//must pay attention to opening the connection

int rows = cmd. ExecuteNonQuery ();//execute command
Console.WriteLine ("\nresult: {0} rows affected", rows);
}
catch (Exception ex)
{
Console.WriteLine ("\nerror:\n{0}", ex. Message);
}
}

Console.read ();
}
}
}

After running, the output is as follows:

From the above information we can see that a row of data has been successfully added.

(2) Scenario Two: Perform a select operation to return multiple data

When we return one or more rows of data by performing a select operation, the excutenonquery needs to rest. But also don't panic, say Command object Reggie like Cloud, deal with this kind of thing need to please excutereader method to play. The Excutereader method returns a DataReader object. DataReader is a fast, lightweight, read-only traversal of the data stream that accesses each row of data. when using DataReader, the following points need to be noted:

• DataReader iterates through one row of data at a time and returns a collection containing the column names.
• The first call to the read () method gets the first row of data and points the cursor to the next row of data. When the method is called again, the next row of data is read.
• The Read () method returns False when no more data rows are detected.
• With the HasRows property, we know whether there are rows of data in the query results.
• When we finish using DataReader, we must pay attention to closing. SQL Server allows only one DataReader to be opened by default.

Well, let's look at a simple example. Query out all the data in the Tb_selcustomer table. The code is as follows:

Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Text;
Using System.Data;
Using System.Data.SqlClient;

Namespace Command2
{
Class Program
{
static void Main (string[] args)
{
Constructing the connection string
SqlConnectionStringBuilder strconn = new SqlConnectionStringBuilder ();
Strconn.datasource = @ "(local) \SQLExpress";
Strconn.initialcatalog = "Db_mydemo";
Strconn.integratedsecurity = true;

using (SqlConnection conn = new SqlConnection (strconn.connectionstring))
{
String strSQL = "SELECT * from Tb_selcustomer";
SqlCommand cmd = new SqlCommand (strSQL, conn);//create command object using constructor

Conn. Open ();//Remember to turn on the connection

Try
{
SqlDataReader reader = cmd. ExecuteReader ();//Execute ExecuteReader

if (reader! = null && reader. HasRows)
{
int rows = 0;//Record row count
Console.WriteLine ("**********records of tb_selcustomer**********\n");
while (reader. Read ())
{
for (int i = 0; i < reader. FieldCount; ++i)
{
Console.WriteLine ("{0}:{1}", Reader. GetName (i), reader. GetValue (i));
}
++rows;
}
Console.WriteLine ("\ n Total {0} row record", rows);
}
Reader. Close ();//Closed DataReader
}
catch (Exception ex)
{
Console.WriteLine ("\nerror:\n{0}", ex. Message);
}
}

Console.read ();
}
}
}

After executing the above code, we can see the data added in the scene (a) . As shown in the results.

(3) Scenario Three: Perform a select operation to return a single value

The above two scenes believe everyone is very familiar with. But what if we just need to return a value (such as the number of rows returned) when we operate the database? Here, I have to admit that the command object is really talented, the Excutescalar method is to deal with individual data the best talent. Excutescalar Returns a System.Object type of data , so we need to force type conversions when we get the data. When there is no data, the Excutescalar method returns System.DBNull. Theory lies in practice, let's look at an example first! Gets the number of rows in the Tb_selcustomer. The code is as follows:

Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Text;
Using System.Data;
Using System.Data.SqlClient;

Namespace Command3
{
    Class Program
    {
        static void Main (string[] args)
        {
            String connsql = @ "Data source=.\sqlexpress; Initial Catalog=db_mydemo; Integrated Security=sspi ";

using (SqlConnection conn = new SqlConnection (connsql))
{
String strSQL = "SELECT count (*) from Tb_selcustomer";


Try
{


Console.WriteLine ("Execution Excutescalar method: total {0} row record", rows);
}
catch (Exception ex)
{
Console.WriteLine ("\nerror:\n{0}", ex. Message);
}
}

            Console.read ();
        }
    }
}

The output results are as follows:

7. Summary

Finally relieved, lasted one weeks, finally the command object some of the most basic but important knowledge points are finished. I don't know how much you know about the command object, but I'm sure that if you read this article carefully, at least you'll be impressed with the basics of the Command object. In the final analysis, the command object is a carrier. It communicates the user's operational information to the database, and the database returns the processing result to the user through the Command object. In the next article, I'll explain some of the high-level applications of command objects, and I hope you'll continue to follow and recommend them.

Article posted: http://liuhaorain.cnblogs.com

Ado. NET Getting Started Tutorial (vi) talking about command object and data retrieval