使用 SQL Server CE 插入到数据库中
本文关键字:数据库 插入 CE SQL Server 使用 | 更新日期: 2023-09-27 18:35:26
我已经重新设计了我的项目3次,最后我想在我的应用程序失控之前问一下。我正在构建一个复杂的系统监控和报告生成应用程序。我一直在试图找出哪种数据库方法最适合我的情况,我认为它是 SQL Server CE,因为我使用的是工作笔记本电脑,可能需要长达 1 周的时间才能安装东西。 如果我应该使用更友好的应用程序,我愿意接受建议。
假设我坚持使用 SQL Server CE,将记录插入到具有许多列的.sdf
文件中的最佳方法是什么?例如,数据库中的表可以包含 100 列。它像现在一样工作正常,但似乎每插入一行大约需要 1 秒。代码如下所示(最终会有更多的列):
con.Open();
SqlCeTransaction trans = con.BeginTransaction();
String insertRecord = "INSERT INTO AS400_Objects(projectName, obBuildObjectName, " +
"obBuildLibrary, obBuildType, obBuildExtendedAttribute, obBuildFormatLevel, " +
"obBuildUserAttribute, obBuildChangeDate, obBuildCreationDate, " +
"obBuildCreatorSystem, obBuildCreatorUserProfile, obBuildJournal, " +
"obBuildJournalStatus, obBuildLastUsedDate, obBuildObjectSize, obBuildOwner, " +
"obBuildRestoreDate, obBuildSaveDate, obBuildSourceFile, " +
"obBuildSourceFileUpdatedDate, obBuildTextDescription, obBuildSystemVersion)" +
"VALUES(@projectName, @obBuildObjectName, @obBuildLibrary, @obBuildType, " +
"@obBuildExtendedAttribute, @obBuildFormatLevel, @obBuildUserAttribute, " +
"@obBuildChangeDate, @obBuildCreationDate, @obBuildCreatorSystem, " +
"@obBuildCreatorUserProfile, @obBuildJournal, @obBuildJournalStatus, " +
"@obBuildLastUsedDate, @obBuildObjectSize, @obBuildOwner, @obBuildRestoreDate, " +
"@obBuildSaveDate, @obBuildSourceFile, @obBuildSourceFileUpdatedDate, " +
"@obBuildTextDescription, @obBuildSystemVersion)";
SqlCeCommand cmd = new SqlCeCommand(insertRecord, con);
cmd.Transaction = trans;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@projectName", packageName);
cmd.Parameters.AddWithValue("@obBuildObjectName", od[i].getValue(ObjectDescription.NAME).ToString());
cmd.Parameters.AddWithValue("@obBuildLibrary", od[i].getValue(ObjectDescription.LIBRARY).ToString());
cmd.Parameters.AddWithValue("@obBuildType", od[i].getValue(ObjectDescription.TYPE).ToString());
cmd.Parameters.AddWithValue("@obBuildExtendedAttribute", od[i].getValue(ObjectDescription.EXTENDED_ATTRIBUTE).ToString());
cmd.Parameters.AddWithValue("@obBuildFormatLevel", "");
cmd.Parameters.AddWithValue("@obBuildUserAttribute", od[i].getValue(ObjectDescription.USER_DEFINED_ATTRIBUTE).ToString());
cmd.Parameters.AddWithValue("@obBuildChangeDate", changeDate);
cmd.Parameters.AddWithValue("@obBuildCreationDate", creationDate);
cmd.Parameters.AddWithValue("@obBuildCreatorSystem", od[i].getValue(ObjectDescription.CREATOR_SYSTEM).ToString());
cmd.Parameters.AddWithValue("@obBuildCreatorUserProfile", od[i].getValue(ObjectDescription.CREATOR_USER_PROFILE).ToString());
cmd.Parameters.AddWithValue("@obBuildJournal", od[i].getValue(ObjectDescription.JOURNAL).ToString());
cmd.Parameters.AddWithValue("@obBuildJournalStatus", journalStatus);
cmd.Parameters.AddWithValue("@obBuildLastUsedDate", lastUsedDate);
cmd.Parameters.AddWithValue("@obBuildObjectSize", od[i].getValue(ObjectDescription.OBJECT_SIZE).ToString());
cmd.Parameters.AddWithValue("@obBuildOwner", od[i].getValue(ObjectDescription.OWNER).ToString());
cmd.Parameters.AddWithValue("@obBuildRestoreDate", restoreDate);
cmd.Parameters.AddWithValue("@obBuildSaveDate", saveDate);
cmd.Parameters.AddWithValue("@obBuildSourcefile", od[i].getValue(ObjectDescription.SOURCE_FILE).ToString());
cmd.Parameters.AddWithValue("@obBuildSourceFileUpdatedDate", sourceFileUpdatedDate);
cmd.Parameters.AddWithValue("@obBuildTextDescription", od[i].getValue(ObjectDescription.TEXT_DESCRIPTION).ToString());
cmd.Parameters.AddWithValue("@obBuildSystemVersion", od[i].getValue(ObjectDescription.SYSTEM_LEVEL).ToString());
cmd.ExecuteNonQuery();
trans.Commit(CommitMode.Immediate);
cmd.Dispose();
con.Close();
con.Dispose();
基于这种情况,我是否可以采取一些措施来提高查询的性能?我来自 AS400 大型机世界,1 秒会给你数百甚至数千行。该应用程序的最终结果是它将创建一个要发送到某处的输出文件,然后双击该文件会将所有这些数据加载到远程计算机上。我看过SqlCEAdapter
,但无法找到有关如何使用它以适应我的情况的良好解释。
提前感谢!
你听说过数据库规范化吗?这样可以避免重复数据,并减少每个 INSERT 所需的列数。该过程在没有 CommitMode.Imimmediate 的情况下运行得也会快得多。最后,您可以使用 SqlCeUpdateableRecord 和 TableDirect 绕过查询进程或快速插入。