C# SQLiteDataReader.GetValue方法代码示例

    protected void Button1_Click(object sender, EventArgs e)
    {
        con = new SQLiteConnection("Data Source=e:\\db\\project.s3db");
        con.Open();
        SQLiteCommand command = new SQLiteCommand("Select * from train", con);
        reader = command.ExecuteReader();

        //SQLiteDataAdapter ad=new SQLiteDataAdapter(,con);

        //GridView gv = new GridView();
        String str = "<table><tr><th>train no</th><th>train name</th><th>train timing</th><th>avaliable tickets</th><th>total tickets</th><th>cost</th></tr>";
        while (reader.Read())
        {
            str += "</tr><td>" + reader.GetValue(0) + "</td>";
            str += "<td>" + reader.GetString(1) + "</td>";
            str += "<td>" + reader.GetString(2) + "</td>";
            str += "<td>" + reader.GetValue(3) + "</td>";
            str += "<td>" + reader.GetValue(4) + "</td>";
            str += "<td>" + reader.GetValue(5) + "</td></tr>";
        }
        str += "</table>";
        traindiv.InnerHtml = str;
        con.Close();
    }

    protected void Button2_Click(object sender, EventArgs e)
    {
        con = new SQLiteConnection("Data Source=e:\\db\\project.s3db");
        con.Open();
        SQLiteCommand command = new SQLiteCommand("Select * from train", con);
        reader = command.ExecuteReader();
        //SQLiteDataAdapter ad=new SQLiteDataAdapter(,con);

        //GridView gv = new GridView();

        while (reader.Read())
        {
            trainno_list.Items.Add(reader.GetValue(0).ToString());
        }
        bookdiv.Style.Value = "visibility:visible";
        con.Close();
    }

        public static object ReadBoolean(SQLiteDataReader reader, int idx)
        {
            try
            {
                object val = reader.GetValue(idx);
                if (val == null)
                    return false;
                if (val is int)
                    return (int)val == 1;

                return val.ToString() == "1";
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static object ReadFloat(SQLiteDataReader reader, int idx)
        {
            try
            {
                object val = reader.GetValue(idx);
                if (val == null)
                    return 0;
                string sval = val.ToString();
                if (String.IsNullOrEmpty(sval))
                    return 0;

                return Single.Parse(sval, System.Globalization.CultureInfo.InvariantCulture);
            }
            catch (Exception)
            {
                return 0;
            }
        }

        public static object ReadDateTime(SQLiteDataReader reader, int idx)
        {
            try
            {
                object val = reader.GetValue(idx);
                if (val == null)
                    return new DateTime(1970, 1, 1);
                if (val is DateTime)
                    return val;

                string sval = val.ToString();
                if (String.IsNullOrWhiteSpace(sval))
                    return new DateTime(1970, 1, 1);
                return DateTime.Parse(sval);
            }
            catch (Exception)
            {
                return new DateTime(1970, 1, 1);
            }
        }

        void ReadHeader()
        {
            SQLiteCommand command = _connection.CreateCommand();
            command.CommandText = "SELECT key, value FROM header;";
            _reader = command.ExecuteReader();

            while (_reader.Read())
            {
                var key = _reader.GetString(0);
                var value = _reader.GetValue(1);

                if (key.ToLower() == "clientbuild")
                {
                    int build;
                    if (int.TryParse(value.ToString(), out build))
                        SetBuild(build);

                    break;
                }
            }

            _reader.Close();
        }

 public static void ReadValue(SQLiteDataReader reader, int index, TypeStorage type, ICdlValueWriter writer)
 {
     switch (type)
     {
         case TypeStorage.Boolean:
             writer.SetBoolean(reader.GetInt32(index) != 0);
             break;
         case TypeStorage.Byte:
             writer.SetByte((byte) reader.GetInt32(index));
             break;
         case TypeStorage.Int16:
             writer.SetInt16((short) reader.GetInt32(index));
             break;
         case TypeStorage.Int32:
             writer.SetInt32((int) reader.GetInt32(index));
             break;
         case TypeStorage.Int64:
             writer.SetInt64((long) reader.GetInt64(index));
             break;
         case TypeStorage.SByte:
             writer.SetSByte((sbyte) reader.GetInt32(index));
             break;
         case TypeStorage.UInt16:
             writer.SetUInt16((ushort) reader.GetInt32(index));
             break;
         case TypeStorage.UInt32:
             writer.SetUInt32((uint) reader.GetInt32(index));
             break;
         case TypeStorage.UInt64:
             writer.SetUInt64((ulong) reader.GetInt64(index));
             break;
         case TypeStorage.DateTime:
             writer.SetDateTime(DateTime.Parse(reader.GetString(index), CultureInfo.InvariantCulture));
             //writer.SetDateTime(DateTime.ParseExact(reader.GetString(index), "s", CultureInfo.InvariantCulture));
             break;
         case TypeStorage.DateTimeEx:
             writer.SetDateTimeEx(DateTimeEx.ParseNormalized(reader.GetString(index)));
             break;
         case TypeStorage.DateEx:
             writer.SetDateEx(DateEx.ParseNormalized(reader.GetString(index)));
             break;
         case TypeStorage.TimeEx:
             writer.SetTimeEx(TimeEx.ParseNormalized(reader.GetString(index)));
             break;
         case TypeStorage.Decimal:
             {
                 var dtype = reader.GetFieldType(index);
                 decimal value;
                 if (dtype == typeof (string))
                 {
                     value = Decimal.Parse(reader.GetString(index), CultureInfo.InvariantCulture);
                 }
                 else
                 {
                     value = (decimal) reader.GetDouble(index);
                 }
                 writer.SetDecimal(value);
             }
             break;
         case TypeStorage.Float:
             writer.SetFloat((float) reader.GetDouble(index));
             break;
         case TypeStorage.Double:
             writer.SetDouble((double) reader.GetDouble(index));
             break;
         case TypeStorage.String:
             writer.SetString(reader.GetString(index));
             break;
         case TypeStorage.Guid:
             writer.SetGuid(new Guid(reader.GetString(index)));
             break;
         case TypeStorage.ByteArray:
             writer.SetByteArray((byte[]) reader.GetValue(index));
             break;
         case TypeStorage.Null:
             writer.SetNull();
             break;
         default:
             throw new Exception("DBSH-00167 Unsupported field type:" + type.ToString());
     }
 }

        //
        // GetUserFromReader
        //    A helper function that takes the current row from the SQLiteDataReader
        // and hydrates a MembershipUser from the values. Called by the 
        // MembershipUser.GetUser implementation.
        //

        private MembershipUser GetUserFromReader(SQLiteDataReader reader)
        {
            if (reader.GetString(1)=="") return null;
            object providerUserKey=null;
            string strGooid=Guid.NewGuid().ToString();
            if (reader.GetValue(0).ToString().Length > 0)
                providerUserKey = new Guid(reader.GetValue(0).ToString());
            else
                providerUserKey = new Guid(strGooid);
            string username = reader.GetString(1);
            string email = reader.GetString(2);

            string passwordQuestion = "";
            if (reader.GetValue(3) != DBNull.Value)
                passwordQuestion = reader.GetString(3);

            string comment = "";
            if (reader.GetValue(4) != DBNull.Value)
                comment = reader.GetString(4);

            bool tmpApproved = (reader.GetValue(5) == null);
             bool isApproved=false;
            if(tmpApproved)
            isApproved = reader.GetBoolean(5);

        bool tmpLockedOut = (reader.GetValue(6) == null);
        bool isLockedOut = false;
            if(tmpLockedOut)
            isLockedOut = reader.GetBoolean(6);

        DateTime creationDate = DateTime.Now;
            try
            {
                if (reader.GetValue(6) != DBNull.Value)
                    creationDate = reader.GetDateTime(7);
            }
            catch { }

            DateTime lastLoginDate = DateTime.Now;
            try
            {
                if (reader.GetValue(8) != DBNull.Value)
                    lastLoginDate = reader.GetDateTime(8);
            }
            catch { }

            DateTime lastActivityDate = DateTime.Now;
            try
            {
                if (reader.GetValue(9) != DBNull.Value)
                    lastActivityDate = reader.GetDateTime(9);
            }
            catch { }
            DateTime lastPasswordChangedDate = DateTime.Now;
            try
            {
                if (reader.GetValue(10) != DBNull.Value)
                    lastPasswordChangedDate = reader.GetDateTime(10);
            }
            catch { }

            DateTime lastLockedOutDate = DateTime.Now;
            try
            {
                if (reader.GetValue(11) != DBNull.Value)
                    lastLockedOutDate = reader.GetDateTime(11);
            }
            catch { }

            MembershipUser u = new MembershipUser(this.Name,
                                                  username,
                                                  providerUserKey,
                                                  email,
                                                  passwordQuestion,
                                                  comment,
                                                  isApproved,
                                                  isLockedOut,
                                                  creationDate,
                                                  lastLoginDate,
                                                  lastActivityDate,
                                                  lastPasswordChangedDate,
                                                  lastLockedOutDate);

            return u;
        }

 //Reader数据封装成list
 private List<DealListEntity> Package(SQLiteDataReader reader)
 {
     List<DealListEntity> DLEL;
     DLEL = new List<DealListEntity>();
     DealListEntity DLE;
     if (reader.HasRows)
     {
         while (reader.Read())
         {
             DLE.deal = Convert.ToInt32(reader.GetValue(0));
             DLE.name = reader.GetValue(1).ToString();
             DLE.id = reader.GetValue(2).ToString();
             DLE.date = Convert.ToDateTime(reader.GetValue(3));
             DLE.type = reader.GetValue(4).ToString();
             DLE.money = Convert.ToDouble(reader.GetValue(5));
             DLE.number = Convert.ToInt32(reader.GetValue(6));
             DLE.taxrate =  Convert.ToDouble(reader.GetValue(7));
             DLE.commission =  Convert.ToDouble(reader.GetValue(8));
             DLE.explain = reader.GetValue(9).ToString();
             DLE.remark = reader.GetValue(10).ToString();
             DLEL.Add(DLE);
         }
     }
     return DLEL;
 }

        //read data from database and add to drawParameterList
        public List<Coordinates.Coordinate> readTrackData(int trackId)
        {
            StringBuilder query;

            dbCoordinatesList = new List<Coordinates.Coordinate>();

            try
            {
                query = new StringBuilder();

                query.Append("SELECT DISTINCT longitude, latitude, altitude, fix FROM gpsData WHERE trackId = " + Int64.Parse(trackId.ToString()) + " order by ID ASC ;");

                createSqliteConn();

                dataReader = readData(query);

                while(dataReader.Read())
                {
                     dbCoordinatesList.Add(new Coordinates.Coordinate(new Geometry.Point( float.Parse(dataReader.GetString(1).ToString()),
                        float.Parse(dataReader.GetString(0).ToString()),
                        float.Parse(dataReader.GetValue(2).ToString().Contains(",") ? dataReader.GetValue(2).ToString() : dataReader.GetValue(2).ToString() + ",0")),
                        true
                        ));//(latitude, longitude, altitude)
                }

                closeSqliteConn();
            }
            catch (SQLiteException sqliteEx)
            {
                MessageBox.Show("m2" + sqliteEx.Message);
            }

            return dbCoordinatesList;
        }

//.........这里部分代码省略.........
                                double v = table.GetDouble(column.ColumnNumber);
                                /*
                                NumberFormatInfo provider = new NumberFormatInfo();
                                provider.NumberDecimalDigits = 2;
                                provider.NumberGroupSeparator = ".";
                                provider.NumberGroupSizes = new int[] { 3 };
                                strText = Convert.ToString(v, provider);
                                 * */
                                strText = v.ToString("N", nfi);
                            }
                        }
                        else if (column.DataType == DataType.PriceDecimal)
                        {
                            if (table.IsDBNull(column.ColumnNumber) == true)
                                strText = column.DefaultValue;
                            else
                            {
                                decimal v = table.GetDecimal(column.ColumnNumber);
                                strText = v.ToString("N", nfi);
                            }
                        }
                        else if (column.DataType == DataType.PriceDecimal)
                        {
                            if (table.IsDBNull(column.ColumnNumber) == true)
                                strText = column.DefaultValue;
                            else
                            {
                                decimal v = table.GetDecimal(column.ColumnNumber);
                                strText = v.ToString("N", nfi);
                            }
                        }
                        else if (column.DataType == DataType.Price)
                        {
                            // Debug.Assert(false, "");
                            if (table.IsDBNull(column.ColumnNumber) == true)
                                strText = column.DefaultValue;	// 2005/5/26
                            else
                                strText = table.GetString(column.ColumnNumber);    // 
                        }
                        else
                            strText = table.GetString(column.ColumnNumber/*, column.DefaultValue*/);
                    }
                    else
                    {
                        strText = table.GetString(0);   // line.Entry;
                    }

                    writer.WriteStartElement(j == 0 ? "th" : "td");
                    if (string.IsNullOrEmpty(column.CssClass) == false)
                        writer.WriteAttributeString("class", column.CssClass);
                    writer.WriteString(strText);
                    writer.WriteEndElement();   // </td>

                    if (report.SumLine == true
                        && column.Sum == true
                        && column.ColumnNumber != -1)
                    {
                        try
                        {
                            // if (column.DataType != DataType.Currency)
                            {
                                object v = table.GetValue(column.ColumnNumber);
#if NO
                                if (report.SumCell != null)
                                {
                                    SumCellEventArgs e = new SumCellEventArgs();
                                    e.DataType = column.DataType;
                                    e.ColumnNumber = column.ColumnNumber;
                                    e.LineIndex = i;
                                    e.Line = line;
                                    e.Value = v;
                                    report.SumCell(this, e);
                                    if (e.Value == null)
                                        continue;

                                    v = e.Value;
                                }
#endif

                                if (sums[j] == null)
                                    sums[j] = v;
                                else
                                {
                                    sums[j] = AddValue(column.DataType,
            sums[j],
            v);
                                    // sums[j] = ((decimal)sums[j]) + v;
                                }
                            }
                        }
                        catch (Exception ex)	// 俘获可能因字符串转换为整数抛出的异常
                        {
                            throw new Exception("在累加 行 " + i.ToString() + " 列 " + column.ColumnNumber.ToString() + " 值的时候，抛出异常: " + ex.Message);
                        }
                    }
                }

                // strResult.Append("</tr>\r\n");
                writer.WriteEndElement();   // </tr>
            }

        public static object ReadString(SQLiteDataReader reader, int idx)
        {
            try
            {
                object val = reader.GetValue(idx);
                if (val == null)
                    return "";

                string sval = val.ToString();
                return String.IsNullOrWhiteSpace(sval) ? String.Empty : sval;
            }
            catch (Exception)
            {
                return "";
            }
        }

        public DataSet DataReader2DataSet ( SQLiteDataReader reader )
        {
            DataSet dataSet = new DataSet ( );
            do
            {
                // Create new data table

                DataTable schemaTable = reader.GetSchemaTable ( );
                DataTable dataTable = new DataTable ( );

                if ( schemaTable != null )
                {
                    // A query returning records was executed

                    for ( int i = 0 ; i < schemaTable.Rows.Count ; i++ )
                    {
                        DataRow dataRow = schemaTable.Rows [ i ];
                        // Create a column name that is unique in the data table
                        string columnName = ( string ) dataRow [ "ColumnName" ];
                        // Add the column definition to the data table
                        DataColumn column = new DataColumn ( columnName , ( Type ) dataRow [ "DataType" ] );
                        dataTable.Columns.Add ( column );
                    }

                    dataSet.Tables.Add ( dataTable );

                    // Fill the data table we just created

                    while ( reader.Read ( ) )
                    {
                        DataRow dataRow = dataTable.NewRow ( );

                        for ( int i = 0 ; i < reader.FieldCount ; i++ )
                            dataRow [ i ] = reader.GetValue ( i );

                        dataTable.Rows.Add ( dataRow );
                    }
                }
                else
                {
                    // No records were returned

                    DataColumn column = new DataColumn ( "RowsAffected" );
                    dataTable.Columns.Add ( column );
                    dataSet.Tables.Add ( dataTable );
                    DataRow dataRow = dataTable.NewRow ( );
                    dataRow [ 0 ] = reader.RecordsAffected;
                    dataTable.Rows.Add ( dataRow );
                }
            }
            while ( reader.NextResult ( ) );
            return dataSet;
        }

 /// <summary>
 /// Analyses the SQLiteDataReader passed to it, filling the
 /// Rows collection using the column name and it's respective value
 /// for each row returned.
 /// </summary>
 /// <param name="reader">The SQLiteDataReader object containing the results of a query.</param>
 public void Analyse(SQLiteDataReader reader)
 {
     int currentRow = 0;
     while (reader.Read())
     {
         if (reader.HasRows)
         {
             Dictionary<string, object> columns = new Dictionary<string, object>();
             for (int i = 0; i < reader.FieldCount; i++)
             {
                 columns.Add(reader.GetName(i), reader.GetValue(i));
             }
             this.Rows.Add(currentRow, columns);
         }
         currentRow++;
     }
 }

        private static File ParseWithReferences(SQLiteDataReader dr)
        {
            if (!dr.Read()) return null;

            File file = Parse(dr);

            List<Tag> tags = new List<Tag>();
            dr.NextResult();

            while (dr.Read())
            {
                Tag tag;
                tags.Add(tag = new Tag
                {
                    Id = dr.GetInt32(0),
                    Description = dr.GetString(1)
                });

                object idTagTypeObj = dr.GetValue(2);
                if (idTagTypeObj != DBNull.Value)
                {
                    tag.TagType = new TagType
                    {
                        Id = dr.GetInt32(2),
                        Description = dr.GetString(3)
                    };
                }
            }

            file.Tags = tags;

            return file;
        }