C# MySqlCommand.Read方法代码示例

 public static UserData[] LoadAll(MySqlConnection Connection)
 {
     Contract.Requires(Connection != null, "Connection is null.");
     MySqlDataReader reader = new MySqlCommand(loadAllQuery, Connection).ExecuteReader();
     List<UserData> r = new List<UserData>();
     object[] row = new object[5];
     int count;
     while (reader.Read())
     {
         count = reader.GetValues(row);
         r.Add(new UserData()
             {
                 _name = (string)row[0],
                 _id = (ulong)row[1],
                 _pass = (string)row[2],
                 _friendlyName = (string)row[3],
                 _gold = (uint)row[5]
             });
     }
     return r.ToArray();
 }

        public static Dictionary<string, object>[] Select(string table, string where, string order, string limit, string fields)
        {
            if (!String.IsNullOrWhiteSpace(where))
            {
                where = " WHERE " + where;
            }

            if (!String.IsNullOrWhiteSpace(order))
            {
                order = " ORDER BY " + order;
            }

            if (!String.IsNullOrWhiteSpace(limit))
            {
                limit = " LIMIT " + limit;
            }

            string sql = "SELECT " + fields + " FROM `" + table + "`" + where + order + limit;

            lock (mySqlReaderLock)
            {
                using (MySqlDataReader resultSet = new MySqlCommand(sql, DefaultConnection()).ExecuteReader())
                {
                    List<Dictionary<string, object>> rtn = new List<Dictionary<string, object>>();

                    while (resultSet.Read())
                    {
                        Dictionary<string, object> row = new Dictionary<string, object>();
                        for (int j = 0; j < resultSet.FieldCount; j++)
                        {
                            row[resultSet.GetName(j)] = resultSet.GetValue(j);
                        }
                        rtn.Add(row);
                    }

                    return rtn.ToArray();
                }
            }
        }

        private static IEnumerable<int> CheckAndAddProducts(IList<string> productSubNumbers, string productMainDescr, IList<string> productSubDescriptions)
        {
            var productIds = new List<int>();
            var productAttributes = new List<string>();

            if (productSubNumbers.Count > 1)
            {
                var attributes = "Rozmiar";

                var productNumberLength = productSubNumbers[0].Length - 2;
                string productNumber = productSubNumbers[0].Substring(0, productNumberLength);

                const string attributesRegex = "((rozm\\.)|(Rozmiar)|(rozmiar))\\s*\\w*";
                foreach (var productSubDescription in productSubDescriptions)
                {
                    var temp = new Regex(attributesRegex).Match(productSubDescription).Value;
                    try
                    {
                        productAttributes.Add(temp.Split(' ')[1]);
                    }
                    catch(Exception)
                    {
                        try
                        {
                            productAttributes.Add(temp.Split('.')[1]);
                        }
                        catch(Exception)
                        {
                            var tempSubDescr = productSubDescription.Split(' ');
                            productAttributes.Add(tempSubDescr[tempSubDescr.Length - 1]);
                        }
                    }
                }

                var productTemp = new Regex(attributesRegex).Match(productSubDescriptions[0]).Value;
                string productName = "";
                try
                {
                    productName = productSubDescriptions[0].Replace(productTemp, "");
                }
                catch(Exception)
                {
                    var tempProductNameAll = productSubDescriptions[0].Split(' ');
                    var tempProductName = "";
                    for (var i = 0; i < tempProductNameAll.Length - 1; i++)
                    {
                        tempProductName += tempProductNameAll[i] + " ";
                    }

                    productName = tempProductName;
                }

                for (var i = 0; i < productSubNumbers.Count; i++)
                {
                    attributes += String.Format(",({0}) {1}", productSubNumbers[i], productAttributes[i]);
                }

                var selectQuery = "select * from jos_vm_product where product_sku = " + productNumber;
                selectQuery = productSubNumbers.Aggregate(selectQuery, (current, productSubNumber) => current + (" or attribute like \"%" + productSubNumber + "%\""));

                var selectCommand = new MySqlCommand(selectQuery, _mySqlConnect).ExecuteReader();

                if (!selectCommand.Read())
                {
                    selectCommand.Close();

                    var insertQuery = String.Format("insert into jos_vm_product (vendor_id, product_parent_id, product_sku, product_s_desc, product_desc, product_thumb_image, product_full_image, product_publish, product_weight, product_weight_uom, product_length, product_width, product_height, product_lwh_uom, product_url, product_in_stock, product_available_date, product_availability, product_special, product_discount_id, ship_code_id, cdate, mdate, product_name, product_sales, attribute, custom_attribute, product_tax_id, product_unit, product_packaging, child_options, quantity_options, child_option_ids, product_order_levels) VALUES (1, 0, {0}, '', '{1}', '', '', 'Y', '0.0000', 'kg', '0.0000', '0.0000', '0.0000', 'cm', '', 0, 1279843200, '', 'N', 0, NULL, 1279922325, 1279922577, '{2}', 0, '{3}', '', 3, 'szt.', 0, 'N,N,N,N,N,Y,20%,10%,', 'none,0,0,1', '', '0,0')", productNumber, productMainDescr, productName, attributes);
                    var insertCommand = new MySqlCommand(insertQuery, _mySqlConnect);
                    insertCommand.ExecuteNonQuery();
                    productIds.Add((int)insertCommand.LastInsertedId);
                }
                else
                {
                    productIds.Add(selectCommand.GetInt32("product_id"));
                    selectCommand.Close();
                }
            }
            else
            {
                for (var j = 0; j < productSubNumbers.Count; j++)
                {
                    var selectQuery = "select * from jos_vm_product where product_sku = " + productSubNumbers[j];
                    selectQuery = productSubNumbers.Aggregate(selectQuery, (current, productSubNumber) => current + (" or attribute like \"%" + productSubNumber + "%\""));

                    var selectCommand = new MySqlCommand(selectQuery, _mySqlConnect).ExecuteReader();

                    if (!selectCommand.Read())
                    {
                        selectCommand.Close();

                        var insertQuery = String.Format("insert into jos_vm_product (vendor_id, product_parent_id, product_sku, product_s_desc, product_desc, product_thumb_image, product_full_image, product_publish, product_weight, product_weight_uom, product_length, product_width, product_height, product_lwh_uom, product_url, product_in_stock, product_available_date, product_availability, product_special, product_discount_id, ship_code_id, cdate, mdate, product_name, product_sales, attribute, custom_attribute, product_tax_id, product_unit, product_packaging, child_options, quantity_options, child_option_ids, product_order_levels) VALUES (1, 0, {0}, '', '{1}', '', '', 'Y', '0.0000', 'kg', '0.0000', '0.0000', '0.0000', 'cm', '', 0, 1279843200, '', 'N', 0, NULL, 1279922325, 1279922577, '{2}', 0, '', '', 3, 'szt.', 0, 'N,N,N,N,N,Y,20%,10%,', 'none,0,0,1', '', '0,0')", productSubNumbers[j], productMainDescr, productSubDescriptions[j]);
                        var insertCommand = new MySqlCommand(insertQuery, _mySqlConnect);
                        insertCommand.ExecuteNonQuery();
                        productIds.Add((int)insertCommand.LastInsertedId);
                    }
                    else
                    {
                        productIds.Add(selectCommand.GetInt32("product_id"));
                        selectCommand.Close();
                        continue;
//.........这里部分代码省略.........

 private void buttonDbTest_Click(object sender, EventArgs e)
 {
     reopenDatabase();
     comboBoxSchema.Items.Clear();
     MySqlDataReader dr = new MySqlCommand("SHOW DATABASES;", _conn).ExecuteReader();
     while(dr.Read())
     {
         comboBoxSchema.Items.Add(dr.GetString(0));
     }
     dr.Close();
 }

 private bool isWhitelisted(string guid, string name)
 {
     bool result = true;
     try
     {
         MySqlConnection mySqlConnection = new MySqlConnection(this.sqlSource);
         mySqlConnection.Open();
         string cmdText = "SELECT * FROM " + this.sqlTable + " WHERE guid = @guid AND name = @name";
         MySqlDataReader mySqlDataReader = new MySqlCommand(cmdText, mySqlConnection)
         {
             Parameters =
             {
                 new MySqlParameter("@guid", guid),
                 new MySqlParameter("@name", name)
             }
         }.ExecuteReader();
         if (mySqlDataReader.Read())
         {
             result = true;
         }
         else if (numPlayers > this.allowedPlayers)
         {
             this.write("    " + name + " disconnected, not a donator. GUID: " + guid, ConsoleColor.Yellow);
             this.write("Player Slots Allowed: " + this.allowedPlayers, ConsoleColor.Yellow);
             this.write("Player Number: " + numPlayers, ConsoleColor.Yellow);
             result = false;
         }
         else
         {
             this.write("    " + name + " connected, but is not a donator. GUID: " + guid, ConsoleColor.Yellow);
             this.write("Player Slots Allowed: " + this.allowedPlayers, ConsoleColor.Yellow);
             this.write("Player Number: " + numPlayers, ConsoleColor.Yellow);
             result = true;
         }
         mySqlConnection.Close();
     }
     catch
     {
         this.write("Could not get to the whitelist!\nPlease check all MYSQL settings\nAnd check the config.txt\nAnd make shure the user has rights to the Database.\nGood luck xD", ConsoleColor.Red);
     }
     return result;
 }

        public object[] DLookupA(string table, string fieldName, string clauses, string[] orderByFieldNames, bool orderByAsc)
        {
            List<object> data = new List<object>();
            bool connectedP = isConnectedDo;
            string query = "";
            try
            {
                query = "SELECT " + fieldName + " FROM " + table + " WHERE (" + clauses + ")" + ((orderByFieldNames.Length > 0) ? " ORDER BY " + getOrderByData(orderByFieldNames) + " " + ((orderByAsc) ? "ASC" : "DESC") : "");

                if (dbType == DatabaseType.MSSQL)
                {
                    getQueryDbChanger(query, SqlTypes.SELECT);
                    SqlDataReader Dr = new SqlCommand(query, MSSQLConn).ExecuteReader();
                    while (Dr.Read())
                    {
                        data.Add(Dr[0]);
                    }
                    Dr.Close();
                }
                else if (dbType == DatabaseType.MYSQL)
                {
                    MySqlDataReader Dr = new MySqlCommand(query, MySQLConn).ExecuteReader();
                    while (Dr.Read())
                    {
                        data.Add(Dr[0]);
                    }
                    Dr.Close();
                }
                else if (dbType == DatabaseType.MSACCESS2003 | dbType == DatabaseType.MSACCESS2007)
                {
                    OleDbDataReader Dr = new OleDbCommand(query, MSDBSQLConn).ExecuteReader();
                    while (Dr.Read())
                    {
                        data.Add(Dr[0]);
                    }
                    Dr.Close();
                }
            }
            catch (Exception eee)
            {
                OnError(eee);
                data.Clear();
                data.Add("ERROR: " + eee.Message);
                data.Add("ERRORI: " + table);
                data.Add("ERRORI: " + fieldName);
                data.Add("ERRORI: " + clauses);
                data.Add("ERRORI: " + query);
            }
            finally
            {
                if (!connectedP)
                    closeConnection();
            }
            return data.ToArray();
        }

 public object DLookup(string sqlQuery)
 {
     object data = null;
     bool connectedP = isConnectedDo;
     try
     {
         if (dbType == DatabaseType.MSSQL)
         {
             getQueryDbChanger(sqlQuery, SqlTypes.SELECT);
             SqlDataReader Dr = new SqlCommand(sqlQuery, MSSQLConn).ExecuteReader();
             if (Dr.Read())
             {
                 data = Dr[0];
             }
             Dr.Close();
         }
         else if (dbType == DatabaseType.MYSQL)
         {
             MySqlDataReader Dr = new MySqlCommand(sqlQuery, MySQLConn).ExecuteReader();
             if (Dr.Read())
             {
                 data = Dr[0];
             }
             Dr.Close();
         }
         else if (dbType == DatabaseType.MSACCESS2003 | dbType == DatabaseType.MSACCESS2007)
         {
             OleDbDataReader Dr = new OleDbCommand(sqlQuery, MSDBSQLConn).ExecuteReader();
             if (Dr.Read())
             {
                 data = Dr[0];
             }
             Dr.Close();
         }
     }
     catch (Exception eee)
     {
         OnError(eee);
         data = eee.Message;
     }
     finally
     {
         if (!connectedP)
             closeConnection();
     }
     return data;
 }

        private int CheckForProduct(int articleNr)
        {
            var selectQuery = String.Format("select product_id from jos_vm_product where product_sku = \"{0}\" or attribute like \"%{0}%\"", articleNr);
            var selectCommand = new MySqlCommand(selectQuery, _mySqlConnect).ExecuteReader();

            if (!selectCommand.Read())
            {
                selectCommand.Close();
                return 0;
            }

            var temp = selectCommand.GetInt32("product_id");
            selectCommand.Close();
            return temp;
        }

    public static void getTencentNews(string url, MySqlConnection conn)
    {
        string filename = "", publishdate = "", typename = "", sql = "";
        string[] picimg = new string[200];
        string[] pictxt = new string[200];
        int picimgPos = 0, pictxtPos = 0;

        HttpWebRequest httpRequest = (HttpWebRequest)WebRequest.Create(url);
        HttpWebResponse httpResponse = (HttpWebResponse)httpRequest.GetResponse();
        Stream receiveStream = httpResponse.GetResponseStream();
        StreamReader streamReader = new StreamReader(receiveStream, Encoding.Default);
        string htmlstr = streamReader.ReadToEnd();  //htmlstr是新闻标题页源代码
        streamReader.Close();
        try
        {
            //正则表达式获取<a href></a>内容url
            MatchCollection matches = Regex.Matches(htmlstr, "<a target=\"_blank\" class=\"linkto\" href=\"(.*?).htm\"(.*?)>(.*?)</a>", RegexOptions.IgnoreCase);
            foreach (Match match in matches)
            {
                //matches2 获取 matches 中的新闻标题
                MatchCollection matches2 = Regex.Matches(match.Value.ToString(), "(>)(.*?)(</a>)", RegexOptions.IgnoreCase);
                foreach (Match match2 in matches2)
                {
                    filename = match2.Value.ToString().Substring(1).Replace("</a>", "");
                    sql = "insert into files(filename,typename,typeid,filecontent,publisher,publishdate,link) values('" + filename + "',";
                }

                //matches3 获取 matches 中的新闻网址
                MatchCollection matches3 = Regex.Matches(match.Value.ToString(), "href=\"([^\"]*?)\"", RegexOptions.IgnoreCase);
                foreach (Match match3 in matches3)
                {
                    string filecontent = "";
                    picimgPos = 0;
                    pictxtPos = 0;
                    string newshtml = match3.Value.ToString().Substring(5).Replace("\"", "");   //newshtml 是新闻网址
                    HttpWebRequest httpRequest2 = (HttpWebRequest)WebRequest.Create(newshtml);
                    HttpWebResponse httpResponse2;
                    try
                    {
                        httpResponse2 = (HttpWebResponse)httpRequest2.GetResponse();
                    }
                    catch (WebException ex)
                    {
                        httpResponse2 = (HttpWebResponse)ex.Response;
                    }
                    Stream receiveStream2 = httpResponse2.GetResponseStream();
                    StreamReader streamReader2 = new StreamReader(receiveStream2, Encoding.Default);
                    string htmlstr2 = streamReader2.ReadToEnd();    //htmlstr2是新闻详情页的源代码
                    streamReader2.Close();

                    //matches5 获取新闻内的图片
                    MatchCollection matches5 = Regex.Matches(htmlstr2, "<img alt=(.*?) src=\"(.*?)\">", RegexOptions.IgnoreCase);
                    foreach (Match match5 in matches5)
                    {
                        picimg[picimgPos] = match5.Value.ToString();
                        picimgPos++;
                    }
                    //matches6 获取新闻内图片的说明
                    MatchCollection matches6 = Regex.Matches(htmlstr2, "<p class=pictext align=center>(.*?)</p>", RegexOptions.IgnoreCase);
                    foreach (Match match6 in matches6)
                    {
                        pictxt[pictxtPos] = match6.Value.ToString();
                        pictxtPos++;
                    }

                    //matches7 获取新闻发布时间
                    MatchCollection matches7 = Regex.Matches(htmlstr2, "<span class=\"article-time\">(.*?)</span>", RegexOptions.IgnoreCase);
                    foreach (Match match7 in matches7)
                    {
                        MatchCollection matches8 = Regex.Matches(match7.Value.ToString(), ">(.*?)</span>", RegexOptions.IgnoreCase);
                        foreach (Match match8 in matches8)
                        {
                            publishdate = match8.Value.ToString().Substring(1).Replace("</span>", "");
                        }
                    }

                    //matches9 获取新闻类型
                    MatchCollection matches9 = Regex.Matches(htmlstr2, "<a target=\"_blank\" accesskey=\"5\" href=\"(.*?)\" title=\"(.*?)\">(.*?)</a>", RegexOptions.IgnoreCase);
                    foreach (Match match9 in matches9)
                    {
                        MatchCollection matches10 = Regex.Matches(match9.Value.ToString(), ">(.*?)</a>", RegexOptions.IgnoreCase);
                        foreach (Match match10 in matches10)
                        {
                            typename = match10.Value.ToString().Substring(1).Replace("</a>", "");
                        }
                    }

                    for (int i = 0; i < (picimgPos > pictxtPos ? pictxtPos : picimgPos); i++)
                        filecontent = filecontent + picimg[i] + pictxt[i];
                    MatchCollection matches4 = Regex.Matches(htmlstr2, "<P style=\"TEXT-INDENT: 2em\">(.*?)</P>", RegexOptions.IgnoreCase);
                    foreach (Match match4 in matches4)
                    {
                        filecontent = filecontent + match4.Value.ToString().Replace("'", "\\'");
                    }
                    conn.Open();
                    MySqlDataReader reader_select_typeid = new MySqlCommand("select typeid from type where typename='" + typename + "'", conn).ExecuteReader();
                    int typeid = 99;
                    while (reader_select_typeid.Read())
                    {
                        if (reader_select_typeid.HasRows)
//.........这里部分代码省略.........

        private void AddPriceForProduct(int productNr, double price)
        {
            price = CheckPrice(price);

            if (productNr == 0) return;

            var selectQuery = String.Format("select product_id from jos_vm_product_price where product_id = {0}", productNr);
            var selectCommand = new MySqlCommand(selectQuery, _mySqlConnect).ExecuteReader();
            var priceString = price.ToString().Replace(',', '.');

            if (!selectCommand.Read())
            {
                selectCommand.Close();

                //nie znaleziono ceny w bazie
                var insertCommand = String.Format("insert into jos_vm_product_price (product_id, product_price, product_currency, product_price_vdate, product_price_edate, cdate, mdate, shopper_group_id, price_quantity_start, price_quantity_end) values ({0}, '{1}', 'EUR', 0, 0, 1280422085, 1280422085, 5, 0, 0)", productNr, priceString);
                new MySqlCommand(insertCommand, _mySqlConnect).ExecuteNonQuery();
            }
            else
            {
                selectCommand.Close();

                //znaleziono cenę w bazie
                var updateCommand = String.Format("update jos_vm_product_price set product_price = \"{0}\" where product_id = {1}", priceString, productNr);
                new MySqlCommand(updateCommand, _mySqlConnect).ExecuteNonQuery();

                //Euro - EUR, Poland - PLN
                updateCommand = String.Format("update jos_vm_product_price set product_currency = 'EUR' where product_id = {0}", productNr);
                new MySqlCommand(updateCommand, _mySqlConnect).ExecuteNonQuery();
            }
        }

        public static Request getItem()
        {
            Request r = new Request();
            try
            {
                MySqlDataReader rdr = new MySqlCommand("SELECT `id`,`steam_id`,`type`,`priority` FROM `queue` WHERE `id` IN (SELECT `queue_id` FROM `queue_status` WHERE `active`=1 AND `status`=" + (int)RequestStatus.Waiting + ") ORDER BY `priority` DESC, `id` ASC LIMIT 1", QueueConnection).ExecuteReader();
                while (rdr.Read())
                {
                    r.ID = rdr.GetInt32(0);
                    r.User = rdr.GetUInt64(1);
                    r.TradeType = (Bot.TradeTypes)rdr.GetInt32(2);
                    r.Priority = rdr.GetInt32(3);
                }
                rdr.Close();
                r.Data = new string[Int32.Parse((new MySqlCommand("SELECT COUNT(`data`) FROM `queue_items` WHERE `queue_id`=" + r.ID, conn)).ExecuteScalar().ToString())];
                rdr = (new MySqlCommand("SELECT `data` FROM `queue_items` WHERE `queue_id`=" + r.ID, conn)).ExecuteReader();
                int i = 0;
                while (rdr.Read())
                    r.Data[i++] = rdr.GetString(0);
                rdr.Close();
            }
            catch (Exception e)
            {
                log.Error("Error getting new request: " + e.Message);
            }

            return r;
        }

        private void BwOnDoWork(object sender, DoWorkEventArgs e)
        {
            BackgroundWorker worker = sender as BackgroundWorker;

            while (_running)
            {
                if ((worker.CancellationPending == true))
                {
                    e.Cancel = true;
                    return;
                }
                else
                {
                    // Perform a time consuming operation and report progress.
                    String str1, str2;
                    MySqlDataReader rd1, rd2;
                    MySqlConnection Con = new MySqlConnection(
                            "Data Source=216.218.224.238;Database=chatapp;Uid=chatappuser;[email protected];MultipleActiveResultSets=true;");
                    // SqlConnection Con = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=Test;User ID=;Password=;Connection Timeout=600");
                    Con.Open();

                    rd1 =
                        new MySqlCommand(
                            "select top 1 Chat from chat where  where email ='" + label4.Text + "'order by id Desc", Con)
                            .ExecuteReader();
                    rd1.Read();
                    str1 = rd1["Chat"].ToString();
                    rd1.Close();
                    rd2 =
                        new MySqlCommand(
                            "select top 1 UserInitial from chat where email ='" + label4.Text + "' order by id Desc",
                            Con)
                            .ExecuteReader();
                    rd2.Read();
                    str2 = rd2["UserInitial"].ToString();
                    rd2.Close();
                    if (str1 != str2)
                    {
                        MySqlDataReader rd3, rd4;
                        rd3 =
                            new MySqlCommand(
                                "select top 1 UserInitial from chat  where email ='" + label4.Text + "'order by id desc",
                                Con).ExecuteReader();

                        var value = rd3.Read();
                        rd3.Close();

                        if (richTextBox1.InvokeRequired)
                        {
                            richTextBox1.BeginInvoke(
                                new MethodInvoker(() => richTextBox1.Text += richTextBox1.Text + " <br /> " + value));
                        }
                        else
                        {
                            richTextBox1.Text = richTextBox1.Text + " <br /> " + value;
                        }
                        rd4 =
                            new MySqlCommand(
                                "Update top 1 chat set Chat = UserInitial  where email ='" + label4.Text +
                                "'order by id desc", Con).ExecuteReader();
                        rd4.Read();
                        rd4.Close();
                        Con.Close();
                    }

                  //  Thread.Sleep(1000); //sleep 1 second

                }
                //System.Threading.Thread.Sleep(500);
            }
        }

 private static List<string> GetTableNames()
 {
     try
     {
         m_Connection.Open();
         MySqlDataReader mysqlDR = new MySqlCommand(ConfigConst.MysqlCmd, m_Connection).ExecuteReader();
         List<string> tables = new List<string>();
         while (mysqlDR.Read())
         {
             string table = mysqlDR.GetString(0);
             if (!tables.Contains(table) && table.Contains(ConfigConst.TableNameKeyWord))
             {
                 tables.Add(table);
             }
         }
         m_Connection.Close();
         return tables;
     }
     catch (Exception e)
     {
         Debug.Log("Error : " + e.Message);
         throw;
     }
 }

 private List<string> GetTableNames()
 {
     try
     {
         this.m_Connection.Open();
         MySqlDataReader mysqlDR = new MySqlCommand(MyConst.MysqlCmd, this.m_Connection).ExecuteReader();
         List<string> tables = new List<string>();
         while (mysqlDR.Read())
         {
             string table = mysqlDR.GetString(0);
             if (!tables.Contains(table) && table.Contains(MyConst.TableNameKeyWord))
             {
                 tables.Add(table);
             }
         }
         this.m_Connection.Close();
         return tables;
     }
     catch (Exception e)
     {
         Console.WriteLine("Error : " + e.Message);
         throw;
     }
 }