In my code I am trying to CREATE INDEX in my code, and since CREATING duplicated INDEX is not allowed, I wanted to check if my INDEX existed in my SQL CE Database.
In C# code, I ran the query using IDbCommand with query "SELECT * FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = 'mytablename'" so if there are results, I have the INDEX already created for this Database. Nothing fancy. However, when I run this, I did not see the result even though I know I created the index.
So I tried just running the query using SQL Server Compact/SQLite Toolbox. I am seeing that I have the INDEX with the tool's querying. I thought my SQL syntax might be wrong so I ran query "SELECT * FROM INFORMATION_SCHEMA.INDEXES" without WHERE to compare how many results I receive. I am seeing 13 results vs 12 results. Obviously the missing one is mytablename.
Does anybody have clue why this weird issue is occuring? If this is does not resolve, I can try catch and catch if there is duplicate and ignore the result. But preferably, I would like to properly catch if the Row exists or not.
The C# code I used is following.
DbProviderFactory factory = DbProviderFactories.GetFactory(provider);
using (DbConnection conn = factory.CreateConnection())
{
conn.ConnectionString = "xxxx";
try
{
conn.Open();
IDbCommand cmd = factory.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = 'mytablename'";
DbDataReader ddr = (DbDataReader)cmd.ExecuteReader();
ddr.Read();
}
catch (Exception ex)
{
}
finally
{
conn.Close();
}
}
