One. Connect to SQL Server with SQLConnection
1. Add to namespace
Using system.data.sqlclient;
2. Connect 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 ();
Improve (more general) method:
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. OLEDBCONNECTION connection
1. Add a namespace
Using system.data.oledb;
2. Connect to SQL Server
String mysqlconnection = "provider = sqloledb; data source = localhost; initial catalog = test; integrated security = SSPI;"
SqlConnection myconnection = new sqlConnection; myconnection.open ();
3. Connect Access (can get a string by establishing the .udl file)
String mysqlconnection = "provider = microsoft.jet.Oledb.4.0; data source = c: / db2000.mdb;
Persist security info = false;
4. Connect Oracle (you can also connect via OracleConnection)
String mysqlconnection = "provider = msdaora; data source = db; user ID = sa; password = sinofindb";
3. Creating a Command object
1. SQLCommand constructor
1 New instance of initializing the SQLCommand class. Public sqlcommand ();
Sqlcommand mycommand = new sqlcommand ();
2 Initialize a new instance of the SQLCommand class with query text. Public Sqlcommand (String);
String myselectQuery = "select * from mindata"; sqlcommand mycommand = new sqlcommand (MySelectQuery); 3 Initialization has a SQLCommand class instance with query text and SQLConnection.
Public SqlCommand (String, SqlConnection);
String mySelectQuery = "SELECT * FROM mindata"; string myConnectString = "user id = sa; password =; database = test; server = mySQLServer"; SqlConnection myConnection = new SqlConnection (myConnectString); SqlCommand myCommand = new SqlCommand (mySelectQuery, myConnection) 4 Initialization 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 =; database = test; server = mySQLServer"; SqlConnection myConnection = new SqlConnection (myConnectString); SqlCommand MyCommand = New Sqlcommand (MySelectQuery, MyConnection, MyTrans);
2. Establish the association between SQLCOMMAND with SQLConnection.
MyCommand.connection = myconnection;
Or: sqlcommand mycommand = myconnection.createcommand;
3. Set the query text for SQLCommand.
Mycommand.commandtext = "Select * from mindata";
Or the second structure: sqlcommand mycommand = new sqlcommand (MySelectQuery);
Provide two query strings for SQLCommand objects, each query string access to different tables, returns different result sets.
Two queries are separated by semicolons.
4. Execute the command.
EXECUTEREADER Returns a row or multi-line EXECUTENONQUERY to perform a Transact-SQL statement for Connection and return the affected row (int) EXECUTESCALAR to return a single value (such as a aggregate value). Returns the first column of the first line of the result. Ignore additional columns or rows ExecuteExmlReader sends CommandText to Connection and generate an XMLReader object.
SqldataReader myReader = mycommand.executeReader ();
Or SqlDataReader MyReader = MyCommand.executeRead (Commandbehavior.CloseConnection);
While (MyReader.Read ()) // Loop read data {console.writeline (MyReader.getstring (0)); // Get the value of the string of the specified column console.writeLine (MyReader. getValue (1)); // Get the value of the specified column represented by this unit format}
Commandtext = "Select count (*) as numberofregions from region; int count = (int) mycomb.executescalar (); Used for OLEDBCommand objects.
four. DataReader
1. Traversal result set
While (MyReader.Read ()) console.writeline ("/ t {0} / t {1}", myreader.getint32 (0), myreader.getstring (1)); MyReader.close ();
2. Use the sequence 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 the Type Accessor.
Public char GetChar (INT i); Get the value of the single string of the specified column PUBLIC DATETIME GETDATETIME (INT i); get the value of the value of the DateTime object specified column PUBLIC SHORT GETIT16 (INT i); gain 16 of the 16 bits of the specified column [C #] Public String GetString (INT i) in symbol integer form; get the value of the string form of the specified column
5. Get column information.
MyReader.fieldCount Gets the number of MYReader.GetfieldType in the current row Get the data type of the object. TypeMyReader.getDataTypename (serial number) Get the name of the source data type MYReader.getName Get the name of the specified column MyReader.Getordinal Sequence number) Get column serial number when given a column name
6. Get information on the data sheet.
MyReader.getschematable () Returns a DataTable
7. Operate multiple result sets. MyReader.nextResult () makes the data reader to the next result set do {while (MyReader.Read ()) console.writeline ("/ t {0} / t {1}", myReader.Getint32 (0), MyReader.getstring (1));} while (myReader.nextResult ());
Fives. DataAdapter
1. Create a new instance of SqlDataAdapter to initialize the SqlDataAdapter class. Public SqlDataAdapter (); uses the specified SQLCommand as a SelectCommand property, initializing a new instance of the SqlDataAdapter class. Public SqlDataAdapter (SqlCommand); initializes new instances of the SQLDataAdapter class with the SELECTCOMMAND string and the SQLConnection object. Public SqlDataAdapter (String, SqlConnection); Initializing the new instance of the SQLDataAdapter class with a SelectCommand string and a connection string. Public SqlDataAdapter (string, string);
2. DataAdapter and SqlConnection, SQLCommand are established. 1. DataAdapter establishes 2 when constructing parameters. Sqldataadapter adapter = new sqldataadapter (); adapter.selectcommand = new SQLCOMMAND (Query, Conn); 3. DataAPter.Fill () method.
Add or refresh the row in the DataSet to match the lines in the data source that use the DataSet name and create a DataTable called "Table". Public override int fix (DataSet); add or refresh rows in the DataSet to match the lines in the data source that use the DataSet and the DataTable name. Public int fix (Dataset, String);
Add or refresh rows to match the DataSet to match the lines in the data source that use the DataSet and the DataTable name.
Public Int Fill (Dataset, Int, Int, String);
Add or refresh the row to match the row in the data source that uses the DataTable name in DataTable. Public int fix (DataTable);
Add or refresh the row in the DataTable to match the lines in the data source that specifies the DataTable and the iDataReader name. Protected Virtual Int Fill (DataTable, iDataRead);
Add or refresh the row in the DataTable to match the rows used to use the DataTable name, the specified SQL SELECT statement and the Commandbehavior data source. Protected Virtual Int Fill (DataTable, IDBCommand, Commandbehavior);
six. DataTable class
Seven. Datacolumn class
Eight. DataRow class
nine. DataSet class 1. Create a DataSet object
New instances of the DataSet class are initialized. Public DataSet (); initialize a new instance of the DataSet class with a given name. Public Dataset (String);
2. Plip DataSet with DataAdapter
DataSet myds = new dataset (); adapter.fill (myds) Adapter.Fill (MyDS, "Table Name"); with a table to populate DataSet.
Ten. DataTableCollection class. A collection of tables showing the table of DataSet.
DataTableCollection DTC = DS.TABLES; DATATABLE TABLES; DATATABLE TABLE = DTC ["Table Name"]; String Strexpr = "Id> 5"; string strsort = "name desc"; DATAROW [] Foundrows = Customertable.Select (strexpr, strsort,);
Dynamic screening and sorting. DataTable.Select () Method: Get the array of DataRow objects,
1 Get all arrays of all DATAROW objects. Public DataRow [] SELECT ();
2 Press the primary key order (if there is no primary key, follow the order of adding the order), obtain an array of all DataRow objects that match the filter criteria. Public DataRow [] SELECT (STRING);
3 Get an array of all DataRow objects that match the specified sort order and match the filter criteria.
Public DataRow [] Select (string, string); 4 Get arrays 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: is a dynamic view of DataTable content.
1. Create an object
Initialize new instances of the DataView class.
Public DataView ();
Initialize new instances of the DataView class with the specified DataTable.
Public DataView (DATATABLE);
Initialize new instances of the DataView class with the specified DataTable, RowFilter, Sort, and DataViewRowState.
Public DataView (DataTable, String, String, DataViewRowState);
DataView myview = new dataview (ds.tables "]," ID> 5 "," name desc ", dataviewstate.currentrows;
2. Get the row of data of the DataView.
Foreach (DATAROWVIEW MYROWVIEW IN MyView) {for (int i = 0; I
