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();
}
/// <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;
}
//ͨ�������ݺ���
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);
}
}
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());
}
}
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;
}
}
