Database programming with ADO.net:-
- Generally, the output of the program will be stored in RAM.
- RAM is volatile, that is after every execution RAM memory will be cleared.
- To store the data permanently, .net is providing two ways
- File handling
- Database handling
Problems with File handling while storing real-time data:-
101 teja 5000
102 raviteja 2000
102 Ravi xyz
103 ravi 6000
- The number of columns is not restricted.
- The type of data is not allowed.
- Possibility for data redundancy.
- Different to manipulate the data.
- No security for the data.
- To overcome these problems, ANSI introduced database concepts
- A collection of interrelated data is called as databases are divided into three types
- Create table EMP ( eno number, ename varchar 2 (10),…………..)
- Create table XYZ
ADO.NET (Active X Data objects for .net)
- ADO.NET is an object library that is used to communicate with any database.
- Object library means a set of predefined class (a set of the namespace)
- ADO.NET is used to develop client Server Applications.
- NET supports 2 types of connection
- Managed Connection
- Unmanaged Connection
Managed connections work with the help of the TDS (Tabular data String) Protocol.
Unmanaged connections work with the help of OLEDB(object linking and Embedding Databases ) provides.
- OLEDB providers are DLL files, which are COM components As Com is platform-dependent, hence connection on OLEDB are called an Unmanaged connection.
Managed connections are faster in data accessing.
- To work with ADO.net, Microsoft introduced System. Data Assembly, which contains 8 Namespaces.
- these Namespaces are divided into 5 Groups.
- System. Data
- System. Data. SQL Types
Unmanaged Name Spaces 4.System. Data. OLEDB (Supports all type of databases)
- System. Data. SQL client (SQL Server only)
- System. Data. oracle client (for oracle only ) (2.0)
- System. Data. ODBC (supports all Databases)
LINQ Related Name Spaces
- System. Data. LINQ
EX 1:- With oracle over Unmanaged connection
- A program to create a table in oracle
A procedure to find provider names automatically.
- Open notepad, save the file with the name
“one. UDL” and close it File name extension universal data Linker
- Double click on One .Udl file
- Select provider
- Choose “Micro-soft oledb provider for oracle ” and click Next
- Provide User name and password of oracle and click ok.
- Right-click on one.udl àopen with à note pad
- Open WFAP
- Place a button
Using System. Data. Oledb;
Code for Button 1 _click
Oledb connection con = new oledb connection
(“User id = Scott ; password = tiger; provider = msdaora.1”);
Con. open ();
Message Box .show (“Connections is ok”);
String q = “Create Table c 194 (Eno number, E name varchar 2 (10), Sql number)”;
Oledb command cmd = new oledb command (q, con);
cmd. Execute Non query ()
Message Box. Show (“Table is created ”);
Catch (oledb Exception oe)
Message Box. Show (oe. Message e );
Program to add records into c 194 table:-
Insert into c 194 value (101, ‘Teja’, 5000)
- Open windows Forms Application Project
- Design the form as shown Using System. Data . oledb
- Code for Button 1_ click
String s = “Insert in to c 194 values (“ + text box1.Text + “, ‘ “ + text box2. Text + “, ‘ “ + text box 3.text +”)”;
Message Box .show (s); Oledb _connection con = new loedb connection (“provider = ms da or a.1 ; user id = scott; password = tiger “);
Con. Open (); Oledb command cmd = new oledb command (s, con);
cmd. Execute Non query (); Message Box. Show (“Record is added”);