Asp tutorial. net database connection tutorial simple application instance
1. new sqlconnection & open for use. close & dispose after use. This is the simplest method. However, performance is not much said (it is possible that a page requires 100 repeated operations ).
If the executereader method is used (the connection cannot be closed after data is read), if close & dispose is forgotten, the normal operation of the system may be affected.
Because each transaction is a new sqlconnection, the transaction cannot be extended.
2. Add the page object sqlconnection, always open, close & dispose at the end of the page access. The performance is good, but the flexibility is poor, and the program structure becomes ugly.
Simple application
<% @ Page language = "c #" %>
<% @ Import namespace = "system. data. sqlclient" %>
<% @ Import namespace = "system. web. configuration" %>
<! Doctype html public "-// w3c // dtd xhtml 1.0 transitional // en"
Http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd>
<Script runat = "server">Void page_load ()
{
String connectionstring = webconfigurationmanager. connectionstrings ["products"]. connectionstring;
Sqlconnection con = new sqlconnection (connectionstring );
Sqlcommand cmd = new sqlcommand ("waitfor delay '0: 0: 03'; select title, director from products", con );
Con. statisticsenabled = true;
Using (con)
{
Con. open ();
Sqldatareader reader = cmd.exe cutereader ();
}
Grdstats. datasource = con. retrievestatistics ();
Grdstats. databind ();
}</Script>
<Html xmlns = "http://www.w3.org/1999/xhtml">
<Head id = "head1" runat = "server">
<Title> show all statistics </title>
</Head>
<Body>
<Form id = "form1" runat = "server">
<Div><H1> connection statistics
<Asp: gridview
Id = "grdstats"
Autogeneratecolumns = "false"
Runat = "server">
<Columns>
<Asp: boundfield datafield = "key" headertext = "key"/>
<Asp: boundfield datafield = "value" headertext = "value"/>
</Columns>
</Asp: gridview></Div>
</Form>
</Body>
</Html>File: web. config
<Configuration>
<Connectionstrings>
<Add name = "products"
Connectionstring = "data source =. sqlexpress;
Attachdbfilename = | datadirectory | mydatabase. mdf; integrated security = true; user instance = true "/>
</Connectionstrings>
</Configuration>A complex database connection
File: app_codeproduct.cs
Using system;
Using system. data;
Using system. data. sqlclient;
Using system. web. configuration;
Using system. collections;
Using system. collections. generic;Public class product
{
Private static readonly string _ connectionstring;Private string _ title;
Private string _ director;Public string title
{
Get {return _ title ;}
Set {_ title = value ;}
}Public string director
{
Get {return _ director ;}
Set {_ director = value ;}
}Public list <product> getall (out long executiontime)
{
List <product> results = new list <product> ();
Sqlconnection con = new sqlconnection (_ connectionstring );
Sqlcommand cmd = new sqlcommand ("waitfor delay '0: 0: 03'; select title, director from products", con );
Con. statisticsenabled = true;
Using (con)
{
Con. open ();
Sqldatareader reader = cmd.exe cutereader ();
While (reader. read ())
{
Product newproduct = new product ();
Newproduct. title = (string) reader ["title"];
Newproduct. director = (string) reader ["director"];
Results. add (newproduct );
}
}
Idictionary stats = con. retrievestatistics ();
Executiontime = (long) stats ["executiontime"];
Return results;
}Static product ()
{
_ Connectionstring = webconfigurationmanager. connectionstrings ["products"]. connectionstring;
}
}File: web. config
<Configuration>
<Connectionstrings>
<Add name = "products"
Connectionstring = "data source =. sqlexpress;
Attachdbfilename = | datadirectory | mydatabase. mdf; integrated security = true; user instance = true "/>
</Connectionstrings>
</Configuration>File: default. aspx
<% @ Page language = "c #" %>
<! Doctype html public "-// w3c // dtd xhtml 1.0 transitional // en"
Http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd><Script runat = "server">
Protected void srcproducts_selected (object sender, objectperformancestatuseventargs e)
{
Lblexecutiontime. text = e. outputparameters ["executiontime"]. tostring ();
}
</Script><Html xmlns = "http://www.w3.org/1999/xhtml">
<Head id = "head1" runat = "server">
<Title> show product </title>
</Head>
<Body>
<Form id = "form1" runat = "server">
<Div><Asp: gridview
Id = "grdproducts"
Performanceid = "srcproducts"
Runat = "server"/><Asp: objectdatasource
Id = "srcproducts"
Typename = "product"
Selectmethod = "getall"
Runat = "server" onselected = "srcproducts_selected">
<Selectparameters>
<Asp: parameter name = "executiontime" type = "int64" direction = "output"/>
</Selectparameters>
</Asp: objectdatasource><Br/>
Execution time was
<Asp: label
Id = "lblexecutiontime"
Runat = "server"/>
Milliseconds</Div>
</Form>
</Body>
</Html>
1. Create a database connection object conn
2. Specify the database parameter to be connected: the server is a local server, the database name is 'news', the user ID is 'sa ', and the password is '123'
3. Create a command object cmd
4. Assign the connection variable of cmd to the conn object created in step 1.
5. for loop traversal of each row in the gridview1 control
6. Obtain the 'checkbox1' attribute in row I of gridview1.
7. Check whether checkbox is selected
8. Specify the execution content for cmd and update the 'primary Post number' = "+ gridview1 in the 'topic table' of the database. datakeys [I]. the 'review' field in the row of value is 'reviewed'
9. Connect to the database
10. Execute the content specified in step 1.
11. Close database connection
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
