C# SQLiteCommand.ExecuteReader方法代码示例

        public bool FindIDByName(string FirstNameToFind, string LastNameToFind)
        {
            SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=C:\\IDDBShared\\IDDatabase.sqlite;Version=3;");
            m_dbConnection.Open();
            string sql = "select * from Users";
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            SQLiteDataReader reader = command.ExecuteReader();

            bool userDNE = true;
            while (reader.Read())
            {
                string currentFirstName = Convert.ToString(reader["firstName"]);
                string currentLastName = Convert.ToString(reader["lastName"]);
                if (currentFirstName == FirstNameToFind && currentLastName == LastNameToFind)
                {
                    UserName.Content = reader["firstName"] + " " + reader["lastName"];
                    IDNumber.Content = Convert.ToString(reader["IDNumber"]);
                    StartingBalance.Content = "$" + Convert.ToString(reader["beginningBalance"]);
                    Current_Balance.Content = "$" + Convert.ToString(reader["currentBalance"]);
                    string photoLocation = Convert.ToString(reader["photo"]);
                    IMGUserPhoto.Source = new BitmapImage(new Uri(photoLocation));
                    userDNE = false;
                }
            }

            return userDNE;
        }

        public static AttendanceReport[] GetAttendaceSummaryReports(string data_path)
        {
            SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + data_path);
            conn.Open();

            SQLiteCommand cmd = new SQLiteCommand(conn);
            cmd.CommandText = "select * from AttendanceSummary";

            SQLiteDataReader reader = cmd.ExecuteReader();

            List<AttendanceReport> attendanceReports = new List<AttendanceReport>();
            while (reader.Read())
            {
                int ID = reader.GetInt32(0);
                string timeIN = reader.GetString(1);
                string timeOUT = reader.GetString(2);
                string deltaTime = reader.GetString(3);
                string activity = reader.GetString(4);
                string mentor = reader.GetString(5);
                int index = reader.GetInt32(6);

                attendanceReports.Add(new AttendanceReport(ID, timeIN, timeOUT, deltaTime, activity, mentor, index));
            }
            reader.Close();
            conn.Close();
            return attendanceReports.ToArray();
        }

        protected internal static Bitmap RetrieveImage(int imgid)
        {
            using (SQLiteCommand command = new SQLiteCommand("select image from images where [email protected]", FetchDbConn()))
            {
                command.Parameters.Add(new SQLiteParameter("@imgid", imgid));

                using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
                {
                    if (!reader.Read())
                    {
                        return null;
                    }

                    // Get the size of the image data by passing nothing to getbytes
                    int dataLength = (int)reader.GetBytes(reader.GetOrdinal("image"), 0, null, 0, 0);
                    byte[] content = new byte[dataLength];

                    reader.GetBytes(reader.GetOrdinal("image"), 0, content, 0, dataLength);

                    using (MemoryStream contentStream = new MemoryStream(content))
                    {
                        using (Bitmap streamBitmap = new Bitmap(contentStream))
                        {
                            return new Bitmap(streamBitmap);
                        }
                    }
                }
            }
        }

        public static DataTable GetDataTable(string sql)
        {
            DataTable dt = new DataTable();

            try

            {

                SQLiteConnection cnn = new SQLiteConnection(@"Data Source=C:\Projects\showdownsharp\db\showdown.db");

                cnn.Open();

                SQLiteCommand mycommand = new SQLiteCommand(cnn);

                mycommand.CommandText = sql;

                SQLiteDataReader reader = mycommand.ExecuteReader();

                dt.Load(reader);

                reader.Close();

                cnn.Close();

            } catch {

            // Catching exceptions is for communists

            }

            return dt;
        }

        private List<Result> QuerySqllite(Doc doc, string key)
        {
            string dbPath = "Data Source =" + doc.DBPath;
            SQLiteConnection conn = new SQLiteConnection(dbPath);
            conn.Open();
            string sql = GetQuerySqlByDocType(doc.DBType).Replace("{0}", key);
            SQLiteCommand cmdQ = new SQLiteCommand(sql, conn);
            SQLiteDataReader reader = cmdQ.ExecuteReader();

            List<Result> results = new List<Result>();
            while (reader.Read())
            {
                string name = reader.GetString(reader.GetOrdinal("name"));
                string docPath = reader.GetString(reader.GetOrdinal("path"));

                results.Add(new Result
                    {
                        Title = name,
                        SubTitle = doc.Name.Replace(".docset", ""),
                        IcoPath = doc.IconPath,
                        Action = (c) =>
                            {
                                string url = string.Format(@"{0}\{1}\Contents\Resources\Documents\{2}#{3}", docsetPath,
                                                           doc.Name+".docset", docPath, name);
                                string browser = GetDefaultBrowserPath();
                                Process.Start(browser, String.Format("\"file:///{0}\"", url));
                                return true;
                            }
                    });
            }

            conn.Close();

            return results;
        }

        public SkillSet(ToonClass @class, Toon toon)
        {
            [email protected] = @class;

            var query = string.Format("SELECT * FROM active_skills WHERE id_toon={0}", toon.PersistentID);
            var cmd = new SQLiteCommand(query, DBManager.Connection);
            var reader = cmd.ExecuteReader();
            if (!reader.HasRows)
            {
                int[] ActiveSkillsList = Skills.GetAllActiveSkillsByClass([email protected]).Take(1).ToArray();

                this.ActiveSkills = new ActiveSkillSavedData[6] {
                    new ActiveSkillSavedData { snoSkill = ActiveSkillsList[0], snoRune = -1 },
                    new ActiveSkillSavedData { snoSkill = Skills.None, snoRune = -1 },
                    new ActiveSkillSavedData { snoSkill = Skills.None, snoRune = -1 },
                    new ActiveSkillSavedData { snoSkill = Skills.None, snoRune = -1 },
                    new ActiveSkillSavedData { snoSkill = Skills.None, snoRune = -1 },
                    new ActiveSkillSavedData { snoSkill = Skills.None, snoRune = -1 }
                };
                this.PassiveSkills = new int[3] { -1, -1, -1 };

                var insQuery = string.Format("INSERT INTO active_skills (id_toon,"+
                    "skill_0,skill_1,skill_2,skill_3,skill_4,skill_5,"+
                    "rune_0,rune_1,rune_2,rune_3,rune_4,rune_5,"+
                    "passive_0,passive_1,passive_2) VALUES ({0},{1},-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1 )",
                                 toon.PersistentID, ActiveSkillsList[0]);
                var insCmd = new SQLiteCommand(insQuery, DBManager.Connection);
                insCmd.ExecuteNonQuery();
            }
            else
            {
                this.ActiveSkills = new ActiveSkillSavedData[6] {
                    new ActiveSkillSavedData {  snoSkill = Convert.ToInt32(reader["skill_0"]), 
                                                snoRune  = Convert.ToInt32(reader["rune_0"]) },
                    new ActiveSkillSavedData {  snoSkill = Convert.ToInt32(reader["skill_1"]), 
                                                snoRune  = Convert.ToInt32(reader["rune_1"]) },
                    new ActiveSkillSavedData {  snoSkill = Convert.ToInt32(reader["skill_2"]), 
                                                snoRune  = Convert.ToInt32(reader["rune_2"]) },
                    new ActiveSkillSavedData {  snoSkill = Convert.ToInt32(reader["skill_3"]), 
                                                snoRune  = Convert.ToInt32(reader["rune_3"]) },
                    new ActiveSkillSavedData {  snoSkill = Convert.ToInt32(reader["skill_4"]), 
                                                snoRune  = Convert.ToInt32(reader["rune_4"]) },
                    new ActiveSkillSavedData {  snoSkill = Convert.ToInt32(reader["skill_5"]), 
                                                snoRune  = Convert.ToInt32(reader["rune_5"]) }
                };
                this.PassiveSkills = new int[3] {
                    Convert.ToInt32(reader["passive_0"]),
                    Convert.ToInt32(reader["passive_1"]),
                    Convert.ToInt32(reader["passive_2"])
                };
            }
            this.HotBarSkills = new HotbarButtonData[6] {
                new HotbarButtonData { SNOSkill = ActiveSkills[0].snoSkill, Field1 = -1, ItemGBId = -1 }, // left-click
                new HotbarButtonData { SNOSkill = ActiveSkills[1].snoSkill, Field1 = -1, ItemGBId = -1 }, // right-click
                new HotbarButtonData { SNOSkill = ActiveSkills[2].snoSkill, Field1 = -1, ItemGBId = -1 }, // bar-1
                new HotbarButtonData { SNOSkill = ActiveSkills[3].snoSkill, Field1 = -1, ItemGBId = -1 }, // bar-2
                new HotbarButtonData { SNOSkill = ActiveSkills[4].snoSkill, Field1 = -1, ItemGBId = -1 }, // bar-3
                new HotbarButtonData { SNOSkill = ActiveSkills[5].snoSkill, Field1 = -1, ItemGBId = -1 }, // bar-4
            };
        }

 public static DataTable buscaSing4()
 {
     DataTable tabla = new dataCredito.sing4DataTable();
     using (SQLiteConnection con = new SQLiteConnection(Datos.conexion))
     {
         using (SQLiteCommand comando = new SQLiteCommand())
         {
             comando.CommandText = "select * from sing4";
             comando.Connection = con;
             con.Open();
             SQLiteDataReader lector = comando.ExecuteReader();
             while (lector.Read())
             {
                 DataRow fila = tabla.NewRow();
                 fila["encabezado"] = lector.GetString(0);
                 fila["saludo"] = lector.GetString(1);
                 fila["pie"] = lector.GetString(2);
                 fila["asesor"] = lector.GetString(3);
                 fila["fecha"] = lector.GetDateTime(4);
                 fila["id"] = lector.GetString(5);
                 fila["idCliente"] = lector.GetInt32(6);
                 tabla.Rows.Add(fila);
             }
         }
         con.Close();
     }
     return (tabla);
 }

        public static void Test( )
        {
            using( var connection = new SQLiteConnection( ConnectionString ) )
            {
                connection.Open( );

                var command = new SQLiteCommand( "CREATE TABLE IF NOT EXISTS Blaat( Id INTEGER PRIMARY KEY NOT NULL, A, B )", connection );
                command.ExecuteNonQuery( );

                command = new SQLiteCommand( "INSERT INTO Blaat (A,B) VALUES( 1,2 )", connection );
                command.ExecuteNonQuery( );

                command = new SQLiteCommand( "INSERT INTO Blaat (A,B) VALUES( 3,4 )", connection );
                command.ExecuteNonQuery( );

                command = new SQLiteCommand( "SELECT * FROM BLAAT", connection );
                using( var reader = command.ExecuteReader( ) )
                {
                    while( reader.Read( ) )
                    {
                        Debug.WriteLine( string.Format( "(DIRECT)VALUES --> {0}:{1},{2}", reader.GetValue( 0 ), reader.GetValue( 1 ), reader.GetValue( 2 ) ) );
                    }

                    reader.Close( );
                }

                //command = new SQLiteCommand( "DROP TABLE BLAAT", connection );
                //command.ExecuteNonQuery();

                connection.Close( );
            }
        }

 /// <summary>
 /// 得到一个表中所有字段
 /// </summary>
 /// <param name="serverID"></param>
 /// <param name="dbName"></param>
 /// <param name="tableName"></param>
 /// <returns></returns>
 public List<Model.Fields> GetFields(string serverID, string dbName, string tableName)
 {
     List<Model.Fields> fieldsList = new List<Model.Fields>();
     using (SQLiteConnection conn = new SQLiteConnection(Common.Config.GetConnectionString(serverID, dbName)))
     {
         conn.Open();
         using (SQLiteCommand cmd = new SQLiteCommand(string.Format("PRAGMA table_info({0})", tableName), conn))
         {
             SQLiteDataReader dr = cmd.ExecuteReader();
             while (dr.Read())
             {
                 fieldsList.Add(new Model.Fields()
                 {
                     Name = dr["name"].ToString(),
                     Type = dr["type"].ToString(),
                     IsNull = "1" != dr["notnull"].ToString(),
                     IsPrimaryKey = "1" == dr["pk"].ToString(),
                     Default = dr["dflt_value"].ToString(),
                     IsIdentity = "1" == dr["pk"].ToString() && "INTEGER" == dr["type"].ToString(),
                     DotNetType = GetFieldType(dr["type"].ToString(), "1" != dr["notnull"].ToString()),
                     DotNetSqlType = GetFieldSqlType(dr["type"].ToString()),
                     Note = "",
                     Length = -1
                 });
             }
             dr.Close();
             dr.Dispose();
         }
     }
     return fieldsList;
 }

        /// <summary>
        /// sinartisi pou fortonei ola ta characteristics gia ena sigkekrimeno order id
        /// </summary>
        public static List<Characteristics> LoadCharacteristicsInOrder(int order_id)
        {
            List<Characteristics> characteristics = LoadCharacteristics();
            List<Characteristics> characteristicsInOrder = new List<Characteristics>();
            DBOpenClose(() =>
            {
                string sql = "select characteristics_id from characteristicsInOrders where order_id ='" + order_id + "'";
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);

                SQLiteDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        int characteristics_id = reader.GetInt32(0);

                        foreach (Characteristics ch in characteristics)
                        {
                            if (ch.Id == characteristics_id)
                                characteristicsInOrder.Add(ch);
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("LoadCharacteristicsInOrder " + ex.Message);
                }

            });
            return characteristicsInOrder;
        }

 public bool IsValid(string username)
 {
     var myDirectory = new DirectoryInfo(AppDomain.CurrentDomain.BaseDirectory);
     string parentDirectory = myDirectory.Parent.FullName;
     using (var cn = new SQLiteConnection(string.Format(@"Data Source={0}{1} Version=3;", parentDirectory, ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString)))
     {
         string _sql = @"SELECT id FROM Users " +
                "WHERE Name = '"+username+"';";
         cn.Open();
         var cmd = new SQLiteCommand(_sql, cn);
         var reader = cmd.ExecuteReader();
         if (reader.HasRows)
         {
             reader.Dispose();
             cmd.Dispose();
             return true;
         }
         else
         {
             reader.Dispose();
             cmd.Dispose();
             return false;
         }
     }
 }

        /// <summary>
        /// sinartisi pou fortonei apo tin basi ola ta characteristics
        /// </summary>
        public static List<Characteristics> LoadCharacteristics()
        {
            List<Characteristics> characteristics = new List<Characteristics>();
            DBOpenClose(() =>
            {
                string sql = "select * from characteristics";
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);

                SQLiteDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        Characteristics characteristic = new Characteristics(reader.GetInt32(0), reader.GetString(1), reader.GetDouble(2));
                        characteristics.Add(characteristic);
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("LoadCharacteristics " + ex.Message);
                }

            });
            return characteristics;
        }

        public override void update()
        {
            //Create a Dictionary to hold the receivers saved in the database
            Dictionary<string, Receiver> receivers = new Dictionary<string, Receiver>();

            //Create a new connection to the database
            using (SQLiteConnection m_dbConnection = new SQLiteConnection(@"Data Source=database.sqlite;Version=3;"))
            {
                //Open database connection
                m_dbConnection.Open();


                using (SQLiteCommand command = m_dbConnection.CreateCommand())
                {
                    //Select everything from the 'receivers' table
                    SQLiteCommand getReceivers = new SQLiteCommand("SELECT * FROM receivers", m_dbConnection);
                    SQLiteDataReader reader = getReceivers.ExecuteReader();

                    //Read every entry in the receivers table
                    while (reader.Read())
                    {
                        string name = (string)reader["name"];
                        Receiver battery = new Receiver((string)reader["name"], (double)reader["weight"], (int)reader["channelCount"], (int)reader["minVoltage"], (int)reader["maxVoltage"]);
                        //Add the battery into the dictionary using the name as the key and a new Receiver object as the value
                        receivers.Add(name, battery);
                    }
                }
                //Close the database connection
                m_dbConnection.Close();
            }
            //Save the updated savedReceiver list 
            savedReceivers = receivers;
        }

 public List<HistoryRow> getAll()
 {
     List<HistoryRow> list = new List<HistoryRow>();
     Debug.WriteLine("Read history");
     try
     {
         string sql = "select * from history order by date asc";
         conn.Open();
         SQLiteCommand command = new SQLiteCommand(sql, conn);
         SQLiteDataReader reader = command.ExecuteReader();
         while (reader.Read())
         {
             list.Add(new HistoryRow(reader.GetInt16(0), reader.GetString(3), reader.GetString(2), reader.GetString(1)));
         }
     }
     catch (SQLiteException e)
     {
         Debug.WriteLine("Not connected : " + e.ToString());
         return list;
     }
     finally
     {
         Debug.WriteLine("End..");
         conn.Close();
     }
     return list;
 }

        public static bool IsExistingClientByHostname(string hostname)
        {
            bool clientExists = false;

            string connStr = ConfigurationManager.ConnectionStrings["uWiMPConnString"].ConnectionString;
            SQLiteConnection conn = new SQLiteConnection(connStr);
            SQLiteDataReader reader;

            SQLiteCommand cmd = new SQLiteCommand("SELECT Hostname FROM `MPClients` WHERE Hostname = $Hostname", conn);
            cmd.Parameters.Add("$Hostname", DbType.String, 255).Value = hostname.ToLower();

            try
            {
                conn.Open();
                reader = cmd.ExecuteReader(CommandBehavior.Default);
                if (reader.HasRows)
                    clientExists = true;
            }
            catch (SQLiteException ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }

            return clientExists;
        }