개정판 1421f1d6
dev issue #000 : sql Insert 속도개선 (SQLiteTransaction 사용)
Change-Id: I629cdd2fbbe9f2733a059905fb40ef6758ae3c8c
| DTI_PID/BaseModel/Project_DB.cs | ||
|---|---|---|
| 54 | 54 |
Project_Info projectInfo = Project_Info.GetInstance(); |
| 55 | 55 |
using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))
|
| 56 | 56 |
{
|
| 57 |
|
|
| 57 |
|
|
| 58 | 58 |
try |
| 59 | 59 |
{
|
| 60 | 60 |
connection.Open(); |
| ... | ... | |
| 360 | 360 |
try |
| 361 | 361 |
{
|
| 362 | 362 |
connection.Open(); |
| 363 |
using (SQLiteCommand cmd = connection.CreateCommand())
|
|
| 363 |
using (SQLiteTransaction transaction = connection.BeginTransaction())
|
|
| 364 | 364 |
{
|
| 365 |
foreach (var item in datas) |
|
| 365 |
try |
|
| 366 |
{
|
|
| 367 |
using (SQLiteCommand cmd = connection.CreateCommand()) |
|
| 368 |
{
|
|
| 369 |
foreach (var item in datas) |
|
| 370 |
{
|
|
| 371 |
cmd.Parameters.Clear(); |
|
| 372 |
cmd.CommandText = string.Format("INSERT OR REPLACE INTO {0} (UID, NAME, SPPID_SYMBOL_PATH) VALUES (@UID, @NAME, @SPPID_SYMBOL_PATH)", SPPID_SYMBOL_MAPPING_TABLE);
|
|
| 373 |
cmd.Parameters.AddWithValue("@UID", item.Item1);
|
|
| 374 |
cmd.Parameters.AddWithValue("@NAME", item.Item2);
|
|
| 375 |
cmd.Parameters.AddWithValue("@SPPID_SYMBOL_PATH", item.Item3);
|
|
| 376 |
cmd.ExecuteNonQuery(); |
|
| 377 |
} |
|
| 378 |
} |
|
| 379 |
transaction.Commit(); |
|
| 380 |
connection.Close(); |
|
| 381 |
} |
|
| 382 |
catch (Exception ex) |
|
| 366 | 383 |
{
|
| 367 |
cmd.Parameters.Clear(); |
|
| 368 |
cmd.CommandText = string.Format("INSERT OR REPLACE INTO {0} (UID, NAME, SPPID_SYMBOL_PATH) VALUES (@UID, @NAME, @SPPID_SYMBOL_PATH)", SPPID_SYMBOL_MAPPING_TABLE);
|
|
| 369 |
cmd.Parameters.AddWithValue("@UID", item.Item1);
|
|
| 370 |
cmd.Parameters.AddWithValue("@NAME", item.Item2);
|
|
| 371 |
cmd.Parameters.AddWithValue("@SPPID_SYMBOL_PATH", item.Item3);
|
|
| 372 |
cmd.ExecuteNonQuery(); |
|
| 384 |
transaction.Rollback(); |
|
| 385 |
} |
|
| 386 |
finally |
|
| 387 |
{
|
|
| 388 |
transaction.Dispose(); |
|
| 373 | 389 |
} |
| 374 | 390 |
} |
| 375 |
connection.Close(); |
|
| 376 | 391 |
} |
| 377 | 392 |
catch (Exception ex) |
| 378 | 393 |
{
|
| ... | ... | |
| 395 | 410 |
try |
| 396 | 411 |
{
|
| 397 | 412 |
connection.Open(); |
| 398 |
using (SQLiteCommand cmd = connection.CreateCommand())
|
|
| 413 |
using (SQLiteTransaction transaction = connection.BeginTransaction())
|
|
| 399 | 414 |
{
|
| 400 |
foreach (var item in datas) |
|
| 415 |
try |
|
| 416 |
{
|
|
| 417 |
using (SQLiteCommand cmd = connection.CreateCommand()) |
|
| 418 |
{
|
|
| 419 |
foreach (var item in datas) |
|
| 420 |
{
|
|
| 421 |
cmd.Parameters.Clear(); |
|
| 422 |
cmd.CommandText = string.Format("INSERT OR REPLACE INTO {0} (UID, SPPID_ATTRIBUTE) VALUES (@UID, @SPPID_ATTRIBUTE)", SPPID_ATTRIBUTE_MAPPING_TABLE);
|
|
| 423 |
cmd.Parameters.AddWithValue("@UID", item.Item1);
|
|
| 424 |
cmd.Parameters.AddWithValue("@SPPID_ATTRIBUTE", item.Item2);
|
|
| 425 |
cmd.ExecuteNonQuery(); |
|
| 426 |
} |
|
| 427 |
} |
|
| 428 |
transaction.Commit(); |
|
| 429 |
connection.Close(); |
|
| 430 |
} |
|
| 431 |
catch (Exception ex) |
|
| 401 | 432 |
{
|
| 402 |
cmd.Parameters.Clear();
|
|
| 403 |
cmd.CommandText = string.Format("INSERT OR REPLACE INTO {0} (UID, SPPID_ATTRIBUTE) VALUES (@UID, @SPPID_ATTRIBUTE)", SPPID_ATTRIBUTE_MAPPING_TABLE);
|
|
| 404 |
cmd.Parameters.AddWithValue("@UID", item.Item1);
|
|
| 405 |
cmd.Parameters.AddWithValue("@SPPID_ATTRIBUTE", item.Item2);
|
|
| 406 |
cmd.ExecuteNonQuery();
|
|
| 433 |
transaction.Rollback();
|
|
| 434 |
}
|
|
| 435 |
finally
|
|
| 436 |
{
|
|
| 437 |
transaction.Dispose();
|
|
| 407 | 438 |
} |
| 408 | 439 |
} |
| 409 |
connection.Close(); |
|
| 410 | 440 |
} |
| 411 | 441 |
catch (Exception ex) |
| 412 | 442 |
{
|
| ... | ... | |
| 417 | 447 |
connection.Dispose(); |
| 418 | 448 |
} |
| 419 | 449 |
} |
| 420 |
|
|
| 421 | 450 |
return true; |
| 422 | 451 |
} |
| 423 | 452 |
} |
| DTI_PID/SPPIDConverter/SPPIDConverter.csproj | ||
|---|---|---|
| 5 | 5 |
<Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration> |
| 6 | 6 |
<Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform> |
| 7 | 7 |
<ProjectGuid>{B6757E78-6B59-40A3-A7BB-E73E8F81B6C3}</ProjectGuid>
|
| 8 |
<OutputType>Library</OutputType>
|
|
| 8 |
<OutputType>WinExe</OutputType>
|
|
| 9 | 9 |
<AppDesignerFolder>Properties</AppDesignerFolder> |
| 10 | 10 |
<RootNamespace>Converter.SPPID</RootNamespace> |
| 11 | 11 |
<AssemblyName>SPPIDConverter</AssemblyName> |
| ... | ... | |
| 137 | 137 |
<Compile Include="Model\LineMapping.cs" /> |
| 138 | 138 |
<Compile Include="Model\AssociationMapping.cs" /> |
| 139 | 139 |
<Compile Include="Model\SymbolMapping.cs" /> |
| 140 |
<Compile Include="Program.cs" /> |
|
| 140 | 141 |
<Compile Include="Properties\AssemblyInfo.cs" /> |
| 141 | 142 |
<Compile Include="Properties\Msg.Designer.cs"> |
| 142 | 143 |
<AutoGen>True</AutoGen> |
내보내기 Unified diff
