C# SqlCeCommand.ExecuteReader方法代码示例

        public IQueryable<Blog> Fetch()
        {
            var blogs = new List<Blog>();

            //const string connectionString = @"Data Source = 'App_Data\TBlogs.sdf'"; Not working even though copied to bin
            const string connectionString = @"Data Source = 'C:\Projects\CSharp\Principles\DependecyInversionPrinciple\DependencyInversionPrinciple WithAnIocContainer\App_Data\TBlogs.sdf'";

            var connection = new SqlCeConnection(connectionString);
            var command = new SqlCeCommand("", connection);

            connection.Open();
            command.CommandText = "SELECT * FROM Blog;";

            var dataReader = command.ExecuteReader();

            while (dataReader.Read())
            {
                var blog = new Blog
                               {
                                   Author = dataReader["Author"].ToString(),
                                   Title = dataReader["Title"].ToString(),
                                   Url = dataReader["Url"].ToString(),
                                   Email = dataReader["Email"].ToString()
                               };

                blogs.Add(blog);
            }

            return blogs.AsQueryable();
        }

        public static List<StudentModel> ReadAllStudents()
        {
            string cmdText =
                @"SELECT Id, ExpGraduation, Name, CanCode, CreditsLeft, Advisor FROM Student";

            List<StudentModel> list = new List<StudentModel>();

            using (SqlCeConnection conn = CreateConnection())
            {
                using (SqlCeCommand cmd = new SqlCeCommand(cmdText, conn))
                {
                    using (SqlCeDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            StudentModel item = new StudentModel()
                            {
                                Id = (int) reader["Id"],
                                ExpGraduation = (DateTime) reader["ExpGraduation"],
                                Name = (string) reader["Name"],
                                CanCode = (bool) reader["CanCode"],
                                CreditsLeft = (int) reader["CreditsLeft"],
                                Advisor = (string) reader["Advisor"]
                            };
                            list.Add(item);
                        }
                    }
                }
            }

            return list;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            DataConnectionDialog dcd = new DataConnectionDialog();
            DataConnectionConfiguration dcs = new DataConnectionConfiguration(null);
            dcs.LoadConfiguration(dcd);

            if (DataConnectionDialog.Show(dcd) == DialogResult.OK)
            {

                textBox2.Text = dcd.ConnectionString;
                connectionString = dcd.ConnectionString;
                comboBox1.Enabled = true;
                using (SqlCeConnection con = new SqlCeConnection(connectionString))
                {
                    comboBox1.Items.Clear();
                    con.Open();
                    using (SqlCeCommand command = new SqlCeCommand("SELECT table_name FROM INFORMATION_SCHEMA.Tables", con))
                    {
                        SqlCeDataReader reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            comboBox1.Items.Add(reader.GetString(0));

                        }
                    }
                }
                //textBox1.Text = dcd.SelectedDataSource.DisplayName;
            }
            dcs.SaveConfiguration(dcd);
        }

    public static bool IsPersonOnBoard(string boardId, string personId)
    {
        string query = "SELECT * FROM [boards] WHERE [email protected] AND [email protected]";

        if (String.IsNullOrEmpty(boardId) || String.IsNullOrEmpty(personId))
        {
            return false;
        }
        using (SqlCeConnection conn = new SqlCeConnection())
        {
            conn.ConnectionString = CommonState.ConnectionString;
            conn.Open();
            using (SqlCeCommand cmd = new SqlCeCommand(null, conn))
            {
                cmd.CommandText = query;
                cmd.Parameters.Add("@boardId", boardId);
                cmd.Parameters.Add("@personId", personId);
                cmd.Prepare();
                using (SqlCeDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        long last = (long)reader["person_lastactivity"];
                        if (last > (CommonState.EpochTime - Configure.CONNECTOIN_TIMEOUT))
                        {
                            return true;
                        }
                    }
                }
            }
        }
        return false;
    }

        public void Tt()
        {
            string connectionString = @"DataSource=db.sdf";

            var conn = new SqlCeConnection(connectionString);
            if(!File.Exists(conn.Database))
            {
                new SqlCeEngine(connectionString).CreateDatabase();
            }
            conn.Open();

            //Creating a table
            var cmdCreate = new SqlCeCommand("CREATE TABLE Products (Id int IDENTITY(1,1), Title nchar(50), PRIMARY KEY(Id))", conn);
            cmdCreate.ExecuteNonQuery();

            //Inserting some data...
            var cmdInsert = new SqlCeCommand("INSERT INTO Products (Title) VALUES ('Some Product #1')", conn);
            cmdInsert.ExecuteNonQuery();

            //Making sure that our data was inserted by selecting it
            var cmdSelect = new SqlCeCommand("SELECT Id, Title FROM Products", conn);
            SqlCeDataReader reader = cmdSelect.ExecuteReader();
            reader.Read();
            Console.WriteLine("Id: {0} Title: {1}", reader["Id"], reader["Title"]);
            reader.Close();

            conn.Close();
        }

        public static IEnumerable<string> GetFileDatabaseTables(string databaseFile, string password = null)
        {
            if (string.IsNullOrEmpty(databaseFile) || !File.Exists(databaseFile))
            {
                throw new Exception("Database not exists");
            }

            IList<string> tableNames = new List<string>();

            SqlCeConnectionStringBuilder connectionString = new SqlCeConnectionStringBuilder();
            connectionString.DataSource = databaseFile;

            if (!string.IsNullOrEmpty(password))
                connectionString.Password = password;

            using (SqlCeConnection sqlCon = new SqlCeConnection(connectionString.ToString()))
            {
                string sqlStmt = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES";
                using (SqlCeCommand cmd = new SqlCeCommand(commandText: sqlStmt, connection: sqlCon))
                {
                    sqlCon.Open();
                    var reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        tableNames.Add((string)reader["table_name"]);
                    }
                    sqlCon.Close();
                }
            }

            return tableNames;
        }

        public static List<BookStoreModel> ReadAllBooks()
        {
            string cmdText =
                @"SELECT Id, Title, Author, PublishedDate, Cost, InStock, BindingType FROM Books";//Cover Add back after figure out how to data type this

            List<BookStoreModel> list = new List<BookStoreModel>();

            using (SqlCeConnection conn = CreateConnection())
            {
                using (SqlCeCommand cmd = new SqlCeCommand(cmdText, conn))
                {
                    using (SqlCeDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            BookStoreModel book = new BookStoreModel();

                            book.Id = (int)reader["Id"];
                            book.Title = (string)reader["Title"];
                            book.Author = (string)reader["Author"];
                            book.PublishedDate = (DateTime)reader["PublishedDate"];
                            book.Cost = (double)reader["Cost"];
                            book.InStock = (bool)reader["InStock"];
                            book.BindingType = (string)reader["BindingType"];
                            //book.Cover = (???)reader["Cover"];

                            list.Add(book);
                        }
                    }
                }
            }

            return list;
        }

 public IList<EmployeeEntity> FindAll()
 {
     using (var cn = new SqlCeConnection(constr))
     {
         cn.Open();
         var sql = "SELECT ID , Name , Age , Email FROM Employee";
         using (var cmd = new SqlCeCommand(sql, cn))
         {
             using (var dr = cmd.ExecuteReader())
             {
                 var result = new List<EmployeeEntity>();
                 while (dr.Read())
                 {
                     var rec = new EmployeeEntity()
                     {
                         ID = (int)dr["ID"],
                         Name = (string)dr["Name"],
                         Age = (int)dr["Age"],
                         Email = (string)dr["Email"],
                     };
                     result.Add(rec);
                 }
                 return result;
             }
         }
     }
 }

 private void connectionBdd(string query)
 {
     connexion();
     cn.Open();
     SqlCeCommand comm = new SqlCeCommand(query, cn);
     SqlCeDataReader dr = comm.ExecuteReader();
 }

        public void ExecuteQuery(SqlCeCommand cmd)
        {
            if (_isDatabaseAvailable)
                try
                {
                    using (var cn = new SqlCeConnection(_conn))
                    {
                        cn.Open();
                        cmd.Connection = cn;
                        XQuery(cmd.ExecuteReader());
                        //return cmd.ExecuteReader();

                    }
                }
                catch (SqlCeException cex)
                {
                    System.Windows.Forms.MessageBox.Show(cex.ToString());
                }
                catch (Exception ex)
                {
                    System.Windows.Forms.MessageBox.Show(ex.ToString());
                }

            //return null;
        }

        /// <summary>
        /// Gets the product detail by category.
        /// </summary>
        /// <param name="category">The category.</param>
        /// <returns></returns>
        public List<ProductDetail> GetProductDetailByCategory(string category)
        {
            // The results of the query
            List<ProductDetail> products = new List<ProductDetail>();

            // Build up the query string
            const string query = "SELECT * FROM Product WHERE Category = @category";

            using (var connection = new SqlCeConnection(_connectionString))
            {
                connection.Open();

                // Build up the SQL command
                SqlCeCommand sqlCommand = new SqlCeCommand(query, connection);
                sqlCommand.Parameters.AddWithValue("@category", category);

                using (SqlCeDataReader sqlCeReader = sqlCommand.ExecuteReader())
                {
                    while (sqlCeReader.Read())
                    {
                        // Build up the object
                        ProductDetail productDetail = new ProductDetail();
                        productDetail.ProductId = Convert.ToInt32(sqlCeReader["ProductId"]);
                        productDetail.ProductDescription = sqlCeReader["ProductDescription"] != null ? sqlCeReader["ProductDescription"].ToString() : string.Empty;
                        productDetail.ImageUrl = sqlCeReader["ImageUrl"] != null ? sqlCeReader["ImageUrl"].ToString() : string.Empty;
                        productDetail.Category = sqlCeReader["Category"] != null ? sqlCeReader["Category"].ToString() : string.Empty;

                        // Add to the collection
                        products.Add(productDetail);
                    }
                }
            }

            return products;
        }

        public static void Select()
        {
            IDbCommand command = new SqlCeCommand();
            IDbConnection connection = new SqlCeConnection();
            string dbfile = new System.IO.FileInfo(System.Reflection.Assembly.GetExecutingAssembly().Location).DirectoryName + "\\MainDB.sdf";
            connection.ConnectionString = "Data Source=" + dbfile;
            command.Connection = connection;
            command.Connection.Open();
            command.Parameters.Clear();
            command.CommandText = String.Format(@"
                SELECT
                [Id],
                [Name],
                [Score]
                FROM [PlayersData]
                ORDER BY [Id]
            ");

            IDataReader reader = command.ExecuteReader();
            Players.Clear();

            while (reader.Read())
            {
                Players.Add(new Player {Id = (int) reader["Id"], Name = (string) reader["Name"], Score = (int) reader["Score"]});
            }
            command.Connection.Close();
        }

        public List<ETipoAssociado> Listar()
        {
            #region declaração de variáveis
            SqlCeConnection cnn = new SqlCeConnection();
            SqlCeCommand cmd = new SqlCeCommand();

            cnn.ConnectionString = Conexao.Caminho;
            cmd.Connection = cnn;
            #endregion declaração de variáveis

            cmd.CommandText = "SELECT * FROM TipoAssociado ORDER BY Descricao";

            cnn.Open();
            SqlCeDataReader rdr = cmd.ExecuteReader();

            List<ETipoAssociado> lstRetorno = new List<ETipoAssociado>();
            while (rdr.Read())
            {
                ETipoAssociado _TipoAssociado = new ETipoAssociado();
                _TipoAssociado.identificador = int.Parse(rdr["identificador"].ToString());
                _TipoAssociado.descricao = rdr["Descricao"].ToString();
                lstRetorno.Add(_TipoAssociado);
            }
            cnn.Close();
            return lstRetorno;
        }

        public ETipoAssociado Consultar(int identificador)
        {
            #region declaração de variáveis
            SqlCeConnection cnn = new SqlCeConnection();
            SqlCeCommand cmd = new SqlCeCommand();

            cnn.ConnectionString = Conexao.Caminho;
            cmd.Connection = cnn;
            #endregion declaração de variáveis

            cmd.CommandText = "SELECT * FROM TipoAssociado WHERE identificador = @identificador";
            cmd.Parameters.Add("@identificador", identificador);

            cnn.Open();
            SqlCeDataReader rdr = cmd.ExecuteReader();
            ETipoAssociado _TipoAssociado = new ETipoAssociado();

            if (rdr.Read())
            {
                _TipoAssociado.identificador = int.Parse(rdr["identificador"].ToString());
                _TipoAssociado.descricao = rdr["Descricao"].ToString();
            }
            cnn.Close();
            return _TipoAssociado;
        }

        static void Listen()
        {
            //[ClientID],[pNumber],[msg],[move],[start board]
            while (true)
            {
                string sql;
                string rcvd = Console.ReadLine();

                if (rcvd.Split('¬')[2].StartsWith("cht:"))
                {
                    sql = "insert into history(ClientID,name,message,status) values (" + System.Diagnostics.Process.GetCurrentProcess().Id + ",'" + name + "','" + msg.Substring(4) + "','N')";
                    SqlCeCommand command = new SqlCeCommand(sql, m_dbConnection);
                    command.ExecuteNonQuery();
                }

                if (rcvd.Split('¬')[2].StartsWith("lst:"))
                {
                    sql = "SELECT * FROM Session where clientID2 is null";
                    SqlCeCommand command = new SqlCeCommand(sql, m_dbConnection);
                    command.CommandText = sql;
                    command.CommandType = System.Data.CommandType.Text;
                    SqlCeDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        Console.WriteLine("¬" + reader["message"].ToString() + "¬");
                    }
                }
            }
        }