SQL command execution in ADO. net

Add a configuration file to the project:

 

 <?  XML   Version  = "1.0"   Encoding  = "UTF-8" ? >  <  Configuration  >    <  Connectionstrings  >      <  Add   Name   = "Students"           Providername   = "System. Data. sqlclient"          Connectionstring   = "Server = (local); Integrated Security = sspi; database = students"  />    </  Connectionstrings  >  </  Configuration  > 

The followingCodeIncluding how to reference the connection string of the configuration file, and four ways to execute SQL command:

Note: system. Configuration System. transaction must be manually added to reference

 Using System; Using System. Collections. Generic;Using System. LINQ; Using System. text; Using System. Data; Using System. Data. Common; Using System. configuration; Using System. Data. sqlclient; Using System. Transactions; Using System. xml; Namespace Sqlconndemo { Class Program { Private Dbconnection getdatabaseconnection ( String Name) {connectionstringsettings settings = configurationmanager. connectionstrings [name]; dbproviderfactory factory = dbproviderfactories. getfactory (settings. providername); dbconnection conn = factory. createconnection (); Conn. connectionstring = settings. connectionstring; Return Conn ;} Private   Static   String Getconnectionstringsconfig ( String Connectionname ){ String Connectionstring = configurationmanager. connectionstrings [connectionname]. connectionstring. tostring (); console. writeline (connectionstring ); Return Connectionstring ;} Static   Void Main ( String [] ARGs ){ String Source = getconnectionstringsconfig ( "Students" ); Try { Using (Transactionscope scope = New Transactionscope (transactionscospontion. Required )){ Using (Sqlconnection conn = New Sqlconnection (source )){// Open the connenction Conn. open (); // Executenonquery method applies to update, insert, delete SQL statement                          String Strupdate = "Update XS" + "Set remarks = 'one course has been completed in advance and earned credits '" + "Where name = 'lorinline '" ; Sqlcommand cmd = New Sqlcommand (strupdate, Conn ); Int Rowreturned = cmd. executenonquery (); console. writeline ( "{0} rows returned" , Rowreturned );// Executereader Method                          String Strselect = "Select student ID, remarks from XS" ; Sqlcommand cmd2 = New Sqlcommand (strselect, Conn); sqldatareader reader = pai2.executereader (); While (Reader. Read () {console. writeline ( "Student ID: {0 }" , Reader [0]);} reader. Close (); // Executescalar Method                          String Countselect = "Select count (*) from XS" ; Sqlcommand cmd3 = New Sqlcommand (countselect, Conn); object o = ipv3.executescalar (); console. writeline (O ); // Executexmlreader Method                          String Strxmlselect = "Select student ID, remarks from XS" + "For XML auto" ; Sqlcommand limit 4 = New Sqlcommand (strxmlselect, Conn); xmlreader xr = ipv4.executexmlreader (); XR. Read (); String Data; Do {DATA = XR. readouterxml (); If (! String . Isnullorempty (data) console. writeline (data );} While (! String . Isnullorempty (data); XR. Close (); // Do something useful                          // Mark complete Scope. Complete (); // Close the connection Conn. Close ();}}} Catch (Sqlexception ex ){ // Log the exception Console. writeline (ex. Message );}}}}