1 / 40

ADO.NET

ADO.NET. By Hanumantha Rao.N MCA. ADO.NET. Active Data Objects .NET

diep
Download Presentation

ADO.NET

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ADO.NET By HanumanthaRao.N MCA

  2. ADO.NET • Active Data Objects .NET • It is very important for a developer to have good idea about how application can connect to databases as this becomes the base for working with any remote data sources. ADO.NET of .NET provides many different options/styles for communicating with DB’s

  3. Architecture

  4. C#.NET ADO.NET

  5. Student Database Project

  6. Start Visual Studio Start  All Programs  Microsoft Visual Studio 2008  Microsoft Visual Studio

  7. Starting New C#.NET Project

  8. Setting Path

  9. Designing Required Components • 6 Labels • 2 TextBoxes • 2 Combo Boxes • 1 DateTime Picker • 11 Buttons • 1 Picture Box • 1 openFileDialog

  10. Labels • Take 6 Labels and change its Text Property as • 1. NewtonsInstitue of Engineering • 2. Roll No • 3. S Name • 4. DOB • 5. Gender • 6. Branch

  11. TextBoxes • Insert 2 Text boxes and Change its Names as • TextBox1  Tbrno • TextBox2  tbsname

  12. ComboBoxes • Insert 2 ComboBoxes and Change its Names as • ComboBox1  cmbgender • ComboBox2  cmbbranch Select ComboBox2  Items Property  MCA MBA CSE IT EEE ECE • Select ComboBox1  Items Property • Male • Female

  13. DateTimePicker • Insert 1 DateTimePicker Component • Change its Name Property  DOB • Format  Custom

  14. Picture Box • Insert PicutureBox Component • Set SizeMode Zoom • Now Insert 11 Buttons And Change Text Property as Insert Update Delete New Exit |<< << >> >>| Browse Search

  15. Adding a Reference: • Goto Project Menu  Add Reference  select 'Microsoft.VisualBasic' from .NET tab. • Inorder to use this we have to include the namespace: • ‘using Microsoft.VisualBasic’ • Inorder to use OleDb Connection include the namespace: • ‘using System.Data.OleDb’ • Inorder to use FileStream or MemoryStream we have to include the namespace:‘using System.IO’.

  16. Creating MSAccess Database • Start  Programs  MSOffice  MSAccess • File  New  Database  NIEStd.accdb • Save This Database in Your Project Location • Ex: D:\Hanu\NIEStd.accdb • Now Click on Create Button

  17. Creating a Table • Select Database  Right Click  Select DesignView 

  18. Getting Connecting string Path • Now open you Notepad and click on Save As button. Name then student.udl. Change save type "ALL FILES".

  19. Create Udl

  20. j • Now double click on student. udl file. A wizard will start like this

  21. Select Database • Click Provider TAB, select Microsoft Jet 4.0 OLE DB (denoted by black arrow) then click next. Now click "Select or enter a database name" and select the desire database then click open.

  22. j Now click on test connection and click OK Now edit this UDL file with note pad and copy link as shown below

  23. Finding Path

  24. Code For Form public partial class Form1 : Form { public Form1() { InitializeComponent(); } OleDbConnection con; OleDbCommandcmd; OleDbDataAdapter adapter; DataSetds; intrno = 0; MemoryStream ms; byte[] photo_aray;

  25. Browse Button openFileDialog1.Filter = "jpeg|*.jpg|bmp|*.bmp|all files|*.*"; DialogResult res = openFileDialog1.ShowDialog(); if (res == DialogResult.OK) { pictureBox1.Image = Image.FromFile(openFileDialog1.FileName); }

  26. Form Load Code • con=new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Hanu\niestd.accdb;Persist Security Info=False"); tbrno.Enabled = false; loaddata(); showdata();

  27. void loaddata() { adapter =new OleDbDataAdapter("select * from niestd",con); ds = new DataSet( );//student-> table name in stud.accdb/stud.mdb file adapter.Fill(ds, "niestd"); ds.Tables[0].Constraints.Add("pk_sno", ds.Tables[0].Columns[0], true); }

  28. void showdata() { tbrno.Enabled = false; tbrno.Text= ds.Tables[0].Rows[rno][0].ToString(); tbsname.Text= ds.Tables[0].Rows[rno][1].ToString(); dob.Text= ds.Tables[0].Rows[rno][2].ToString(); cmbgender.Text=ds.Tables[0].Rows[rno][3].ToString(); cmbbranch.Text=ds.Tables[0].Rows[rno][4].ToString(); pictureBox1.Image = null; if (ds.Tables[0].Rows[rno][5] != System.DBNull.Value) { photo_aray = (byte[])ds.Tables[0].Rows[rno][5]; MemoryStream ms = new MemoryStream(photo_aray); pictureBox1.Image = Image.FromStream(ms); } }

  29. Insert Button cmd = new OleDbCommand("insert into niestd(rollno,sname,dob,gender,branch,photo) values('" + tbrno.Text + "','" + tbsname.Text + "','" + dob.Text + "','" + cmbgender.Text + "','" + cmbbranch.Text + "',@photo)", con); conv_photo(); con.Open(); int n = cmd.ExecuteNonQuery(); con.Close(); if (n > 0) { MessageBox.Show("record inserted"); loaddata(); rno++; } else MessageBox.Show("insertion failed");

  30. Convert Image void conv_photo() { if (pictureBox1.Image != null) { ms = new MemoryStream(); pictureBox1.Image.Save(ms, ImageFormat.Jpeg); byte[] photo_aray = new byte[ms.Length]; ms.Position = 0; ms.Read(photo_aray, 0, photo_aray.Length); cmd.Parameters.AddWithValue("@photo", photo_aray); } }

  31. UpDate Button • cmd = new OleDbCommand("update niestd set sname='" + tbsname.Text + "', dob='" + dob.Text + "',gender='" +cmbgender.Text +"',branch='" +cmbbranch.Text + "', photo=@photo where rollno='" + tbrno.Text+"'", con); • conv_photo(); • con.Open(); • int n = cmd.ExecuteNonQuery(); • con.Close(); • if (n > 0) • { • MessageBox.Show("Record Updated"); • loaddata(); • } • else • MessageBox.Show("Updation Failed");

  32. Delete Button • cmd = new OleDbCommand("delete from niestd where rollno=" + tbrno.Text, con); • con.Open(); • int n = cmd.ExecuteNonQuery(); • con.Close(); • if (n > 0) • { • MessageBox.Show("Record Deleted"); • loaddata(); • rno = 0; • showdata(); • } • else • MessageBox.Show("Deletion failed");

  33. First Button • private void btnfirst_Click(object sender, EventArgs e) • { • if (ds.Tables[0].Rows.Count > 0) • { • rno = 0; • showdata(); • MessageBox.Show("First Record"); • } • else • MessageBox.Show("no records"); • }

  34. Previous Button private void btnprev_Click(object sender, EventArgs e) { if (ds.Tables[0].Rows.Count > 0) { if (rno > 0) { rno--; showdata(); } else MessageBox.Show("First Record"); } else MessageBox.Show("no records"); }

  35. Next Button private void btnnext_Click(object sender, EventArgs e) { if (ds.Tables[0].Rows.Count > 0) { if (rno < ds.Tables[0].Rows.Count - 1) { rno++; showdata(); } else MessageBox.Show("Last Record"); } else MessageBox.Show("no records"); }

  36. Last Button Code private void btnlast_Click(object sender, EventArgs e) { if (ds.Tables[0].Rows.Count > 0) { rno = ds.Tables[0].Rows.Count - 1; showdata(); MessageBox.Show("Last Record"); } else MessageBox.Show("no records"); }

  37. Clear Button Code private void btnclear_Click(object sender, EventArgs e) { tbrno.Text = tbsname.Text = " "; tbrno.Enabled = true; cmbbranch.Text = cmbgender.Text = " "; pictureBox1.Image = null; }

  38. Exit Button • private void btnexit_Click(object sender, EventArgs e) • { • this.Close(); • }

  39. Executing Project • Now Goto Debug  Start Debug OR • Press F5

  40. Thankyou • By • HanumanthaRao.N MCA • Venkataiah.M MCA • HanimiReddy. A MCA Contact www.hanutechvision.blogspot.in www.venky-venky4ever.blogspot.in

More Related