C# Odbc.OdbcDataReader类代码示例

        /// <summary>
        /// DisplayRecords: This function displays the records in the view if any.
        /// </summary>
        static void DisplayRecords()
        {
            Console.Write("\tRecords in Prescirption list...");

            try
            {
                reader = (OdbcDataReader)command.ExecuteReader();
                // read the returned resultset
                while (reader.Read())
                {
                    /*
                     * This view consists the following fields:
                     * rxdef_id (TINYINT)
                     * drug_name (CHARACTER 50)
                     * description (CHARACTER 50)
                     * rx_date (DATE)
                     * For the purpose of keeping it simple, we are displaying the rxdef_id and drug_name on the console (the first two fields)
                     */
                    Console.WriteLine("\n\t\t{0}  {1}  ", reader.GetInt16(0), reader.GetString(1));
                }
                // close the reader
                reader.Close();
            }
            catch (Exception e)
            {
                ExceptionDisplay(e);
            }
        }

        public static bool CreateForm(OdbcDataReader reader, AppUser user)
        {
            int fCount = reader.FieldCount;
            for (int i = 0; i < fCount; i++)
            {
                string name = reader.GetName(i);
                // Map to DB field. Need to change if db changed
                switch (name)
                {
                    case "username": user._username = reader.GetString(i);
                        break;
                    case "user_id": user._userId = reader.GetInt32(i);
                        break;
                    case "passwd": user._encodedPassword = reader.GetString(i);
                        break;
                    case "firstname": user._firstname = reader.GetString(i);
                        break;
                    case "surname": user._surname = reader.GetString(i);
                        break;
                    case "role_id": user._roleId = reader.GetInt32(i);
                        break;
                    case "branch_id": user._branchID = reader.GetInt32(i);
                        break;
                    case "is_valid": user._isValid = reader.GetInt32(i) > 0 ? true : false;
                        break;

                    // helper info
                    case "branch_name": user._branchName = reader.GetString(i);
                        break;

                }
            }
            return reader.HasRows;
        }

        public static bool CreateForm(OdbcDataReader reader, Branch branch)
        {
            int fCount = reader.FieldCount;
            for (int i = 0; i < fCount; i++)
            {
                string name = reader.GetName(i);

                // Map to DB field. Need to change if db changed
                switch (name) {
                    case "branch_id": branch._branchID = reader.GetInt32(i);
                                      break;
                    case "branch_name": branch._branchName = reader.GetString(i);
                                      break;
                    case "branch_code": branch._branchCode = reader.GetString(i);
                                      break;
                    case "address": branch._address = reader.GetString(i);
                                      break;
                    case "tel": branch._tel = reader.GetString(i);
                                      break;
                    case "img": branch._img = reader.GetString(i);
                                      break;
                    case "supervisor": branch._supervisor = reader.GetString(i);
                                      break;
                }
            }
            return reader.HasRows;
        }

        public static bool CreateForm(OdbcDataReader reader, Room room)
        {
            int fCount = reader.FieldCount;
            for (int i = 0; i < fCount; i++)
            {
                string name = reader.GetName(i);

                // Map to DB field. Need to change if db changed
                switch (name) {
                    case "room_id": room._roomID = reader.GetInt32(i);
                                      break;
                    case "branch_id": room._branchID = reader.GetInt32(i);
                                      break;
                    case "name": room._name = reader.GetString(i);
                                      break;
                    case "seat_no": room._seatNo = reader.GetInt32(i);
                                      break;
                    case "img": room._img = reader.GetString(i);
                                      break;
                    case "description": room._description = reader.GetString(i);
                                      break;

                    // helper info
                    case "branch_name": room._branchName = reader.GetString(i);
                                      break;

                }
            }
            return reader.HasRows;
        }

 //Constructor
 internal DbCache(OdbcDataReader record, int count) {
     _count = count;
     _record = record;
     _randomaccess = (!record.IsBehavior(CommandBehavior.SequentialAccess));
     _values = new object[count];
     _isBadValue = new bool[count];
 }

        /// <summary>
        /// DisplayRecords: This function displays the records in the view if any.
        /// </summary>
        static void DisplayRecords()
        {
            Console.Write("\tRecord in getpatientbalance stored procedure");

            try
            {
                reader = (OdbcDataReader)command.ExecuteReader();
                // read the returned resultset
                while (reader.Read())
                {
                    /*
                     * This view consists the following fields:
                     * pat_id
                     * pat_0_30_days
                     * pat_31_60_days
                     * pat_61_90_days
                     * pat_91_120_days
                     * pat_121_plus_days
                     * last_pmt_date
                     * last_pmt_amt
                     * last_pmt_type
                     * last_pmt_desc
                     */
                    Console.WriteLine("\npat_id:{0}\npat_0_30_days: {1}\npat_31_60_days: {2} \npat_61_90_days: {3}\npat_91_120_plus_days: {4} \npat_121_plus_days: {5}\nlast_pmt_date: {6} \nlast_pmt_amt: {7}\nlast_pmt_type: {8}\nlast_pmt_desc: {9}",
                        reader.GetValue(0), reader.GetValue(1), reader.GetValue(2), reader.GetValue(3), reader.GetValue(4), reader.GetValue(5), reader.GetValue(6), reader.GetValue(7), reader.GetValue(8), reader.GetValue(9));
                }
                // close the reader
                reader.Close();
            }
            catch (Exception e)
            {
                ExceptionDisplay(e);
            }
        }

 public XDocument formatTasks(OdbcDataReader r)
 {
     xdoc = new XDocument();
     XElement title, notes, startdatetime, enddatetime, place, id;
     XElement root = new XElement("message");
     XAttribute type = new XAttribute("type", "tasks");
     XAttribute errorStatus = new XAttribute("error_status", "0");
     root.Add(type);
     XElement tasks = new XElement("tasks");
     while (r.Read())
     {
         XElement task = new XElement("task");
         id = new XElement("id", r.GetValue(0).ToString());
         task.Add(id);
         title = new XElement("title", r.GetValue(1).ToString());
         task.Add(title);
         notes = new XElement("notes", r.GetValue(2).ToString());
         task.Add(notes);
         startdatetime = new XElement("startdatetime", r.GetValue(3).ToString());
         task.Add(startdatetime);
         enddatetime = new XElement("enddatetime", r.GetValue(4).ToString());
         task.Add(enddatetime);
         place = new XElement("place", r.GetValue(5).ToString());
         task.Add(place);
         tasks.Add(task);
     }
     root.Add(tasks);
     root.Add(errorStatus);
     xdoc.Add(root);
     return xdoc;
 }

        // Create an instance dataReader
        // Return type object of OdbcDataReader
        public void GetReader(string strQuery)
        {
            //  Create a Command object
            aCommand = new OdbcCommand(strQuery, connection);
            // Create data reader object using strQuery string

            ReadData = aCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }

public static void InsertData(OdbcDataReader read)
{
string table = @"d:\documents and settings\axkhan2\desktop\437proj\mytariff.dbf";
string query = "insert into " + table + "values (@ln, @orgcity, @destcity, @orgzone, @destzone, @ocountry, @dcountry, @orgcountry, @destcountry)"; //, @origin, @dest, @min, @rate, @rate, @basert, @frmwt, @towt, @cur, @validfrm, @validtill, @filename	
OleDbConnection con = new OleDbConnection(@"Provider=VFPOLEDB.1;Data Source=d:\documents and settings\axkhan2\desktop\437proj\");
try
{
OleDbCommand cmd = new OleDbCommand(query, con);
con.Open();
while(read.Read())
{
cmd.Parameters.Add(new OleDbParameter("@ln", Convert.ToInt32(read[0].ToString())));
cmd.Parameters.Add(new OleDbParameter("@orgcity", read[2].ToString()));
cmd.Parameters.Add(new OleDbParameter("@destcity", read[5].ToString()));
cmd.Parameters.Add(new OleDbParameter("@orgzone", read[4].ToString()));
cmd.Parameters.Add(new OleDbParameter("@destzone", read[7].ToString()));
cmd.Parameters.Add(new OleDbParameter("@ocountry", read[2].ToString()));
cmd.Parameters.Add(new OleDbParameter("@dcountry", read[6].ToString()));
cmd.Parameters.Add(new OleDbParameter("@orgcountry", "NL"));
cmd.Parameters.Add(new OleDbParameter("@destcountry", "FI"));

/*cmd.Parameters.Add(new OleDbParameter("origin", String.Empty));
cmd.Parameters.Add(new OleDbParameter("dest", String.Empty));
cmd.Parameters.Add(new OleDbParameter("min", double.Parse(read[10].ToString())));
cmd.Parameters.Add(new OleDbParameter("basert", null));
cmd.Parameters.Add(new OleDbParameter("rate", null));
cmd.Parameters.Add(new OleDbParameter("frmwt", null));
cmd.Parameters.Add(new OleDbParameter("towt", null));
cmd.Parameters.Add(new OleDbParameter("cur", null));
cmd.Parameters.Add(new OleDbParameter("validfrm", null));
cmd.Parameters.Add(new OleDbParameter("validtill", null));
*/
/*cmd.Parameters.Add(new OleDbParameter("rate", double.Parse(read["rate"].ToString())));
cmd.Parameters.Add(new OleDbParameter("frmwt", double.Parse(read["from_wt"].ToString())));
cmd.Parameters.Add(new OleDbParameter("towt", double.Parse(read["to_wt"].ToString())));
cmd.Parameters.Add(new OleDbParameter("cur", read["currency"].ToString()));
cmd.Parameters.Add(new OleDbParameter("validfrm", DateTime.Parse(read["valid_from"].ToString())));
cmd.Parameters.Add(new OleDbParameter("validtill", DateTime.Parse(read["valid_till"].ToString())));
*/
cmd.Parameters.Add(new OleDbParameter("validtill", "mytariff.xls"));

cmd.ExecuteNonQuery();
}
}
catch(OleDbException ex)
{
Console.WriteLine(ex.Message);
}
finally 
{
con.Close();
}

}

 private static void PrintResults(OdbcDataReader reader) {
   for (int ii = 0; ii < reader.FieldCount; ii += 1) {
     System.Console.Write("{0}{1}",
         reader.GetName(ii),
         ii + 1 < reader.FieldCount ? "\t" : "\n");
   }
   while (reader.Read()) {
     for (int ii = 0; ii < reader.FieldCount; ii += 1) {
       System.Console.Write("{0}{1}",
           reader.GetValue(ii),
           ii + 1 < reader.FieldCount ? "\t" : "\n");
     }
   }
 }

        public static List<List<string>> convertOdbcDataToList(OdbcDataReader reader)
        {
            List<List<string>> lr = new List<List<string>>();
            while(reader.Read())
            {
                List<string> lsItem = new List<string>();

                for (int i = 0; i < reader.FieldCount; i++)
                {
                    lsItem.Add(reader[i].ToString());
                }
                lr.Add(lsItem);
            }
            return lr;
        }

        public static bool CreateForm(OdbcDataReader reader, Role role)
        {
            int fCount = reader.FieldCount;
            for (int i = 0; i < fCount; i++)
            {
                string name = reader.GetName(i);

                // Map to DB field. Need to change if db changed
                switch (name) {
                    case "name": role._name = reader.GetString(i);
                                      break;
                    case "role_id": role._roleId = reader.GetInt32(i);
                                      break;
                }
            }
            return reader.HasRows;
        }

        public QuestionEntity read(OdbcDataReader reader)
        {
            var res = new QuestionEntity();
            res.id = reader.GetInt32(0);
            res.ans = reader.GetString(2);


            var choicejson = reader.GetString(3);
            var decoder = new JavaScriptSerializer();
            res.choices = decoder.Deserialize< ArrayList >(choicejson);

            res.imageURL = reader.GetString(4);
            res.statement = reader.GetString(5);
            res.type = getTypeString( reader.GetInt32(1) );

            return res;

        }

        public static bool CreateForm(OdbcDataReader reader, Payment payment)
        {
            int fCount = reader.FieldCount;
            for (int i = 0; i < fCount; i++)
            {
                string name = reader.GetName(i);

                // Map to DB field. Need to change if db changed
                switch (name) {
                    case "course_id": payment._courseID = reader.GetInt32(i);
                                      break;
                    case "sum_all_cost": payment._sumAllCost = reader.GetInt32(i);
                                      break;
                    case "sum_max_payable": payment._sumMaxPayable = reader.GetInt32(i);
                                      break;
                    case "sum_paid_cost": payment._sumPaidCost = reader.GetInt32(i);
                                      break;
                    case "last_paid_date": payment._lastPaidDate = new DateTime(reader.GetDate(i).Ticks);
                                      break;
                    case "paid_round": payment._paidRound = reader.GetInt32(i);
                                      break;
                    case "status": payment._status = reader.GetInt32(i);
                                      break;
                    // helper info
                    case "bts_course_id": payment._btsCourseID = reader.GetString(i);
                                      break;
                    case "course_name": payment._courseName = reader.GetString(i);
                                      break;
                    case "course_start_date": payment._courseStartDate = new DateTime(reader.GetDate(i).Ticks);
                                      break;
                    case "course_end_date": payment._courseEndDate = new DateTime(reader.GetDate(i).Ticks);
                                      break;
                    case "paid_group_id": payment._paidGroupID = reader.GetInt32(i);
                                      break;

                }
            }
            return reader.HasRows;
        }

 //initializing the leader handler
 public void BeginHandling()
 {
     try
     {
         //writer.WriteLine("I am not done");
         command = " SELECT T.Number, T.Assigner, T.Issue_Date, I.Assigned, I.Attachment, I.JobDone, I.Status, I.Sequence, I.Due_Date, I.Requirements" +
                 " FROM Ticket AS T, Ticket_Information AS I" +
                 " WHERE T.Number=I.Number AND I.Assigned='" + userName +
                 "' AND (I.Status = 'Assigned' OR I.Status='Waiting' OR I.Status='Work_In_Progress') AND I.Due_Date > #" + System.DateTime.Now + "#";
         sqlCommand = new OdbcCommand(command, dbConn);
         dbReader = sqlCommand.ExecuteReader();
         //Console.WriteLine("I am done");
         while (dbReader.Read())
         {
             line = "";//empty the line so you can read the next row
             for (int i = 0; i < dbReader.FieldCount - 1; i++)
             {
                 line += dbReader.GetString(i) + "##";// but the whole row in one column and send it, seperated by commas
             }
             writer.WriteLine(line);
             writer.Flush();
         }
         writer.WriteLine(".");// end of reading from the database
         writer.Flush();
         DB_Handler.DisposeAll(sqlCommand, dbReader);
         WaitForQueries();
         //do not forget to close the reader
     }
     catch
     {
         Console.WriteLine(" The Database is down please try again later");//for debugging server
     }
 }