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 {}
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 {}