ADO NET Namespace ADO NET u ADO NET
- Slides: 105
ADO. NET
Namespace-и на ADO. NET u Пространства от имена от ADO. NET v v v System. Data – основни архитектурни класове на ADO. NET System. Data. Common – общи класове за всички data Provider-и System. Data. Sql. Client и System. Data. Sql. Types – Data Provider класове за достъп до SQL Server System. Data. Ole. Db – връзка с Ole. DB System. Data. Odbc – връзка с ODBC System. Xml – връзка с XML
Еволюция на ADO към ADO. NET ADO Connection Command ADO. NET Xxx. Connection Xxx. Transaction Xxx. Command Data. Set Recordset Xxx. Data. Reader Xxx. Data. Adapter
Компоненти на ADO. NET Disconnected model Data. Set Data. Adapter SQL Server. NET Data Provider Ole. Db. NET Data Provider Connected model Data. Reader Oracle. NET Data Provider Command ODBC. NET Data Provider
Sql. Connection – пример const string CONNECTION_STRING = "Server=localhost; Database=Northwind; " + "Integrated Security=true; " + "Persist Security Info=false"; // Create the connection Sql. Connection con = new Sql. Connection(CONNECTION_STRING); using (con) { // Open connection con. Open(); // Use the connection here //. . . }
State. Change – пример private const string CONNECTION_STRING = "Server=. ; " + " Database=Pubs; Integrated Security=true"; private Sql. Connection m. Conn; private void Init. Connection() { m. Conn = new Sql. Connection(CONNECTION_STRING); m. Conn. State. Change += new State. Change. Event. Handler(Conn. State. Change); m. Conn. Open(); } private void Conn. State. Change(object sender, State. Change. Event. Args e) { Debug. Write. Line("SQL Server connection: " + e. Original. State. To. String() + " --> " + e. Current. State. To. String()); }
Info. Message – пример private const string CONNECTION_STRING = "Server=. ; " + "Database=Pubs; Integrated Security=true"; private Sql. Connection m. Conn; private void Init. Connection() { m. Conn = new Sql. Connection(CONNECTION_STRING); m. Conn. Info. Message += new Sql. Info. Message. Event. Handler(Conn. Info. Message); m. Conn. Open(); } private void Conn. Info. Message(object sender, Sql. Info. Message. Event. Args e) { Debug. Write. Line("SQL Server message: " + e. Message + " Source: " + e. Source); }
Класовете в свързана среда Sql. Data. Reader Xml. Reader Sql. Command Sql. Connection Sql. Parameter
Свързан модел – пример using System; using System. Data. Sql. Client; class Test. Sql. Command { private const string CONNECTION_STRING = "Server=. ; " + " Database=pubs; Integrated Security=true"; private const string COMMAND_SELECT_AUTHORS = "SELECT au_fname, au_lname, phone FROM authors"; static void Main() { Sql. Connection con = new Sql. Connection(CONNECTION_STRING); con. Open(); try { (примерът продължава)
Свързан модел – пример Sql. Command command = new Sql. Command(COMMAND_SELECT_AUTHORS, con); Sql. Data. Reader reader = command. Execute. Reader(); using (reader) { while (reader. Read()) { string first. Name = (String) reader["au_fname"]; string last. Name = (String) reader["au_lname"]; string phone = (String) reader["phone"]; Console. Write. Line("{0} {1} - {2}", first. Name, last. Name, phone); } } } finally { con. Close(); } } }
ASPX ADO. NET <%@ Import Namespace="System. Data. Ole. Db" %> <script runat="server"> sub Page_Load dim dbconn, sql, dbcomm, dbread dbconn=New Ole. Db. Connection ("Provider=Microsoft. Jet. OLEDB. 4. 0; data source=" & server. mappath("northwind. mdb")) dbconn. Open() sql="SELECT * FROM customers" dbcomm=New Ole. Db. Command(sql, dbconn) dbread=dbcomm. Execute. Reader() customers. Data. Source=dbread customers. Data. Bind() dbread. Close() dbconn. Close() end sub </script> <html> <body> <form runat="server"> <asp: Repeater id="customers" runat="server"> <Header. Template> <table border="1" width="100%"> <tr> <th>Companyname</th> <th>Contactname</th> <th>Address</th> <th>City</th> </tr> </Header. Template> <Item. Template> <tr> <td><%#Container. Data. Item("companyname")%></td> <td><%#Container. Data. Item("contactname")%></td> <td><%#Container. Data. Item("address")%></td> <td><%#Container. Data. Item("city")%></td> </tr> </Item. Template> <Footer. Template> </table> </Footer. Template> </asp: Repeater> </form> </body> </html>
Създаване на Sql. Command u Можете да създадете Sql. Command обект по няколко начина: v Програмно Sql. Command cmd = new Sql. Command( "SELECT * FROM Products, con); v v От Server Explorer във VS. NET От Toolbox във VS. NET
Параметрични заявки – пример private void Insert. Shipper(string a. Name, string a. Phone) { Sql. Command cmd. Insert. Shipper = new Sql. Command( "INSERT INTO Shippers(Company. Name, Phone) " + "VALUES (@Name, @Phone)", db. Connection); Sql. Parameter param. Name = new Sql. Parameter("@Name", Sql. Db. Type. NVar. Char); param. Name. Value = a. Name; cmd. Insert. Shipper. Parameters. Add(param. Name); Sql. Parameter param. Phone = new Sql. Parameter("@Phone", Sql. Db. Type. NVar. Char); param. Phone. Value = a. Phone; cmd. Insert. Shipper. Parameters. Add(param. Phone); cmd. Insert. Shipper. Execute. Non. Query(); }
Използване на транзакции u Работа с транзакции в SQL Server: BEGIN TRANSACTION DECLARE @order. Details. Error int, @product. Error int DELETE FROM "Order Details" WHERE Product. ID=42 SELECT @order. Details. Error = @@ERROR DELETE SELECT FROM Products @product. Error WHERE Product. ID=42 = @@ERROR IF @order. Details. Error = 0 AND @product. Error = 0 COMMIT TRANS ELSE ROLLBACK TRANS
Използване на транзакции u u Нивото на изолация се дефинира с енумерацията Isolation. Level Нива на изолация на Sql. Transaction v v v u Read. Uncommited Read. Commited Repeatable. Read Serializable Unspecified Пример: Sql. Transaction trans = db. Connection. Begin. Transaction(Isolation. Level. Serializable);
OLE DB Data Provider u Ole. Db. Connection – осъществява връзка с OLE DB източник на данни Ole. Db. Connection db. Conn = new Ole. Db. Connection( @"Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=C: My. DB. mdb; Persist Security Info=False"); u u Ole. Db. Command – изпълнява SQL команди върху OLE DB връзка към база данни Ole. Db. Parameter – параметър на команда Ole. Db. Data. Reader – за извличане на данни от команда, изпълнена през OLE DB Ole. Db. Data. Adapter – обменя данни между Data. Set обекти и OLE DB база данни
Връзка с OLE DB – пример u Имаме база данни C: Library. mdb, създадена с MS Access u В нея има таблица Users: u Използваме "Microsoft Jet 4. 0 Provider" за връзка от ADO. NET през OLE DB Създаваме Connection String чрез Ole. Db. Connection компонентата от Toolbox-a на VS. NET: u Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source= C: Library. mdb; Persist Security Info=False
Връзка с OLE DB – пример Ole. Db. Connection db. Conn = new Ole. Db. Connection( @"Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source" + @"=C: Library. mdb; Persist Security Info=False"); db. Conn. Open(); Ole. Db. Command cmd = new Ole. Db. Command( "INSERT INTO Users ([username], [password]) " + "VALUES (@user, @pass)", db. Conn); cmd. Parameters. Add("@user", Ole. Db. Type. Var. Char). Value = "new user name"; cmd. Parameters. Add("@pass", Ole. Db. Type. Var. Char). Value = "secret password"; int affected = cmd. Execute. Non. Query(); Console. Write. Line("{0} records were inserted", affected);
Работа с дати – пример CREATE TABLE Messages ( Msg. Id int identity not null primary key, Msg. Text nvarchar(1000), Msg. Date datetime –- Don’t use varchar for dates! ) public void Add. Msg(string a. Text, Date. Time a. Date) { Sql. Command cmd. Insert. Msg = new Sql. Command( "INSERT INTO Messages(Msg. Text, Msg. Date) " + "VALUES (@Msg. Text, @Msg. Date)", m. Db. Con); (примерът продължава)
Работа с дати – пример Sql. Parameter param. Msg. Text = new Sql. Parameter( "@Msg. Text", Sql. Db. Type. NVar. Char); param. Msg. Text. Value = a. Text; cmd. Insert. Msg. Parameters. Add(param. Msg. Text); Sql. Parameter param. Msg. Date = new Sql. Parameter( "@Msg. Date", Sql. Db. Type. Date. Time); param. Msg. Date. Value = a. Date; cmd. Insert. Msg. Parameters. Add(param. Msg. Date); cmd. Insert. Msg. Execute. Non. Query(); }
Модел на данните Data. Set Data. Table 1 Data. Table … Data. Table 2 1 … … 1 3 2 … …… 2 4 3 … …… 4 3 …… 4 … <? xml version="1. 0" standalone="yes"? > <xs: schema id="Data. Set. Users" target. Namespace="http: //www. tempuri. org/Data. Set. Users. xsd" xmlns: mstns="http: //www. tempuri. org/Data. Set 1. xsd" xmlns: xs="http: //www. w 3. org/2001/XMLSchema" xmlns: msdata="urn: schemas-microsoft-com: xml-msdata" attribute. Form. Default="qualified" element. Form. Default="qualified"> <xs: element name="Data. Set. Users" msdata: Is. Data. Set="true" msdata: Locale="bg-BG"> <xs: complex. Type> <xs: choice max. Occurs="unbounded"> <xs: element name="Users"> <xs: complex. Type> <xs: sequence> <xs: element name="username" type="xs: string" min. Occurs="0" /> <xs: element name="password" type="xs: string" min. Occurs="0" /> </xs: sequence> </xs: complex. Type> </xs: element> </xs: choice> </xs: complex. Type> </xs: element> </xs: schema> XML Schema
Работа с Data. Table – пример Data. Table tbl = new Data. Table("Authors"); tbl. Columns. Add("au_id", typeof(int)); tbl. Columns. Add("au_fname", typeof(string)); tbl. Columns. Add("au_lname", typeof(string)); tbl. Columns. Add("au_phone", typeof(string)); // The row is detached (not added to the table) Data. Row row = tbl. New. Row(); row[0] = 1; row[1] = "Branimir"; row[2] = "Giurov"; row["phone"] = "+359 2 XXXX"; tbl. Rows. Add(row);
Използване на ограничения (constraints) u Дефиниране на първичен ключ (Primary key) // Single column PK dt. Customers. Primary. Key = new Data. Column() { dt. Customers. Columns("Customer. ID") } // Multiple columns PK dt. Employees. Primary. Key = new Data. Column() { dt. Employees. Columns("Last. Name"), dt. Employees. Columns("First. Name") }
Използване на ограничения (constraints) u Дефиниране на Unique. Constraint и Foreign. Key. Constraint ds. Tables["Product"]. Constraints. Add( new Unique. Constraint("UC_Product. Name", ds. Tables["Product"]. Columns["Product. Name"])); Foreign. Key. Constraint cust. Order. FK = new Foreign. Key. Constraint("Cust. Order. FK", ds. Tables["Cust. Table"]. Columns["Customer. ID"], ds. Tables["Orders. Table"]. Columns["Customer. ID"]); cust. Order. FK. Delete. Rule = Rule. None; // Cannot delete a customer value // that has associated existing orders. ds. Tables["Orders. Table"]. Constraints. Add(cust. Order. FK);
Data. Relation обекти u Get. Child. Rows() – връща редовете наследници на даден ред ds. Northwind. Relations. Add("FK_Customers. Orders", dt. Customers. Columns["Customer. ID"], dt. Orders. Columns["Customer. ID"], true); // Process all customers and their orders foreach (Data. Row dr. Customer in ds. Northwind. Tables["Customers"]. Rows) { foreach (Data. Row dr. Order in dr. Customer. Get. Child. Rows("FK_Customer. Orders")) { // Do some work with the rows } }
Data. View клас u Аналогия на изгледите (View обектите) в базите от данни v v v Динамичен изглед на данните базиран на израз за филтриране Филтриране по версията на данните Сортиране по колона или колони Data. Table users. Table =. . . ; Data. View users. View = new Data. View(users. Table); // Show only modified versions of current rows and new rows users. View. Row. State. Filter = Data. View. Row. State. Modified. Current | Data. View. Row. State. Added;
Read. Xml и Write. Xml – пример Data. Set ds. Students = new Data. Set(); // A string that contains the XML data string xml. Student. Data = "<students><student>" + "<name>Petar Petrov</name><fn>12345</fn>" + "</student><student>" + "<name>Ivanov</name><fn>54321</fn>" + "</student></students>"; // A String. Reader to pass the XML to Read. Xml(. . . ) String. Reader sr. Xml. Students = new String. Reader(xml. Student. Data); ds. Students. Read. Xml(sr. Xml. Students, Xml. Read. Mode. Infer. Schema); sr. Xml. Students. Close(); (примерът продължава)
Read. Xml и Write. Xml – пример // A String. Writer to store the data from the Data. Set String. Writer sw. Students = new String. Writer(); ds. Students. Write. Xml(sw. Students, Xml. Write. Mode. Write. Schema); string str. Students = sw. Students. To. String(); sw. Students. Close(); // Print to the console the XML before reading it // in the Data. Set, and the XML produced by the // Data. Set's Write. Xml(. . . ) method Console. Write. Line("XML before: n" + xml. Student. Data + "nnn. XML after: n " + str. Students);
Използване на Data. Adapter u Свойства на Data. Adapter v v u Accept. Changes. During. Fill Continue. Update. On. Error Събития на Data. Adapter v v v Fill. Error Row. Updating Row. Updated
Data. Adapter – архитектура Select. Command Insert. Command Sql. Data. Adapter Update. Command Delete. Command Sql. Connection
Data. Adapter – пример static void Main() { string str. Con = "Data Source=(local); " + "Integrated Security=SSPI; Database=Northwind"; Sql. Connection cn. Northwind = new Sql. Connection(str. Con); Sql. Command cmd. Select = Create. Select. Command(cn. Northwind); Sql. Data. Adapter da. Employees = new Sql. Data. Adapter(cmd. Select); Data. Set ds. Northwind = new Data. Set(); da. Employees. Fill(ds. Northwind, "Employees"); // Set the Auto. Increment property of Employee. ID column Data. Table employees. Table = ds. Northwind. Tables["Employees"]; Data. Column column. Employee. Id = employees. Table. Columns["Employee. ID"]; column. Employee. Id. Auto. Increment = true; column. Employee. Id. Auto. Increment. Seed = -1; column. Employee. Id. Auto. Increment. Step = -1; (примерът продължава)
Data. Adapter – пример // Create the commands for the data adapter da. Employees. Insert. Command = Create. Insert. Command(cn. Northwind); da. Employees. Delete. Command = Create. Delete. Command(cn. Northwind); da. Employees. Update. Command = Create. Update. Command(cn. Northwind); // Add new record and update the database Data. Row row = employees. Table. New. Row(); row["Last. Name"] = "Ivanov"; row["First. Name"] = "Ivan"; employees. Table. Rows. Add(row); da. Employees. Update(ds. Northwind, "Employees"); Console. Write. Line("Inserted row id={0}. ", row["Employee. ID"]); // Change the added record and update the database row["Last. Name"] = "Petrov"; da. Employees. Update(ds. Northwind, "Employees"); Console. Write. Line("Updated the row. "); (примерът продължава)
Data. Adapter – пример // Delete the added record and update the database row. Delete(); da. Employees. Update(ds. Northwind, "Employees"); Console. Write. Line("Deleted the row. "); } static Sql. Command Create. Select. Command(Sql. Connection a. Connection) { string str. Select = "SELECT Employee. ID, Last. Name, First. Name" + " FROM Employees"; Sql. Command cmd. Select = new Sql. Command(str. Select, a. Connection); return cmd. Select; } static Sql. Command Create. Insert. Command(Sql. Connection a. Connection) { string str. Insert = "INSERT Employees(Last. Name, First. Name) "+ "VALUES(@Last. Name, @First. Name); " + "SET @Employee. ID=Scope_Identity()"; (примерът продължава)
Data. Adapter – пример Sql. Command cmd. Insert = new Sql. Command(str. Insert, a. Connection); Sql. Parameter. Collection cparams = cmd. Insert. Parameters; Sql. Parameter emp. ID = cparams. Add("@Employee. ID", Sql. Db. Type. Int, 0, "Employee. ID"); emp. ID. Direction = Parameter. Direction. Output; cparams. Add("@Last. Name", Sql. Db. Type. NVar. Char, 20, "Last. Name"); cparams. Add("@First. Name", Sql. Db. Type. NVar. Char, 10, "First. Name"); return cmd. Insert; } static Sql. Command Create. Update. Command(Sql. Connection a. Connection) { string str. Update = "UPDATE Employees SET " + "Last. Name=@Last. Name, First. Name=@First. Name " + "WHERE Employee. ID=@Employee. ID"; Sql. Command cmd. Update = new Sql. Command(str. Update, a. Connection); Sql. Parameter. Collection cparams = cmd. Update. Parameters; (примерът продължава)
Data. Adapter – пример Sql. Parameter emp. ID = cparams. Add("@Employee. ID", Sql. Db. Type. Int, 0, "Employee. ID"); emp. ID. Source. Version = Data. Row. Version. Original; cparams. Add("@Last. Name", Sql. Db. Type. NVar. Char, 20, "Last. Name"); cparams. Add("@First. Name", Sql. Db. Type. NVar. Char, 10, "First. Name"); return cmd. Update; } static Sql. Command Create. Delete. Command(Sql. Connection a. Connection) { string str. Delete = "DELETE FROM Employees " + "WHERE Employee. ID = @Employee. ID"; Sql. Command cmd. Delete = new Sql. Command(str. Delete, a. Connection); Sql. Parameter emp. ID = cmd. Delete. Parameters. Add( "@Employee. ID", Sql. Db. Type. Int, 0, "Employee. ID"); emp. ID. Source. Version = Data. Row. Version. Original; return cmd. Delete; }
Data. Set. Get. Changes() – пример if(! my. Data. Set. Has. Changes(Data. Row. State. Modified)) return; // Create temporary Data. Set variable Data. Set modified. Data. Set; // Get. Changes for modified rows only modified. Data. Set = my. Data. Set. Get. Changes( Data. Row. State. Modified); // Check the Data. Set for errors if(modified. Data. Set. Has. Errors) { // Insert code to resolve errors } // After fixing errors, update the data source // with the Data. Adapter used to create the Data. Set adp. Update(modified. Data. Set);
Несвързан модел – типичен сценарий на работа 4. Прилагаме направените промени и разрешаваме конфликтите user. Data. Adapter. Row. Updated += new Sql. Row. Updated. Event. Handler(On. Row. Updated); private void On. Row. Updated(object sender, Sql. Row. Updated. Event. Args e) { // Handle the conflict … е. Status = Update. Status. Continue; } user. Data. Adapter. Update(ds. Changes); 5. Зареждаме отново Data. Set-а от базата данни, за да работим с актуални данни user. Data. Adapter. Fill(ds. Users);
Xml. Data. Document – пример Data. Set my. Data. Set = new Data. Set(); // … Fill the Data. Set … Xml. Data. Document xml. Doc = new Xml. Data. Document(my. Data. Set); // Get all elements with Product. ID = 43 Xml. Node. List node. List = xml. Document. Element. Select. Nodes("descendant: : Customers[*/Order. Details/ Product. ID=43]"); foreach (Xml. Node my. Node in node. List) { Data. Row customer = xml. Doc. Get. Row. From. Element( (Xml. Element)my. Node); Console. Write. Line(customer["Company. Name"]); }
Ado.net vb.net
2160711
Ado.net overview
¿qué es ado.net?
Ado.net c
Sqlite net framework
Ado net rest
Ado.net active directory
What are the ado.net objectives
Describe about ado objects
Executereader
Ado.net diagram
Ado.net objects
Linq vs ado.net
Ado.net object model
Connection-oriented
Include cstdio
Identify the type of namespace: "challenger.ate.tbda.edu.".
Namespace in c++
C#10 namespace
C include iostream
Using namespace std c++
Using namespace std
윤년 순서도
Cppinclude
Iostream
#include iostream using namespace std
Using namespace std
Javax.xml.bind.jaxbcontext not present
In a namespace each name is made of several parts
System.collections.generic namespace
Fungsi using namespace std
#include iostream #include string using namespace std
#include iostream using namespace std
Namespace python
Namespace in c++
Include iostream using namespace std int main
Namespace 意思
Using namespace std
Root cern download
Namespace in c++
Pigou féle adó
Much ado about nothing summary act 1
Locucao verbal
Ado mision y vision
What happens in act 2 of much ado about nothing
Facts about much ado about nothing
Ado
He is of a very melancholy disposition
Ado ido to so cho gerundio
Access vba ado
As ado
Allusions in much ado about nothing
Ado atlas
Much ado
Much ado about nothing summary act 1
Don john quotes
Much ado
What is ado
Formação do imperativo afirmativo
Muhammad yusufga tarif
Pigou féle adó
Diabete mellito in ado
Ado to opc
I do much wonder that one man
Fio fis fit
Adodb recordset find
Imperativos ejemplos en ingles
Ejemplos de prefijos con su significado
Plot of much ado about nothing
Ado 5ch
Ado integration adapters
Achmed lach net ich krieg mein tach net
Schedule source
Kubilay oral
Steve kirsch net worth
Base of pentagonal prism
Proxy.hinet.net
Protective leaf like enclosure for the flower bud
Network traffic creater alpha skynet
Net cisco academy
Drcr.net protocol
Ias 2 net realisable value
Formula of net price
Chem quiz.net
Net benefit
Azure bug bounty
Distribution requirements planning
Baypa.net
Noncarbohydrate precursors
Vb net data types
Gxdaminh
Net lysis buffer
Zilali ilham
Destiny.sandi.net
Closing entries accounting
Cecilio pedro net worth
Active transport examples
Neisd.net
