// ******************************************************** ********** // * Name: A program details DataReader, and display Command Usage // * Function: Demos DataReader's operations. // * Note: If you need to check my text BLOG inside of ado.net articles on // * Author: Snow and cold winter //*BOLG:http://blog.9cbs.net/bineon//*** *********************************************************** ****** Using system; use system.data; use system.data.sqlclient; using system.data.oledb;
Class Sqlreader {const string connStr = "Data Source = Bineon; user ID = sa; password = 87345587; initial catalog = contract"; sqlConnection conn;
PUBLIC SQLREADER () {conn = new sqlconnection (connStr);} // *************************************************** **** // * Demo DataReader's two value methods // ****************************************** ***** public void basicReader () {string sql = "select * from friend"; SqlCommand cmd; cmd = conn.CreateCommand (); cmd.CommandText = sql; conn.Open (); SqlDataReader reader = cmd.ExecuteReader (); Regard (reader.read ()) {Console.WriteLine ("NO: {0} / tname: {1} / tphonenum: {2}, / taddress: {3}", Reader.GetInt32 (0). Totring (), reader.getstring (1), reader [2] .tostring (), reader ["faddress"]. TOSTRING ());} showSplit (); reader.close (); conn.Close (); // ************************************* * Demonstrate the operation with parameter queries, Use sqlcilent // *********************************************************************** Public void HasparamReader () {SQLCommand CMD ; cmd = conn.CreateCommand (); string sql = "select Fname, Fphone, Faddress from friend where Fid> @Fid"; cmd.CommandText = sql; SqlParameter param = new SqlParameter ( "@ Fid", SqlDbType.Int, 4 ); Param.value = 15; cmd.Parameters.Add (param) Conn.open (); // When closing Reader, close the database connection to SqlDataReader Reader = cmd.executeRead (Commandbehavior.CloseConnection); while (Reader.Read ()) {Console.WriteLine ("Name: {0} / TPHONENUM: {1} / taddress: {2} ", reader.getstring (0), reader.getstring (1), reader.getstring (2));} showSplit (); // Do not need to turn off CONN, system automatically calls This method is to close conn.
Reader.close ();} // ************************************************ / / * Demonstrate with parameter query, using oledb // ******************************************************** PUBLIC void hasOledbParamReader () {SqlCommand cmd; cmd = conn.CreateCommand (); string sql = "select Fname, Fphone, Faddress from friend where Fid>?"; string oledbConnStr = "Provider = sqloledb;" connStr; OleDbConnection oleConn = new OleDbConnection (oledbConnStr); OleDbCommand oleCmd = new OleDbCommand (sql, oleConn); oleCmd.Parameters.Add ( "nothing", 15); oleConn.Open (); OleDbDataReader oleReader = oleCmd.ExecuteReader (); while (oleReader.Read ( )) {Console.WriteLine ("name: {0} / tPhoneNum: {1} / taddress: {2}", Olereader.getstring (0), OleReader.getstring (1), OleReader.getstring (2));} SHOWSPLIT (); OLEREADER.CLOSE (); oleconn.close ();} // ***************************************** ******* // * Demo the output parameter of the stored procedure // ****************************************** ****** public void output () {sqlcommand cmd; cmd = conn.createcommand (); cmd.commandtext = "getInfo"; cmd.commandtype = Comma ndType.StoredProcedure; SqlParameter param = cmd.Parameters.Add ( "@ Fid", 16); param = cmd.Parameters.Add ( "@ Fname", SqlDbType.VarChar, 8); param.Direction = ParameterDirection.Output; param = cmd.Parameters.Add ("@ fphone", sqldbtype.varchar, 8); param.direction = parameterDirection.output; conn.open (); cmd.executenonQuery (); string fname = cmd.Parameters [@ fname " ] .Value.tostring (); string fPhone = cmd.Parameters ["@ fphone"]. Value.toString (); console.writeline (fphone); conn.close (); showsplit ();
} // ************************************* / / * demo read multiple unrelated Record /// ********************************************** Public VoID MultiResult () {SQLCommand CMD ; Cmd = conn.createcommand (); string sqla = "select fname from friend"; string sqlb = "select fPhone from friend"; cmd.commandtext = SQLA "; SQLB; conn.open (); sqlDataReader Reader = cmd.executeReader (); int i = 1; do {console.writeline ("" i.tostring () "The record set is as follows: / n"); while (Reader.Read ()) {Console. WriteLine (reader [0] .tostring () "/ t");} i ;} while (readR.nextResult ()); // nextResult () Move to the next record set reader.close (); conn.close (); Showsplit ();} // ************************************************************** * Use DataReader to get database mode information // ********************************************************** PUBLIC VOID GETSCHEMA () {SQLCOMMAND CMD; cmd = conn.createCommand (); string sql = "SELECT FID, FNAME, FPHONE"; cmd.commandtext = SQL; conn.open (); sqlDataReader Reader = cmd.executeReader (); DataTable Schematable = reader.getschematab le (); DataRowCollection SchemaColumns = SchemaTable.Rows; DataColumnCollection SchemaProps = SchemaTable.Columns; foreach (DataRow SchemaColumn in SchemaColumns) {foreach (DataColumn SchemaColumnProp in SchemaProps) {Console.WriteLine (SchemaColumnProp.ColumnName "=" SchemaColumn [SchemaColumnProp. ColumnName] .tostring ());}}}}} Reader.close (); conn.close (); showsplit ();} // ******************************** ***************** / / * Code segment from the database reads binary data // * This code segment is just a piece of binary pieces, not // * The whole program, so You can't execute, you can integrate it into your Winform project.
