sqlce 真的是如何不成熟的数据库?

dahuiing 2009-8-2 16:56 楼主

有人可以指点一下我吗?搞这个问题都差不多三天了,都没解决.
开发平台 c#.net vs2008 数据库sqlce, pda平台wince 系统,一款叫i60的pda,

原先用sqlce,在往一个表中批插入时,只要这个表的字段大于4个以上,
比如说插入1000条,这时,在插入到300多条时就出错,或者报TypeLoadException,或者不知是什么错误.

后来换了sqlite,得确,在批插入时非常快,也没有错误.
但又换来个问题, sqlite 在单插入记录时相对慢,比如说会到100ms多到200ms,注意是单插入,不定时插入,也就是说开事务没用.

而相对sqlce来说,单插入sqlce 速度只要50ms到100ms,
老大说sqlite这样的单插入时间不可接受,唯有用回sqlce,
但就又回到原来的问题了.............

各位哥哥姐姐们,有没有遇到过sqlce 这样的问题??
难道我要在批插入sqlce 时,这样处理?
批插入10条,然后,close连接,再重连,再插入10条,这样...??

回复评论（5）

沙发 rubyangel

附我的代码

public partial class Form1 : Form
{
public static readonly string APPPATH = Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);
//string cesqlconnstr = "";
public Form1()
{
InitializeComponent();
//cesqlconnstr = "DataSource=testdb.sdf";
}
public delegate void dlgShoMsg(string pMsg);
private void ShowMsg(string pMsg)
{
if (this.label1.InvokeRequired)
{
dlgShoMsg dlg = new dlgShoMsg(ShowMsg);
this.label1.Invoke(dlg, new object[] { pMsg });
}
else
{
this.label1.Text = pMsg;
}
}
private void button1_Click(object sender, EventArgs e)
{
string sFileName = "testdb.sdf";
System.IO.File.Delete(APPPATH + "\"+ sFileName);
string sPaaa = "DataSource=" + APPPATH + "\" + sFileName;
Db4SqlCE.CreateSDF(sPaaa);
Db4SqlCE db = new Db4SqlCE(sPaaa);
string sSql = @"CREATE TABLE [pubgoods] (
[goodsid] [nvarchar] (50) NOT NULL PRIMARY KEY,
[styleid] [nvarchar] (50) NULL ,
[styledesc] [nvarchar] (200) NULL ,
[sizecategoryid] [nvarchar] (50) NULL ,
[colorid] [nvarchar] (50) NULL ,
[colordesc] [nvarchar] (50) NULL ,
[sizeid] [nvarchar] (50) NULL ,
[sizedesc] [nvarchar] (50) NULL
)
";
db.Execute(sSql);
System.Threading.Thread th = new System.Threading.Thread(new System.Threading.ThreadStart
(Doit));
th.IsBackground = true;
th.Start();
}
private void Doit()
{
int iRow = 1000;
string sFileName = "testdb.sdf";
string sPaaa = "DataSource=" + APPPATH + "\" + sFileName;
Db4SqlCE db = new Db4SqlCE(sPaaa);
db.ConnectionKeepOpenTf = true;
db.TransactionBegin();
try
{
string sSql = "";
for (int i = 1; i <= iRow; i++)
{
sSql = string.Format(@"INSERT INTO [pubgoods]([goodsid], [styleid], [styledesc],
[sizecategoryid], [colorid], [colordesc], [sizeid], [sizedesc])
select '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}'"
, i.ToString(), "styleid".ToString().Trim()
, "styledesc".ToString().Trim(), "sizecategoryid".ToString().Trim()
, "colorid".ToString().Trim(), "colordesc".ToString().Trim()
, "sizeid".ToString().Trim(), "sizedesc".ToString().Trim());
db.Execute(sSql,true);
this.ShowMsg(i.ToString() + "/" + iRow.ToString());
}
}
catch (Exception err)
{
db.TransactionRollback();
MessageBox.Show(err.Message);
return;
}
db.TransactionCommit();
}
}