Indexes in SQL CE 2.0

[tiger79]

Hi,
I'd like to create programmaticalyy some extra indexes in my database.

Here is one of my tables :
SqlCeEngine eng = new SqlCeEngine(@"Data Source=\My Documents\DICT.sdf");

eng.CreateDatabase();

SqlCeConnection con = new SqlCeConnection(@"Data Source=\My
Documents\DICT.sdf");

con.Open();

string DDL1 = "CREATE TABLE AttributeValues(AttributeId int not null,"

+ "Value int not null,"

+ "ValueLevel int not null,"

+ "Ordered int null,"

+ "Description nvarchar(128) not null,"

+ "ParentAttributeId int not null,"

+ "ParentValue int not null,"

+ "ParentValueLevel int null,"

+ "AttributeValuesIndex int null,"

+ "CREATE INDEX Descr ON AttributeValues(Description),"

+ "PRIMARY KEY (AttributeId, Value))";

//+ "CREATE INDEX Descr ON A

//+ "INDEX Descr (Description))";

//+ "FOREIGN KEY (AttributeId) REFERENCES Attributes(AttributeId) ON DELETE
CASCADE)";

SqlCeCommand cmdDDL1 = new SqlCeCommand(DDL1, con);

cmdDDL1.CommandType = CommandType.Text;

cmdDDL1.ExecuteNonQuery();



As u can see I've tried several ways but I just cant manage to get an
index...

I allso tried to make the table and create the index afterwards :

//SqlCeCommand insertCommand5 = con.CreateCommand();

//insertCommand5.CommandText = "CREATE INDEX Descr ON
AttributeValues(Description)";/*



Didn't works as well

Any idea when and how I can create a couple of extra indexes for a table ?

Thanks in advance...

 

[Alex Feinman [MVP]]

SqlCeCommand cmd = con.CreateCommand();
cmd.CommandText = "CREATE TABLE AttributeValues(AttributeId int not null,"

+ "Value int not null,"

+ "ValueLevel int not null,"

+ "Ordered int null,"

+ "Description nvarchar(128) not null,"

+ "ParentAttributeId int not null,"

+ "ParentValue int not null,"

+ "ParentValueLevel int null,"

+ "AttributeValuesIndex int null, "
+ " CONSTRAINT CK PRIMARY KEY(AttributeID, Value) )";

cmd.ExecuteNonQuery();

cmd.CommandText = "CREATE INDEX Descr ON AttributeValues(Description)"

+ ")";
cmd.ExecuteNonQuery();

 

[tiger79]

Nope, doesn't work
Same old error : There was an error parsing the query
source looks like this now :
SqlCeEngine eng = new SqlCeEngine(@"Data Source=\My Documents\DICT.sdf");

eng.CreateDatabase();

SqlCeConnection con = new SqlCeConnection(@"Data Source=\My
Documents\DICT.sdf");

con.Open();

SqlCeCommand cmd = con.CreateCommand();

cmd.CommandText = "CREATE TABLE AttributeValues(AttributeId int not null,"

//string DDL1 = "CREATE TABLE AttributeValues(AttributeId int not null,"

+ "Value int not null,"

+ "ValueLevel int not null,"

+ "Ordered int null,"

+ "Description nvarchar(128) not null,"

+ "ParentAttributeId int not null,"

+ "ParentValue int not null,"

+ "ParentValueLevel int null,"

+ "AttributeValuesIndex int null,"

+ "CONSTRAINT CK PRIMARY KEY(AttributeID, Value))";

//+ "PRIMARY KEY (AttributeId, Value))";

cmd.ExecuteNonQuery();

cmd.CommandText = "CREATE INDEX Descr ON AttributeValues(Description)"

+ ")";

cmd.ExecuteNonQuery();



it looks compliant to what you suggested me... unfortunately it doesnt work


Did it work when u tried it ???

thanx

 

[tiger79]

bt, if i remove that last cmd.CommandText = "CREATE INDEX Descr ON
AttributeValues(Description)"

+ ")";

cmd.ExecuteNonQuery();

it creates the whole table (obviously without the hard-needed index) so the
problem is in that line of code

 

[Alex Feinman [MVP]]

Obviously the last ")"; is mismatched. Remove it