Personal Summary of database development (ado.net)

ado| Data | Database one. Connecting SQL Server with SqlConnection



1. Add namespaces

Using System.Data.SqlClient;

2. Connecting to the database

SqlConnection myconnection = new SqlConnection ();
myconnection.connectionstring = "User id=sa;password=sinofindb;initial catalog=test;data source=127.0.0.1; Connect timeout=30 ";
Myconnection.open ();



Improved (more general) approach:

String mysqlconnection= "user Id=sa;password=sinofindb;database =test;data source=127.0.0.1; Connect timeout=30 ";
SqlConnection myconnection = new SqlConnection (Mysqlconnection);
Myconnection.open ();





Two. Connect with OleDbConnection



1. Add namespaces

Using System.Data.OleDb;



2. Connect SQL Server

String mysqlconnection= "Provider=sqloledb;data source=localhost;initial catalog=test;integrated Security=SSPI;";

SqlConnection myconnection = new SqlConnection (Mysqlconnection);
Myconnection.open ();



3. Connect to access (you can get a string by creating a. udl file)

String mysqlconnection= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\db2000.mdb;

Persist Security Info=false;



4. Connect Oracle (also available via OracleConnection connection)

String mysqlconnection= "Provider=msdaora;data source=db; User Id=sa;password=sinofindb ";



Three. Create Command object



1. SqlCommand Constructors

① Initializes a new instance of the SqlCommand class. public SqlCommand ();

SqlCommand mycommand = new SqlCommand ();



② Initializes a new instance of the SqlCommand class with query text. Public SqlCommand (string);

String myselectquery = "SELECT * from Mindata";
SqlCommand mycommand = new SqlCommand (mySelectQuery);
③ Initializes an instance of the SqlCommand class with query text and SqlConnection.

Public SqlCommand (String, SqlConnection);

String myselectquery = "SELECT * from Mindata";
String myconnectstring = "User id=sa;password=;d atabase=test;server=mysqlserver";
SqlConnection myconnection = new SqlConnection (myconnectstring);
SqlCommand mycommand = new SqlCommand (myselectquery,myconnection);


④ Initializes a SqlCommand class instance with query text, SqlConnection, and Transaction.

Public SqlCommand (String, SqlConnection, SqlTransaction);

SqlTransaction Mytrans = Myconnection.begintransaction ();
String myselectquery = "SELECT * from Mindata";
String myconnectstring = "User id=sa;password=;d atabase=test;server=mysqlserver";
SqlConnection myconnection = new SqlConnection (myconnectstring);
SqlCommand mycommand = new SqlCommand (myselectquery,myconnection, Mytrans);


2. Establish the association between SqlCommand and SqlConnection.

Mycommand.connection = myconnection;

Or: SqlCommand mycommand = Myconnection.createcommand;



3. Sets the query text for the SqlCommand.

myCommand.CommandText = "SELECT * from Mindata";

or the 2nd construct: SqlCommand mycommand = new SqlCommand (mySelectQuery);

The SqlCommand object is provided with two query strings, each of which accesses a different table and returns a different result set.

Two query statements are separated by semicolons.



4. Execute the order.

ExecuteReader
Return one or more rows

ExecuteNonQuery
Execute Transact-SQL statements against Connection and return the number of affected rows (int)

ExecuteScalar
Returns a single value, such as an aggregate value. Returns the first column of the first row in the result set. Ignore extra columns or rows

ExecuteXmlReader
Sends the CommandText to the Connection and generates a XmlReader object.




SqlDataReader myreader = Mycommand.executereader ();

or SqlDataReader myreader = Mycommand.executereader (commandbehavior.closeconnection);

while (Myreader.read ())//loop read data
{
Console.WriteLine (myreader.getstring (0));//Get the value of the string form of the specified column
Console.WriteLine (myreader. GetValue (1))//Gets the value of the specified column in the native format
}


CommandText = "SELECT count (*) as numberofregions from region";
int count = (int) mycommand.executescalar ();



About the use of OleDbCommand objects.









Four The use of DataReader



1. Traverse result set

while (Myreader.read ())

Console.WriteLine ("\t{0}\t{1}", Myreader.getint32 (0), myreader.getstring (1));

Myreader.close ();



2. Use the ordinal indexer.

while (Myreader.read ())

Console.WriteLine ("\t{0}\t{1}", Myreader[0]. ToString (), myreader[1]. ToString ());

Myreader.close ();



3. Use the column name indexer.

while (Myreader.read ())

Console.WriteLine ("\t{0}\t{1}", myreader["code"). ToString (), myreader["name". ToString ());

Myreader.close ();



4. Use type accessors.

public char GetChar (int i); Gets the value of the specified column in a single string form

Public DateTime getdatetime (int i); Gets the value of the DateTime object of the specified column

public short GetInt16 (int i); Gets the [C #] of the specified column in the form of a 16-bit signed integer

public string GetString (int i); Gets the value of the specified column in the form of a string



5. Get the column information.

Myreader.fieldcount to get the number of columns in the current row

Myreader.getfieldtype (ordinal) Gets the type of the object's data type

Myreader.getdatatypename (ordinal) Gets the name of the source data type

Myreader.getname (ordinal) Gets the name of the specified column

Myreader.getordinal (ordinal) Gets the column ordinal given the column name



6. Get the information of the data table.

Myreader.getschematable () returns a DataTable



7. Manipulate multiple result sets.

Myreader.nextresult () Advances the data reader to the next result set

Todo

{

while (Myreader.read ())

Console.WriteLine ("\t{0}\t{1}", Myreader.getint32 (0), myreader.getstring (1));

}

while (Myreader.nextresult ());











Five DataAdapter



1. Create SqlDataAdapter

Initializes a new instance of the SqlDataAdapter class.

Public SqlDataAdapter ();



Initializes a new instance of the SqlDataAdapter class with the specified SqlCommand as the SelectCommand property.

Public SqlDataAdapter (SqlCommand);



Initializes a new instance of the SqlDataAdapter class with the SelectCommand string and the SqlConnection object.

Public SqlDataAdapter (String, SqlConnection);



Initializes a new instance of the SqlDataAdapter class with a SelectCommand string and a connection string.

Public SqlDataAdapter (String, string);



2. DataAdapter and Sqlconnection,sqlcommand establish associations.

1. DataAdapter is established when the parameter is constructed

2. SqlDataAdapter adapter = new SqlDataAdapter ();

Adapter. SelectCommand = new SqlCommand (query, conn);



3. DataAdapter.Fill () method.

Add or refresh rows in the dataset to match the rows in the data source using the dataset name, and create a DataTable named "Table."

public override int Fill (DATASET);



Add or refresh rows in the dataset to match the rows in the data source that use the dataset and DataTable names.

public int Fill (DataSet, String);



Adds or refreshes rows in the specified range of the dataset to match the rows in the data source that use the dataset and DataTable names.

public int Fill (DataSet, int, int, string);



Add or Refresh rows in the DataTable to match the rows in the data source that use the DataTable name.

public int Fill (DataTable);



Add or Refresh rows in the DataTable to match the rows in the data source that use the specified DataTable and IDataReader names.

protected virtual int Fill (DataTable, IDataReader);



Add or Refresh rows in the DataTable to match the rows in the data source using the DataTable name, the specified SQL SELECT statement, and the CommandBehavior.

protected virtual int Fill (DataTable, IDbCommand, CommandBehavior);







Six DataTable class

Seven DataColumn class

Eight DataRow class









Nine DataSet class
1. Creating a DataSet Object

Initializes a new instance of the DataSet class.

public DataSet ();



Initializes a new instance of the DataSet class with the given name.

Public DataSet (string);


2. Populating a DataSet with DataAdapter

DataSet myds=new DataSet ();

Adapter.fill (myDS)

Adapter.fill (myds, "table name"); Fill a DataSet with a table.









10. DataTableCollection class. A collection of tables representing the DataSet.

DataTableCollection DTC = ds. Tables;

DataTable table = dtc["table name"];



String strexpr = "ID > 5";

String strsort = "Name DESC";

datarow[] FoundRows = Customertable.select (strexpr, StrSort,);



For dynamic filtering and sorting.

DataTable.Select () Method: Gets an array of DataRow objects,

① gets an array of all the DataRow objects.

Public datarow[] Select ();



② gets an array of all the DataRow objects that match the filter criteria, in the order of the primary key (if there is no primary key, followed by the order of addition).

Public datarow[] Select (string);



③ gets an array of all the DataRow objects that match the filter criteria in the specified sort order.

Public datarow[] Select (String, string);



④ gets an array of all DataRow objects that match the filter in the sort order and the specified state.

Public datarow[] Select (String, String, DataViewRowState);











Eleven. DataView class: A dynamic view of the contents of a DataTable.

1. Creating objects

Initializes a new instance of the DataView class.

public DataView ();



Initializes a new instance of the DataView class with the specified DataTable.

Public DataView (DataTable);



Initializes a new instance of the DataView class with the specified DataTable, RowFilter, Sort, and DataViewRowState.

Public DataView (DataTable, String, String, DataViewRowState);



DataView myview = new DataView (ds. tables["Suppliers"],

"ID > 5",

"Name DESC",

DataViewRowState.CurrentRows);





2. Get DataView of the row data.

foreach (DataRowView myrowview in MyView)

{

for (int i = 0; i < MyView. Table.Columns.Count; i++)

Console.Write (Myrowview [i] + "t");

Console.WriteLine ();

}