驾校理论考试系统之数据提取三（3）

àì夳堔傛蜴生んèń 2021-12-16 16:59 195阅读 0赞

<table> 
 <tbody> 
 <tr> 
 <td> <a href="http://115.com/file/ben6jw0o" rel="nofollow"><img src="https://images.cnblogs.com/cnblogs_com/zuizuihao/201207/20120718234521550.png" alt=""></a> SaveTODataXML.zip </td> 
 <td> <a href="http://115.com/file/an8i0r06" rel="nofollow"><img src="https://images.cnblogs.com/cnblogs_com/zuizuihao/201207/201207182345212469.png" alt=""></a> data.xml 1 </td> 
 <td> <a href="http://115.com/file/an8iwlwl" rel="nofollow"><img src="https://images.cnblogs.com/cnblogs_com/zuizuihao/201207/201207182345219404.png" alt=""></a> SQLiteDBHelper.cs </td> 
 <td> <a href="http://115.com/file/e7138uw9" rel="nofollow"><img src="https://images.cnblogs.com/cnblogs_com/zuizuihao/201207/201207182345227976.png" alt=""></a> SQLite.Interop.dll </td> 
 </tr> 
 <tr> 
 <td style="text-align:left;"> <a href="http://115.com/file/c2cuac3k" rel="nofollow"><img src="https://images.cnblogs.com/cnblogs_com/zuizuihao/201207/201207182345226547.png" alt=""></a> System.Data.SQLite.dll </td> 
 <td style="text-align:left;"> <a href="http://115.com/file/an8i0kf2" rel="nofollow"><img src="https://images.cnblogs.com/cnblogs_com/zuizuihao/201207/201207182345223483.png" alt=""></a> 空CarTraining.db </td> 
 <td style="text-align:left;"> <a href="http://115.com/file/e713jxki" rel="nofollow"><img src="https://images.cnblogs.com/cnblogs_com/zuizuihao/201207/20120718234522418.png" alt=""></a> 满数据库CarTraining.db </td> 
 <td>&nbsp;</td> 
 </tr> 
 </tbody> 
</table>

首先将新建一个项目SaveTODataXML，然后将上次生成的data.xml

放入到bin/debug中，这样是为了避免加长长的目录名，直接引号使用就好了！！

然后加入用来帮助处理sqlite语句的类文件

还有引用两个dll，SQLite.Interop.dll,和System.Data.SQLite.dll

将System.Data.SQLite.dll引用到项目

还有就是新建好的空的数据库CarTraining.db也要加入到项目中

![201207182345239762.png][]

可以用**Navicat for SQLite查看，具体用法参看帮助文档。**

一切准备就绪，在来看看代码是如何实现的。

从分析data.xml开始，首先我们有两种类型的题目，判断和选择

然后选择里面，题目部分，

<Title>动画3中有几种违法行为。（行驶中遮挡号牌，超速行驶）</Title>

<A>一种违法行为</A>

二种违法行为

<C>三种违法行为</C>

<D>四种违法行为</D>

我们要把他们合并成一个字符串，填入Question

public static string ConcatTNodeText(XmlNode node)

string retStr = null;

foreach (XmlNode x in node.ChildNodes\[0\].ChildNodes)

retStr += x.InnerText + "\\n";

return retStr;

利用此方法！！

其次是问题部分<Answer>B </Answer>

需要转换调用publicstatic int ConvertStrToNum(string str)方法转换为数字类型

再有就是source部分，这里

</Source>

<Image>c:\\users\\xialang\\documents\\visual studio 2010\\Projects\\DocToDataXml\\DocToDataXml\\bin\\Debug\\驾校考试试题\\安全行车常识\\安全装置/单选\_files/image1.jpeg

</Image>

</Source>

由于处理图片的视频的方式不同，这里同样需要一个if/else;而且有没有source，处理方法又有不同，所以又加一个if/else，具体参看源代码

再来说说sqlite语句吧，由于我们调用了！SQliteDBHelper中的语句所以会比较容易！处理，只需要在

Navicat for SQLite中编写好了Insert语句，填入参数parameters

再调用db.ExecuteNonQuery(sql, parameters);即可

点击执行！！

![201207182345243808.png][]

总共有2024-1001，一共1024条数据放入数据库

![201207182345275986.png][]

打开Navicat for SQLite 发现数据已经成功的填入了！开心一下！！

最后我们还有一行数据未填入，那就是Random属性！！

定义数组arr publicstatic int\[\] arr = new int\[1024\];保存1024个随机数

调用方法public static void RandomSort(int CountNum)

产生1024个随机数，并填入数据arr

然后调用publicstatic void ShowData()更新表中的Random属性！！

这次我们没有取巧调用SQLiteDBHelper现成的方法

using (SQLiteConnection connection = new SQLiteConnection("Data Source= " \+ @"C:\\Users\\xialang\\Documents\\Visual Studio 2010\\Projects\\TrainingCar\\TrainingCar\\bin\\Debug\\CarTraining.db"))

connection.Open();

using (DbTransaction transaction = connection.BeginTransaction())

using (SQLiteCommand command = new SQLiteCommand(connection))

for (int i = 0; i < dt.Rows.Count; i++)

string sql1 = "update OTHEREXAM set Random = '" \+ arr\[i\] + "' where TestID = '" \+ dt.Rows\[i\]\[0\] + "'";

command.CommandText = sql1;

command.ExecuteNonQuery();

transaction.Commit();

而是调用这样处理，原因我在这里解释一下，学过数据库的都知道，事务这个概念，我们可以想象一下，我们有一千个update语句需要数据库引擎处理，是一次性要他处理，还是请求他处理1000次快！！！这就是我们要吧for语句放入到using (DbTransaction transaction = connection.BeginTransaction())之中的原因！！

F5执行后，数据已经成功的填入了！！

![201207182345287839.png][]

这是我在帮助文档中看到的！我来译一译！来自SQLite .Net Help

#### The Importance of Transactions ####

事务的重要性

If you are inserting data in SQLite without first starting a transaction: **DO NOT PASS GO! Call BeginTransaction() right now, and finish with Commit()!** If you think I'm kidding, think again. SQLite's A.C.I.D. design means that every single time you insert any data outside a transaction, an implicit transaction is constructed, the insert made, and the transaction destructed. **EVERY TIME.** If you're wondering why in the world your inserts are taking 100x longer than you think they should, look no further.

如果处理SQLite插入语句时，一开始没有开启事务：千万不要这样做，你会郁闷的，赶紧 BeginTransaction()，完成时Commit()它，如果你认为我是吓你的，SQLite的是这样设计的，事务之外每次插入一行数据，事务会自动创建和销毁，注意是每次，如果你想知道到底什么原因实际用的插入时间要比预期的要多耗费100倍的时间，问题就在这里！！

#### Prepared Statements  ####

Lets have a quick look at the following code and evaluate its performance:

**例句**

看看下列代码，评估一下性能怎么样！

using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))

{

int n;

for (n = 0; n < 100000; n ++)

{

mycommand.CommandText = String.Format("INSERT INTO [MyTable] ([MyId]) VALUES({0})", n + 1);

mycommand.ExecuteNonQuery();

}

This code seems pretty tight, but if you think it performs well, you're dead wrong. Here's what's wrong with it:

*  I didn't start a transaction first! This insert is dog slow! 
 *  The CLR is calling "new" implicitly 100,000 times because I am formatting a string in the loop for every insert 
 *  Since SQLite precompiles SQL statements, the engine is constructing and deconstructing 100,000 SQL statements and allocating/deallocating their memory 
 *  All this construction and destruction is involving about 300,000 more native to managed interop calls than an optimized insert

这段代码看起来很紧凑，但如果你认为它性能不错，那就错了！，而且错的很彻底。原因如下：

*  没有开启事务，插入过程会死慢死慢的
 *  公共语言运行时隐式调用了"new"10000次，因为每次插入后都会格式化string数据
 *  由于SQLite会预编译SQL语句，所以引擎会构造和析构10000次SQL语句和分配和销毁内存！！
 *  所有这些构造和析构多达30000次，都由于managed interop可接管代码交互call调用，而不是为了优化Insert操作！

So lets rewrite that code slightly:

所以我们重写了一点代码

using (SQLiteTransaction mytransaction = myconnection.BeginTransaction())

{

using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))

{

SQLiteParameter myparam = new SQLiteParameter();

int n;

mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)";

mycommand.Parameters.Add(myparam);

for (n = 0; n < 100000; n ++)

{

myparam.Value = n + 1;

mycommand.ExecuteNonQuery();

}

mytransaction.Commit();

}

Now this is a blazing fast insert for ***any*** database engine, not just SQLite. The SQL statement is prepared one time -- on the first call to ExecuteNonQuery(). Once prepared, it never needs re-evaluating. Furthermore, we're allocating no memory in the loop and doing a very minimal number of interop transitions. Surround the entire thing with a transaction, and the performance of this insert is so far and away faster than the original that it merits a hands-on-the-hips pirate-like laugh.

Every database engine worth its salt utilizes prepared statements. If you're not coding for this, you're not writing optimized SQL, and that's the bottom line.

现在插入操作快了很多了吧！这对每个数据库引擎来所都是如此。SQl语句只准备了一次，在ExecuteNonQuery()第一次调用，准备好后，不需要重新预处理了。更重要的是，循环里面，不需要分配内存了，只需要少部分与SQLite的交互操作。整个过程都在事务中进行的，插入性能比原先快了好多，这很值得高兴！！

如果你不是专门做数据库编码的，也不会写优化好的SQL语句，那么每一个数据库引擎都有提供这些优化语句，只需代入数据处理就可以了。

转载于:https://www.cnblogs.com/zuizuihao/archive/2012/07/19/2598424.html

[201207182345239762.png]: /images/20211216/e01f71b640b04f25afeee18cf703b66a.png
[201207182345243808.png]: /images/20211216/375a0d910a984380a4660f1038f9893e.png
[201207182345275986.png]: /images/20211216/9984bc481de84265b2beac6b8c6df0dd.png
[201207182345287839.png]: /images/20211216/39aee8eb29114f8ab856920aae9572b5.png