"ADO" 1, simple configuration and use

1, a number of basic knowledge points

ExecuteReader (); Returns the data queried, one line at a time, for select
ExecuteNonQuery (); Returns the number of rows affected, for delete,insert,update
ExecuteScalar (); Returns the first row, the numeric value of the first column, the type of Object, for querying the maximum, absolute value, statistical data ... such as
---------------
Use of using ()
---------------
The using () is used to release resources, and when the program runs outside of the using, the resource is automatically freed, equivalent to Conn. Dispose ()
The using is called after the dispose,dispose is out of scope to make such a judgment: there is no close, no first close, then Dispose

Conn. Open () Connect
Conn. Close () Closes the connection, turns off, and then opens
Conn. Dispose () destroys the connection and cannot be opened after destruction

using (SqlConnection conn = new SqlConnection ("server=.; Database=mytest;uid=sa;pwd=gao; ")) {Conn. Open ();} Console.WriteLine ("Open database Connection succeeded"); Console.readkey ();

---------------------------
Place the connection string in the configuration file
---------------------------
In app. config or Web. config, add the following

<connectionStrings> <add name= "connstr" connectionstring= "server=.; Database=mytest;uid=sa;pwd=gao "/></connectionstrings>

Add Reference: System.Configuration
Add namespaces: Using System.Configuration;

String Str = configurationmanager.connectionstrings["ConnStr"]. ConnectionString; Gets the string SqlConnection conn = new SqlConnection (STR);    Call string

2. Read the specified multi-column value

using (SqlConnection conn = new SqlConnection ("Data source=.; Database=mytest;uid=sa;pwd=gao; ")) {    Conn. Open ();    using (SqlCommand cmd = conn. CreateCommand ())    {        Cmd.commandtext = "SELECT * from Mydo";        using (SqlDataReader dr = cmd. ExecuteReader ())        {while        (Dr. Read ())        {           int dbId = Dr. GetString (Dr. GetOrdinal ("id"));        Gets the ID column value of           string dbName = Dr. GetString (Dr. GetOrdinal ("username")); Get username           string dbpwd = Dr. GetString (Dr. GetOrdinal ("passwd"));    Get Passwd           Console.WriteLine ("Id={0},username={1},passwd={2}", Dbid,dbname,dbpwd);}}}    

3, the use of ExecuteScalar ()

Returns the first row, the value of the first column, the type of Object

using (SqlConnection conn = new SqlConnection ("Data source=.; Database=mytest;uid=sa;pwd=gao; ")) {   Conn. Open ();   using (SqlCommand cmd = conn. CreateCommand ())   {       Cmd.commandtext = "SELECT count (*) from Mydo";          Counts the number of rows       Console.WriteLine (convert.tostring (CMD). ExecuteScalar ()));  Return first row, first column   }}

Displays the newly inserted primary key value

using (SqlConnection conn = new SqlConnection ("Data source=.; Database=mytest;uid=sa;pwd=gao; ")) {  Conn. Open ();  using (SqlCommand cmd = conn. CreateCommand ())  {    Cmd.commandtext = "INSERT into T_user (name,pwd) output inserted. Id values (' admin ', ' 1234 ');              int id = convert.toint32 (cmd). ExecuteScalar ());    Console.WriteLine ("Newly inserted primary key value: {0}", id);}        }

ADO to get SQL output and ReturnValue

Set the parameter to OutPut parameter cmd. Parameters.Add ("@Amount", Sqldbtype.decimal). The direction=parameterdirection.output;//setting parameter is RETRUNVALUECMD.PARAMETERS.ADD ("@Amount", Sqldbtype.decimal). direction=parameterdirection.returnvalue;//gets the output parameter value cmd. parameters["@Amount"]. Value;

"ADO" 1, simple configuration and use