"ADO" 8, the use of datasets

Query of a DataSet

SqlDataReader is suitable for reading large data, it is a one-piece read, the data read is stored on the server
When the data is being read, suddenly with the service is interrupted, will not be able to read the subsequent data
The dataset is suitable for reading small data, which is a memory that reads all data concurrently and puts it into the native program's memory
When the data is being read, suddenly with the service interrupted, the data can continue to read

A dataset can hold several tables (DataTable) with several rows per table (DataRow)

　　　　private void Button3_Click (object sender, EventArgs e)//Click event {string Str = Configurationmanager.c onnectionstrings["ConnStr"].            ConnectionString; using (SqlConnection conn = new SqlConnection (STR)) {Conn.                Open (); using (SqlCommand cmd = conn.                    CreateCommand ()) {cmd.commandtext = "select * from T_age";      DataSet DataSet = new DataSet ();  Create a dataset set to hold the queried data SqlDataAdapter adapter = new SqlDataAdapter (cmd); Specifies the statement adapter to execute.           Fill (DataSet); Populate the dataset with the queried data into a DataTable table = DataSet. Tables[0]; Query the first table for (int i = 0; i < table. Rows.Count; i++)//traverse each row, the DataTable contains several rows {DataRow row = table.    Rows[i];  Iterate through each line to get the contents of each row string name = Convert.ToString (row["name"]);                  Querying the data for the Name column of the current row      MessageBox.Show (name); Display content}}}}

Ii. Update of the dataset

row["Name"]= "Xgao"; Update line
Table. Rows.remove (); Delete Row
Table. NewRow (); New row

　　　　private void Button3_Click (object sender, EventArgs e)//Click event {string Str = Configurationmanager.c onnectionstrings["ConnStr"].            ConnectionString; using (SqlConnection conn = new SqlConnection (STR)) {Conn.                Open (); using (SqlCommand cmd = conn.                    CreateCommand ()) {cmd.commandtext = "select * from T_age";                          DataSet DataSet = new DataSet ();                      SqlDataAdapter adapter = new SqlDataAdapter (cmd); Adapter.                              Fill (DataSet); DataTable table = DataSet.                     Tables[0]; DataRow row = table.              Rows[0];      row["Name"]= "small High"; Change the Name field of the first row to "small high" table.    Rows.removeat (1); Delete the second row of DataRow dr = table.                        NewRow ();//Add new line SqlCommandBuilder builder = new SqlCommandBuilder (adapter)//auto-Generate action command Adpter.  Update (DataSet);                  }                }            }        } 

Third, VS automatically generate strongly typed datasets (typed datasets)

DataSet with XML Schema (XSD)
It's essentially a dataset, but it's more of a schema definition

Add-New Item-Data set
Drag and drop tables from Server Explorer into the dataset (dataset), and notice that the drag-and-drop process automatically generates classes of strongly typed datasets based on the table structure.
The database is automatically written in app. config without the data being dragged over, or the program is connected to it.

Note: The table must be set with a primary key, the value of the column must have a value
Weakness: When the table field is incremented, you need to reconfigure the build

Example of using dataset in code (Mydo table): Mydotableadapter adapter = new Mydotableadapter ();
How do I know the class name of adapter? The Adapter,name property of the lower half of the selected dataset is the class name, and you need to right-click the class name-parse
Get all data: adapter. GetData (),
Example program: Traverse to show all data
I<adapter. GetData (). Count;
Adapter. GetData () [i]. Age;

FAQ: The class name is generally: Table name +tableadapter, table name +datatable, table name +row, and then "parse" to populate the class name

Experiment one: Manipulate the Mydo table and iterate through the values of all the username fields-----Query

        private void Button7_click (object sender, EventArgs e)        {            Mydotableadapter adapter = new Mydotableadapter ();        Create a TableAdapter            SQL wrapper. dataset1.mydodatatable data = adapter. GetData ();       Get data, strongly typed DataTable for            (int i = 0; i < data. Count; i++)                   //Traversal data            {                SQL encapsulation. Dataset1.mydorow userrow = data[i];         Get a row of                MessageBox.Show (userrow.username);              Print the line's Username property            }        }

SQL encapsulation. Dataset1.mydodatatable//Why is it so long? Because the Mydodatatable class is defined inside the DataSet1 class.

SQL Encapsulation: assembly name namespace
DataSet1: Class name of the outer class

Experiment two: Manipulate the T_age table to iterate through the values of all Name and age fields-----Query

private void Button1_Click (object sender, EventArgs e)        {            T_agetableadapter adapter = new T_agetableadapter (); C2/>WF strongly typed. T_age.t_agedatatable Myage = adapter. GetData ();            for (int i = 0; i < Myage. Count; i++)            {                WF strongly typed. T_age.t_agerow data = Myage[i];                String msg = string. Format ("name: {0}, Age: {1}", data.) Name, data. Age);                MessageBox.Show (msg);            }        }

Experiment three: Manipulating the T_age table, modifying the name value inside the t_age table------Updating update

   private void Button1_Click (object sender, EventArgs e)        {            T_agetableadapter adapter = new T_agetableadapter (); C4/>WF strongly typed. T_age.t_agedatatable myage = adpater. GetData ();            for (int i = 0; i < Myage. Count; i++)            {                WF strongly typed. T_age.t_agerow data = Myage[i];                String msg = string. Format ("name: {0}, Age: {1}", data.) Name, data. Age);                MessageBox.Show (msg);            }        Myage[0]. Name = "Test";    Change the first data in the Name field of the table to test        adapter. Update (myage);    Update data to the server on the Myage table        }

FAQ: Error message: When passing a DataRow collection with modified rows, the update requires a updatecommand of the handover
Cause: There is no primary key in the table and a field in the table is set as the primary key

Experiment four: Manipulating the t_age table, inserting values into the Name and age fields-------inserting insert

 　　    private void Button3_Click (object sender, EventArgs e)        {            T_agetableadapter adapter = new T_agetableadapter (); C3/>adapter. Insert ("Zhangsan");    Simply call the Insert () method to insert            the data MessageBox.Show ("Insert succeeded! ");        }

Experiment Five: Manipulate the T_age table, delete a row of data in the table-------Delete

        private void Button3_Click (object sender, EventArgs e)        {            T_agetableadapter adapter = new T_agetableadapter (); C10/>adapter.delete (3, "Zhangsan");    Delete the specified data, more trouble, you need to specify            MessageBox.Show ("delete success!") ");        }

----------------reading of strongly typed dataset data

Strongly typed DataSet

Newsdataset ds = new Newsdataset (), var danews = new Newsdatasettableadapters.newstableadapter (), var dacate = new NewsDataS Ettableadapters.categorytableadapter ();d Anews.fill (ds.news);d Acate.fill (ds.category); This.gv1.DataSource = Ds.category;this.gv1.databind (); this.gv2.DataSource = Ds.news;this.gv2.databind ();

----------------a more streamlined read of strongly typed DataSet data

This.gv1.DataSource = new Newsdatasettableadapters.categorytableadapter (). Getcate (); This.gv1.DataBind ();

----------------Insert, Delete, change of a strongly typed DataSet

New Newsdatasettableadapters.categorytableadapter (). Insert ("test Type"); new Newsdatasettableadapters.categorytableadapter (). Delete (); new Newsdatasettableadapters.categorytableadapter (). Update ("modified", 16);

Iv. adding your own SQL statements for typed datasets

1. In the DataSet, right-add-Query-use the SQL statement to select the type of operation, such as: Select,insert,update,delete
As follows: Save named Getdatabyage
SELECT ID, Name, age from T_age WHERE (age > @Age)

1. Role

T_agetableadapter adapter = new T_agetableadapter (); WF strong type DataSet.T_Age.T_AgeDataTable data = adapter. Getdatabyage (+);   Value for    (int i = 0; i < data). Count; i++) {    WF strongly typed DataSet.T_Age.T_AgeRow p = data[i];    Only MessageBox.Show (p.name) with age greater than 30 are output    ;}

V. Optimizing the time for strongly typed datasets to insert data in bulk

In a strongly typed DataSet, the adapter used inside. Insert (), which operates on a database, operates in the following mode:

1. Before inserting the data, determine if the connection is open, or open it if it is not
2. Once the data has been inserted, determine if the connection to the original (the one that was not automatically opened by the program) is turned off, and if it is off, it will continue to open

The default connection is off, so it works when inserting data:

1. Automatically open connection, insert data
2. After the data is inserted, it is found that the original connection is closed, so close

It can be seen that we just before the program to open the connection, the program will not automatically close the connection, if you do bulk INSERT, you can in the loop before the statement, the connection will be opened before the loop, and then close the connection, which will greatly accelerate the data insertion time between

Instance:
Create a new Stopwatch table, field ID int,name nvarchar, age nvarchar (20)

Before optimization, it takes 12 seconds to optimize, it takes 2 seconds to complete.

private void Button1_Click (object sender, EventArgs e)                //Click event {    Stopwatch sw = new Stopwatch ();    Sw. Start ();    Stopwatchtableadapter adapter = new Stopwatchtableadapter ();    Adapter. Connection.Open ();                         Before looping, open the connection for    (int i = 0; i < i++)    {    adapter. Insert (i.ToString (), i.ToString (), i.tostring ());    Insert Data    }    adapter. Connection.close ();            After the loop, then close the connection    SW. Stop ();    MessageBox.Show (SW. Elapsed.tostring ());    Output time elapsed, in seconds}

Add a column of data to the already-built DataTable

Public DataTable Getonetwonum ()     {        datatable dt = Softwaretypebll.getonetwonum ();    Get a datatable        DataColumn dc = new DataColumn ("Twoname", typeof (System.String));  Set up a column of        dt. Columns.Add (DC);    Adds a column to the Datatable in a        foreach (DataRow row in dt. Rows)        {            string twostr = "random content";            row["twoname"] = Twostr;    Add new content to the specified column        }        return dt;    Returns the modified DataTable    }

"ADO" 8, the use of datasets