SQL Mobile INSERT problem

[Guest]

Hi all,
I've a database composed from one field as primary key (smallint) and second
field (NVarChar,20).
I try to insert 1000 record with this code and run correctly. Why if I try
to insert 10000 record the program fails ?
string insertSql = "INSERT INTO Impianti" + "(Codice, Nome) VALUES (@Codice,
@Nome)";
SqlCeConnection cn = new SqlCeConnection(@"Data Source =""\Hard
Disk\Database\MioDatabase.sdf"";");
cn.Open();
int recordsAffected = 0;
try
{
for (int i = 0; i < 1000; i++)
{
SqlCeCommand cmd = new SqlCeCommand(insertSql, cn);
cmd.Parameters.Add(new System.Data.SqlServerCe.SqlCeParameter("@Codice",
System.Data.SqlDbType.SmallInt, 2));
cmd.Parameters["@Codice"].Value = i + 1;
cmd.Parameters.Add(new System.Data.SqlServerCe.SqlCeParameter("@Nome",
System.Data.SqlDbType.NVarChar, 20));
cmd.Parameters["@Nome"].Value = (i + 1).ToString();
recordsAffected = cmd.ExecuteNonQuery();
}
}
catch
{
}
finally
{
cn.Close();
}

 

[jonfroehlich]

Though this is not directly related to yoru question, I would use a
SqlCeTransaction object to insert that many records at one time. It
should lead to better performance. For example,

SqlCeTransaction sqlTransact = sqlConn.BeginTransaction();

for(i up to N){
SqlCeCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = String.Format("INSERT INTO MyTable VALUES ('{0}',
'{1}')", i, "blah");
cmd.Transaction = sqlTransact;
cmd.ExecuteNonQuery();
}

sqlTransact.Commit();
sqlTransact.Dispose();

 

[Ilya Tumanov [MS]]

Since you forgot to mention how exactly it fails, I would guess you're
running out of resources because you're creating command for every record
instead of reusing one. Also please wrap it up into transaction as another
poster suggested. Do not switch to none-parameterized query though, that
would make things worse:



SqlCeTransaction sqlTransact = sqlConn.BeginTransaction();



SqlCeCommand cmd = new SqlCeCommand(insertSql, cn);

cmd.Transaction = sqlTransact;



cmd.Parameters.Add(new System.Data.SqlServerCe.SqlCeParameter("@Codice",
System.Data.SqlDbType.SmallInt, 2));



cmd.Parameters.Add(new System.Data.SqlServerCe.SqlCeParameter("@Nome",
System.Data.SqlDbType.NVarChar, 20));



for (int i = 0; i < 1000; i++)
{
cmd.Parameters[0].Value = i + 1;
cmd.Parameters[1].Value = (i + 1).ToString();
recordsAffected = cmd.ExecuteNonQuery();
}



sqlTransact.Commit();

sqlTransact.Dispose();


--
Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).