Stored procedure i ASP.NET
OBS! Om du vill jobba med att skapa en stored
procedure i en SQL Serverdatabas ifrån VS2010 måste
du ha fullversion, expressversionen tillåter dig ej att
skapa triggers, stored procedures, mm.
ADO.NET Data Providers
Ett antal klasser som tillåter dig att koppla upp dig mot en databas,
exekvera SQL-kommandon och erhålla data.
• Connection
o Etablerar en kontakt till en datakälla.
• Command
o Exekverar SQL-kommandon och stored procedures.
• DataReader
o Snabb read-only & forward only läsning.
• DataAdapter
o Kan fylla ett dataset med data och ändra data i databasen.
Providers för olika typer av databaser
• SQL Server provider
o Optimerad för SQL Server version 7 eller senare
• OLE DB provider
o Tidigare versioner av SQL Server eller datakällor som har
en OLE DB provider - lite sämre prestanda
• Oracle provider
o Optimerad för Oracle 8i eller senare
• ODBC provider
o Alla datakällor som har ODBC - sämre prestanda.
ADO.NET Namespace
• System.Data
o behållare för data som datasets, tables, rows, columns...
• System.Data.Common
o Mestadels abstrakta klasser som som andra namespace
ärver ifrån, tex DbConnection, DbCommand, etc.
• System.Data.Client
o Klasser för uppkoppling to a Microsoft SQL Server, tex
SqlCommand, SqlConnection, SqlDataReader &
SqlDataAdapter.
• System.Data.SqlTypes
o Datatyper i SQL Server (kan användas istället för .NETs)
Exempel på två connectionstrings
Utdrag ur web.config:
<configuration>
<connectionStrings>
<!-- user instance databas (finns ej i SQL Server utan bara i express) -->
<add name="hemma" connectionString="data source=.\SQLEXPRESS;Integrated
Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User
Instance=true" providerName="System.Data.SqlClient" />
<!-- Anslutning mot en SQL Server databas (den ni använder på högskolans server) -->
<add name="cletus_jeha" connectionString="Data Source=ideweb.hh.se;User
ID=jeha;Password=dittpasswd" providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
I Visual Studio kan du också lägga din mdf-fil i App_Data.
Du kan dubbelklicka på databasfilen för att modda...
Exportera databasen till SQL-kod
I server explorer kan du exportera din databas genom att generera
SQL-kod, högerklicka på din databas och välj
"Publish to provider...".
Du kan då spara din databas som en sql-fil med data och allt.
Vill du sedan skapa en kopia på
ideweb.hh.se kan du bara öppna
den i SQL Server Management Studio
och exekvera den.
Tips: Vill du föra över filer ifrån en tabell till
en annan fungerar SQL-koden:
INSERT INTO temp (kid,fnamn,enamn)
SELECT kid,fnamn,enamn FROM student;
Command
•
CommandType.Text
o Skrivna SQL-frågor exekveras
• CommandType.StoredProcedure
o Stored procedure exekveras
• CommandType.TableDirect (undvik)
o Returnerar alla rader i en tabell. Men sämre prestanda pga bakåtkompabilitet.
// För vanlig SQL-fråga:
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandType.Text = "SELECT * FROM student";
// Alternativt kan ovan skrivas såhär eftersom CommandType.Text är default:
SqlCommand cmd = new SqlCommand("SELECT * FROM student",con);
// För stored procedure kan det se ut såhär:
SqlCommand cmd = new SqlCommand("sp_GrabbaStudenter",con);
cmd.CommandType = CommandType.StoredProcedure;
Metoder för command
• ExecuteNonQuery()
o exekverar alla frågor utom SELECT som t ex insert,
detete och update. De returnerar bara det antal rader som
påverkats.
• ExecuteScalar()
o exekverar en en fråga som endast returnerar första
rad/kolumn-rutan, t ex aggregatfunktioner som COUNT()
eller SUM()
• ExecuteReader()
o exekverar SELECT-frågor som returnerar en postsamling
(read-only, forward only)
Fördelar med stored procedure
• Enklare att underhålla
o du behöver ej göra om något i applikationerna
o standardiserar logik för dataaccess på ett ställe, i
databasen, vilka kan återanvändas för samtliga
applikationer
• Ger en säkrare databasanvändning
o begränsa åtkomst av underliggande tabeller och låt alla
köra stored procedures istället
• Kan förbättra prestanda
o om flera kommandon körs i en sp blir prestandan bättre,
speciellt om databasservern är på en annan server.
o är kompilerade och därför snabbare
Transaktioner
• En transaktion är en samling frågor som exekveras som en
helhet eller inte alls.
• Följer dessa steg:
1.
2.
3.
4.
5.
6.
7.
Skapa connection
Öppna connection
Skapa instans av transaktion
Starta transaktion
Exekvera SQL-uttryck
Fullfölj (commit) eller rulla tillbaka (rollback)
Stäng connection
• Du kan skapa transaktioner både i .NET och i stored
procedures.
Vilka egenskaper har en transaktion?
• Atomic
• Alla steg i transactionen lyckas eller misslyckas som helhet
• Consistent
•
Underliggande databas tas ifrån ett konsistent läge till ett annant.
Inga halvgjorda uppdateringar sker…
• Isolated
•
Varje transaktion är oberoende av andra transaktioner som körs
mot samma data vid samma tillfälle.
• Durable
•
Garanterar att de ändringar som gjorts skrivs till disk. (Klarar även
att skriva detta pga loggfiler hela tiden lagras om det efter commit
skulle bli strömavbrott eller så…)
Vilka egenskaper har en transaktion?
• Här ser ni ett exempel på en databas I SQL-server:
• Säg att vi vill lista alla anställda för ett företag:
(se exempel 5)
• Säg att vi vill flytta en anställd från ett företag till ett annat
företag…
(se exempel 6 a och b)
• Var ligger skillnaden i dessa två frågor?
