更新数据集从sql C＃ [英] Update dataset from sql C#

问题描述

我有一个用于访客登录的信息亭，当他们登录时，会将数据发送到SQL数据库。然后我将这些数据拖入一个数据集，然后到监视应用程序的datagridview。此数据每15秒刷新一次。

I have a kiosk that is used for visitor sign in, when they sign in it sends the data to a SQL db. I then pull this data into a dataset then into a datagridview on the monitoring application. This data is refreshed every 15 seconds.

如何更新此数据集/ datagridview而不必每次都抓取所有数据？初始拉约500行，我不想每次都拉所有那些500。我想检查是否有任何新记录并将它们添加到数据集。

How can I update this dataset/datagridview without grabbing all the data every time? The initial pull is about 500 lines, I don't want to pull all those same 500 everytime. I want to check to see if there are any new records and add them to the dataset.

这里是我获取数据的代码

Here is the code where I get the data

private void UpdateUsers()
    {
        SqlConnection conn = ConnectSQL("10.100.25.250", "bhi_kiosk");
        var query = new SqlCommand("SELECT * FROM Users WHERE uStatus=1", conn);
        SqlDataReader read = null;
        listView1.Items.Clear();

        conn.Open();
        read = query.ExecuteReader();
        var dt = new DataTable();
        dt.Load(read);
        query.Connection.Close();
        dataGridView1.DataSource = dt;
}

推荐答案

开始：

DataTable dt = new DataTable();
DateTime lastChange = new DateTime(1753, 1, 1);
protected override void OnLoad(EventArgs e)
{
    base.OnLoad(e);
    dataGridView1.DataSource = dt;
    UpdateUsers();
}

然后将新的DataTable合并成旧版本：

And then merge new DataTable into old one:

private void UpdateUsers()
{
    SqlConnection conn = ConnectSQL("10.100.25.250", "bhi_kiosk");
    var query = new SqlCommand("SELECT * FROM Users WHERE uStatus=1 AND LastChange > @LastChange ORDER BY LastChange", conn);
    var lastChangeparameter = query.Parameters.Add("@LastChange", SqlDbType.DateTime);
    lastChangeparameter.Value = lastChange;
    //listView1.Items.Clear();
    conn.Open();
    using (SqlDataReader read = query.ExecuteReader())
    {
        using (var dt_merge = new DataTable())
        {
            dt_merge.Load(read);
            dt.Merge(dt_merge);
            if (dt.Rows.Count > 0)
            {
                lastChange = (DateTime)dt.Rows[dt.Rows.Count - 1]["LastChange"];
            }
        }
    }
    conn.Close();
}

你会得到你想要的。几个指针：真的应该尝试... catch块，以确保conn被处理的情况下的错误。查询取决于LastChange的顺序，因为下一个读取时间取自最大值（LastChange）。如果客户端时间与服务器时间不完全一致，您可以使用服务器中的datetime来避免丢失记录。

You will get what you want. Couple of pointers: there really should be a try ... catch block to ensure conn is disposed in case of error. Query depends on order by LastChange because next reading time is taken from maximum (LastChange). You use datetime from server to avoid possibility of loosing a record if client time is not perfectly aligned with server time.

这篇关于更新数据集从sql C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！