The Command object contains access information submitted to the database system.
OleDbCommand and SqlCommand objects have the same basic objects and operation methods. Here we only introduce the usage of OleDbCommand, and the usage of SqlCommand can be deduced by analogy.
The most commonly used method to initialize a Command object is to bring in two parameters when creating a Command instance.
OleDbCommand myComm=new OleDbCommand(strQuery,myConnection);
strQuery is the SQL statement or stored procedure name, and myConnection is the created Connection object instance.
The main properties of the Command object are
Connection:
CommandText: Set or get the Connection object instance used by the Command object
CommandType: StoredProceduce, TableDirect, Text (standard SQL statement)
Parameters: Get the parameter value set
ExecuteReader: Execute the SQL statement or stored procedure name specified by CommandText, and the return type is DataReader
ExecuteNonQuery: The same function as ExecuteReader, except that the return value is the number of record rows affected by the execution of the SQL statement or stored procedure.
DataReader--SqlDataReader, OleDbDataReader
FieldCount: Displays the total number of fields in the current operation record
IsClosed: Determine whether the DataReader is closed
Close: Close DataReader
GetString: Returns the value in the specified column as String
GetValue: Returns the value in the specified column in its own type
GetValues: Returns a collection of all fields of the current record
Read: Read the next record. Example:
<% @ Page Language="C#" %>
<% @ Import Namespace="System.Data"%>
<% @ Import Namespace="System.Data.OleDb"%>
--------------------------------------------------
<% @ Import Namespace="System.Data.SqlClient"%>
<Script Language="c#" Runat="Server">
OleDbDataReader dr;
String myconnstring="Provider=Microsoft.Jet.OLEDB.4.0; DataSource="+Server.MapPath(".")+"..mdb";
string sql=".."
OleDbConnection my_conn=new OleDbConnection(myconnstring);
OleDbCommand my_comm=new OleDbCommand(sql,my_conn);
my_comm.Connection.Open();
dr=my_comm.ExecuteReader();
//DataGrid dg
dg.DataSource=dr
dg.DataBind();
</Script>
<head>
<% while(dr.Read())
...{;}
%>
</head>
DataSet DataAdapter
DataAdapter is used in conjunction with DataSet
OleDbDataAdapter my_adapter=new OleDbDataAdapter();
--------------------------------------------------
SqlDataAdapter my_sqladapter=new SqlDataAdapter();
my_adapter.SelectCommand.Connection=my_conn;
or
my_adapter.UpdateCommand.Connection=my_conn;
my_adapter.DeleteCommand.Connection=my_conn;
my_adapter.InsertCommand.Connection=my_conn;
If you need to execute Sql statements, you must also assign a value to the corresponding CommandText property. http://sucai.downcodes.com/
my_adapter.SelectCommand.CommandText=strSelect;
my_adapter.UpdateCommand.CommandText=strUpdate;
my_adapter.InsertCommand.CommandText=strInsert;
my_adapter.DeleteCommand.CommandText=strDelete;
If you just query the database, you can complete the above work when creating the DataAdapter instance.
OleDbDataAdapter MyAdapter=new OleDbDataAdapter(sql,my_conn);
DataSet ds=new DataSet();
OleDbConnection my_conn=new OleDbConnection(myconnstring);
OleDbDataAdapter my_adapter=new OleDbDataAdapter(sql,my_conn);
my_adapter.fill(ds,"score");
DataGrid score;
score.DataSource=ds.Table["score"].Defaultview;
score.DataBind();
//InsertCommand
DataRow dr=ds.Table["Score"].NewRow();
dr["name"]=..
ds.Tables["Score"].Rows.Add(dr);
//update
DataAdapter.Update(ds,"Score");
//modify
DataRow dr=ds.Tables["Score"].Rows[0];
dr["name"]=;
//Delete
DataRow dr=ds.Talbes["Score"].Rows[0];
dr.Delete();
//Note:
OleDbConnection my_conn=new OleDbConnection(myconnstring);
OleDbDataAdapter my_adapter=new OleDbDataAdapter(sql,my_conn);
OleDbCommandBuilder custCB=new OleDbCommandBuilder(my_adapter);//DataAdapter cannot automatically generate (Transact-SQL) when the data changes, so you must use CommandBuilder, which can automatically generate Sql statements! Just one step
RejectChanges is a method common to DataSet, DataTable, and DataRow to enable the operation object to reject the changes that have occurred and demobilize the data.
if(DataSet.HasErrors)
...{
DataSet.RejectChanges();
}
else...{ DataSet.AcceptChanges();}
-------------------------------------------------- -------------------------------------------------- ----------------------------------
DataReader->DataTable
public static DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)
...{
/**//**//**////Define DataTable and schema
DataTable datatable = new DataTable();
DataTable schemaTable = dataReader.GetSchemaTable(); //Import the table structure of a data table
try
...{ /**//**//**////Dynamicly add data columns of the table
foreach(DataRow myRow in schemaTable.Rows)
...{
DataColumn myDataColumn = new DataColumn();
myDataColumn.DataType = myRow.GetType();
myDataColumn.ColumnName = myRow[0].ToString();
datatable.Columns.Add(myDataColumn);
}
/**//**//**////Add table data
while(dataReader.Read())
...{
DataRow myDataRow = datatable.NewRow();
for(int i=0;i<schemaTable.Rows.Count;i++)
...{
myDataRow[i] = dataReader[i].ToString();
}
datatable.Rows.Add(myDataRow);
myDataRow = null;
}
schemaTable = null;
/**//**//**////Close the data reader
dataReader.Close();
return datatable;
}
catch(Exception ex)
...{
/**//**//**////Throws a type conversion error
SystemError.SystemLog(ex.Message);
throw new Exception(ex.Message,ex);
}
}
}
}