C# SqlCommand.ExecuteReader方法代码示例

        private void ReadOrderData(string filterPrimkey, string connectionString, int rowIndex)
        {
            var queryString =
              "SELECT numcode, charcode, nominal, name, value FROM dbo.values_history inner join dbo.description on  dbo.description.id = dbo.values_history.id WHERE dbo.values_history.primkey ='" + filterPrimkey + "' ;";

            try
            {
                using (var connection =
                      new SqlConnection(connectionString))
                {
                    var command =
                        new SqlCommand(queryString, connection);
                    connection.Open();

                    var reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        WriteCurrency(reader, rowIndex);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Text = ex.Message;
            }
        }

        public void GetScheduledAppointments()
        {
            var con = new SqlConnection("Data Source=CSDB;Initial Catalog=SEI_Ninja;Persist Security Info=True;UID=sei_timemachine;PWD=z5t9l3x0");



            string sql = @"SELECT e.eventID, e.eventName, e.eventLocation, et.eventDate, et.eventDuration, u.user_first_name + ' ' + u.user_last_name AS name
                             FROM [SEI_Ninja].[dbo].SCHEDULED_USERS su
                                  JOIN [SEI_Ninja].[dbo].EVENT_TIMES et ON (su.eventTimeID = et.eventTimeID)
                                  JOIN [SEI_TimeMachine2].[dbo].[USER] u ON (su.userID = u.user_id)
                                  JOIN [SEI_Ninja].[dbo].EVENT e ON (et.eventID = e.eventID)
                            WHERE e.eventOwner = 'mgeary'
                            ORDER BY e.eventID";
            using (var command = new SqlCommand(sql, con))
            {
                con.Open();
                using (var reader = command.ExecuteReader())
                {
                    var list = new List<ScheduledAppointment>();
                    while (reader.Read())
                        list.Add(new ScheduledAppointment
                        {
                            eventID = reader.GetInt32(0),
                            eventName = reader.GetString(1),
                            eventLocation = reader.GetString(2),
                            eventDate = reader.GetDateTime(3),
                            eventDuration = (float)reader.GetDouble(4),
                            eventUserName = reader.GetString(5)
                        });
                    allAppointments = list.ToArray();
                }
            }
        }

        private void DataPortal_Fetch(CategoryCriteria criteria)
        {
            bool cancel = false;
            OnFetching(criteria, ref cancel);
            if (cancel) return;

            string commandText = String.Format("SELECT [CategoryId], [Name], [Descn] FROM [dbo].[Category] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
            using (var connection = new SqlConnection(ADOHelper.ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(commandText, connection))
                {
                    command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
                    using(var reader = new SafeDataReader(command.ExecuteReader()))
                    {
                        if (reader.Read())
                            Map(reader);
                        else
                            throw new Exception(String.Format("The record was not found in 'dbo.Category' using the following criteria: {0}.", criteria));
                    }
                }
            }

            OnFetched();
        }

        public RootObjectOut GetMessageByUser(UserIn jm)
        {
            RootObjectOut output = new RootObjectOut();
            String jsonString = "";
            try
            {
                String strConnection = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
                SqlConnection Connection = new SqlConnection(strConnection);
                String strSQL = string.Format("SELECT message FROM messages WHERE msgTo = '{0}' AND [msgID] = (SELECT MAX(msgID) FROM messages WHERE msgTo='{1}')", jm.user.ToString(),jm.user.ToString());
                SqlCommand Command = new SqlCommand(strSQL, Connection);
                Connection.Open();
                SqlDataReader Dr;
                Dr = Command.ExecuteReader();
                if (Dr.HasRows)
                {
                    if (Dr.Read())
                    {
                        jsonString = Dr.GetValue(0).ToString();
                    }
                }
                Dr.Close();
                Connection.Close();
            }
            catch (Exception ex)
            {
                output.errorMessage = ex.Message;
            }
            finally
            {
            }
            JavaScriptSerializer ser = new JavaScriptSerializer();
            output = ser.Deserialize<RootObjectOut>(jsonString);

            return output;
        }

 public List<Model.Beneficios> Select()
 {
     List<Model.Beneficios> lstBeneficios = new List<Model.Beneficios>();
     SqlConnection conexao = new SqlConnection(strCon);
     string sql = "Select * from Beneficios";
     SqlCommand cmd = new SqlCommand(sql, conexao);
     conexao.Open();
     try
     {
         SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
         while (reader.Read())
         {
             Model.Beneficios beneficios = new Model.Beneficios();
             beneficios.id = Convert.ToInt32(reader["id"].ToString());
             beneficios.descr = Convert.ToString(reader["descr"].ToString());
             lstBeneficios.Add(beneficios);
         }
     }
     catch
     {
         Console.WriteLine("Deu erro na Seleção de Beneficios...");
     }
     finally
     {
         conexao.Close();
     }
     return lstBeneficios;
 }

        public static void Main()
        {
            var connection = new SqlConnection(connectionString: "Server=(local); Database=Northwind; Integrated Security=true;");
            connection.Open();
            var command = new SqlCommand(cmdText: "select c.CategoryName, p.ProductName from Products p inner join Categories c on c.CategoryId = p.CategoryId;", connection: connection);

            var categoriesAndProducts = new Dictionary<string, List<string>>();
            var reader = command.ExecuteReader();
            while (reader.Read())
            {
                var category = (string)reader["CategoryName"];
                var product = (string)reader["ProductName"];
                if(categoriesAndProducts.ContainsKey(category))
                {
                    categoriesAndProducts[category].Add(product);
                }
                else
                {
                    categoriesAndProducts.Add(category, new List<string>());
                }
            }

            connection.Close();

            foreach (var kvp in categoriesAndProducts)
            {
                Console.WriteLine(new string(c: '=', count: 20) + "\n" + kvp.Key + "\n" + new string(c: '=', count: 20));

                foreach (var product in kvp.Value)
                {
                    Console.WriteLine("-- " + product);
                }
            }
        }

        private static ICollection<string> FindProductBy(string partOfName)
        {
            var names = new List<string>();
            string connectionString = "Data Source=.;Initial Catalog=Northwind;Integrated Security=SSPI;";

            var dbCon = new SqlConnection(connectionString);

            dbCon.Open();
            using (dbCon)
            {
                SqlCommand command = new SqlCommand(
                    "SELECT p.ProductName FROM Products p WHERE p.ProductName LIKE @Search", dbCon);

                command.Parameters.AddWithValue("@Search", "%" + partOfName + "%");

                SqlDataReader reader = command.ExecuteReader();

                Console.WriteLine("\nProductNams that contains ({0}):\n", partOfName);
                using (reader)
                {
                    string categoryName = string.Empty;
                    string currentCategoryName = string.Empty;
                    while (reader.Read())
                    {
                        string productName = (string)reader["ProductName"];

                        names.Add(productName);
                    }
                }
            }

            return names;
        }

        static void Main(string[] args)
        {
            Console.Write("Enter some text to search for it in the Products: ");
            var searchedProduct = Console.ReadLine();

            SqlConnection dbCon = new SqlConnection("Server=localhost; " +
                "Database=NORTHWND; Integrated Security=true");
            dbCon.Open();
            using (dbCon)
            {
                SqlCommand cmd = new SqlCommand(
                    "SELECT ProductName FROM Products " +
                    "WHERE CHARINDEX (@searchedProduct, ProductName)>0", dbCon);

                cmd.Parameters.AddWithValue("@searchedProduct", searchedProduct);

                SqlDataReader reader = cmd.ExecuteReader();
                using (reader)
                {
                    while (reader.Read())
                    {
                        string productName = (string)reader["ProductName"];
                        Console.WriteLine(productName);
                    }
                }
            }
        }

        /// <summary>
        /// Return the song with a given id
        /// </summary>
        /// <param name="songId">The id of the song to get</param>
        /// <returns>The song with the given id, or null if no such movie exists</returns>
        public Song GetSong(int songId) {
            SqlCommand command = new SqlCommand("SELECT * FROM Song WHERE id =" + songId, connection);
            SqlDataReader reader = command.ExecuteReader();

            if (reader.Read()) {
                string album = reader["album"].ToString();
                reader.Close();
                command.CommandText = "SELECT * FROM Files WHERE id =" + songId;
                reader = command.ExecuteReader();
                reader.Read();
                Song song = new Song() {
                    Id = songId,
                    Album = album,
                    RentPrice = int.Parse(reader["rentPrice"].ToString()),
                    BuyPrice = int.Parse(reader["buyPrice"].ToString()),
                    Uri = reader["URI"].ToString(),
                    Title = reader["title"].ToString(),
                    Description = reader["description"].ToString(),
                    Year = short.Parse(reader["year"].ToString()),
                    CoverUri = reader["coverURI"].ToString(),
                    ViewCount = int.Parse(reader["viewCount"].ToString())
                };
                reader.Close();
                return song;

            }
            reader.Close();
            return null;
        }

    public static void getBook(int OrgID, ref string[] fields, ref int[] fieldNums, ref string[,] books)
    {
        int ObjID = 0;
        int counter = 0;

        SqlDataReader read;
        SqlCommand cmd = new SqlCommand();
        string e4Conn = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        SqlConnection conn = new SqlConnection(e4Conn);

        conn.Open();
        cmd.Connection = conn;
        cmd.CommandText = "SELECT * FROM Objects WHERE OrgID = '" + OrgID + "' AND ObjName = 'Book'";
        read = cmd.ExecuteReader();
        if (read.HasRows)
        {
            read.Read();
            try
            {
                ObjID = System.Convert.ToInt32(read["ObjID"]);
                read.Close();
                cmd.CommandText = "Select * FROM Fields WHERE ObjID = '" + ObjID + "'";
                read = cmd.ExecuteReader();
                if (read.HasRows)
                {
                    while (read.Read())
                    {
                        fields[counter] = System.Convert.ToString(read["FieldName"]);
                        fieldNums[counter] = System.Convert.ToInt32(read["FieldNumber"]);
                        counter++;
                    }
                    read.Close();
                    cmd.CommandText = "SELECT * FROM Data WHERE ObjID = '" + ObjID + "'";
                    read = cmd.ExecuteReader();
                    if (read.HasRows)
                    {
                        counter = 0;
                        while (read.Read())
                        {
                            books[counter, 0] = System.Convert.ToString(read["Value00"]);
                            books[counter, 1] = System.Convert.ToString(read["Value01"]);
                            books[counter, 2] = System.Convert.ToString(read["Value02"]);
                            books[counter, 3] = System.Convert.ToString(read["Value03"]);
                            books[counter, 4] = System.Convert.ToString(read["Value04"]);
                            books[counter, 5] = System.Convert.ToString(read["Value05"]);
                            books[counter, 6] = System.Convert.ToString(read["Value06"]);
                            books[counter, 7] = System.Convert.ToString(read["Value07"]);
                            books[counter, 8] = System.Convert.ToString(read["Value08"]);
                            books[counter, 9] = System.Convert.ToString(read["Value09"]);
                            books[counter, 10] = System.Convert.ToString(read["Value10"]);
                            counter++;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
            }
        }
    }

        private void button1_Click(object sender, EventArgs e)
        {
            listBox1.Items.Clear();
            comboBox1.Items.Clear();

            String strcon = "Data Source=VINOTH;Integrated Security=SSPI;Initial Catalog=Dafesty";
            SqlConnection cn = new SqlConnection(strcon);
            SqlCommand cm = new SqlCommand();

            cm.CommandText = "select movietitle from movies order by movietitle";
            cm.Connection = cn;
            cn.Open();
            SqlDataReader rd = cm.ExecuteReader();
            int count = 0;
            while (rd.Read())
            {
                listBox1.Items.Add(rd[0].ToString());
                count++;
            }
            label3.Text = count.ToString() + " Row(s) Selected";
            cm.CommandText = "select distinct Rating from Movies ";
            rd.Close();
            rd = cm.ExecuteReader();
            while(rd.Read())
                comboBox1.Items.Add(rd[0]);
            rd.Close();
            cn.Close();
        }

        public void mandarMensajeDeExito()
        {
            SqlCommand cmd = new SqlCommand("select * from THE_CVENGERS.COMPRA where COMPRA_ID = "+ idComp, Conexion.getConexion());
            SqlDataReader rd = cmd.ExecuteReader();
            rd.Read();
               bool sw = bool.Parse(rd["COMPRA_FORMA_DE_PAGO"].ToString());
            rd.Close();
            float monto = 0f;
             foreach (int it in checkedListBox1.CheckedIndices) { monto += float.Parse(((ItemsDevolucion)checkedListBox1.Items[it]).getPrecio()); }

            String mens = "La devolucion se ha realizado con exito. El monto de $"+ monto.ToString();
            if (!sw)
                mens += " sera retribuido en efectivo.";
            else
            {
                cmd.CommandText = "select THE_CVENGERS.tipoTarjetaCompra(" + idComp + ") 't'";
                rd = cmd.ExecuteReader();
                rd.Read();
                String tipoT = rd["t"].ToString();
                rd.Close();
                cmd.CommandText = "select THE_CVENGERS.numeroTarjetaCompra(" + idComp + ") 'n'";
                rd = cmd.ExecuteReader();
                rd.Read();
                String numT = rd["n"].ToString();
                rd.Close();
                mens += " sera retribuido a la tarjeta "+tipoT+" de numero "+numT;
            }
            MessageBox.Show(mens,"Información",MessageBoxButtons.OK);
        }

        public static string[,] GetListChevaux(SqlConnection conn)
        {
            SqlCommand sql = new SqlCommand("select id, nom, description, emplacement, race, discipline, idusager from cheval ");
            sql.Connection = conn;
            conn.Open();

            SqlDataReader sqlDR = sql.ExecuteReader();
            int nombre = 0;
            while (sqlDR.Read())
            {
                nombre++;
            }
            string[,] Tab = new string[nombre, 7];
            sqlDR.Close();
            if (nombre > 0)
            {
                SqlDataReader sqlDR2 = sql.ExecuteReader();
                int cpt = 0;
                while (sqlDR2.Read())
                {
                    Tab[cpt, 0] = sqlDR2.GetInt32(0).ToString();
                    Tab[cpt, 1] = sqlDR2.GetString(1);
                    Tab[cpt, 2] = sqlDR2.GetString(2);
                    Tab[cpt, 3] = sqlDR2.GetString(3);
                    Tab[cpt, 4] = sqlDR2.GetString(4);
                    Tab[cpt, 5] = sqlDR2.GetString(5);
                    Tab[cpt, 6] = sqlDR2.GetInt32(6).ToString();
                    cpt++;
                }
                sqlDR2.Close();
            }
            conn.Close();

            return Tab;
        }

        /// <summary>
        /// Return the movie with a given id
        /// </summary>
        /// <param name="movieId">The id of the movie to get</param>
        /// <returns>The movie with the given id, or null if no such movie exists</returns>
        public Movie GetMovie(int movieId) {
            SqlCommand command = new SqlCommand("SELECT * FROM Movie WHERE id =" + movieId, connection);
            SqlDataReader reader = command.ExecuteReader();

            if (reader.Read()) {
                reader.Close();
                command.CommandText = "SELECT * FROM Files WHERE id =" + movieId;
                reader = command.ExecuteReader();
                reader.Read();
                Movie mov = new Movie() {
                    Id = movieId,
                    RentPrice = int.Parse(reader["rentPrice"].ToString()),
                    BuyPrice = int.Parse(reader["buyPrice"].ToString()),
                    Uri = reader["URI"].ToString(),
                    Title = reader["title"].ToString(),
                    Description = reader["description"].ToString(),
                    Year = short.Parse(reader["year"].ToString()),
                    CoverUri = reader["coverURI"].ToString(),
                    ViewCount = int.Parse(reader["viewCount"].ToString())
                };
                reader.Close();
                return mov;

            }
            reader.Close();
            return null;
        }

        /// <summary>
        /// весь процесс заполнения информации
        /// </summary>
        void BaseCreation()
        {
            using (SqlConnection con = new SqlConnection(App.GetConnectString()))
            {
                con.Open();
                //Имя пользователя
                SqlCommand cmd = new SqlCommand(string.Format("SELECT Name FROM Password WHERE ID={0}", App.curPnID), con);
                TBlName.Text = cmd.ExecuteScalar().ToString();

                //Всего очков
                cmd = new SqlCommand(string.Format("SELECT SUM(Points) FROM AchieveInfo WHERE PersonID={0}", App.curPnID), con);
                TBlPAll.Text = "Всего: "+cmd.ExecuteScalar().ToString();

                //Очки месяц
                cmd = new SqlCommand(string.Format("SELECT SUM(Points) FROM AchieveInfo WHERE PersonID={0} AND Date Between convert(varchar(6), getdate(), 112) + '01' and dateadd(day, -1, dateadd(month, 1, convert(varchar(6), getdate(), 112) + '01'))", App.curPnID), con);
                TBlPMnt.Text = App.MonthName[DateTime.Now.Month]+": " + cmd.ExecuteScalar().ToString();

                //Лучший/худший результат всего
                cmd = new SqlCommand(string.Format("SELECT DISTINCT(p.Name) , Sum(Points) FROM AchieveInfo ac LEFT OUTER JOIN Theme p ON ac.ThemeID=p.ID WHERE PersonID={0} GROUP BY p.Name", App.curPnID), con);
                TBlBSAll.Text = "Всего: "+FindMax(cmd.ExecuteReader());
                TBlWSAll.Text = "Всего: "+FindMin(cmd.ExecuteReader());

                //Лучший/худший результат - месяц
                cmd = new SqlCommand(string.Format("SELECT DISTINCT(p.Name) , Sum(Points) FROM AchieveInfo ac LEFT OUTER JOIN Theme p ON ac.ThemeID=p.ID WHERE PersonID={0} AND Date Between convert(varchar(6), getdate(), 112) + '01' and dateadd(day, -1, dateadd(month, 1, convert(varchar(6), getdate(), 112) + '01')) GROUP BY p.Name", App.curPnID), con);
                TBlBSMnt.Text = App.MonthName[DateTime.Now.Month] + ": " + FindMax(cmd.ExecuteReader());
                TBlWSMnt.Text = App.MonthName[DateTime.Now.Month] + ": " + FindMin(cmd.ExecuteReader());
            }
        }