ADO.net //Overview Data-->DataReader-->Data Provider--> DataSet Data Provider: Connection, Command, DataAdapter DataSet: DataRelationCollection, DataTable collection(including DataTable)) DataTable: DataRowCollection, DataColumnColl, ConstrainColl DataAdapter: retrieve data from DB, fill tables in DataSet
//SQL Server .net data provider using System.Data using System.Data.SqlClient; ... string strConnection = "server=allan; uid=sa; pwd=; database=northwind"; string strCommand = "Select productName, unitPrice from Products"; SqlDataAdapter dataAdapter = new SqlDataAdapter(strCommand, strConnection); DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet, "Products"); DataTable dataTable = dataSet.Table[0]; foreach(DataRow row in dataTable.Rows) { lbProducts.Items.Add(row["ProductName"]+"($" +row["UnitProice"] + ")"); } //OLEDB Data provider using System.Data.OleDb; ... string strConnection = "provider=Microsoft.JET.OLEDB.4.0; data source=c:\\nwind.mdb"; OleDbDataAdapter dataAdapter = ... //DataGrids using System.Data.SqlClient public class Form1: System.Windows.Forms.Form { private System.Windows.Forms.DataGrid dgOrders; private System.Data.DataSet dataSet; private System.Data.SqlClient.Sqlconnection connection; private System.Data.SqlClient.SqlCommand; private System.Data.SqlClient.SqlDataAdapter dataAdapter; private void Form1_Load(object sender, System.EventArgs e) { string connectionString = "server=allan; uid=sa; pwd=;database=northWind"; connection = new System.Data.SqlClient.Sqlconnection(connectionString); connection.Open(); dataSet = new System.Data.DataSet(); dataSet.CaseSensitive = true; command = new System.Data.SqlClient.SqlCommand(); command.Connection = connection; command.CommandText = "Select * from Orders"; dataAdapter = new System.DataSqlClient.SqlAdapter(); //DataAdapter has SelectCommand, InsertCommand, UpdaterCommand //and DeleteCommand dataAdapter.SelectCommand = command; dataAdapter.TableMappings.Add("Table", "Orders"); dataAdapter.Fill(dataSet); ProductDataGrid.DataSource = dataSet.Table["Orders"].DefaultView; //Data Relationships, add code below command2 = new System.Data.SqlClient(); command2.Connection = connection; command2.CommandText = "Select * form [order details]"; dataAdapter2 = new System.Data.SqlClient.SqlDataAdapter(); dataAdapter2.SelectCommand = command2; dataAdapter2.TableMappings.Add("Table", "Details"); dataAdatper2.Fill(dataSet); System.Data.DataRelation dataRelation; System.Data.DataColumn datacolumn1; System.Data.DataColumn datacolumn2; dataColumn1 = dataSet.Table["Orders"].Columns["OrderID"]; dataColumn2 = dataSet.Table["Details"].Columns["OrderID"]; dataRelation new System.Data.DataRelation("OrdersToDetails", dataColumn1, dataColumn2); dataSet.Relations.Add(dataRelation); productDataGrid.dataSource = dataset.DefaultViewManger; productDataGrid.DataMember = "Orders"; //display Order table, it has mapping to Order Detail } }
//Update Data using ADO.net string cmd = "update Products set ..."; ... //creat connection, comand obj command.Connection = connection; command.CommandText=cmd; command.ExecuteNonQuery(); //Transaction 1.SQL Transaction 2. Connection Transaction //1. SQL Transaction //creat connection and command obj connnetion.open(); command.Connection = conntection; command.CommandText ="<storedProcedureName>"; //SP has used transaction command.CommandType= CommandType.StoredProcedure; System.Data.SqlClient.SqlParamenter param; param = command.Parameters.Add("@ProductID", SqlDbType.Int); param.Direction = ParameterDirection.Input; param.Value = txtProductID.Text.Trim(); ... //pass all parameter need by StoredProcedure command.ExecuteNonQuery(); //2. Connection Transaction //create connection and command obj ... System.DataSqlClient.SqlTransaction transaction; transaction = connection.BeginTransaction(); command.Transaction = transaction; command.Connection = connection; try { command.CommandText="<SP>"; //this SP has no transaction in it command.CommandType = CommandType.StoredProcedure; System.DataSqlClient.SqlParameter param; .. } catch (Exception ex) { //give Err message transaction.Rollback(); } //Update DataSet, then update DB at once //create connection, command obj, using command.Transaction ... param = command.Parameters.Add("@QupplierID", SqlDbType.Int); param.Direction = ParameterDirection.Input; param.SourceColumn = "SupplierID"; param.SourceVersion = DataRowVersion.Current; //which version try { //ADO.net will loop each row to update DB int rowsUpdated = dataAdapter.Update(dataSet, "Products"); transaction.Commit(); } catch { transactrion.Rollback(); } // Concurrency Update Database //compare will original data, avoid conflict //Give SQL SP, both Original and Current Data as parameter //SQL will write like this: update ... where ... SupplierID = @OldSupplierID
//original version param = command.Parameters.Add("@OldSupplierID", SqlDbtype.Int); param.Driection = ParameterDiretion.Input; param.SourceColumn ="SupplierID"; param.SourceVersion = DataRowVersion.Original; //current version param = command.Parameters.Add("@SupplierID", SqlDbtype.Int); param.Driection = ParameterDiretion.Input; param.SourceColumn ="SupplierID"; param.SourceVersion = DataRowVersion.Current;
//SqlCommandBuilder SqlCommandBuilder bldr = new SqlCommandBuilder(dataAdapter); dataAdapter.UpdateCommand = bldr.GetUpdateCommand(); dataAdapter.DeleteCommand = bldr.GetDelteCommand(); dataAdapter.InsertCommand = bldr.GetInsertCommand(); try { //This need not SQL, for bldr has build it for us. int rowsUpdated = dataAdapter.Update(dataSet, "Products"); } catch {}
[C语言系列]NET 中C#的switch语句的语法 [系统软件]托拽Explore中的文件到VB.net的窗口 [系统软件]Boost库在XP+Visual C++.net中的安装 [常用软件]新配色面板:Paint.Net3.0RC1官方下载 [常用软件]用内建的“Net Meeting”聊天 [VB.NET程序]Henry的VB.NET之旅(三)—共享成员 [VB.NET程序]Henry的VB.NET之旅(二)—构造与析构 [VB.NET程序]Henry的VB.NET之旅(一)—失踪的窗体 [VB.NET程序]在托盘上显示Balloon Tooltip(VB.NET) [VB.NET程序]Henry手记-VB.NET中动态加载Treeview节点(二)
|