如何以特定的方式从C＃中获取mysql数据库中的数据？ [英] How to fetch data from mysql database in a specific way C#?
问题描述
大家好，我有winform visual studio 2012.
i有一个表A，我想以特定方式获取数据。
这是我想要实现的但是我得到了意想不到的结果。
实际上当用户在早上9点打开winform应用程序时，我从表A中获取，数据在前一天之间晚上（晚上10点）和当天的9Am。我保存了最后一个ID
然后在每个2小时之后，从同一个表中，我获取之前保存的最后一个ID之上的数据（只有id>之前ID的数据）。
我该怎么办？
实际上我的结果不是我想要的。我总是从表中获取相同的行（数据）.i不获取id为>的行以前的身份
我的尝试：
int rnber = 0 ;
int rnber_last;
dchild = new DataTable（）;
string qrychild =SELECT request_number，pincode，city WHERE request_number>' + rnber +';;
adap_child = new MySqlDataAdapter（qrychild，strcon）;
adap_child.Fill（dchild）;
rnber_last = Convert.ToInt32（dchild.AsEnumerable（）。Max（r => r [request_number]））;
rnber = rnber_last;
hi all, i have winform visual studio 2012.
i hav a table "A" to which i want to fetch data in a specific way.
this is what i want to implement but i got unexpected result.
in fact when user opens the winform app on morning at 9 Am, i fetch from table A, data between day before night(10 pm) and current day 9Am. i save the last ID
Then after after each 2 hours, from the same table, i fetch data above last ID saved before (only data where id > previous id).
how can i do it?
actually my result is not what i want. i fetched always the same rows(data) from table A.i don't fetch rows where id > previous id
What I have tried:
int rnber = 0;
int rnber_last;
dchild = new DataTable();
string qrychild = "SELECT request_number,pincode,city WHERE request_number > '" + rnber + "' ;";
adap_child = new MySqlDataAdapter(qrychild, strcon);
adap_child.Fill(dchild);
rnber_last = Convert.ToInt32(dchild.AsEnumerable().Max(r => r["request_number"]));
rnber = rnber_last;
推荐答案
正如@PeterLeow指出的那样，如果你有一个日期时间列，这将会容易得多。正如我所描述的那样，我无法确定前一天晚上10点之后加载的数据。
所以我在这里提供的解决方案是关注的在request_number上，与您发布的代码段类似。
这是完整代码
As @PeterLeow has pointed out, this would be a lot easier if you had a datetime column. As things have been described I can see no way of identifying the data that has been loaded after 10pm the previous day.
So the solution I'm offering here is focussing on therequest_numberin a similar way to the code snippet you posted.
Here is the full code
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Windows.Forms;
namespace Sandbox
{
public partial class Sandbox : Form
{
readonly string connectionString = ConfigurationManager.ConnectionStrings["ConnectToDB"].ConnectionString;
private const string Qrychild = "SELECT request_number,pincode,city FROM testtable WHERE request_number > @no;";
private int rnberLast;
private const string LastNumber = "lastNumber";
private DataTable dchild;
public Sandbox()
{
InitializeComponent();
}
private void Sandbox_Load(object sender, EventArgs e)
{
//Initial data load
rnberLast = GetDataSinceLastId(LastIdFromConfig());
WriteSetting(LastNumber, rnberLast.ToString());
//Initialise the timer
timerCheckDB.Interval = (2*60*60*1000); //2 hours
timerCheckDB.Enabled = true;
}
private int LastIdFromConfig()
{
try
{
var s = ReadSetting(LastNumber);
if (!int.TryParse(s, out rnberLast))
rnberLast = 0;
}
catch (Exception)
{
rnberLast = 0;
}
return rnberLast;
}
static string ReadSetting(string key)
{
var configFile = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
var settings = configFile.AppSettings.Settings;
return settings[key] != null ? settings[key].Value : "";
}
static void WriteSetting(string key, string value)
{
var configFile = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
var settings = configFile.AppSettings.Settings;
if (settings[key] == null)
settings.Add(key, value);
else
settings[key].Value = value;
configFile.Save(ConfigurationSaveMode.Modified);
ConfigurationManager.RefreshSection(configFile.AppSettings.SectionInformation.Name);
}
private int GetDataSinceLastId(int lastId)
{
using (var cnn = new SqlConnection(connectionString))
{
using (var adapChild = new SqlDataAdapter(Qrychild, cnn))
{
dchild = new DataTable();
adapChild.SelectCommand.Parameters.AddWithValue("@no", lastId);
adapChild.Fill(dchild);
//dataGridView1.DataSource = dchild; //visual debugging aid
return dchild.Rows.Count > 0 ? int.Parse(dchild.AsEnumerable().Max(r => r["request_number"]).ToString()) : lastId;
}
}
}
private void timerCheckDB_Tick(object sender, EventArgs e)
{
rnberLast = GetDataSinceLastId(rnberLast);
WriteSetting(LastNumber, rnberLast.ToString());
}
}
}
说明：
方法 GetDataSinceLastId 获取 lastId 的保存数量，并检索 request_number 大于该数字。注意你的代码和我的代码之间的差异：我使用了参数化查询，我使用 int.Parse 而不是 Convert.ToInt
方法 LastIdFromConfig 读取应用程序配置文件以获取最后一个 request_number 在上一次运行中从数据库中读取的内容。一旦加载了新数据（如果有的话）， WriteSetting 方法就会用新的最后一个id（request_number）更新配置文件
加载表单并初始化并启用计时器后，就会发生初始数据加载。 timerCheckDB_Tick 随后尝试以给定的间隔重新加载数据。
让这个工作的关键是持久化最后一个request_number - 这是我的App.Config文件
Explanation:
The method GetDataSinceLastId takes the number of the lastId saved and retrieves all data rows where the request_number is greater than that number. Note the differences between your code and mine: I've used a parameterised query and I'm using int.Parse instead of Convert.ToInt
The method LastIdFromConfig reads the application configuration file to get the last request_number that was read from the database on the previous run. As soon as the new data (if any) has been loaded, then the WriteSetting method updates the config file with the new last id (request_number)
An initial data load happens as soon as the form is loaded and the timer is initialise and enabled. The timerCheckDB_Tick subsequently attempts to reload the data at the given interval.
The key to getting this to work is persisting the last request_number - here is my App.Config file
<configuration>
<startup>
<supportedruntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
<connectionstrings>
<add name="ConnectToDB">
connectionString="Data Source=MyDataSource;Initial Catalog=Sandbox14;Integrated Security=True"
providerName="System.Data.SqlClient" />
</add></connectionstrings>
<appsettings>
<add key="lastNumber" value="5" />
</appsettings>
</configuration>（注意每次在debug中运行时，配置文件都会在bin\debug文件夹中被覆盖模式。要防止这种情况，请转到项目，属性，调试选项卡，并取消勾选启用Visual Studio托管过程复选框）
在这种情况下，我使用了App.config来保存数据。更好的选择是将其设置为用户设置
使用应用程序设置和用户设置 [ ^ ]
用户设置已应用 [ ^ ]
C＃中的Windows窗体用户设置 [< a href =http://www.codeproject.com/Articles/15013/Windows-Forms-User-Settings-in-Ctarget =_ blanktitle =New Window> ^ ]
这不符合您的要求
(Note the config file is overwritten in the bin\debug folder everytime you run in debug mode. To prevent this go to the Project, Properties, Debug tab and untick the "Enable the Visual Studio hosting process" checkbox)
In this case I've used the App.config to persist the data. A better option is to have this as a User Setting
Using Application Settings and User Settings[^]
User Settings Applied[^]
Windows Forms User Settings in C#[^]
This does not fit your requirement as stated
实际上当用户打开时早上9点的winform app，我从表A中获取数据b前一天晚上（晚上10点）和当天的一天9Am。我保存了最后一个ID
in fact when user opens the winform app on morning at 9 Am, i fetch from table A, data between day before night(10 pm) and current day 9Am. i save the last ID
这个版本将始终检索在前一个id之后加载的数据。为了能够以这种方式处理时间，您需要在表上使用 DateTime 列，并且您可能希望在设置中保留DateTimeLastRun。
我的道歉 - 我忘了提到你需要将SqlConnection和SqlDataAdapter更改为 MySqlConnection 和 MySqlDataAdapter
This version will always retrieve data loaded after the previous id. To be able to handle time in this way you will need a DateTime column on your table and you will probably want to persist the DateTimeLastRun in the settings.
My apologies - I forgot to mention that you will need to change SqlConnection and SqlDataAdapter to MySqlConnection and MySqlDataAdapter in the code above
这篇关于如何以特定的方式从C＃中获取mysql数据库中的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
