如何在SQLite中找到MAX元素?

mklgxw1f  于 2022-11-14  发布在  SQLite
关注(0)|答案(4)|浏览(422)

我需要选择PolygonID列的最大ID。我这样保存我的数据

string sql = "create table Polygons (PolygonId int, PointId int, X double, Y double)";

           // Выполнение нашей команды
           using (SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection))
                {
                    command.ExecuteNonQuery();
                }

                int pointId = 1;
                for (int i = 0; i < listOfCustomPolygons.Count; i++)
                    for (int j = 0; j < listOfCustomPolygons[i].listOfVertexes.Count; j++)
                    {
                        string strSQL =
                            string.Format("INSERT INTO Polygons (PolygonId,PointId,X,Y) Values ('{0}','{1}','{2}','{3}')",
                                            i+1,pointId,listOfCustomPolygons[i].listOfVertexes[j].X,
                                          listOfCustomPolygons[i].listOfVertexes[j].Y );
                        pointId++;

                        using (SQLiteCommand insertCommand = new SQLiteCommand(strSQL, m_dbConnection))
                        {
                            insertCommand.ExecuteNonQuery();
                        }
                    }

之后,我想从表Polygons和列PolygonID中选择最大值,但我得到了一个IndexOutOfRangeException异常。如何才能解决这个问题?

using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + openFileDialog.FileName + ";Version=3;"))
                {
                    connection.Open();
                    string selectMaxId = "Select Max(PolygonId) From Polygons";

                    string selectQuery = "Select * From Polygons";

                    SQLiteCommand selectMaxCmd = new SQLiteCommand(selectMaxId,connection);
                    SQLiteDataReader dataReader = selectMaxCmd.ExecuteReader();
                    int maxId = Convert.ToInt32(dataReader["Select Max(PolygonId) From Polygons"]); // This is don't work! Why?
f5emj3cl

f5emj3cl1#

我找到了解决方案!它应该看起来像是

string selectMaxId = "Select Max(PolygonId) From Polygons";
SQLiteCommand selectMaxCmd = new SQLiteCommand(selectMaxId,connection);
object val = selectMaxCmd.ExecuteScalar();
int maxId = int.Parse(val.ToString());

我希望它能帮助那些面临类似问题的人)

b5lpy0ml

b5lpy0ml2#

首先,不要在每次运行代码时都创建表:),但您可能知道
您可以这样打字:
int maxId = Convert.ToInt32(dataReader["Select Max(PolygonId) From Polygons"]);
试试这个:

string selectMaxId = "Select Max(PolygonId) From Polygons";
SQLiteCommand selectMaxCmd = new SQLiteCommand(selectMaxId,connection);
SQLiteDataReader dataReader = selectMaxCmd.ExecuteReader();

int maxID = -1;

while(dataReader.read())
{
    maxID = (int)dataReader.GetValue(0);
}
nzkunb0c

nzkunb0c3#

//This Works for me in WPF C#: 

int MaxNum=0;
sqliteCon.Open();
string Query = "SELECT MAX(Promo_No)FROM Promo_File";
SQLiteCommand createCommand = new SQLiteCommand(Query, sqliteCon);
SQLiteDataReader DR = createCommand.ExecuteReader();
while (DR.Read())
{
   MaxNum = DR.GetInt16(0);
}
sqliteCon.Close();
h4cxqtbf

h4cxqtbf4#

我也有同样的问题!您必须学习SQLiteCommand的差异方法。1.SQLiteCommand.ExecuteReader()。获取SqlDataReader。2.SQLiteCommand.ExecuteScalar()。从数据库中获取单个值。
Microsoft Doc:cmd.CommandText=“SELECT COUNT(*)from dbo.Region”;
Int32 count=(Int32)cmd.ExecuteScalar();

相关问题