WINFORM CREATTION ABOUT SOMETHING THAT AMAZING
- 1. W I N F O R M S & A D O. N E T D A Y 0 3
- 2. W I N F O R M S & A D O . N E T Data Grid view Data Table SQL Data Adaptor SQL Command 2
- 3. A D O. N E T Microsoft's ActiveX Data Objects Applications used for computational features, it is also built for work to interact with a database. Databases deal with the purposes of retrieving and storing data. All applications interact with one or more databases. Every application in .Net requires ADO.NET-dependent database functionality. 3
- 4. ADO.NET is a data retrieval technique that allows access to data from different sources. A set of classes that have data access services for the programmer, providing a set of components for creating applications that share and distribute data. Allows connection to database systems such as Microsoft SQL Server, Oracle, Ms-Access ... using Open DataBase Connectivety (ODBC) or Object Linking And Embedding Database (OLE DB). ADO.NET is an integral part of .Net FrameWork, providing queries to relational data, XML data, and application data. 4
- 5. A D O. N E T V T H 働 V I 畛 N C S 畛 . N E T DataSet (m畛t 畛i t動畛ng DataSet) c坦 th畛 ch畛a m畛t l動畛ng l畛n d畛 li畛u: D畉ng b畉ng (畛i t動畛ng DataTable) V m畛i quan h畛 c畛a ch炭ng.(畛i t動畛ng DataRelation) C叩c rng bu畛c (畛i t動畛ng Constraint). trong cache b畛 nh畛 v sau 坦 c坦 th畛 動畛c xu畉t sang m畛t file b棚n ngoi hay m畛t dataset kh叩c v畛i s畛 h畛 tr畛 c畛a XML.. 5
- 6. N A M E S PA C E S I N A D O . N E T System.Data: class, interface, delegate, and enumeration defines and implements a part of ADO.NET structure SQL.Data.Common: classes shared by data providers. System.Data.Design: the classes used to generate arbitrary typed datasets. Sytem.Data.Odbc: dataprovider .Net FrameWork for ODBC System.Data.Oledb: dataprovider .Net FrameWork for OLEDB System.Data.Sql: classes that support specific SQL server functions System.Data.OracleClient: dataprovider .Net FrameWork for Oracle. System.Data.SqlClient dataprovider .Net FrameWork for SQL server. System.Data.SqlTypes: classes for SQL Server own data types. From the namespaces above, it shows that ADO.Net can do with old technologies such as: OLE DB and ODBC. However, SQL Server Data Provider will communicate directly with SQL Server without adding OLE DB (Object Linking and Embedding in Data Base) class or Open DataBase Connectivity, so this is the most effective connection. 6
- 7. A D O. N E T A RC H I T E C T U R E .Net FrameWork allows to access data from local or remote machines. ADO.net model and its components are used to process data in a database. Two key components in ADO.net are: Data Provider DataSets. 7
- 8. DataProvider: Connects to a data source and assists in accessing and processing data. Each data provider has its own namespace. For example we use SQL Server, we use Data Provider SQL Server (System.Data.SQLClient) for efficient access. Data Provider OLE DB supports access to earlier versions of SQL Server such as Access, DB 2 ... In case the application uses many databases at the same time such as Access and Oracle at the same time, OLE DB should be used for efficient access. Function: Provides access to data with an active connection to the data source Provides data transfer back and forth and disconnected data tables DataSet: Support the offline data storage, independent of a relational type, data update required. 8
- 9. C O N N E C T E D DA TA ADO th畉 h畛 tr動畛c s畛 d畛ng c董 ch畉 Connected Data, trong c董 ch畉 ny, k畉t n畛i s畉 動畛c thi畉t l畉p khi c坦 y棚u c畉u b畛i 畛ng d畛ng. K畉t n畛i s畉 duy tr狸 cho t畛i khi 畛ng d畛ng 動畛c 坦ng l畉i. C董 ch畉 ny th畛 hi畛n i畛m y畉u trong b畉o m畉t d畛 li畛u c滴ng nh動 lm gi畉m l動u l動畛ng m畉ng kh担ng c畉n thi畉t khi s畛 l動畛ng truy c畉p nhi畛u. 畛 kh叩c ph畛c ADO.Net gi畛i thi畛u c董 ch畉 DisConnected Data. 9
- 10. D I S C O N N E C T E D DATA Trong c董 ch畉 ny, k畉t n畛i 畉n c董 s畛 d畛 li畛u s畉 動畛c thi畉t l畉p khi 動畛c y棚u c畉u b畛i 畛ng d畛ng. M畛t khi y棚u c畉u 動畛c x畛 l箪, k畉t n畛i tr棚n 畉n c董 s畛 d畛 li畛u s畉 t畛 畛ng 坦ng l畉i. V狸 th畉 ADO.Net s畉 ti畉t ki畛m 動畛c ti nguy棚n c滴ng nh動 tng c動畛ng 叩ng k畛 cho b畉o m畉t d畛 li畛u khi k畉t n畛i ch畛 動畛c duy tr狸 trong m畛t th畛i gian r畉t ng畉n. 10
- 11. DA TA P R O V I D E R S Q L S E R V E R DataProvider cho SQL Server n畉m trong namespace System.Data.SqlClient. C叩c l畛p th動畛ng d湛ng : Sqlcommand: th畛c thi c叩c query, c但u l畛nh ho畉c th畛 t畛c l動u tr畛 SQL SlqConnection: t動畛ng tr動ng cho m畛t k畉t n畛i v畛i c董 s畛 d畛 li畛u SQL Server. SQLDataAdapter: th畛 hi畛n cho m畛t c畉u n畛i gi畛a m畛t dataset v ngu畛n d畛 li畛u. SqlDataReader: Cung c畉p m畛t lu畛ng d畛 li畛u ch畛 h動畛ng v畛 ph鱈a tr動畛c (forward only) v ch畛 畛c (read-only). SqlError : L動u tr畛 th担ng tin v畛 c叩c l畛i v c畉nh b叩o SQL Server. SqlException : 畛nh ngh挑a m畛t ngo畉i l畛(exception) 動a ra tr棚n m畛t l畛i. SqlParameter : t動畛ng tr動ng cho tham s畛 l畛nh. SqlTransaction : th畛 hi畛n cho m畛t giao t叩c SQL Server. 11
- 12. V D 畛 DATA P R O V I D E R S Q L S E R V E R Ch動董ng tr狸nh truy xu畉t d畛 li畛u t畛 NorthWind. L畉y t棚n v h畛 c畛a thnh vi棚n trong table. 12
- 13. Thi畉t k畉 giao di畛n : D湛ng ListView v Button control t畉o ra giao di畛n 13
- 14. X叩c 畛nh chu畛i k畉t n畛i 畉n database string strCon = "Data Source=.SQLEXPRESS;AttachDbFilename=C:SQL Server 2000 Sample DatabasesNORTHWND.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True"; M達 query 畛 l畉y d畛 li畛u 動畛c l動u vo trong chu畛i : string SQL = "select * from Customers " Khai b叩o c叩c bi畉n k畉t n畛i v datareader : SqlConnection Con = new SqlConnection(); SqlDataReader dr= null; 14
- 15. T畉o k畉t n畛i v m畛 trong kh畛i try-catch : try { SqlDataReader dr; Con.Open(); Con.ConnectionString = strCon; 畛 th畛c thi query, 畉u ti棚n t畉o m畛t 畛i t動畛ng l畛nh v sau 坦 t畉o m畛t datareader b畉ng c叩ch g畛i ExecuteReader(); tr棚n 畛i t動畛ng l畛nh tr棚n SqlCommand Com; Com = new SqlCommand(strSQL, Con); dr = Com.ExecuteReader(); Sau c湛ng kh畛i finally s畉 動畛c d湛ng 畛 坦ng c叩c k畉t n畛i. finally { Con.Dispose(); } 15
- 16. 畛 I T 働 畛 N G C O N N E C T I O N Tr動畛c khi c坦 th畛 t動董ng t叩c v畛i m畛t database, ta c畉n thi畉t l畉p m畛t session hay c嘆n g畛i l m畛t phi棚n lm vi畛c v畛i database server. 畛 畉t 動畛c m畛c 鱈ch b畉ng c叩ch s畛 d畛ng m畛t 畛i t動畛ng 動畛c g畛i l Connection b畉n ch畉t l m畛t instance c畛a l畛p th畛c thi giao di畛n System.Data.IDbConnection cho m畛t dataprovider c畛 th畛 no 坦. M畛t s畛 l畛p k畉t n畛i : ODBC // System.Data.ODBC // OdbcConnection Oracle // System.Data.Oracle.Client// OracleConnection SQL Server // System.Data.SQLServer // SQLConnection. 16
- 17. V D 畛 17
- 18. S Q L C O N N E C T I O N C L A S S L畛p SQL Connection 動畛c d湛ng 畛 k畉 n畛i v畛i c董 s畛 d畛 li畛u SQL Server. M畛t s畛 ph動董ng th畛c v thu畛c t鱈nh th動畛ng d湛ng ConnectionString : thu畛c t鱈nh ch畛 ra chu畛i 動畛c d湛ng 畛 m畛 m畛t c董 s畛 d畛 li畛u c畛a SQL Server State : thu畛c t鱈nh ch畛 tr畉ng th叩i hi畛n hnh c畛a k畉t n畛i Close : ph動董ng th畛c 坦ng k畉t n畛i hi畛n hnh Open : ph動董ng th畛c m畛 k畉t n畛i 畉n database v畛i th動畛c t鱈nh 動畛c ci 畉c trong ConnectionString StateChange : event x畉y ra khi tr畉ng th叩i k畉t n畛i thay 畛i. 18
- 19. V D 畛 19
- 20. 畛 I T 働 畛 N G C O M M A N D 畛i t動畛ng ny cho ph辿p ta th畛c thi c叩c l畛nh 畉n d畛 li畛u ngu畛n. M畛t khi 達 c坦 k畉t n畛i v畛i database, 畛i t動畛ng command cho ph辿p ch畛a v th畛c thi c叩c c但u l畛nh SQL 畉n database ny. N坦 c坦 th畛 動畛c d湛ng 畛 th畛c thi c叩c c但u l畛nh nh動 : Update Delete Insert Select 畛 th畛c thi c叩c c但u l畛nh trong SQL server, namespace System.Data.SqlClient.SqlCommand 動畛c d湛ng 畉n. 20
- 21. V D 畛 21
- 22. L 畛 P S Q L C O M M A N D L畛p SQLCommand cho ph辿p th畛c thi c叩c l畛nh 畉n c董 s畛 d畛 li畛u SQL Server C叩c thu畛c t鱈nh, ph動董ng th畛c v event th動畛ng d湛ng : CommandText : thu畛c t鱈nh ch畛 c叩c l畛nh 達 動畛c th畛c thi t畉i d畛 li畛u ngu畛n Connection : thu畛c t鱈nh k畉t h畛p l畛nh v畛i m畛t k畉t n畛i CommandTimeout : ph動董ng th畛c ch畛 ra th畛i gian ch畛 tr動畛c khi k畉t th炭c m畛t l畛nh v 動a ra th担ng b叩o l畛i. CommandType :ph動董ng th畛c tr畉 v畛 gi叩 tr畛 li棚n quan t畛i thu畛c t鱈nh CommandText ExecuteNonQuery: ph動董ng th畛c th畛c hi畛n c叩c c但u l畛nh SQL 畉n d畛 li畛u ngu畛n v nh畉n v畛 s畛 l動畛ng hng b畛 t叩c 畛ng v鱈 d畛 Update, Delete ExecuteReader: ph動董ng th畛c g畛i CommandText 畉n Connection v tr畉 v畛 c畛a 畛i t動畛ng data reader v hi畛n th畛 ExecuteScalar: ph動董ng th畛c th畛c thi c叩c c但u l畛nh truy v畉n v tr畉 v畛 1 gi叩 tr畛 董n. 22
- 23. V D 畛 : C O N N E C T I O N T E X T 23
- 24. V D 畛 : E X E C U T E S C A L A R 24
- 25. DATA S E T V DATA A DA P T O R Dataset : ADO.NET ch畛a d畛 li畛u trong DataSet c湛ng v畛i m畛i quan h畛 gi畛a c叩c d畛 li畛u 坦. DataSet gi畛ng nh動 m畛t h狸nh 畉nh v畛 database trong b畛 nh畛, c坦 th畛 c坦 nhi畛u DataTable v c叩c m畛i quan h畛 gi畛a ch炭ng 畉i di畛n b畛i c叩c DataRelation. Dataset 動畛c d湛ng 畛 th畛 hi畛n v c畉p nh畉t d畛 li畛u c坦 th畛 l畉y t畛 nhi畛n ngu畛n. Dataset h畛 tr畛 c董 ch畉 truy xu畉t ki畛u disconnect. C叩c ph動董ng th畛c h畛 tr畛 ki畛m so叩t s畛 thay 畛i hay c畉p nh畉t d畛 li畛u : 1. HasChanges() 2. HasErrors() 3. GetChanges() 4. AcceptChanges() 5. RejectChange() 25
- 26. DataTable : L m畛t thnh ph畉n trong DataSet, DataTable ch畛a d畛 li畛u c畛a m畛t b畉ng trong DataSet v thu畛c l畛p DataTable. DataTable bao g畛m : T畉p h畛p Columns thu畛c l畛p DataColumnCollection trong 坦 m畛i c畛t l m畛t 畛i t動畛ng thu畛c l畛p DataColumn. T畉p h畛p Rows thu畛c l畛p DataRowCollection trong 坦 m畛i d嘆ng l m畛t 畛i t動畛ng thu畛c l畛p DataRow. DataRelation : DataSet bao g畛m t畉p h畛p c叩c table 畉i di畛n b畛i c叩c 畛i t動畛ng DataTable v quan h畛 gi畛a c叩c table 坦 畉i di畛n b畛i c叩c 畛i t動畛ng DataRelation. 26
- 27. DataAdaptor :DataAdapter 動畛c ADO.NET cung c畉p nh動 m畛t c担ng c畛 gi炭p k畉t n畛i d畛 li畛u c畛a m畛t table trong DataSet v畛i database tr棚n Server. 畛 l畉y d畛 li畛u cho m畛t table trong DataSet, s畛 d畛ng ph動董ng th畛c Fill() 畛 c畉p nh畉t d畛 li畛u cho DataSet, s畛 d畛ng ph動董ng th畛c Update(). 畛 畛c d畛 li畛u, DataAdapter c湛ng SELECT query th担ng qua thu畛c t鱈nh SelectCommand. 畛 c畉p nh畉t d畛 li畛u, DataAdapter d湛ng c叩c c但u truy v畉n UPDATE, INSERT, DELETE th担ng qua c叩c thu畛c t鱈nh UpdateCommand, InsertCommand v DeleteCommand. 27
- 28. C叩c b動畛c th動畛ng th畉y khi s畛 d畛ng DataAdapter nh動 sau: 1. T畉o m畛t 畛i t動畛ng DataAdapter. 2. T畉o Select query cho 畛i t動畛ng SelectCommand. 3. T畉o ConnectionString cho 畛i t動畛ng Connection c畛a DataAdapter. 4. (C坦 ho畉c kh担ng) T畉o c叩c 畛i t動畛ng Command c畉p nh畉t d畛 li畛u. 5. G畛i ph動董ng th畛c Fill() 畛 l畉y d畛 li畛u cho m畛t DataSet. 6. G畛i ph動董ng th畛c Update() c畛a DataAdapter 畛 c畉p nh畉t d畛 li畛u t畛 DataSet. 28
- 29. V D 畛 29 da ds Fill(ds, product) Datatable dt(product) Adapter
- 30. DATA G R I D V I E W 畛i t動畛ng DataGridView d湛ng 畛 tr狸nh by d畛 li畛u trong 畛i t動畛ng DataSet, DataTable, DataView. D湛ng 畛 畛nh ngh挑a c叩c c畛t tr棚n DataGridView Control v th棚m c叩c control kh叩c nh動: Button, TextBox, ComboBox, CheckBox, Image, Link 30
- 31. C C T H U 畛 C T N H DataSource d湛ng 畛 i畛n d畛 li畛u t畛 畛i t動畛ng DataSet, DataTable, DataView. 31
- 32. T E X T B O X C叩c 担 trong DataGridView 動畛c xem nh動 l m畛t TextBox. 畛 th棚m m畛t TextBox hay m畛t 担 trong control DataGridView ch畛n R-Click-Add Column. 畛nh ngh挑a l畉i t棚n cho c畛t. 32
- 33. Th棚m t棚n cho c叩c tr動畛ng m畛i. L畉y d畛 li畛u cho c叩c tr動畛ng m畛i R-Click Edit Column Khai b叩o DataPropertyName t動董ng 畛ng v畛i t棚n c畛t. 33
- 34. 34
- 35. DAY 0 3 S T U D E N T L I S T F O R M U P D A T E D E L E T E F O R M A D O P R A C T I S E 35