using sql server in c
TRANSCRIPT
UniversityCollegeofSoutheastNorway
http://home.hit.no/~hansha
UsingSQLServerinC#withExamples
Hans-PetterHalvorsen,2016.11.01
<ThisDocumentisunderConstruction>
2
TableofContents1. Introduction.......................................................................................................................4
2. SQLServer..........................................................................................................................5
2.1. DatabaseSystems.......................................................................................................5
2.2. StructuredQueryLanguage........................................................................................5
3. VisualStudio.......................................................................................................................6
3.1. The.NETFramework...................................................................................................6
3.2. TheC#Language.........................................................................................................6
4. DatabaseProgramminginC#.............................................................................................7
4.1. ADO.NET......................................................................................................................7
4.2. EnterpriseLibrary&DataAccessApplicationBlock....................................................8
4.3. LINQ.............................................................................................................................8
4.3.1. LINQtoSQL..........................................................................................................8
4.4. EntityFramework........................................................................................................8
4.4.1. LINQtoEntities.....................................................................................................8
5. ADO.NET...........................................................................................................................10
5.1. Introduction..............................................................................................................10
5.2. Examples...................................................................................................................10
6. EnterpriseLibrary.............................................................................................................11
6.1. DataAccessApplicationBlock...................................................................................11
6.1.1. Installation..........................................................................................................11
7. LINQ..................................................................................................................................13
3 TableofContents
Tutorial:UsingSQLServerinC#-withExamples
7.1. LINQtoSQL...............................................................................................................13
8. EntityFramework.............................................................................................................14
8.1. …................................................................................................................................14
9. WebServices....................................................................................................................15
10. 3-TierArchitecure..........................................................................................................18
11. References.....................................................................................................................19
4
1. IntroductionThisTutorialgivesanoverviewofdifferenttechniquesforcommunicationgwithSQLServerinVisualStudioandC#.
FormoreinformationaboutSoftwareandProgramming,pleaseseemywebsitelocatedhere:
http://home.hit.no/~hansha/
5
2. SQLServer…
2.1. DatabaseSystems…
FormoreinformationaboutDatabaseSystems,pleaseseetheTutorial“IntroductiontoDatabaseSystems”locatedhere:
http://home.hit.no/~hansha/?tutorial=database
2.2. StructuredQueryLanguage…
FormoreinformationaboutSQL,pleaseseetheTutorial“StructuredQueryLanguage”locatedhere:
http://home.hit.no/~hansha/?tutorial=sql
6
3. VisualStudioMicrosoftVisualStudioisanintegrateddevelopmentenvironment(IDE)fromMicrosoft.
HomepageofVisualStudio:http://www.visualstudio.com
FormoreinformationaboutVisualStudioandC#,pleaseseetheTutorial“IntroductiontoVisualStudioandC#”locatedhere:
http://home.hit.no/~hansha/?tutorial=csharp
3.1. The.NETFrameworkThe.NETFramework(pronounced“dotnet”)isasoftwareframeworkthatrunsprimarilyonMicrosoftWindows.ItincludesalargelibraryandsupportsseveralprogramminglanguagessuchasC#..NETisincludedwithVisualStudio.
3.2. TheC#LanguageC#ispronounced“seesharp”.C#isanobject-orientedprogramminglanguageandpartofthe.NETfamilyfromMicrosoft.C#isverysimilartoC++andJava.C#isdevelopedbyMicrosoftandworksonlyontheWindowsplatform.
7
4. DatabaseProgramminginC#
VisualStudio,.NETandC#offerslotsofdifferenttechniquesforcommunicationgwithDatabases,espessiallySQLServer.
Someofthetechniquesdiscussedinthisdocumentare:
• ADO.NET• EnterpriseLibrary&DataAccessApplicationBlock• LINQ
o LINQtoSQLo LINQtoEnitities
• EntityFramework
4.1. ADO.NETADO.NETisthebasicdata-accesstechnologyfor.NET.
ADO.NET(ActiveXDataObjectfor.NET)isasetofcomputersoftwarecomponentsthatprogrammerscanusetoaccessdataanddataservices.ItisapartofthebaseclasslibrarythatisincludedwiththeMicrosoft.NETFramework.Itiscommonlyusedbyprogrammerstoaccessandmodifydatastoredinrelationaldatabasesystems,thoughitcanalsoaccessdatainnon-relationalsources.
http://msdn.microsoft.com/en-us/library/e80y5yhx(v=vs.110).aspx
ADO.NETprovidesconsistentaccesstodatasourcessuchasSQLServerandXML,andtodatasourcesexposedthroughOLEDBandODBC.
TheADO.NETclassesarefoundinSystem.Data.dll.
ADO.NETCodeExamples:http://msdn.microsoft.com/en-us/library/dw70f090(v=vs.110).aspx
8 Error!Referencesourcenotfound.
Tutorial:UsingSQLServerinC#-withExamples
4.2. EnterpriseLibrary&DataAccessApplicationBlock
MicrosoftEnterpriseLibraryisacollectionofdifferentlibrariesthatmakesyourlifeasaprogrammereasier.InthisdocumentwewillonlyusetheDataAccessApplicationBlock.
TheDataAccessApplicationBlocksimplifiesmanycommondataaccesstaskssuchasreadingdatafordisplay,passingdatathroughapplicationlayers,andsubmittingchangeddatabacktothedatabasesystem.Itincludessupportforbothstoredproceduresandin-lineSQL,canexposethedataasasequenceofobjectsforclient-sidequerying,andprovidesaccesstothemostfrequentlyusedfeaturesofADO.NETinsimple-to-useclasses.
YoucanusetheNuGetPackageManagerinVisualStudiotoinstalltheEnterpriseLibraryassembliesthatyouneedinyourprojects.
DataAccessApplicationBlockWebSite:
http://msdn.microsoft.com/en-us/library/dn440726(v=pandp.60).aspx
4.3. LINQ…
4.3.1. LINQtoSQL
LINQtoSQLisamethodtoworkwithMicrosoftSQLServerdatabasesusingLINQ.
MicrosoftrecommendsthatyouuseEntityFrameworkfornewapplications,becauseLINQtoSQLwillnotbefurtherdeveloped.
4.4. EntityFramework…
EntityFrameworkOverview:http://msdn.microsoft.com/en-us/library/bb399567(v=vs.110).aspx
4.4.1. LINQtoEntities
9 Error!Referencesourcenotfound.
Tutorial:UsingSQLServerinC#-withExamples
…
10
5. ADO.NET…
5.1. Introduction…
5.2. Examples…
11
6. EnterpriseLibraryEnterpriseLibraryispartofMicrosoftPatters&Practices.MicrosoftPatters&PracticesarerecommendationsonhowtodesignanddevelopcustomapplicationsusingtheMicrosoftplatform.
MicrosoftPatters&PracticesWebSite:http://msdn.microsoft.com/en-us/library/ff921345.aspx
EnterpriseLibraryWebSite:http://msdn.microsoft.com/en-us/library/ff648951.aspx
MicrosoftEnterpriseLibraryisacollectionofdifferentlibrariesthatmakesyourlifeasaprogrammereasier.InthisdocumentwewillonlyusetheDataAccessApplicationBlock.
6.1. DataAccessApplicationBlockTheDataAccessApplicationBlocksimplifiesmanycommondataaccesstaskssuchasreadingdatafordisplay,passingdatathroughapplicationlayers,andsubmittingchangeddatabacktothedatabasesystem.Itincludessupportforbothstoredproceduresandin-lineSQL,canexposethedataasasequenceofobjectsforclient-sidequerying,andprovidesaccesstothemostfrequentlyusedfeaturesofADO.NETinsimple-to-useclasses.
6.1.1. Installation
YoucanusetheNuGetPackageManagerinVisualStudiotoinstalltheEnterpriseLibraryassembliesthatyouneedinyourprojects.
InstallationoftheTheDataAccessApplicationBlock:
12 Error!Referencesourcenotfound.
Tutorial:UsingSQLServerinC#-withExamples
13
7. LINQ…
7.1. LINQtoSQL…
14
8. EntityFramework…
8.1. ……
15
9. WebServicesInmanysituationsourappsdon’tnecessarlyhavedirectaccesstothedatbase,especiallywhenyoursofwareisusedoursidethelocalcompanynetwork.InthesesituationsweneedtouseaWebServiceinordertowriteorreaddatafromthedatabase.
TodayWebServiceshavebeenverypopular.AWebserviceisamethodofcommunicationsbetweentwodevicesovertheWorldWideWeb,andmakesiteasytosharedataoveranetworkortheinternet.
AWebServiceis
• AWebAPI• AStandarddefinedbyW3C• Cross-platformandPlatform-independentCommunication• DistributedApplicationDevelopment
WebServicescanbeimplementedandusedinmostProgrammingLanguages(C#/ASP.NET,PHP,LabVIEW,Objective-C,Java,etc.)
16 Error!Referencesourcenotfound.
Tutorial:UsingSQLServerinC#-withExamples
WebServicesusesstandardWebtechnology(Webprotocols)suchasHTTP,REST,SOAP,XML,WSDL,JSON,etc.
WebServicestechnologyusedinWebServices:
• HTTP-HypertextTransferProtocol
• XML–ExtensibleMarkupLanguage
• WSDL-WebServicesDescriptionLanguage
• SOAP-SimpleObjectAccessProtocol
17 Error!Referencesourcenotfound.
Tutorial:UsingSQLServerinC#-withExamples
18
10. 3-TierArchitecure…
19
11. References….
Hans-PetterHalvorsen,M.Sc.
E-mail:[email protected]
Blog:http://home.hit.no/~hansha/
UniversityCollegeofSoutheastNorway
www.usn.no