One. SqlConnection connection with SQL Server 1. Add namespace using System.Data.SqlClient; 2. database connection SqlConnection myConnection = new SqlConnection (); myConnection.ConnectionString = "user id = sa; password = sinofindb; initial catalog = test; data source = 127.0.0.1; connection 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; myconnection.open (); two. 1. OleDbConnection is added with namespace 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 Access (can obtain a string by establishing .udl file) String mysqlconnection = "provike = microsoft.jet.Oledb.4.0; data source = c: / db2000.mdb; Persist Security info = false; 4. Connecting Oracle (also through OracleConnection connection) String mysqlconnection = "provider = msdaora; data source = db; user id = sa; password = sinofindb"; 3. Creating a Command object 1. SQLCommand constructor 1 Initializes a new instance of the SqlCommand class .public SqlCommand (); SqlCommand myCommand = new SqlCommand (); ② a new instance of the SqlCommand class is initialized with a query text .public SqlCommand (string); String mySelectQuery = "SELECT * FROM mindata"; SqlCommand myCommand = New SQLCOMMAND (MySelectQuery); 3 Initializing the 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; sqlcommand mycommand = new SQLCommand (MySelectQuery, MyConnection, MyTrans); 2. Create SQLCommand and 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); Provides two query strings for SQLCommand objects, each query string access different tables, return different results set. 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.ExecuteReader (CommandBehavior.CloseConnection); while (myReader.Read ()) // loop reads 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 the native format} commandtext = "select count (*) as numberofregions from region; INT count = (int) MyCommand.executescalar (); About OLEDBCOMMAND objects. four. Use of DataReader 1. Traversing 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 DateTime object in the specified column PUBLIC SHORT GetInt16 (Int i); Get 16-bit symbolic forms of the specified column [C #] Public String GetString (INT i); get the value of the string form of the specified column 5. Get column information. MyReader.fieldcount Gets the number of columns in the current row MyReader.GetfieldType (serial number) Gets the Type MyReader.GetDataTypeName for the data type of the object Get the name of the source data type MYReader.getName (serial number) Get the name of the specified column MYReader.getordinal (serial number) in a given column name Get the column sequence number 6. Get the information of the data table. MyReader.getsChematable () Returns a DataTable 7. Operating 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 ()); 5.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 and DataTable names in the specified range of DataSets. Public int fix (int, int, string); add or refresh rows in DataTable to match rows in the data source using the DataTable name. Public int fix (DataTable); add or refresh rows in DataTable to match the lines in the data source that specifies the DataTable and iDataAder name. Protected Virtual Int Fill (DATATABLE, IDATAREADER); add or refresh rows in DataTable to match lines using the DataTable name, specified SQL SELECT statement, and Commandbehavior data sources. Protected Virtual Int Fill (DataTable, IDBCommand, Commandbehavior); Six. DataTable class seven. Datacolumn class eight. DataRow class nine. DataSet class 1. Create a new instance of the DataSet object to initialize the DataSet class. Public DataSet (); initialize a new instance of the DataSet class with a given name. Public Dataset (String); 2. Fill DataSet DataSet myds = new dataset (); adapter.Fill (MyDS) Adapter.Fill (MyDS, "Table Name"; use a table to populate DataSet. Ten. DataTableCollection class. A collection of tables showing the table of DataSet.
