ADO.NET create table

[Guest]

Hi,

I'm trying to make index on a table with SQLCE, but I don't know how I can
do that in C#. For example, when I create a table in C# on PPC, I do the
following tasks :

---------------------------------------------------------------------------
cmndDB.CommandText =
" CREATE TABLE MOTIFS_ENL " +
" ( CODE nchar(2) not null " +
" CONSTRAINT PKMOTIFS_ENL PRIMARY KEY " +
" , LIBELLE nchar(50) not null " +
" )";
cmndDB.ExecuteNonQuery();

 

[Guest]

Look in SQL Books online under CREATE INDEX for the SQL syntax

-Chris

 

[Guest]

OK, thanks.
But should I use the primary key as index, or should I add the index to the
current table ?

Look in SQL Books online under CREATE INDEX for the SQL syntax

-Chris

 

[Ginny Caughey [MVP]]

Gandalf,

How large will the table be? Will you be using the primary key in your WHERE
clauses? Generally you would already know what kind of indexes your need.

If you are working with a SQL Mobile database, you can use SQL Server 2005
Management Studio to see the query plan generated by the query processor for
various queries to help determine if an index would help.

--
Ginny Caughey
..NET Compact Framework MVP

OK, thanks.
But should I use the primary key as index, or should I add the index to
the
current table ?

 

[Guest]

A PK _is_ a clustered index. As to whether you should add other indexes,
that all depends on your usage - only you (and the SQL Query Performance
Analyzer) can answer that. As a general rule, if it's in a WHERE clause of
a statement used even semi-frequently, and index on the field will improve
query performance.

-Chris

OK, thanks.
But should I use the primary key as index, or should I add the index to
the
current table ?

 

[Guest]

Thanks for your information.
In fact, the processus is the following : each time I scan a barcode, I
verify that the code isn't present in the table.

The loop in C# is the following :
------------------------------------------------
request = "SELECT CAB_COLIS FROM COLIS WHERE CAB_COLIS='"+code+"'";
if(clsD.searchData(request) == false)
{
// Display the code in a textbox and add the barcode in the table
}
else
{// Display an error message
}
-----------------------------------------------------

Where the searchData function is the following :
---------------------------------------------------------
public bool searchData(string req)
{
bool status = false;
SqlCeConnection connDB = new SqlCeConnection(strConn);
SqlCeCommand cmndDB = new SqlCeCommand(req, connDB);
SqlCeDataReader drdrDB;

try
{
connDB.Open();
drdrDB = cmndDB.ExecuteReader();

while ( drdrDB.Read() )
{
status = true;
}

drdrDB.Close();

connDB.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString(),
"Erreur", System.Windows.Forms.MessageBoxButtons.OK,System.Windows.Forms.MessageBoxIcon.Hand,System.Windows.Forms.MessageBoxDefaultButton.Button2);
}
return status;
}
---------------------------------------------------------------------------

So, I open each time the connection to the database and I make a request
including the primary key in the where clause.

Thanks for any suggestion.

 

[Ginny Caughey [MVP]]

Gandalf,

Then an index over CAB_COLIS would probably be a good idea unless the table
is quite small.

 

[Guest]

OK, I tried to create an index :
--------------------------------------------------
CREATE INDEX ON MYTABLE (MY_COLUMN)
--------------------------------------------------

But I had always an index because if I looked on the Query Analyser on the
Pocket PC, I see under"MYTABLE" indexes "PKMYTABLE".
The PKMYTABLE is created by the following request : "CONSTRAINT PKMYTABLE
PRIMARY KEY".

So should I think that a PRIMARY KEY is equivalent as an INDEX ?

Thanks for any information.

 

[Ginny Caughey [MVP]]

Gandalf,

If the key you're seeking on in the WHERE clause is your primary key, then
you don't need an additional index.

 

[Chris Tacke, MVP]

Just like I said on the 10th in this same thread, a primary key IS an index
and a clustered one at that.

-Chris

 

[Guest]

OK, in fact I tried to make the following task and it seems to resolve the
problem :
when I insert data in the database, I made before an "open" and a "close"
connection to the database.
Now, during the insertion process, I make an open and then a close when the
data are inserted in the database.

Thanks for your information and your help.