Darbs ar MS SQL Server

1. Darbs ar MS SQL Server Datu bāzes aplikācijas programmēšana

2. Apvienotā un sadalītā vide Apvienotā vide (connected environment) ir pats vienkāršākais gadījums, kad programma izveido pieslēgumu datu bāzei un šīs pieslēgums tiek uzturēts visa darba laikā. Šīs pieejas priekšrocības ir: viegla kontrole, pielietojuma un datu bāzes realizācijas vienkāršums. Apvienotās vides ir efektīvas tikai darbam vienā datorā vai nelielā lokālā tīklā, savukārt, strādājot ar attālināto datu bāzi caur Internet, stipri paaugstinās servera un tīkla noslodze, un darbs tādā sistēmā kļūst praktiski neiespējams. Šī trūkuma novēršanai izmanto otro pieeju darbam ar datu bāzēm, kuru sauc par sadalīto vidi (disconnected environment). Šajā gadījumā pielietojuma programma pieslēdzas datu bāzei, nolasa nepieciešamus datus un pārtrauc savienojumu. Kad visas izmaiņas ar datiem ir paveiktas, tiek izveidots jauns pieslēgums, un informācija tiek ierakstīta datu bāžē. Sadalītās sistēmas trūkumi var būt tad, kad dati, ar kurām strādā lietotājs, var būt nodzēsti vai izmainīti, kas var izraisīt konfliktus. Tādēļ dažreiz pielieto abu iepriekš minēto pieeju kombināciju, lai nodrošināt efektīvāku programmas darbu.

3. Objektu mijiedarbība SQL Server gadījumā SqlCommand SqlConnection SQL Server Darbs apvienotā vidē ADO.NET satur dažas klases, kuras nodrošina darbu sadalītā vidē: Connection – realizē darbu ar savienojumu; Command – ļauj izpildīt komandas darbam ar datu bāzes informāciju; DataReader – kursors, kurš ļauj pārvietoties pa datu bāzes ierakstiem. SqlDataReader

4. Objektu mijiedarbība SQL Server gadījumā RAM SQL Server DataSet SQL Server .NET provaiders Darbs sadalītā vidē Darbam sadalītā vidē tiek izmantotas divas speciālās klašu kopas: DataSet – ļauj nolasīt datus no datu bāzes un manipulēt ar tām lokāli; .NET datu provaideri – ļauj pievienoties datu bāzei, nolasīt datus lokālā atmiņā un pēc to apstrādes ierakstīt datu bāzē.

5. Darbs sadalītā vidē Vispārīgā gadījumā darbs sadalītā vidē sastāv no šādiem soļiem: • Atvērām pieslēgumu ar datu bāzi. • Aizpildām DataSet objektu ar datiem no datu bāzes ar DataAdapter metodes palīdzību. • Aizvērām pieslēgumu (dati tiek saglabāti klienta-datora lokālā atmiņa, bet servera resursi atbrīvojas). • Darbs ar DataSet objekta saturu. • Atkal atvērām pieslēgumu. • Atjaunojām datus datu bāzē no DataSet objekta. • Aizvērām pieslēgumu.

6. Komandas objektu lietojums Definējot pieslēgumu ar ConnectionString palīdzību un atvērot pieslēgumu ar metodi Open reāli nekādas operācijas ar datiem neveic. Šīm nolūkam ir paredzēts objekts Command, kurš ļauj izpildīt SQL pieprasījumus un procedūras programmas kodā. Atkarībā no provaidera jāizvēlas kādu no minētām komandu klasēm: System.Data.SqlClient.SqlCommand – SQL Server; System.Data.OleDb.OleDbCommand – OLE DB; System.Data.OracleClient.OracleCommand – Oracle; System.Data.Odbc.OdbcCommand – ODBC.

7. Visiem komandu objektiem ir vienādas īpašības, svarīgākas no kurām ir šādas: (Name) – komandas objekta vārds; Connection – pieslēguma nosaukums, caur kuru objekts savienosies ar DB; CommandType – var būt trīs iespējamas vērtības: Text – tiek lietota pēc noklusēšanas un nosaka, ka tiks lietots parastais SQL pieprasījums: StoredProcedure – uzglabātās procedūras nosaukums; TableDirect – tabulas vai tabulu nosaukumi, no kurām tiks ņemta informācija; CommandText – atkarībā no CommandType vērtības satur SQL pieprasījumu, procedūras vai tabulu nosaukumu; Parameters – kolekcija parametru uzglabāšanai; CommandTimeout – rezultāta gaidīšanas laiks pēc komandas izpildīšanas (pēc noklusējums 30 sekundes).

8. Komandas objektu izveidošanai un lietošanai ir nepieciešams norādīt vismaz 3 īpašības: nosaukums, pieslēgums un komandas teksts: konn.ConnectionString = "Data Source=ALEXEY\\SQLEXPRESS;" + "Initial Catalog=Northwind;Integrated Security=true;"; SqlCommand kom = new SqlCommand( “select * from Categories”, konn); konn.Open(); Parametru lietošanas gadījumos rīkojas šādi: konn.ConnectionString = "Data Source=ALEXEY\\SQLEXPRESS;" + "Initial Catalog=Northwind;Integrated Security=true;"; //definē un piešķir parametra vērtību int param=0; param=int.Parse(TextBox1.Text); //formatē komandu un izveido komandas objektu string komanda=“select * from categories where CategoryID>”+ param.ToString(); SqlCommand kom = new SqlCommand(komanda, konn); konn.Open();

9. Parametru pievienošana komandai Iepriekšējā piemērā komanda tika savākta no daļām, tas var izraisīt problēmas lielu parametru skaita gadījumos. Tādēļ ērtāk izmantot komandas objekta kolekciju Parameters, kuru var aizpildīt ar parametriem un tālāk tos izmantot. SqlConnection konn = new SqlConnection(); konn.ConnectionString = "Data Source=ALEXEY\\SQLEXPRESS;" + "Initial Catalog=Northwind;Integrated Security=true;"; //definē un piešķir parametra vērtību int param=0; param=int.Parse(TextBox1.Text); //formatē komandu un izveido komandas objektu string komanda= “select * from categories where CategoryID>@par”; //izveidojam parametru, norādot nosaukumu un vērtību SqlParameter par1 = new SqlParameter(“par”,param); SqlCommand kom = new SqlCommand(komanda, konn); //pievienojam parametru kolekcijai kom.Parameters.Add(par1); konn.Open();

10. Komandu izpildīšana Kad komandas objekts ar visām īpašībām un parametriem ir nodefinēts, ir nepieciešams izpildīt šajā objektā uzglabāto komandu. Šīm nolūkam var izmantot 4 pieejas: ExecuteScalar – ja komandas rezultāts ir viena vērtība; ExecuteReader – ja komanda atgriež vienu vai vairākas rindas; ExecuteNonQuery – ja komanda atjauno vai maina DB struktūru; ExecuteXmlReader – ja vēlas iegūt komandas rezultātu XML formātā.

11. ExecuteScalar metodes lietojums Pieņemsim, ka ir nepieciešams parādīt preču skaitu, kuru cena pārsniedz norādīto: SqlConnection konn = new SqlConnection(); konn.ConnectionString = "Data Source=ALEXEY\\SQLEXPRESS;" + "Initial Catalog=Northwind;Integrated Security=true;"; int param=0; param=int.Parse(textBox1.Text); string komanda= "select count(*) from products where unitprice>@par"; SqlParameter par1 = new SqlParameter("par",param); SqlCommand kom = new SqlCommand(komanda, konn); kom.Parameters.Add(par1); konn.Open(); //izpilda komandu un izvada rezultātu formā label1.Text = kom.ExecuteScalar().ToString(); //aizver pieslēgumu konn.Close();

12. ExecuteReader metodes lietojums ExecuteReader ir kursors, kurš ļauj pārvietoties pa iegūtiem ierakstiem. Piemēram, gribam saņemt informāciju par visiem preču nosaukumiem: SqlConnection konn = new SqlConnection(); konn.ConnectionString = "Data Source=ALEXEY\\SQLEXPRESS;" + "Initial Catalog=Northwind;Integrated Security=true;"; string komanda="select productname from products"; SqlCommand kom = new SqlCommand(komanda, konn); konn.Open(); //izpildām komandu ierakstot informāciju DataReader objektā SqlDataReader dat = kom.ExecuteReader(); //kamēr pastāv ieraksti, parādīt tos textBox-ā while (dat.Read()) { textBox1.Text = textBox1.Text+dat[0].ToString()+ Environment.NewLine; } //aizver DataReader un pieslēgumu dat.Close(); konn.Close();

13. ExecuteNonQuery metodes lietojums ExecuteNonQuery pielieto insert, delete vai update komandu izpildei. Pieņemsim, ka ir programma, kur lietotājam jāievada informācija tabulā region: SqlConnection konn = new SqlConnection(); konn.ConnectionString = "Data Source=ALEXEY\\SQLEXPRESS;" + "Initial Catalog=Northwind;Integrated Security=true;"; int ID=0; string Apraksts = “”; try //veic pārbaudi pret nepareizo lietotāja ievadu {ID=int.Parse(textBox1.Text); Apraksts=textBox2.Text;} catch (FormatException) {MessageBox.Show(“Nepareizs formats”);} string komanda="insert into region values(@ID, @Apraksts)"; SqlParameter par1 = new SqlParameter("ID", ID); SqlParameter par2 = new SqlParameter("Apraksts", Apraksts); SqlCommand kom = new SqlCommand(komanda, konn); kom.Parameters.Add(par1); kom.Parameters.Add(par2); konn.Open(); kom.ExecuteNonQuery();//izpilda komandu konn.Close();

14. Transakciju izmantošana Transakciju izmantošana ļauj izpildīt vairākas darbības tikai tad, ja visas no šīm darbībām notika veiksmīgi, pretējā gadījumā visas darbības tiks atceltas: SqlConnection konn = new SqlConnection(); konn.ConnectionString = "Data Source=ALEXEY\\SQLEXPRESS;" + "Initial Catalog=Northwind;Integrated Security=true;"; konn.Open(); string komanda="insert into region values(7,’Description’)"; SqlCommand kom = new SqlCommand(komanda, konn); SqlTransaction trans = konn.BeginTransaction(); try { kom.Transaction = trans; kom.ExecuteNonQuery(); trans.Commit(); } catch (Exception) { trans.Rollback(); } finally {konn.Close();}

15. Kļūdas situāciju apstrāde darbā ar datu bāzi Strādājot ar datu bāzi var rasties dažādas izņēmuma vai kļūdas situācijas, kuras noved pie programmas aizvēršanas. Šādas situācijas var sadalīt uz kļūdām klienta pusē un kļūdām servera pusē. Visas izņēmumu situācijas tiek mantotas no System.Exception klases, un ir iespējams izmantot tikai šo klasi, tomēr parasti ir nepieciešams uzzināt konkrētāk kļūdas iemeslu. Kļūdas klienta pusē Izņēmuma situācijas var rasties ne tikai metožu izsaukuma gadījumos, bet arī objektu īpašību noteikšanā. Piemēram, mainot īpašību ConnectionString automātiski notiek teksta analīze, kurš bija norādīts pieslēguma rindā. Ja šajā rindā ir kļūdaina informācija, tiek aktivizēta ArgumentException izņēmumu situācija.

16. Nākamais piemērs demonstrē kļūdas apstrādi gadījumiem, kad ConnectionString parametri tiks pierakstīti nepareizi. Rezultātā tiks izdots paziņojuma kods par kļūdu pieslēgumā. SqlConnection konn = new SqlConnection(); try { konn.ConnectionString="DataSource=ALEXEY\\SQLEXPRESS;"+ "Initial Catalog=Northwind;Integrated Security=true;"; konn.Open(); } catch (ArgumentException ex) { MessageBox.Show(ex.ToString()); }

17. Metode Open izsauc InvalidOperationException gadījumos, ja pieslēguma rinda ir definēta nepareizi vai pieslēgums jau ir atvērts: SqlConnection konn = new SqlConnection(); try { konn.ConnectionString = "Data Source=ALEXEY\\SQLEXPRESS;" + "Initial Catalog=Northwind;Integrated Security=true;"; konn.Open(); konn.Open();//atvēram peislēgumu vēlreiz } catch (ArgumentException ex) { MessageBox.Show(ex.ToString()); } catch (InvalidOperationException) { MessageBox.Show("Pieslēgums jau ir atvērts"); }

18. Kļūdas servera pusē Ja izņēmumu situācija notiek serverī, tad kļūdu izsauc jau SQL Server. Šādu kļūdu apstrādei izmanto objektu SqlException. Šīs objekts satur sevī kolekciju ar nosaukumu Errors, kura ļauj iegūt informāciju par serverī notikušām kļūdām. Kolekcijas elementi ir objekti ar tipu SqlError. SqlError klases īpašības ir šādas: Class kļūdas nopietnums; LineNumber atgriež procedūras vai pieprasījuma rindas numuru, kur notika kļūda; Message kļūdu raksturojošs teksts; Number numurs, kurš raksturo kļūdas tipu.

19. Daži kļūdas numuru piemēri ir parādīti tabulā: Īpašības Class iespējamas vērtības ir:

20. catch (SqlException kl) { string kludasPazin; foreach (SqlError kludas in kl.Errors) { switch (kludas.Number) { case 17: kludasPazin = “Servera nosaukums?"; break; case 4060: kludasPazin = "Datu baze?"; break; case 18456: kludasPazin = "Lietotajs vai parole?"; break; default: kludasPazin = kludas.Message; break; } MessageBox.Show(kludasPazin+kludas.Number); } }