private void ChangedDatabase()
{
m_adapter = new SQLiteDataAdapter("select * from serverlist", m_dbwrap.Database.Connection);
SQLiteCommandBuilder cb = new SQLiteCommandBuilder(m_adapter);
m_adapter.InsertCommand = cb.GetInsertCommand();
m_adapter.DeleteCommand = cb.GetDeleteCommand();
m_adapter.UpdateCommand = cb.GetUpdateCommand();
Reload();
}
protected override void ProcessRecord()
{
base.BeginProcessing();
if (ShouldProcess("Database", "BeginTransaction"))
{
if (_Transaction == null)
{
_command = connection.CreateCommand();
_command.Transaction = connection.BeginTransaction();
}
else
{
_command = _Transaction.Connection.CreateCommand();
_command.Transaction = _Transaction;
}
}
_command.CommandText = "SELECT * FROM '" + name + "' LIMIT 1";
SQLiteDataAdapter adapter = new SQLiteDataAdapter(_command);
SQLiteCommandBuilder commandbuilder = new SQLiteCommandBuilder(adapter);
adapter.InsertCommand = (SQLiteCommand)commandbuilder.GetInsertCommand().Clone();
commandbuilder.DataAdapter = null;
if (ShouldProcess("Database", "Update"))
{
foreach (DataRow row in inputobject.Rows)
{
if (row.RowState == DataRowState.Unchanged)
{
row.SetAdded();
}
}
adapter.Update(inputobject);
}
if (ShouldProcess("Transaction", "Commit"))
{
if (_command.Transaction != null)
{
if (_Transaction == null)
{
_command.Transaction.Commit();
}
}
}
inputobject.AcceptChanges();
}
/// <summary>
/// 修改数据表记录
/// </summary>
/// <returns></returns>
public static bool UpdateTable(DataTable srcTable, string tableName) {
bool isok = false;
try {
SQLiteCommand command = new SQLiteCommand();
using (SQLiteConnection conn = GetSQLiteConnection()) {
if (conn.State != ConnectionState.Open) conn.Open();
command.Connection = conn;
command.CommandText = "SELECT * FROM " + tableName;
SQLiteDataAdapter SQLiteDA = new SQLiteDataAdapter(command);
SQLiteCommandBuilder SQLiteCB = new SQLiteCommandBuilder(SQLiteDA);
SQLiteDA.InsertCommand = SQLiteCB.GetInsertCommand();
SQLiteDA.DeleteCommand = SQLiteCB.GetDeleteCommand();
SQLiteDA.UpdateCommand = SQLiteCB.GetUpdateCommand();
SQLiteDA.Update(srcTable);
}
isok = true;
} catch { ;}
return isok;
}
// Utilizes the SQLiteCommandBuilder, which in turn utilizes SQLiteDataReader's GetSchemaTable() functionality
internal void InsertMany(DbConnection cnn, bool bWithIdentity)
{
int nmax = 1000;
using (DbTransaction dbTrans = cnn.BeginTransaction())
{
using (DbDataAdapter adp = new SQLiteDataAdapter())
{
using (DbCommand cmd = cnn.CreateCommand())
{
cmd.Transaction = dbTrans;
cmd.CommandText = "SELECT * FROM TestCase WHERE 1=2";
adp.SelectCommand = cmd;
using (DbCommandBuilder bld = new SQLiteCommandBuilder())
{
bld.DataAdapter = adp;
using (adp.InsertCommand = (SQLiteCommand)((ICloneable)bld.GetInsertCommand()).Clone())
{
bld.DataAdapter = null;
if (bWithIdentity)
{
adp.InsertCommand.CommandText += ";SELECT last_insert_rowid() AS [ID]";
adp.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
}
using (DataTable tbl = new DataTable())
{
adp.Fill(tbl);
for (int n = 0; n < nmax; n++)
{
DataRow row = tbl.NewRow();
row[1] = n + nmax;
tbl.Rows.Add(row);
}
frm.Write(String.Format(" InsertMany{0} ({1} rows) Begins ... ", (bWithIdentity == true) ? "WithIdentityFetch" : " ", nmax));
int dtStart = Environment.TickCount;
adp.Update(tbl);
int dtEnd = Environment.TickCount;
dtEnd -= dtStart;
frm.Write(String.Format("Ends in {0} ms ... ", (dtEnd)));
dtStart = Environment.TickCount;
dbTrans.Commit();
dtEnd = Environment.TickCount;
dtEnd -= dtStart;
frm.WriteLine(String.Format("Commits in {0} ms", (dtEnd)));
}
}
}
}
}
}
}
//ͨ�������ݺ���
private string UpdateTable(DataTable srcTable, string tableName)
{
try
{
command.CommandText = "SELECT * FROM " + tableName;
SQLiteDataAdapter oda = new SQLiteDataAdapter(command);
SQLiteCommandBuilder ocb = new SQLiteCommandBuilder(oda);
oda.InsertCommand = ocb.GetInsertCommand();
oda.DeleteCommand = ocb.GetDeleteCommand();
oda.UpdateCommand = ocb.GetUpdateCommand();
oda.Update(srcTable);
}
catch (Exception ex)
{
return ex.ToString();
}
return "success";
}
/// <summary>
/// JVUmaOutput処理
/// </summary>
public static void JVUmaOutput()
{
DataTable umaDataTable = new DataTable();
string delDate = DateTime.Today.AddMonths(-6).ToString("yyyyMMdd");
// 出力データ取得
using (SQLiteCommand command = DbConn.CreateCommand())
{
command.CommandText = "SELECT * FROM uma WHERE DelKubun='0' OR (DelKubun='1' AND DelDate>='" + delDate + "')";
using (SQLiteDataAdapter da = new SQLiteDataAdapter(command))
{
da.Fill(umaDataTable);
}
}
// 年替わり更新
if (JVRelayClass.IsNextYear == true)
{
foreach (DataRow umaDataRow in umaDataTable.Rows)
{
if (umaDataRow["UmaClass"].ToString() != GetUmaClass(umaDataRow))
{
umaDataRow["UmaClass"] = GetUmaClass(umaDataRow);
}
}
// データ更新
using (SQLiteTransaction tran = DbConn.BeginTransaction())
{
using (SQLiteCommand command = DbConn.CreateCommand())
{
command.Transaction = tran;
command.CommandText = "SELECT * FROM uma";
using (SQLiteDataAdapter da = new SQLiteDataAdapter(command))
using (SQLiteCommandBuilder cb = new SQLiteCommandBuilder(da))
{
cb.SetAllValues = false;
cb.ConflictOption = ConflictOption.OverwriteChanges;
da.UpdateCommand = cb.GetUpdateCommand();
da.InsertCommand = cb.GetInsertCommand();
da.DeleteCommand = cb.GetDeleteCommand();
da.Update(umaDataTable);
}
}
tran.Commit();
}
}
// 出力
foreach (DataRow dr in umaDataTable.Rows)
{
OutputUmaData(eOutput.Umanushi, dr);
}
}
static void Main(string[] args)
{
string dbLocation = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "tilecache.s3db");
int minz = 7;
int maxz = 10;
double minx = -95.844727;
double miny = 35.978006;
double maxx = -88.989258;
double maxy = 40.563895;
string mapServiceUrl = "http://sampleserver1.arcgisonline.com/ArcGIS/rest/services/Demographics/ESRI_Census_USA/MapServer";
int maxDegreeOfParallelism = 10;
bool replaceExistingCacheDB = true;
bool showHelp = false;
bool verbose = false;
string mapServiceType = "agsd";
string settings = string.Empty;
bool showInfo = false;
ITileUrlSource tileSource = new OSMTileUrlSource()
{
MapServiceUrl = TileHelper.OSM_BASE_URL_TEMPLATE,
};
var options = new OptionSet()
{
{"h|?|help", "Show this message and exits", h => showHelp = h != null},
{"v|verbose", "Display verbose information logs while running", v => verbose = v != null},
{"t|type=", "Type of the map service to be cached", t => mapServiceType = t.ToLower()},
{"m|mapservice=", "Url of the Map Service to be cached", m => mapServiceUrl = m},
{"s|settings=", "Extra settings needed by the type of map service being used", s => settings = s},
{"o|output=", "Complete file path and file name where the tile cache needs to be outputted", o => dbLocation = o},
{"z|minz=", "Minimum zoom scale at which to begin caching", z => int.TryParse(z, out minz)},
{"Z|maxz=", "Maximum zoom scale at which to end caching", Z => int.TryParse(Z, out maxz)},
{"x|minx=", "Minimum X coordinate value of the extent to cache", x => double.TryParse(x, out minx)},
{"y|miny=", "Minimum Y coordinate value of the extent to cache", y => double.TryParse(y, out miny)},
{"X|maxx=", "Maximum X coordinate value of the extent to cache", X => double.TryParse(X, out maxx)},
{"Y|maxy=", "Maximum Y coordinate value of the extent to cache", Y => double.TryParse(Y, out maxy)},
{"p|parallelops=", "Limits the number of concurrent operations run by TileCutter", p => int.TryParse(p, out maxDegreeOfParallelism)},
{"r|replace=", "Delete existing tile cache MBTiles database if already present and create a new one.", r => Boolean.TryParse(r, out replaceExistingCacheDB)},
{"i|info=", "", i => showInfo = i != null}
};
options.Parse(args);
if (showHelp)
{
ShowHelp(options);
return;
}
var tiles = GetTiles(minz, maxz, minx, miny, maxx, maxy);
if (showInfo)
{
var zoomLevels = Enumerable.Range(0, 21);
var tileCountByZoomLevel = new Dictionary<int, int>();
foreach (var level in zoomLevels)
tileCountByZoomLevel[level] = 0;
foreach (var tile in tiles)
tileCountByZoomLevel[tile.Level]++;
foreach (var item in tileCountByZoomLevel)
if (item.Value > 0)
Console.WriteLine(string.Format("Zoom level {0} - {1} tiles", item.Key, item.Value));
Console.WriteLine("Press any key to exit...");
Console.ReadKey();
return;
}
if (!string.IsNullOrEmpty(mapServiceType))
tileSource = GetTileSource(mapServiceType, mapServiceUrl, settings);
string tileCacheDirectory = Path.GetDirectoryName(dbLocation);
string tilecacheFileName = Path.GetFileNameWithoutExtension(dbLocation);
if (!Directory.Exists(tileCacheDirectory))
{
Console.WriteLine("The tilecache path provided is not valid");
return;
}
string errorLogFile = Path.Combine(tileCacheDirectory, tilecacheFileName + ".log");
//if the user option to delete existing tile cache db is true delete it or else add to it
if (replaceExistingCacheDB && File.Exists(dbLocation))
File.Delete(dbLocation);
//Connect to the sqlite database
string connectionString = string.Format("Data Source={0}; FailIfMissing=False", dbLocation);
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
//Check if the 'metadata' table exists, if not create it
var rows = connection.GetSchema("Tables").Select("Table_Name = 'metadata'");
if (!rows.Any())
{
var command = connection.CreateCommand();
command.CommandText = "CREATE TABLE metadata (name text, value text);";
command.ExecuteNonQuery();
connection.Execute("CREATE UNIQUE INDEX name ON metadata ('name')");
connection.Execute(@"INSERT INTO metadata(name, value) values (@a, @b)",
new[] {
new { a = "name", b = "cache" },
new { a = "type", b = "overlay" },
new { a = "version", b = "1" },
new { a = "description", b = "some info here" },
new { a = "format", b = "png" },
new { a = "bounds", b = string.Format("{0},{1},{2},{3}", minx, miny, maxx, maxy)}
});
}
//Check if the 'images' table exists, if not create it
rows = connection.GetSchema("Tables").Select("Table_Name = 'images'");
if (!rows.Any())
{
var command = connection.CreateCommand();
command.CommandText = "CREATE TABLE [images] ([tile_id] INTEGER NOT NULL PRIMARY KEY, [tile_md5hash] VARCHAR(256) NOT NULL, [tile_data] BLOB NULL);";
command.ExecuteNonQuery();
command = connection.CreateCommand();
command.CommandText = "CREATE UNIQUE INDEX images_hash on images (tile_md5hash)";
command.ExecuteNonQuery();
}
//Check if the 'map' table exists, if not create it
rows = connection.GetSchema("Tables").Select("Table_Name = 'map'");
if (!rows.Any())
{
var command = connection.CreateCommand();
command.CommandText = "CREATE TABLE [map] ([map_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [tile_id] INTEGER NOT NULL, [zoom_level] INTEGER NOT NULL, [tile_row] INTEGER NOT NULL, [tile_column] INTEGER NOT NULL);";
command.ExecuteNonQuery();
}
}
FileStream errorLog = File.Create(errorLogFile);
StreamWriter errorWriter = new StreamWriter(errorLog);
Console.WriteLine("Output Cache file is: " + dbLocation);
BlockingCollection<TileImage> images = new BlockingCollection<TileImage>();
Task.Factory.StartNew(() =>
{
ParallelOptions parallelOptions = new ParallelOptions() { MaxDegreeOfParallelism = maxDegreeOfParallelism };
Parallel.ForEach(tiles, parallelOptions, (tile) =>
{
string tileUrl = tileSource.GetTileUrl(tile);
WebClient client = new WebClient();
try
{
byte[] image = client.DownloadData(tileUrl);
images.Add(new TileImage()
{
Tile = tile,
Image = image
});
if (verbose)
Console.WriteLine(string.Format("Tile Level:{0}, Row:{1}, Column:{2} downloaded.", tile.Level, tile.Row, tile.Column));
}
catch (WebException ex)
{
errorWriter.WriteLine(String.Format("{0},{1},{2} - {3}", tile.Level, tile.Column, tile.Row, ex.Message));
errorLog.Flush();
Console.WriteLine(string.Format("Error while downloading tile Level:{0}, Row:{1}, Column:{2} - {3}.", tile.Level, tile.Row, tile.Column, ex.Message));
return;
}
finally { client.Dispose(); }
});
}).ContinueWith(t =>
{
images.CompleteAdding();
if (verbose)
Console.WriteLine("All downloads complete.");
errorLog.Flush();
errorLog.Dispose();
});
int currentTileId = 1;
Action<TileImage[]> processBatch = (batch) =>
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
using (SQLiteTransaction transaction = connection.BeginTransaction())
{
using (SQLiteCommand mapCommand = connection.CreateCommand(),
imagesCommand = connection.CreateCommand(), tileCountCommand = connection.CreateCommand(),
mapDeleteCommand = connection.CreateCommand(),
imageDeleteCommand = connection.CreateCommand())
{
tileCountCommand.CommandText = "select ifnull(max(tile_id), 1) from images";
var tileCountObj = tileCountCommand.ExecuteScalar();
if (tileCountObj != null)
{
int.TryParse(tileCountObj.ToString(), out currentTileId);
currentTileId++;
}
//Create a dummy query for the [map] table and fill the adapter with it
//the purpose of this is to get the table structure in a DataTable
//the adapter also builds the insert command for it in when it is populated
mapCommand.CommandText = "SELECT * FROM [map] WHERE 1 = 2";
SQLiteDataAdapter mapAdapter = new SQLiteDataAdapter(mapCommand);
//Create a dummy query for the [images] table and fill the adapter with it
//the purpose of this is to get the table structure in a DataTable
//the adapter also builds the insert command for it in when it is populated
imagesCommand.CommandText = "SELECT * FROM [images] WHERE 1 = 2";
SQLiteDataAdapter imagesAdapter = new SQLiteDataAdapter(imagesCommand);
using (SQLiteCommandBuilder mapCmdBuilder = new SQLiteCommandBuilder(mapAdapter),
imagesCmdBuilder = new SQLiteCommandBuilder(imagesAdapter))
{
using (imagesAdapter.InsertCommand = (SQLiteCommand)((ICloneable)imagesCmdBuilder.GetInsertCommand()).Clone())
using (mapAdapter.InsertCommand = (SQLiteCommand)((ICloneable)mapCmdBuilder.GetInsertCommand()).Clone())
{
imagesCmdBuilder.DataAdapter = null;
mapCmdBuilder.DataAdapter = null;
using (DataTable mapTable = new DataTable(),
imagesTable = new DataTable())
{
if (batch.Any())
{
var conditions = batch.Select(b => string.Format("([zoom_level]={0} and [tile_row]={1} and [tile_column]={2})", b.Tile.Level, b.Tile.Row, b.Tile.Column));
var whereClause = string.Join("or", conditions);
var mapDeleteStatement = string.Format("delete from map where {0}", whereClause);
var imageDeleteStatement = string.Format("delete from images where [tile_id] in (select [tile_id] from map where {0})", whereClause);
imageDeleteCommand.CommandText = imageDeleteStatement;
imageDeleteCommand.ExecuteNonQuery();
mapDeleteCommand.CommandText = mapDeleteStatement;
mapDeleteCommand.ExecuteNonQuery();
}
imagesAdapter.Fill(imagesTable);
mapAdapter.Fill(mapTable);
//Dictionary to eliminate duplicate images within batch
Dictionary<string, int> added = new Dictionary<string, int>();
//looping thru keys is safe to do here because
//the Keys property of concurrentDictionary provides a snapshot of the keys
//while enumerating
//the TryGet & TryRemove inside the loop checks for items that were removed by another thread
List<int> tileIdsInCurrentBatch = new List<int>();
foreach (var tileimg in batch)
{
string hash = Convert.ToBase64String(new System.Security.Cryptography.MD5CryptoServiceProvider().ComputeHash(tileimg.Image));
int tileid = -1;
if (!added.ContainsKey(hash))
{
mapCommand.CommandText = "SELECT [tile_id] FROM [images] WHERE [tile_md5hash] = @hash";
mapCommand.Parameters.Add(new SQLiteParameter("hash", hash));
object tileObj = mapCommand.ExecuteScalar();
if (tileObj != null && int.TryParse(tileObj.ToString(), out tileid))
added.Add(hash, tileid);
else
{
tileid = currentTileId++;
added.Add(hash, tileid);
DataRow idr = imagesTable.NewRow();
idr["tile_md5hash"] = hash;
idr["tile_data"] = tileimg.Image;
idr["tile_id"] = added[hash];
imagesTable.Rows.Add(idr);
}
}
tileIdsInCurrentBatch.Add(added[hash]);
DataRow mdr = mapTable.NewRow();
mdr["zoom_level"] = tileimg.Tile.Level;
mdr["tile_column"] = tileimg.Tile.Column;
mdr["tile_row"] = tileimg.Tile.Row;
mdr["tile_id"] = added[hash];
mapTable.Rows.Add(mdr);
}//for loop thru images
tileIdsInCurrentBatch.Clear();
imagesAdapter.Update(imagesTable);
mapAdapter.Update(mapTable);
transaction.Commit();
if (verbose)
Console.WriteLine(String.Format("Saving an image batch of {0}.", batch.Length));
}//using for datatable
}//using for insert command
}//using for command builder
}//using for select command
}
}//using for connection
};
List<TileImage> tilebatch = new List<TileImage>();
foreach (var tileImage in images.GetConsumingEnumerable())
{
tilebatch.Add(tileImage);
if (tilebatch.Count < 50)
continue;
processBatch(tilebatch.ToArray());
tilebatch.Clear();
}
if (verbose)
Console.WriteLine("Saving remaining images that didn't fit into a batch.");
processBatch(tilebatch.ToArray());
tilebatch.Clear();
if (verbose)
Console.WriteLine("Creating Index on table [map] and Creating View [tiles].");
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
connection.Execute("CREATE UNIQUE INDEX IF NOT EXISTS map_index on map (zoom_level, tile_column, tile_row)");
connection.Execute("CREATE VIEW IF NOT EXISTS tiles as SELECT map.zoom_level as zoom_level, map.tile_column as tile_column, map.tile_row as tile_row, images.tile_data as tile_data FROM map JOIN images on images.tile_id = map.tile_id");
connection.Close();
}
Console.WriteLine("All Done !!!");
}
private void applyButton_Click(object sender, EventArgs e)
{
SQLiteConnection mCN = ConnectionManager.connection;
SQLiteDataAdapter mDA = modulesTableAdapter.Adapter;
SQLiteCommandBuilder mCB = new SQLiteCommandBuilder(mDA);
DataSet mDS = modulesDataSet;
DataSet dsChanges = new DataSet();
if (!mDS.HasChanges()) return;
dsChanges = mDS.GetChanges(DataRowState.Modified);
if (dsChanges != null)
{
mDA.UpdateCommand = mCB.GetUpdateCommand();
mDA.Update(dsChanges, "modules");
}
dsChanges = mDS.GetChanges(DataRowState.Added);
if (dsChanges != null)
{
mDA.InsertCommand = mCB.GetInsertCommand();
mDA.Update(dsChanges, "modules");
}
dsChanges = mDS.GetChanges(DataRowState.Deleted);
if (dsChanges != null)
{
mDA.DeleteCommand = mCB.GetDeleteCommand();
mDA.Update(dsChanges, "modules");
}
mDS.AcceptChanges();
UpdateModulesDropDown();
(treeView.Model as SlowTreeModel).Root.UpdateModulesFromDbRec();
treeView.Invalidate();
}
/// <summary>
/// Provides information to setup the data adapter by the connection of <see cref="System.Data.IDbConnection"/>.
/// </summary>
/// <param name="datasource"></param>
/// <param name="connection">The <see cref="System.Data.IDbConnection"/> represents an open connection to a data source.</param>
/// <param name="query">The <see cref="IAdfQuery"/> whose SQL information is to be retrieved.</param>
/// <returns>The requested <see cref="System.Data.IDbDataAdapter"/> information object.</returns>
public virtual IDbDataAdapter SetUpAdapter(DataSources datasource, IDbConnection connection, IAdfQuery query)
{
var da = (SQLiteDataAdapter) GetAdapter();
if (query != null)
{
da.TableMappings.Add("Table", query.LeadTable());
var selectCommand = (SQLiteCommand) GetCommand(datasource, connection, query);
da.SelectCommand = selectCommand;
// create command builder for a new command, so we can customize the insert command
var newda = new SQLiteDataAdapter(selectCommand);
// Create and associate a commandbuilder which can generate insert and update queries for the DataAdapter. This is a necessary step!
var commandBuilder = new SQLiteCommandBuilder(newda);
da.InsertCommand = commandBuilder.GetInsertCommand();
da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
da.UpdateCommand = commandBuilder.GetUpdateCommand();
da.DeleteCommand = commandBuilder.GetDeleteCommand();
}
return da;
}
/// <summary>
/// Pre-condition: The parameters contain the updated dataset and the table name respectively
/// Post-Condition: The updated dataset will be persisted in the database.
/// Description: This method will update the database based on
/// the updated records in the dataset for the specified table.
/// </summary>
/// <param name="DataSet_param"></param>
/// <param name="strTableName_param"></param>
public void saveData(DataSet pDataSet, string pStrTableName)
{
//Specify SELECT Statment for data adapter
string strSQL = "SELECT * FROM " + pStrTableName;
//Create an instance of the data adaopter
//OleDbDataAdapter dbDA = new OleDbDataAdapter(strSQL, _dbConn);
SQLiteDataAdapter dbDA = new SQLiteDataAdapter(strSQL, _dbConn);
try
{
//Set up the command builder - not suitable for large databases
//OleDbCommandBuilder dbBLD = new OleDbCommandBuilder(dbDA);
SQLiteCommandBuilder dbBLD = new SQLiteCommandBuilder(dbDA);
dbDA.InsertCommand = dbBLD.GetInsertCommand();
dbDA.UpdateCommand = dbBLD.GetUpdateCommand();
dbDA.DeleteCommand = dbBLD.GetDeleteCommand();
//Subscribe to the OleDbRowUpdateEventHandler
//dbDA.RowUpdated += new OleDbRowUpdatedEventHandler(dbDA_RowUpdated);
dbDA.RowUpdated += new EventHandler<RowUpdatedEventArgs>(dbDA_RowUpdated);
//Update the database using the 'Update' method of the data adapter object
if (_dbConn.State == ConnectionState.Closed) _dbConn.Open();
dbDA.Update(pDataSet, pStrTableName);
//Close the connection
_dbConn.Close();
//Refresh the dataset
pDataSet.Tables[pStrTableName].AcceptChanges();
}
catch
{
_dbConn.Close();
//MessageBox.Show(e.ToString());
}
}
//�������������ds.Tables[0] ����sTblName�����ݿ������
public static bool SqlBulkInsert(DataTable dt, string sTblName)
{
int affect = 0;
SQLiteConnection conn = null;
try
{
conn = new SQLiteConnection(connectionString);
SQLiteCommand myCommand = new SQLiteCommand("select * from " + sTblName, conn);
SQLiteDataAdapter myAdapter = new SQLiteDataAdapter(myCommand);
SQLiteCommandBuilder myCommandBuilder = new SQLiteCommandBuilder(myAdapter);
myAdapter.InsertCommand = myCommandBuilder.GetInsertCommand();
foreach (DataRow dr in dt.Rows)
{
if (dr.RowState != DataRowState.Added)
{
dr.SetAdded();
}
}
conn.Open();
affect = myAdapter.Update(dt);
conn.Close();
return true;
}
catch (Exception e)
{
if(conn != null) conn.Close();
sLastErr = e.Message;
}
return false;
}
private void ProjectManager_Load(object sender, EventArgs e)
{
connection = new SQLiteConnection("Data source=" + database.fileName);
command = new SQLiteCommand(connection);
if (table.ToLower().Equals("activity"))
command.CommandText = "SELECT ID," + fields + " FROM " + table + " WHERE ProjectID='" + projectID + "'";
else
command.CommandText = "SELECT ID," + fields + " FROM " + table;
dataAdapter = new SQLiteDataAdapter(command);
dataSet = new DataSet(this.table);
dataSet.Locale = System.Globalization.CultureInfo.InvariantCulture;
commandBuilder = new SQLiteCommandBuilder(dataAdapter);
commandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
dataAdapter.Fill(dataSet, this.table);
dataAdapter.InsertCommand = commandBuilder.GetInsertCommand();
Console.Out.WriteLine(commandBuilder.GetInsertCommand().CommandText);
dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand();
Console.Out.WriteLine(commandBuilder.GetDeleteCommand().CommandText);
dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
Console.Out.WriteLine(commandBuilder.GetUpdateCommand().CommandText);
dataGridView1.DataSource = dataSet.Tables[0];
dataGridView1.Columns["ID"].Visible = false;
if (table.ToLower().Equals("activity"))
{
dataGridView1.Columns["ProjectID"].Visible = false;
}
dataGridView1.Columns["Name"].HeaderText = localization.GetString("String23");
}
/// <summary>
/// JVUmaReadWriting処理
/// </summary>
public static void JVUmaReadWriting()
{
bool reading = true;
int nCount = 0;
object buffObj = new byte[0];
int buffsize = 110000;
string timeStamp;
string buffname;
DataTable umaDataTable = new DataTable();
DataTable raceDataTable = new DataTable();
DataTable raceUmaDataTable = new DataTable();
// 初期化時
if (JVRelayClass.Option == (int)JVRelayClass.eJVOpenFlag.SetupSkipDialog)
{
using (SQLiteCommand command = DbConn.CreateCommand())
{
command.CommandText = "DELETE FROM uma";
command.ExecuteNonQuery();
}
}
using (SQLiteCommand command = DbConn.CreateCommand())
{
command.CommandText = "SELECT * FROM uma";
using (SQLiteDataAdapter da = new SQLiteDataAdapter(command))
{
da.Fill(umaDataTable);
}
}
raceDataTable.Columns.Add("RaceKey", typeof(string));
raceDataTable.Columns.Add("RaceDate", typeof(string));
raceDataTable.Columns.Add("DataKubun", typeof(string));
raceDataTable.Columns.Add("GradeCD", typeof(string));
raceDataTable.Columns.Add("SyubetuCD", typeof(string));
raceUmaDataTable.Columns.Add("RaceKey", typeof(string));
raceUmaDataTable.Columns.Add("RaceDate", typeof(string));
raceUmaDataTable.Columns.Add("KettoNum", typeof(string));
raceUmaDataTable.Columns.Add("KakuteiJyuni", typeof(string));
if (umaDataTable.PrimaryKey.Length == 0)
{
umaDataTable.PrimaryKey = new[] { umaDataTable.Columns["KettoNum"] };
}
if (raceDataTable.PrimaryKey.Length == 0)
{
raceDataTable.PrimaryKey = new[] { raceDataTable.Columns["RaceKey"] };
}
if (raceUmaDataTable.PrimaryKey.Length == 0)
{
raceUmaDataTable.PrimaryKey = new[] { raceUmaDataTable.Columns["RaceKey"], raceUmaDataTable.Columns["KettoNum"] };
}
ProgressUserState.Maxinum = ReadCount;
ProgressUserState.Value = 0;
ProgressUserState.Text = "データ読み込み中...";
MainBackgroundWorker.ReportProgress(0, ProgressUserState);
do
{
//---------------------
// JVLink読込み処理
//---------------------
buffObj = new byte[0];
int nRet = AxJVLink.JVGets(ref buffObj, buffsize, out buffname);
timeStamp = AxJVLink.m_CurrentFileTimeStamp;
byte[] buff = (byte[])buffObj;
string buffStr = System.Text.Encoding.GetEncoding(932).GetString(buff);
// 正常
if (0 < nRet)
{
string recordSpec = JVData_Struct.MidB2S(ref buff, 1, 2);
buffObj = new byte[0];
buff = new byte[0];
switch (recordSpec)
{
case "UM":
{
JVData_Struct.JV_UM_UMA uma = new JVData_Struct.JV_UM_UMA();
uma.SetDataB(ref buffStr);
WriteDbUmaData(eOutput.Umanushi, uma, umaDataTable);
}
break;
case "RA":
{
JVData_Struct.JV_RA_RACE race = new JVData_Struct.JV_RA_RACE();
race.SetDataB(ref buffStr);
WriteDbRaceData(eOutput.Umanushi, race, raceDataTable);
}
break;
case "SE":
{
JVData_Struct.JV_SE_RACE_UMA raceUma = new JVData_Struct.JV_SE_RACE_UMA();
raceUma.SetDataB(ref buffStr);
WriteDbRaceUmaData(eOutput.Umanushi, raceUma, raceUmaDataTable);
}
break;
default:
// 対象外recspecのファイルをスキップする。
AxJVLink.JVSkip();
nCount++;
ProgressUserState.Value = nCount;
ProgressUserState.Text = "データ読み込み中...";
MainBackgroundWorker.ReportProgress(0, ProgressUserState);
break;
}
}
// ファイルの切れ目
else if (-1 == nRet)
{
nCount++;
ProgressUserState.Value = nCount;
ProgressUserState.Text = "データ読み込み中...";
MainBackgroundWorker.ReportProgress(0, ProgressUserState);
}
// 全レコード読込み終了(EOF)
else if (0 == nRet)
{
ProgressUserState.Value = ProgressUserState.Maxinum;
ProgressUserState.Text = "データ読み込み完了";
MainBackgroundWorker.ReportProgress(0, ProgressUserState);
reading = false;
}
// エラー
else if (-1 > nRet)
{
// エラーファイルをスキップする。
AxJVLink.JVSkip();
nCount++;
ProgressUserState.Value = nCount;
ProgressUserState.Text = "データ読み込み中...";
MainBackgroundWorker.ReportProgress(0, ProgressUserState);
}
System.Threading.Thread.Sleep(10);
}
while (true == reading);
// データ整備
if (raceUmaDataTable.Rows.Count > 0)
{
foreach (DataRow raceUmaDataRow in raceUmaDataTable.Select("", "RaceDate"))
{
DataRow raceDataRow = raceDataTable.Rows.Find(raceUmaDataRow["RaceKey"]);
DataRow umaDataRow = umaDataTable.Rows.Find(raceUmaDataRow["KettoNum"]);
if (raceDataRow != null && umaDataRow != null)
{
string raceDate = umaDataRow["RaceDate"].ToString();
if ("" == raceDate || 0 > raceDate.CompareTo(raceUmaDataRow["RaceDate"].ToString()))
{
if (raceDataRow["DataKubun"].ToString() != "9" && raceDataRow["DataKubun"].ToString() != "0")
{
// レースを追加
umaDataRow["BeforeUmaClass"] = umaDataRow["UmaClass"];
umaDataRow["BeforeRaceDate"] = umaDataRow["RaceDate"];
umaDataRow["BeforeRaceDataKubun"] = umaDataRow["RaceDataKubun"];
umaDataRow["BeforeRaceGradeCD"] = umaDataRow["RaceGradeCD"];
umaDataRow["BeforeRaceSyubetuCD"] = umaDataRow["RaceSyubetuCD"];
umaDataRow["BeforeRaceKakuteiJyuni"] = umaDataRow["RaceKakuteiJyuni"];
umaDataRow["RaceDate"] = raceUmaDataRow["RaceDate"];
umaDataRow["RaceDataKubun"] = raceDataRow["DataKubun"];
umaDataRow["RaceGradeCD"] = raceDataRow["GradeCD"];
umaDataRow["RaceSyubetuCD"] = raceDataRow["SyubetuCD"];
umaDataRow["RaceKakuteiJyuni"] = raceUmaDataRow["KakuteiJyuni"];
umaDataRow["UmaClass"] = GetUmaClass(umaDataRow);
}
}
else if (0 == raceDate.CompareTo(raceUmaDataRow["RaceDate"].ToString()))
{
if (raceDataRow["DataKubun"].ToString() == "9" || raceDataRow["DataKubun"].ToString() == "0")
{
// レース中止、データ削除のため戻す
umaDataRow["UmaClass"] = umaDataRow["BeforeUmaClass"];
umaDataRow["RaceDate"] = umaDataRow["BeforeRaceDate"];
umaDataRow["RaceDataKubun"] = umaDataRow["BeforeRaceDataKubun"];
umaDataRow["RaceGradeCD"] = umaDataRow["BeforeRaceGradeCD"];
umaDataRow["RaceSyubetuCD"] = umaDataRow["BeforeRaceSyubetuCD"];
umaDataRow["RaceKakuteiJyuni"] = umaDataRow["BeforeRaceKakuteiJyuni"];
umaDataRow["BeforeUmaClass"] = null;
umaDataRow["BeforeRaceDate"] = null;
umaDataRow["BeforeRaceDataKubun"] = null;
umaDataRow["BeforeRaceGradeCD"] = null;
umaDataRow["BeforeRaceSyubetuCD"] = null;
umaDataRow["BeforeRaceKakuteiJyuni"] = null;
}
else
{
// レース結果の更新
if (umaDataRow["RaceDataKubun"].ToString() != raceDataRow["DataKubun"].ToString())
{
umaDataRow["RaceDataKubun"] = raceDataRow["DataKubun"];
}
if (umaDataRow["RaceGradeCD"].ToString() != raceDataRow["GradeCD"].ToString())
{
umaDataRow["RaceGradeCD"] = raceDataRow["GradeCD"];
}
if (umaDataRow["RaceSyubetuCD"].ToString() != raceDataRow["SyubetuCD"].ToString())
{
umaDataRow["RaceSyubetuCD"] = raceDataRow["SyubetuCD"];
}
if (umaDataRow["RaceKakuteiJyuni"].ToString() != raceUmaDataRow["KakuteiJyuni"].ToString())
{
umaDataRow["RaceKakuteiJyuni"] = raceUmaDataRow["KakuteiJyuni"];
}
if (umaDataRow["UmaClass"].ToString() != GetUmaClass(umaDataRow))
{
umaDataRow["UmaClass"] = GetUmaClass(umaDataRow);
}
}
}
}
}
}
// データ更新
using (SQLiteTransaction tran = DbConn.BeginTransaction())
{
using (SQLiteCommand command = DbConn.CreateCommand())
{
command.Transaction = tran;
command.CommandText = "SELECT * FROM uma";
using (SQLiteDataAdapter da = new SQLiteDataAdapter(command))
using (SQLiteCommandBuilder cb = new SQLiteCommandBuilder(da))
{
cb.SetAllValues = false;
cb.ConflictOption = ConflictOption.OverwriteChanges;
da.UpdateCommand = cb.GetUpdateCommand();
da.InsertCommand = cb.GetInsertCommand();
da.DeleteCommand = cb.GetDeleteCommand();
da.Update(umaDataTable);
}
command.CommandText = "DELETE FROM uma WHERE BirthYear <= '" + DiscardBirthYear + "'";
command.ExecuteNonQuery();
command.CommandText = "UPDATE timestamp SET date ='" + LastFileTimestamp + "'";
command.ExecuteNonQuery();
}
tran.Commit();
JVRelayClass.DbTimeStamp = LastFileTimestamp;
}
}
