Read database data and convert to List<>

First, when there is help class Dbhelpersql

1. The following method for returning SqlDataReader

1 /// <summary>2         ///executes the query statement and returns SqlDataReader (note: After calling the method, be sure to close the SqlDataReader)3         /// </summary>4         /// <param name= "strSQL" >Query Statements</param>5         /// <returns>SqlDataReader</returns>6          Public StaticSqlDataReader ExecuteReader (stringstrSQL)7         {8SqlConnection connection =NewSqlConnection (connectionString);9SqlCommand cmd =NewSqlCommand (strSQL, connection);Ten             Try One             { A connection. Open (); -SqlDataReader myreader =cmd. ExecuteReader (commandbehavior.closeconnection); -                 returnmyreader; the             } -             Catch(System.Data.SqlClient.SqlException e) -             { -                 Throwe; +             }    -  +}

2, three Layer Dal layer method

1 /// <summary>2         ///convert the read data into a list3         /// </summary>4          Publiclist<int> Getuserlist (inthid)5         {6             stringsql ="Select V_userid from uservoters where v_headlineid="+ HID +"";7SqlDataReader dr =dbhelpersql.executereader (SQL);8list<uservotersmodel> plist =NewList<uservotersmodel>();9              while(Dr. Read ())Ten             { OneUservotersmodel UVM =NewUservotersmodel (); AUvm. V_userid =int. Parse (dr["V_userid"]. ToString ()); - plist. ADD (UVM); -             } thelist<int> list =Newlist<int>(); -              for(inti =0; I < plist. Count; i++) -             { -List. ADD (int. Parse (Plist[i]. V_userid.tostring ())); +             } -             returnlist; +}

3. The method of putting list parameters into SQL statements

1  /// <summary>2         ///get a list of non-voting users3         /// </summary>4          PublicDataSet Getnopersonlist (list<int>list)5         {6StringBuilder strSQL =NewStringBuilder ();7             if(List! =NULL&& list. Count >0)8             {9                 stringListmes =string. Empty;Ten                 foreach(intStrinchlist) One                 { AListmes + ="'"+ str +"',"; -                 } -Strsql.append ("Select U_uid,u_name,u_sex,u_phone,u_qq from dbo. Usersinfo where u_id not in ("+ Listmes.trimend (',') +") and u_level=4 and U_name= ' ORDER by u_id DESC"); the                 returnDbhelpersql.query (strsql.tostring ()); -             } -             Else -             { +Strsql.append ("Select U_uid,u_name,u_sex,u_phone,u_qq from dbo. Usersinfo where u_level=4 and U_name= ' ORDER by u_id DESC"); -                 returnDbhelpersql.query (strsql.tostring ()); +             } A}

Second, no help class directly read the database

1 /// <summary>2         ///read the database information and convert it into a list3         /// </summary>4          PublicList<maildata>Getexpiredlicense ()5         {6             stringConnectionString ="Data source=01;initial Catalog=music; Persist Security info=true; User Id=musicname; password=123456";7SqlConnection conn =NewSqlConnection (connectionString);8 Conn.         Open (); 9             stringsql =string. Format (@"SELECT * from Tale" );Ten              OneSqlDataAdapter da =NewSqlDataAdapter (SQL, connectionString); ADataSet ds =NewDataSet (); - da. Fill (DS); -list<maildata> list =NewList<maildata> ();//Maildata the entity class to query the table the             foreach(DataRow IteminchDs. tables[0]. Rows) -             { - list. ADD (Maildata.convertrow (item)); -             } +             returnlist; -}

Read database data and convert to List<>