转摘:ADO.NET 学习笔记
1
2
//Overview
3
Data-->DataReader-->Data Provider--> DataSet
4
Data Provider: Connection, Command, DataAdapter
5
DataSet: DataRelationCollection,
6
DataTable collection(including DataTable))
7
DataTable: DataRowCollection, DataColumnColl, ConstrainColl
8
DataAdapter: retrieve data from DB, fill tables in DataSet
9
10
11
//SQL Server .net data provider
12
using System.Data
13
using System.Data.SqlClient;
14
15
string strConnection = "server=allan; uid=sa; pwd=; database=northwind";
16
string strCommand = "Select productName, unitPrice from Products";
17
SqlDataAdapter dataAdapter = new SqlDataAdapter(strCommand, strConnection);
18
DataSet dataSet = new DataSet();
19
dataAdapter.Fill(dataSet, "Products");
20
DataTable dataTable = dataSet.Table[0];
21
foreach(DataRow row in dataTable.Rows) {
22
lbProducts.Items.Add(row["ProductName"]+"($" +row["UnitProice"] + ")");
23
}
24
25
//OLEDB Data provider
26
using System.Data.OleDb;
27
28
string strConnection = "provider=Microsoft.JET.OLEDB.4.0; data source=c:\\nwind.mdb";
29
OleDbDataAdapter dataAdapter =
30
31
//DataGrids
32
using System.Data.SqlClient
33
public class Form1: System.Windows.Forms.Form
34
{
35
private System.Windows.Forms.DataGrid dgOrders;
36
private System.Data.DataSet dataSet;
37
private System.Data.SqlClient.Sqlconnection connection;
38
private System.Data.SqlClient.SqlCommand;
39
private System.Data.SqlClient.SqlDataAdapter dataAdapter;
40
41
private void Form1_Load(object sender, System.EventArgs e)
42
{
43
string connectionString = "server=allan; uid=sa; pwd=;database=northWind";
44
connection = new System.Data.SqlClient.Sqlconnection(connectionString);
45
connection.Open();
46
dataSet = new System.Data.DataSet();
47
dataSet.CaseSensitive = true;
48
49
command = new System.Data.SqlClient.SqlCommand();
50
command.Connection = connection;
51
command.CommandText = "Select * from Orders";
52
dataAdapter = new System.DataSqlClient.SqlAdapter();
53
//DataAdapter has SelectCommand, InsertCommand, UpdaterCommand
54
//and DeleteCommand
55
dataAdapter.SelectCommand = command;
56
dataAdapter.TableMappings.Add("Table", "Orders");
57
dataAdapter.Fill(dataSet);
58
ProductDataGrid.DataSource = dataSet.Table["Orders"].DefaultView;
59
60
//Data Relationships, add code below
61
command2 = new System.Data.SqlClient();
62
command2.Connection = connection;
63
command2.CommandText = "Select * form [order details]";
64
dataAdapter2 = new System.Data.SqlClient.SqlDataAdapter();
65
dataAdapter2.SelectCommand = command2;
66
dataAdapter2.TableMappings.Add("Table", "Details");
67
dataAdatper2.Fill(dataSet);
68
69
System.Data.DataRelation dataRelation;
70
71
System.Data.DataColumn datacolumn1;
72
System.Data.DataColumn datacolumn2;
73
dataColumn1 = dataSet.Table["Orders"].Columns["OrderID"];
74
dataColumn2 = dataSet.Table["Details"].Columns["OrderID"];
75
dataRelation new System.Data.DataRelation("OrdersToDetails", dataColumn1, dataColumn2);
76
dataSet.Relations.Add(dataRelation);
77
productDataGrid.dataSource = dataset.DefaultViewManger;
78
productDataGrid.DataMember = "Orders"; //display Order table, it has mapping to Order Detail
79
80
}
81
}
82
83
//Update Data using ADO.net
84
string cmd = "update Products set ";
85
86
//creat connection, comand obj
87
command.Connection = connection;
88
command.CommandText=cmd;
89
command.ExecuteNonQuery();
90
91
//Transaction 1.SQL Transaction 2. Connection Transaction
92
93
//1. SQL Transaction
94
//creat connection and command obj
95
connnetion.open();
96
command.Connection = conntection;
97
command.CommandText ="<storedProcedureName>"; //SP has used transaction
98
command.CommandType= CommandType.StoredProcedure;
99
System.Data.SqlClient.SqlParamenter param;
100
param = command.Parameters.Add("@ProductID", SqlDbType.Int);
101
param.Direction = ParameterDirection.Input;
102
param.Value = txtProductID.Text.Trim();
103
//pass all parameter need by StoredProcedure
104
command.ExecuteNonQuery();
105
106
//2. Connection Transaction
107
//create connection and command obj
108
109
System.DataSqlClient.SqlTransaction transaction;
110
transaction = connection.BeginTransaction();
111
command.Transaction = transaction;
112
command.Connection = connection;
113
try
114
{
115
command.CommandText="<SP>"; //this SP has no transaction in it
116
command.CommandType = CommandType.StoredProcedure;
117
System.DataSqlClient.SqlParameter param;
118
..
119
}
120
catch (Exception ex)
121
{
122
//give Err message
123
transaction.Rollback();
124
}
125
126
//Update DataSet, then update DB at once
127
//create connection, command obj, using command.Transaction
128
129
param = command.Parameters.Add("@QupplierID", SqlDbType.Int);
130
param.Direction = ParameterDirection.Input;
131
param.SourceColumn = "SupplierID";
132
param.SourceVersion = DataRowVersion.Current; //which version
133
try
134
{ //ADO.net will loop each row to update DB
135
int rowsUpdated = dataAdapter.Update(dataSet, "Products");
136
transaction.Commit();
137
}
138
catch
139
{
140
transactrion.Rollback();
141
}
142
143
144
// Concurrency Update Database
145
//compare will original data, avoid conflict
146
//Give SQL SP, both Original and Current Data as parameter
147
//SQL will write like this: update where SupplierID = @OldSupplierID
148
149
//original version
150
param = command.Parameters.Add("@OldSupplierID", SqlDbtype.Int);
151
param.Driection = ParameterDiretion.Input;
152
param.SourceColumn ="SupplierID";
153
param.SourceVersion = DataRowVersion.Original;
154
//current version
155
param = command.Parameters.Add("@SupplierID", SqlDbtype.Int);
156
param.Driection = ParameterDiretion.Input;
157
param.SourceColumn ="SupplierID";
158
param.SourceVersion = DataRowVersion.Current;
159
160
//SqlCommandBuilder
161
SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter);
162
dataAdapter.UpdateCommand = bldr.GetUpdateCommand();
163
dataAdapter.DeleteCommand = bldr.GetDelteCommand();
164
dataAdapter.InsertCommand = bldr.GetInsertCommand();
165
try
166
{
167
//This need not SQL, for bldr has build it for us.
168
int rowsUpdated = dataAdapter.Update(dataSet, "Products");
169
}
170
catch {}
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
22
![](/Images/OutliningIndicators/InBlock.gif)
23
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
29
![](/Images/OutliningIndicators/None.gif)
30
![](/Images/OutliningIndicators/None.gif)
31
![](/Images/OutliningIndicators/None.gif)
32
![](/Images/OutliningIndicators/None.gif)
33
![](/Images/OutliningIndicators/None.gif)
34
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
35
![](/Images/OutliningIndicators/InBlock.gif)
36
![](/Images/OutliningIndicators/InBlock.gif)
37
![](/Images/OutliningIndicators/InBlock.gif)
38
![](/Images/OutliningIndicators/InBlock.gif)
39
![](/Images/OutliningIndicators/InBlock.gif)
40
![](/Images/OutliningIndicators/InBlock.gif)
41
![](/Images/OutliningIndicators/InBlock.gif)
42
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
43
![](/Images/OutliningIndicators/InBlock.gif)
44
![](/Images/OutliningIndicators/InBlock.gif)
45
![](/Images/OutliningIndicators/InBlock.gif)
46
![](/Images/OutliningIndicators/InBlock.gif)
47
![](/Images/OutliningIndicators/InBlock.gif)
48
![](/Images/OutliningIndicators/InBlock.gif)
49
![](/Images/OutliningIndicators/InBlock.gif)
50
![](/Images/OutliningIndicators/InBlock.gif)
51
![](/Images/OutliningIndicators/InBlock.gif)
52
![](/Images/OutliningIndicators/InBlock.gif)
53
![](/Images/OutliningIndicators/InBlock.gif)
54
![](/Images/OutliningIndicators/InBlock.gif)
55
![](/Images/OutliningIndicators/InBlock.gif)
56
![](/Images/OutliningIndicators/InBlock.gif)
57
![](/Images/OutliningIndicators/InBlock.gif)
58
![](/Images/OutliningIndicators/InBlock.gif)
59
![](/Images/OutliningIndicators/InBlock.gif)
60
![](/Images/OutliningIndicators/InBlock.gif)
61
![](/Images/OutliningIndicators/InBlock.gif)
62
![](/Images/OutliningIndicators/InBlock.gif)
63
![](/Images/OutliningIndicators/InBlock.gif)
64
![](/Images/OutliningIndicators/InBlock.gif)
65
![](/Images/OutliningIndicators/InBlock.gif)
66
![](/Images/OutliningIndicators/InBlock.gif)
67
![](/Images/OutliningIndicators/InBlock.gif)
68
![](/Images/OutliningIndicators/InBlock.gif)
69
![](/Images/OutliningIndicators/InBlock.gif)
70
![](/Images/OutliningIndicators/InBlock.gif)
71
![](/Images/OutliningIndicators/InBlock.gif)
72
![](/Images/OutliningIndicators/InBlock.gif)
73
![](/Images/OutliningIndicators/InBlock.gif)
74
![](/Images/OutliningIndicators/InBlock.gif)
75
![](/Images/OutliningIndicators/InBlock.gif)
76
![](/Images/OutliningIndicators/InBlock.gif)
77
![](/Images/OutliningIndicators/InBlock.gif)
78
![](/Images/OutliningIndicators/InBlock.gif)
79
![](/Images/OutliningIndicators/InBlock.gif)
80
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
81
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
82
![](/Images/OutliningIndicators/None.gif)
83
![](/Images/OutliningIndicators/None.gif)
84
![](/Images/OutliningIndicators/None.gif)
85
![](/Images/OutliningIndicators/None.gif)
86
![](/Images/OutliningIndicators/None.gif)
87
![](/Images/OutliningIndicators/None.gif)
88
![](/Images/OutliningIndicators/None.gif)
89
![](/Images/OutliningIndicators/None.gif)
90
![](/Images/OutliningIndicators/None.gif)
91
![](/Images/OutliningIndicators/None.gif)
92
![](/Images/OutliningIndicators/None.gif)
93
![](/Images/OutliningIndicators/None.gif)
94
![](/Images/OutliningIndicators/None.gif)
95
![](/Images/OutliningIndicators/None.gif)
96
![](/Images/OutliningIndicators/None.gif)
97
![](/Images/OutliningIndicators/None.gif)
98
![](/Images/OutliningIndicators/None.gif)
99
![](/Images/OutliningIndicators/None.gif)
100
![](/Images/OutliningIndicators/None.gif)
101
![](/Images/OutliningIndicators/None.gif)
102
![](/Images/OutliningIndicators/None.gif)
103
![](/Images/OutliningIndicators/None.gif)
104
![](/Images/OutliningIndicators/None.gif)
105
![](/Images/OutliningIndicators/None.gif)
106
![](/Images/OutliningIndicators/None.gif)
107
![](/Images/OutliningIndicators/None.gif)
108
![](/Images/OutliningIndicators/None.gif)
109
![](/Images/OutliningIndicators/None.gif)
110
![](/Images/OutliningIndicators/None.gif)
111
![](/Images/OutliningIndicators/None.gif)
112
![](/Images/OutliningIndicators/None.gif)
113
![](/Images/OutliningIndicators/None.gif)
114
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
115
![](/Images/OutliningIndicators/InBlock.gif)
116
![](/Images/OutliningIndicators/InBlock.gif)
117
![](/Images/OutliningIndicators/InBlock.gif)
118
![](/Images/OutliningIndicators/InBlock.gif)
119
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
120
![](/Images/OutliningIndicators/None.gif)
121
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
122
![](/Images/OutliningIndicators/InBlock.gif)
123
![](/Images/OutliningIndicators/InBlock.gif)
124
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
125
![](/Images/OutliningIndicators/None.gif)
126
![](/Images/OutliningIndicators/None.gif)
127
![](/Images/OutliningIndicators/None.gif)
128
![](/Images/OutliningIndicators/None.gif)
129
![](/Images/OutliningIndicators/None.gif)
130
![](/Images/OutliningIndicators/None.gif)
131
![](/Images/OutliningIndicators/None.gif)
132
![](/Images/OutliningIndicators/None.gif)
133
![](/Images/OutliningIndicators/None.gif)
134
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
135
![](/Images/OutliningIndicators/InBlock.gif)
136
![](/Images/OutliningIndicators/InBlock.gif)
137
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
138
![](/Images/OutliningIndicators/None.gif)
139
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
140
![](/Images/OutliningIndicators/InBlock.gif)
141
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
142
![](/Images/OutliningIndicators/None.gif)
143
![](/Images/OutliningIndicators/None.gif)
144
![](/Images/OutliningIndicators/None.gif)
145
![](/Images/OutliningIndicators/None.gif)
146
![](/Images/OutliningIndicators/None.gif)
147
![](/Images/OutliningIndicators/None.gif)
148
![](/Images/OutliningIndicators/None.gif)
149
![](/Images/OutliningIndicators/None.gif)
150
![](/Images/OutliningIndicators/None.gif)
151
![](/Images/OutliningIndicators/None.gif)
152
![](/Images/OutliningIndicators/None.gif)
153
![](/Images/OutliningIndicators/None.gif)
154
![](/Images/OutliningIndicators/None.gif)
155
![](/Images/OutliningIndicators/None.gif)
156
![](/Images/OutliningIndicators/None.gif)
157
![](/Images/OutliningIndicators/None.gif)
158
![](/Images/OutliningIndicators/None.gif)
159
![](/Images/OutliningIndicators/None.gif)
160
![](/Images/OutliningIndicators/None.gif)
161
![](/Images/OutliningIndicators/None.gif)
162
![](/Images/OutliningIndicators/None.gif)
163
![](/Images/OutliningIndicators/None.gif)
164
![](/Images/OutliningIndicators/None.gif)
165
![](/Images/OutliningIndicators/None.gif)
166
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
167
![](/Images/OutliningIndicators/InBlock.gif)
168
![](/Images/OutliningIndicators/InBlock.gif)
169
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
170
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)