What should I do with ExecuteReader, ExecuteNonQuery, ExecuteScalar, SqlDataReader, and SqlDataAdapter in C #?

For C # Beginners, we often encounter ExecuteReader, ExecuteNonQuery, ExecuteScalar, SqlDataReader, SqlDataAdapter these objects or methods when they start to stun, Do not know which object is suitable for the use of additions and deletions, which object is suitable for the use of the query, I search through the Internet and some personal understanding, finishing a bit, there is the wrong place to hope that we can give a correction.

One
Database connection string
Private readonly static string connectionString = configurationmanager.connectionstrings["MyConnection"]. ToString ();

///
Execute Add, Delete, change method, support stored procedure
///
Connection string, self-satisfied, please use sqlhelper.connstring to assign a value
The command type, or CommandType.Text if it is an SQL statement, otherwise commandtype.storedprocdure
SQL statement or stored procedure name
SQL parameter, or null if there are no arguments
Number of rows affected
public static int ExecuteNonQuery (CommandType commandtype, String cmdtext, params sqlparameter[] para)
{
using (SqlConnection conn = new SqlConnection (connectionString))
{
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = conn;
Cmd.commandtype = CommandType;
Cmd.commandtext = Cmdtext;
IF (para! = null)
{
Cmd. Parameters.addrange (para);
}
Conn. Open ();
Return Convert.ToInt32 (cmd. ExecuteNonQuery ());
}
}
Note: 1, ExecuteNonQuery () executes the Command object's SQL statement, returns a variable of type int, returns the number of rows affected after the database operation. It is suitable for verifying and modifying the database.

2, ExecuteScalar () also returns an int type variable. If the SQL statement is a select query, only the first column of the first row in the query result set is returned, and the other rows and columns are ignored. If the SQL statement is not a select query, the return result has no effect. (Recommended when querying a database) because you do not know exactly what the SQL statement is (possibly int, possibly char, etc.), the ExecuteScalar () method returns a most basic type of object, which is the base class for all types. It can be converted to any type, so it needs to be cast before it is used.

Two

///

Methods for executing queries that support stored procedures

SQL parameter, or null if there are no arguments

Returns the first column of the first row of the query results

public static Object Getscalar (CommandType commandtype, String cmdtext, params sqlparameter[] para)

{

object result = NULL;

using (SqlConnection conn = new SqlConnection (connectionString))

{

SqlCommand cmd = new SqlCommand ();

Cmd. Connection = conn;

Cmd.commandtype = CommandType;

Cmd.commandtext = Cmdtext;

IF (para! = null)

{

Cmd. Parameters.addrange (para);

}

Conn. Open ();

result = cmd. ExecuteScalar ();

}

return result;

}

}

Note: ExecuteScalar () also returns an int type variable. If the SQL statement is a select query, only the first column of the first row in the query result set is returned, and the other rows and columns are ignored. If the SQL statement is not a select query, the return result has no effect. (Recommended when querying a database) because you do not know exactly what the SQL statement is (possibly int, possibly char, etc.), the ExecuteScalar () method returns a most basic type of object, which is the base class for all types. It can be converted to any type, so it needs to be cast before it is used.

Three:

///

Methods for executing queries that support stored procedures

SQL parameter, or null if there are no arguments

Reader SqlDataReader

public static SqlDataReader ExecuteReader (CommandType commandtype, String cmdtext, params sqlparameter[] para)

{

SqlConnection conn = new SqlConnection (connectionString);

SqlDataReader dr = null;

SqlCommand cmd = new SqlCommand ();

Cmd. Connection = conn;

Cmd.commandtype = CommandType;

Cmd.commandtext = Cmdtext;

IF (para! = null)

{

Cmd. Parameters.addrange (para);

}

Conn. Open ();

Dr = cmd. ExecuteReader (commandbehavior.closeconnection);

Return Dr;

Note: 1, the purpose of the ExecuteReader method exists: To query the database as soon as possible and get results

2, ExecuteReader Returns a DataReader object, if called in the SqlCommand object,

The SqlDataReader is returned, and if called in the OleDbCommand object, the return is OleDbDataReader, and the method and property of DataReader can be called to iterate over the result set.

Four:

///

Methods for executing queries that support stored procedures

SQL statement or stored procedure name

SQL parameter, or null if there are no arguments

Data set

public static DataSet GetDataSet (CommandType commandtype, String cmdtext, params sqlparameter[] para)

{

using (SqlConnection conn = new SqlConnection (connectionString))

{

SqlDataAdapter da = new SqlDataAdapter ();

Da. SelectCommand = new SqlCommand ();

Da. SelectCommand.Connection = conn;

Da.SelectCommand.CommandText = Cmdtext;

Da.SelectCommand.CommandType = CommandType;

IF (para! = null)

{

Da. SelectCommand.Parameters.AddRange (para);

}

DataSet ds = new DataSet ();

Conn. Open ();

Da. Fill (DS);

return DS;

}

}

SqlDataReader and Sqldataadapter+dataset the Difference

One, SqlDataReader//connection-based, read-only access suitable for smaller data volumes. (Connection mode)

SqlDataAdapter//Based on non-connected, suitable for large data volume, can be modified, and finally return the results of the changes to the database. Requires a bit more resources (disconnected mode)

Second, SqlDataAdapter the data set into a dataset after reading the data, and the dataset's data exists in local customer service machine memory.

Third, SqlDataReader returned is a data reader, only one of the reading, operation is not flexible, generally in read-only time to use.

SqlDataAdapter returns a dataset or table that can manipulate the data in it.

Four, the different wording:

Sqldatreader the database must be opened before execution, and a command object must be generated. Again by command. The ExecuteReader () method assigns a value. You must close the join manually after completion.

SqlCommand cmd = new SqlCommand ("SELECT * from Stu", Conn);

Conn. Open ();

SqlDataReader rdr = cmd. ExecuteReader ();

。。。。。

Conn.close ();

SqlDataAdapter execution, automatically hit the database, and do not use the command of the ExecuteReader method to assign value, after completion of automatic disconnection.

SqlDataAdapter adptr = new SqlDataAdapter (SQL, conn);

DataSet ds = new DataSet ();

Adptr. Fill (ds, "Stu");

Instance:

1, using SqlDataReader to read data

Class DataReader

{

static void Main ()

{

String str = "Server=localhost;uid=sa;pwd=123;database=northwind";

SqlConnection conn = new SqlConnection (str);

SqlCommand cmd = new SqlCommand ("SELECT * from Stu", Conn);

Conn. Open ();

SqlDataReader rdr = cmd. ExecuteReader ();

DataTable table=new datable ();

Table. Load (RDR);

Rdr. Close ();

Conn. Close ();

}

2, use SqlDataAdapter +dataset to read the modified data

Class SqlDataAdapter

{

static void Main ()

{

String str = "Server=localhost;uid=sa;pwd=123;database=northwind";

SqlConnection conn = new SqlConnection (str);

String sql = "SELECT * from Stu";

SqlDataAdapter adptr = new SqlDataAdapter (SQL, conn);//adepter Object

DataSet ds = new DataSet ();//dataset Object

Adptr. Fill (ds, "Stu");//populate the dataset and name the current table

DataTableReader rdr = ds. CreateDataReader ();

while (RDR. Read ())//reading data from table

{

for (int i = 0; i < RDR. FieldCount; i++)

{

Console.Write (RDR. GetName (i) + "\ T" + rdr. GetValue (i) + "\ T");

}

Console.WriteLine ();

}

}

Reference Links:
http://blog.163.com/z[email protected]/blog/static/1314263812009111381920187/
Since the Xianningbo master

SqlDataReader and Sqldataadapter+dataset the Difference

The summary is in place, so he pasted down his article as a whole.

What should I do with ExecuteReader, ExecuteNonQuery, ExecuteScalar, SqlDataReader, and SqlDataAdapter in C #?