using sql server in c

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:hans.p.halvorsen@hit.no

Blog:http://home.hit.no/~hansha/

UniversityCollegeofSoutheastNorway

www.usn.no