如何使用C＃中的查询获得SQLite表格架构

Question

我一直在高低搜索，使用许多方法从sqlite的表中获取表格架。请原谅我是否已将其发布到其他地方。

当前查询是，

SELECT TOP (0) * FROM 'TableName'

但这一无所获。

SELECT * FROM 'TableName'

如果桌子为空，也不会返回。

我还使用了sqliteconnection.getschema（），但它不会返回所讨论的表。它返回带有其他列的表。

DataTable dtReturnTable = new DataTable();
sqlitecon.Open();
string[] sParameters = new string[4];
sParameters[2] = sTableName;
dtReturnTable = sqlitecon.GetSchema("Columns", sParameters);
sqlitecon.Close();

Answer 1

所以我做的是

private DataTable GetSQLiteTableWithQuery(string sQuery)
        {
            SQLiteConnection sqlconlocal = new SQLiteConnection(sSQLITEconnstring);
            DataTable dtReturnTable = new DataTable();
            SQLiteCommand sqliteCommand = new SQLiteCommand(sQuery, sqlconlocal);
            using (sqlconlocal)
            {
                try
                {
                    sqlconlocal.Open();
                    SQLiteDataReader sqliteReader = sqliteCommand.ExecuteReader();
                    if (sqliteReader.HasRows)
                    {
                        for (int i = 0; i < sqliteReader.FieldCount; i++)
                        {
                            dtReturnTable.Columns.Add(new DataColumn(sqliteReader.GetName(i)));
                        }
                    }
                    int j = 0;
                    while (sqliteReader.Read())
                    {
                        DataRow dtRow = dtReturnTable.NewRow();
                        dtReturnTable.Rows.Add(dtRow);
                        for (int i = 0; i < sqliteReader.FieldCount; i++)
                        {
                            dtReturnTable.Rows[j][i] = (sqliteReader.GetValue(i));
                        }
                        j++;
                    }
                    sqlconlocal.Close();
                }
                catch (SQLiteException e)
                {
                    sqlconlocal.Close();
                }
            }
            return dtReturnTable;
        }

public DataTable GetSQLiteSchema(string sTableName)
        {
            string sQuery = "PRAGMA table_info('" + sTableName + "')";
            DataTable dtWorking = GetSqLiteTableBasedOnQuery(sQuery);
            DataTable dtReturn = new DataTable();
            foreach (DataRow dr1 in dtWorking.Rows)
            {
                string sColName = dr1[1].ToString();
                string sColtype = dr1[2].ToString();
                if (sColtype.ToLower().Contains("nvarchar"))
                {
                    dtReturn.Columns.Add(sColName, typeof(string));
                }
                else if (sColtype.ToLower().Contains("decimal"))
                {
                    dtReturn.Columns.Add(sColName, typeof(decimal));
                }
                else if (sColtype.ToLower().Contains("int"))
                {
                    dtReturn.Columns.Add(sColName, typeof(int));
                }
                else if (sColtype.ToLower().Contains("date"))
                {
                    dtReturn.Columns.Add(sColName, typeof(DateTime));
                }
                else if (sColtype.ToLower().Contains("bit"))
                {
                    dtReturn.Columns.Add(sColName, typeof(bool));
                }
            }
            return dtReturn;
        }