The total tricks of database development (important)

From: http://blog.csdn.net/litp/archive/2004/09/14/104302.aspx

1. Use sqlconnection to connect to SQL Server

1. Add a namespace

Using system. Data. sqlclient;

2. Connect 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) methods:

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 ();

 

II. Use oledbconnection to connect

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 (mysqlconnection );
Myconnection. open ();

 

3. Connect to access (strings can be obtained by creating a. udl file)

String mysqlconnection = "provider = Microsoft. Jet. oledb.4.0; Data Source = C:/db2000.mdb;

Persist Security info = false;

4. Connect to Oracle (you can also connect through oracleconnection)

String mysqlconnection = "provider = msdaora; Data Source = dB; user id = sa; Password = sinofindb ";

 

3. Create a command object

 
1. sqlcommand Constructor

① Initialize a new instance of the sqlcommand class. Public sqlcommand ();

Sqlcommand mycommand = new sqlcommand ();

 

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

String myselectquery = "select * From mindata ";
Sqlcommand mycommand = new sqlcommand (myselectquery );
③ Initialize 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 );
 

④ Initialize the 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 an association between sqlcommand and sqlconnection.

Mycommand. Connection = myconnection;

Or: sqlcommand mycommand = myconnection. createcommand;

 

3. Set the query text of sqlcommand.

Mycommand. commandtext = "select * From mindata ";

Or 2nd types of structures: sqlcommand mycommand = new sqlcommand (myselectquery );

The sqlcommand object provides two query strings. Each query string Accesses Different tables and returns different result sets.

The two query statements are separated by semicolons.

 

4. Run the command.

Executereader ()
Returns one or more rows.
 
Executenonquery ()
Execute a Transact-SQL statement on the connection and return the affected number of rows (INT)
 
Executescalar ()
Returns a single value (such as an aggregate value). returns the first column of the first row of the result set. Ignore extra columns or rows
 
Executexmlreader ()
Send commandtext to connection and generate an xmlreader object.
 

 

Sqldatareader myreader = mycommand. executereader ();

Or sqldatareader myreader = mycommand. executereader (commandbehavior. closeconnection );

While (myreader. Read () // read data cyclically
{
Console. writeline (myreader. getstring (0); // obtain the value in the string format of the specified Column
Console. writeline (myreader. getvalue (1); // obtain the value of a specified column in the local format
}
 

Commandtext = "select count (*) as numberofregions from Region ";
Int COUNT = (INT) mycommand. executescalar ();

 

The use of oledbcommand objects.

 

4. Use of datareader

1. traverse the result set

While (myreader. Read ())
Console. writeline ("/t {0}/t {1}", myreader. getint32 (0), myreader. getstring (1 ));
Myreader. Close ();
 

2. Use the ordinal index.

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 accessors.

Public char getchar (int I); returns a single string value of the specified column.
Public datetime getdatetime (int I); get the value in the datetime format of the specified Column
Public short getint16 (int I); get the 16-bit signed integer form of the specified column [C #]
Public String getstring (int I); get the value in the string format of the specified Column

5. Obtain the column information.

Myreader. fieldcount gets the number of columns in the current row
Myreader. getfieldtype (serial number) is the type of the object's data type.
Myreader. getdatatypename (serial number) Get the name of the source data type
Myreader. getname (serial number) to get the name of the specified Column
Myreader. getordinal (sequence number) obtains the sequence number of a given column name.

6. Obtain the data table information.

Myreader. getschematable () returns a datatable
 

7. Operate multiple result sets.
Myreader. nextresult () enables the data reader to move forward 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 sqldataadapter
Initialize a new instance of the sqldataadapter class.
Public sqldataadapter ();
Use the specified sqlcommand as the selectcommand attribute to initialize a new instance of the sqldataadapter class.
Public sqldataadapter (sqlcommand );
Use the selectcommand string and sqlconnection object to initialize a new instance of the sqldataadapter class.
Public sqldataadapter (string, sqlconnection );
Use the selectcommand string and a connection string to initialize a new instance of the sqldataadapter class.
Public sqldataadapter (string, string );

2. Create an association between dataadapter, sqlconnection, and sqlcommand.
1. dataadapter is created when constructing Parameters
2. sqldataadapter adapter = new sqldataadapter ();
Adapter. selectcommand = new sqlcommand (query, Conn );

 
3. dataadapter. Fill () method.

Add or refresh rows in dataset to match the rows in the data source with the dataset name, and create a datatable named "table.
Public override int fill (Dataset );
Add or refresh rows in dataset to match the rows in the data source that uses the dataset and able names.
Public int fill (dataset, string );

Add or refresh rows in the specified range of dataset to match the rows in the data source using the dataset and datatable names.

Public int fill (dataset, Int, Int, string );

Add or refresh rows in datatable to match rows in the data source that uses the datatable name.
Public int fill (datatable );

 
Add or refresh rows in the datatable to match the rows in the data source that uses the specified datatable and idatareader names.
Protected virtual int fill (datatable, idatareader );

Add or refresh rows in the datatable to match rows in the data source that uses the datatable name, specified SQL SELECT statement, and commandbehavior.
Protected virtual int fill (datatable, idbcommand, commandbehavior );
 

6. datatable class

For details, see: An encapsulation class for ms SQL operations.

VII. datacolumn class

8. datarow class
 

9. dataset class
1. Create a DataSet object

Initializes a new instance of the dataset class.
Public dataset ();
 
Initialize a new instance of the dataset class with the given name.
Public dataset (string );
 

2. Fill dataset with dataadapter

Dataset myds = new dataset ();
Adapter. Fill (myds)
Adapter. Fill (myds, "table name"); fill dataset with a table.

 

10. datatablecollection class. Indicates the set of dataset tables.

Datatablecollection DTC = Ds. tables;
Datatable table = DTC ["table name"];
String strexpr = "id> 5 ";
String strsort = "name DESC ";
Datarow [] foundrows = customertable. Select (strexpr, strsort ,);

Dynamic Filtering and sorting.
Datatable. Select () method: Get the array of the datarow object,

① Obtain the array of all datarow objects.
Public datarow [] Select ();

 

② Obtain the array of all datarow objects that match the filtering conditions in the primary key order (if no primary key exists.
Public datarow [] Select (string );

③ Obtain the array of all datarow objects in the specified sorting order that matches the filtering conditions.

Public datarow [] Select (string, string );

④ Obtain the array of all datarow objects that match the filters and specified states in the sorting order.

Public datarow [] Select (string, String, dataviewrowstate );

11. Dataview class: A dynamic view of the datatable content.

1. Create an object

Initialize a new instance of the dataview class.

Public dataview ();

Use the specified datatable to initialize a new instance of the dataview class.

Public dataview (datatable );

Use the specified able, rowfilter, sort, and dataviewrowstate to initialize a new instance of the dataview class.

Public dataview (datatable, String, String, dataviewrowstate );

Dataview myview = new dataview (Ds. Tables ["Suppliers"], "id> 5", "Name DESC ",
Dataviewrowstate. currentrows );

2. Obtain the row data of dataview.

Foreach (datarowview myrowview in myview)
{
For (INT I = 0; I <myview. Table. Columns. Count; I ++)
Console. Write (myrowview [I] + "/t ");
Console. writeline ();
}

 For details, see: An encapsulation class for ms SQL operations.