Common statements for Asp.net and SQL database operations

1. Add a database connection in Web. config:

Find <etettings/> or <connectionstrings/> and change it:

<Deleetask>

<Add key = "subtitle" value = "Shanghai yadong International Cargo Co., Ltd. LCL Protection System"/>

<Add key = "connstring" value = "Server = localhost; initial catalog = demo; Integrated Security = true"/>

<! -- <Add key = "connstring" value = "Server = 127.0.0.1; uid = sa; Pwd = sa; database = Demo"/> -->

</Appsettings>

The key (or name) can be defined by yourself. This name will be used in the program in the future.

 

 

2. Common Database code: 1). Open the database for operations:

Sqlconnection conn = new sqlconnection (configurationmanager. connectionstrings ["dataconnection"]. connectionstring); // instantiate the database connection <br/> try <br/> {<br/> Conn. open (); // open the database <br/>}< br/> catch (sqlexception ex) <br/>{< br/> throw new exception (ex. message, ex); // throw an exception <br/> // response. write ("<MCE: Script Type =" text/JavaScript "> <! -- <Br/> alert ('error! Contact the Administrator ') <br/> // --> </MCE: SCRIPT> "); // give the user a friendly prompt <br/>}< br/> finally <br/>{< br/> Conn. close (); // close the database <br/>}
Note: system. Configuration reference should be added.

 

2). Common operations using the gridview:

Add system. Configuration reference
Add system. Data Reference
Add system. Data. sqlclient reference

Code in the webpage initialization event:

Sqlconnection conn = new sqlconnection (configurationmanager. connectionstrings ["dataconnection"]. connectionstring); // instantiate the database connection <br/> string SQL = "select * from table name"; <br/> try <br/>{< br/> Conn. open (); // open the database <br/> sqldataadapter myadp = new sqldataadapter (SQL, Conn); // instantiate sqldataadapter, execute the SQL statement <br/> dataset myds = new dataset (); // instantiate dataset <br/> myadp. fill (myds); // fill the sqldataadapter in the dataset. <br /> Gridview1.datasource = myds; // specify the data source of the gridview as dataset. <br/> gridview1.databind (); // bind the gridview <br/>}< br/> catch (sqlexception ex) <br/>{< br/> throw new exception (ex. message, ex); // throw an exception <br/> // response. write ("<MCE: Script Type =" text/JavaScript "> <! -- <Br/> alert ('error! Contact the Administrator ') <br/> // --> </MCE: SCRIPT> "); // give the user a friendly prompt <br/>}< br/> finally <br/>{< br/> Conn. close (); // close the database <br/>} 

 

Pagination in the gridview:
Set the allowpaging attribute of gridviw to true, set the pagesize (number of entries displayed per page), add the pageindexchanging event, and add the following code to the event:
Gridview1.pageindex = E. newpageindex; // index to the new page when the page number changes
Gridview1.databind (); // rebind the gridview

 

To add the delete function in the gridview, follow these steps:

A. Add

<Asp: templatefield> <itemtemplate> </P> <p> </itemtemplate> </ASP: templatefield> 

B. Add a linkbutton control in the middle of the previous step to set the attribute oncommand = "event name", comandargument = '<% # eval ("fields to be bound") %>'
C. manually add an event to the CS file in the background:

Protected void event name (Object sender, commandeventargs e) <br/>{</P> <p>} 

Note that the event name is consistent with the event name in oncommand = "event name" in step 1.
D. Set in the event:

Int id = int. parse (string) E. commandargument ); 

Note: Id indicates the ID number to be deleted. The function of this sentence is to obtain the ID number to be deleted based on the value in comandargument.
E. Execute the deletion code in the event:

Int id = int. parse (string) E. commandargument); // defines the integer variable ID to receive the value from the commandargument attribute in the linkbutton <br/> sqlconnection conn = new sqlconnection (configurationmanager. connectionstrings ["dataconnection"]. connectionstring); // instantiate the database connection <br/> string SQL = "delete from Admin where adminid =" + ID; // define the SQL deletion statement <br/> try <br/>{< br/> Conn. open (); // open the database <br/> sqlcommand mycmd = new sqlcommand (SQL, Conn ); // D. instantiate <br/> mycmd. executenonquery (); // execute sqlcommand <br/> response. redirect ("admin_admin_manage.aspx"); // redirection <br/>}< br/> catch (sqlexception ex) <br/>{< br/> throw new exception (ex. message, ex); // throw an exception <br/> // response. write ("<MCE: Script Type =" text/JavaScript "> <! -- <Br/> alert ('error! Contact the Administrator ') <br/> // --> </MCE: SCRIPT> "); // give the user a friendly prompt <br/>}< br/> finally <br/>{< br/> Conn. close (); // close the database <br/>} 

 

Format of the deleted SQL statement:
1. Delete All: delete from users
2. Delete the record with the specified ID: delete from users where id = 1;
3. Delete the record with the specified condition: delete from users where username = 'langhp'
4. If you want to add the deletion confirmation step during deletion, you can add the following attributes to the linkbutton: onclientclick = "Return confirm ('Are you sure you want to delete? ')"

 

 

To add a modification function in the gridview, follow these steps:

A. Add

 

<Asp: templatefield> <itemtemplate> </P> <p> </itemtemplate> </ASP: templatefield> 

 

B. Add a linkbutton control in the middle of the previous step to set the attribute oncommand = "event name", comandargument = '<% # eval ("fields to be bound") %>'
C. manually add an event to the CS file in the background:

Protected void event name (Object sender, commandeventargs e) <br/>{</P> <p>} 

Note that the event name is consistent with the event name in oncommand = "event name" in step 1.
D. Set in the event:

Int id = int. parse (string) E. commandargument ); 

Note: ID is the ID number to be modified. The function of this sentence is to obtain the ID number to be deleted based on the value in comandargument.
E. Execute the redirection command in the event so that it can be redirected to a new webpage for modification:

Response. Redirect ("usermodify. aspx? Id = "+ id ); 

 

F. design the layout in the new web page so that the content to be modified can be first modified. Then, add the following content before the initial activation event in the CS code file in the background:
Then, in the initial activation event on the page, query the records of the specified ID to be modified based on the SQL statement, and initialize the controls on the page:

Sqlconnection conn = new sqlconnection (configurationmanager. connectionstrings ["dataconnection"]. connectionstring); // instantiate the database connection <br/> id = int. parse (string) request. params ["ID"]); // receives the variable value from the address bar, that is, the value of the ID to be modified <br/> string SQL = "select * from Admin where adminid =" + ID; // define the SQL statement, query records with a specified ID from the database <br/> try <br/>{< br/> Conn. open (); // open the database <br/> sqlcommand mycmd = new sqlcommand (SQL, Conn); // instantiate sqlcommand <B R/> sqldatareader mydr = mycmd. executereader (); // execute sqlcommand <br/> If (mydr. read () // If any returned value <br/>{< br/> txt_adminname.text = mydr ["adminname"]. tostring (). trim (); // initialize the user name <br/>}< br/> catch (sqlexception ex) <br/>{< br/> throw new exception (ex. message, ex); // throw an exception <br/> // response. write ("<MCE: Script Type =" text/JavaScript "> <! -- <Br/> alert ('error! Contact the Administrator ') <br/> // --> </MCE: SCRIPT> "); // give the user a friendly prompt <br/>}< br/> finally <br/>{< br/> Conn. close (); // close the database <br/>} 

 

 

G. Update the record of the specified ID in the modify button event:
Update table name set field name = value where id = Specified ID
Common Code:

Sqlconnection conn = new sqlconnection (configurationmanager. connectionstrings ["dataconnection"]. connectionstring); // instantiate the database connection <br/> string adminpass = txt_pass1.text.tostring (). trim (); // defines the variable receiving username <br/> string adminname = txt_adminname.text.tostring (). trim (); // define the variable receiving password <br/> string SQL = "Update admin set adminpass = '" + adminpass + "'where adminname ='" + adminname + "'"; // define an SQL update statement <br/> try <br/>{< br/> Conn. open (); // open the database <br/> sqlcommand mycmd = new sqlcommand (SQL, Conn); // instantiate sqlcommand <br/> mycmd. executenonquery (); // execute sqlcommand <br/> response. redirect ("admin_admin_manage.aspx"); // redirection <br/>}< br/> catch (sqlexception ex) <br/>{< br/> throw new exception (ex. message, ex); // throw an exception <br/> // response. write ("<MCE: Script Type =" text/JavaScript "> <! -- <Br/> alert ('error! Contact the Administrator ') <br/> // --> </MCE: SCRIPT> "); // give the user a friendly prompt <br/>}< br/> finally <br/>{< br/> Conn. close (); // close the database <br/>} 

 

H. Redirect the webpage to the desired webpage. Generally, the code is commonly used to add data records to the page displaying all records:

First, check whether duplicate names exist. For example, duplicate user names or duplicate titles are not allowed.
Then, if there is no duplicate name, write the record to the database: insert into (Field 1, Field 2) values (value 1, value 2)

<Br/> sqlconnection conn = new sqlconnection (configurationmanager. connectionstrings ["dataconnection"]. connectionstring); // instantiate the database connection <br/> try <br/> {<br/> Conn. open (); // open the database <br/> string adminname = txt_adminname.text.tostring (). trim (); // obtain the user name entered by the user <br/> string SQL = "select adminname from Admin where adminname = '" + adminname + "'"; // define the query statement for detection <br/> sqlcommand mycmd = new sqlcommand (SQL, Conn ); // Instantiate qlcommand <br/> sqldatareader mydr = mycmd. executereader (); // returns the execution result to sqldatareader. <br/> If (mydr. read () // if there is a duplicate name, the message <br/>{< br/> response. write ("already exist"); <br/>}< br/> else // write data to the database without duplicate names <br/>{< br/> mydr. close (); // close sqldatareader <br/> string adminpass = txt_adminpass1.text.tostring (). trim (); // obtain the password entered by the user <br/> string sql2 = "insert into admin (adminname, adminpass) values ('" + adminname + "', '"+ Admin Pass + "')"; // define the insert statement <br/> sqlcommand mycmd2 = new sqlcommand (sql2, Conn ); // instantiate sqlcommand <br/> my‑2.executenonquery (); // execute <br/> response. redirect ("admin_admin_manage.aspx"); // Go Back To The Management page <br/>}< br/> catch (sqlexception ex) <br/>{< br/> throw new exception (ex. message, ex); // throw an exception <br/> // response. write ("<MCE: Script Type =" text/JavaScript "> <! -- <Br/> alert ('error! Contact the Administrator ') <br/> // --> </MCE: SCRIPT> "); // give the user a friendly prompt <br/>}< br/> finally <br/>{< br/> Conn. close (); // close the database <br/>} 

 

Click the title to display the specific news content (application in the gridview ):
1. Add a template column to the gridview

2. Add the hyperlink control to the two to set the navigateurl attribute:
Navigateurl = '<% # "display Page name. aspx? Variable = "+ eval (" field in data table ") %>'
Example: navigateurl = '<% # "show. aspx? Id = "+ eval (" newsid ") %>'
Note: the fields in the data table are generally ID fields!
3. Add <% # eval ("field in data table") %> to hyperlink.
Example: <% # eval ("newstitle") %>
Note: fields in the data table are generally title fields (because the fields to be displayed are titles )!
4. Set the headertext attribute of templatefield
5. Create An ASPX page and design the page layout.
6. Select the record of the specified ID in the initialization code of the newly created page. Common Code:
Sqlconnection conn = new <br/> sqlconnection (configurationmanager. connectionstrings ["dataconnection"]. connectionstring); // <br/> connection instantiation <br/> try <br/> {<br/> Conn. open (); // open the database <br/> int id = int. parse (string) request. params ["ID"]); // note that the ID is the name changed from step 1 <br/> string SQL = "select * from news where newsid =" + ID; <br/> sqlcommand mycmd = new sqlcommand (SQL, Conn); <br/> sqldatareader mydr = mycmd. execut Ereader (); <br/> If (mydr. read () <br/>{< br/> label1.text = mydr ["newscontent"]. tostring (). trim (); <br/>}< br/> catch (sqlexception ex) <br/>{< br/> throw new exception (ex. message, ex); // throw an exception <br/> // response. write ("<MCE: Script Type =" text/JavaScript "> <! -- <Br/> alert ('error! Contact the Administrator ') <br/> // --> </MCE: SCRIPT> "); // give the user a friendly prompt <br/> Information <br/>}< br/> finally <br/>{< br/> Conn. close (); // close the database <br/>} 

 

========================================================== = Other Tips =================================================== =

 

MD5 encryption:
Data encryption is a common technology used to encrypt passwords.
Two encryption methods are available: two-way encryption (encryption or decryption) and one-way encryption (encryption only, encryption not allowed). MD5 encryption is one-way encryption.

Usage:
1. reference the system. Web. Security namespace on the page to be encrypted
2. encrypt the strings to be encrypted:
Example:

String usrpwd = formsauthentication. hashpasswordforstoringinconfigfile (textbox1.text, "MD5"); <br/> string Pwd = formsauthentication. hashpasswordforstoringinconfigfile (STRING variable, "MD5 ");
3. Note that the encrypted string length is 32 bits. You must modify the length of the corresponding field in the data table.
* *** Note: the admin-encrypted string is 21232f297a57a5a743894a0e4a801fc3. This is an alternative !!!

 

 

In the gridview, for ultra-long fields (such as title processing)
Ternary OPERATOR: condition? When the condition is true: when the condition is false
Substring function (truncation string): substring (start position, number of digits)
Instance:
<% # Eval ("field name"). tostring (). length> 10? Eval ("field name"). tostring (). substring () + "...... ": Eval (" field name "). tostring () %> 

 

 

Use
Response. Write ("<MCE: script language = 'javascript '> <! -- <Br/> alert ('information! '); Window. Parent. Location. href = 'webpage to be redirected'; <br/> // --> </MCE: SCRIPT> "); 

 

How to Set variable information in the web. config file:
Add a value under appsettings in Web. config:
<Add value = "value" Key = "name"/>
Read from the CS page:

Configurationmanager. deleetpipeline ["key name in deleetpipeline"]. tostring ();