いろんなソフトのデータの保存方法についてシリアライズ化して保存したXmlファイルをまとめて
Zipファイルにすることで対応しようとしていたが、複数人で触ることを考えてsqliteのほうがトラブルが少なそうだったので
データの保存の関連を修正した。
データの取得については汎用性を持たせたかったので、DataSet、DataTableで取得するようにしておいた。
データを取得するときにインテリセンスが効かないので微妙な気もするが、
SQL側で集計したり、分析した結果を表示したりして列名を色々作ることが多そうなのでDataTableを利用したほうが融通が利く気がする。
融通を聞かせすぎると統一感がなくなるけど、どうせそこまで統一したシステムなんて組めないからいいんじゃないかな・・・。
インジェクション対策としてパラメータも利用できるようにしてみた。
sqlite以外でもコネクション周りを修正すれば利用できるDataBaseAccessクラスを作成した。
SQL Serverの場合であればSQLiteConnectionStringBuilderなどを軒並みSQLConnectionに変更すれば対応できる、気がする。
※SQL文の書き方が違うのでそのあたりは頑張っていただくしかないが・・・。
どこかの機会に遅いSQLを早くするコツも書いてみたいと思う。いつになるかわからないけど。
そのときにサンプルプログラムを作成し、調査した内容をメモしておく。
sqliteをNuGetパッケージの管理からインストールし、参照の追加を行う
下記の using を追加する。
using System.Data;
using System.Data.SQLite;
↓↓↓↓↓ サンプルソース ↓↓↓↓↓
private void btnSample_Click(object sender, RoutedEventArgs e)
{
string path = @"C:\test\SampleDataBase.db";
var sqlConnectionSb = new SQLiteConnectionStringBuilder { DataSource = path };
// ▽▽▽ データベース作成 ▽▽▽
DataBaseAccess dataBaseAccess = new DataBaseAccess();
dataBaseAccess.CreateDataBase_NewFile(path);
// △△△ データベース作成 △△△
// ▽▽▽ データ登録処理 ▽▽▽
List<DataBaseAccess.ExecutionSQL> lstInsUpd = new List<DataBaseAccess.ExecutionSQL>();
// データ登録（ファイル実体）
DataBaseAccess.ExecutionSQL InsSqlFile = new DataBaseAccess.ExecutionSQL(DataBaseAccess.ExecutionSQL.SqlType.INSERT);
InsSqlFile.ExecuteSQL = " INSERT INTO T_File(intProjectNo,intCardNo,txtCommentNo,intFileNo,bloFile) "
+ " VALUES(@intProjectNo , @intCardNo , @txtCommentNo , @intFileNo , @bloFile) ";
InsSqlFile.Parameters.Add(new DataBaseAccess.SQLPara("@intProjectNo", "1"));
InsSqlFile.Parameters.Add(new DataBaseAccess.SQLPara("@intCardNo", "1"));
InsSqlFile.Parameters.Add(new DataBaseAccess.SQLPara("@txtCommentNo", "0"));
InsSqlFile.Parameters.Add(new DataBaseAccess.SQLPara("@intFileNo", "1"));
// ファイルをByte型配列で読み込み
var data = System.IO.File.ReadAllBytes(@"C:\test\sample.txt");
InsSqlFile.Parameters.Add(new DataBaseAccess.SQLPara("@bloFile", data));
lstInsUpd.Add(InsSqlFile);
// データ登録（ファイル情報）
DataBaseAccess.ExecutionSQL InsSqlFileInfo = new DataBaseAccess.ExecutionSQL(DataBaseAccess.ExecutionSQL.SqlType.INSERT);
InsSqlFileInfo.ExecuteSQL = " INSERT INTO T_FileInfo(intProjectNo,intCardNo,txtCommentNo,intFileNo,txtFileName) "
+ " VALUES(@intProjectNo , @intCardNo , @txtCommentNo , @intFileNo , @txtFileName) ";
InsSqlFileInfo.Parameters.Add(new DataBaseAccess.SQLPara("@intProjectNo", "1"));
InsSqlFileInfo.Parameters.Add(new DataBaseAccess.SQLPara("@intCardNo", "1"));
InsSqlFileInfo.Parameters.Add(new DataBaseAccess.SQLPara("@txtCommentNo", "0"));
InsSqlFileInfo.Parameters.Add(new DataBaseAccess.SQLPara("@intFileNo", "1"));
InsSqlFileInfo.Parameters.Add(new DataBaseAccess.SQLPara("@txtFileName", "sample.txt"));
lstInsUpd.Add(InsSqlFileInfo);
// データ登録実行
dataBaseAccess.ExecuteSQL_InsUpdDel(path, lstInsUpd);
// △△△ データ登録処理 △△△
// ▽▽▽ データ取得処理 ▽▽▽
List<DataBaseAccess.ExecutionSQL> lstSelect = new List<DataBaseAccess.ExecutionSQL>();
// データを取得するためのSQL（ファイル実体）
DataBaseAccess.ExecutionSQL SelSqlFile = new DataBaseAccess.ExecutionSQL(DataBaseAccess.ExecutionSQL.SqlType.SELECT);
SelSqlFile.TableName = "T_File";
SelSqlFile.ExecuteSQL = " SELECT * FROM T_File ";
lstSelect.Add(SelSqlFile);
// データを取得するためのSQL（ファイル情報）
DataBaseAccess.ExecutionSQL SelSqlFileInfo = new DataBaseAccess.ExecutionSQL(DataBaseAccess.ExecutionSQL.SqlType.SELECT);
SelSqlFileInfo.TableName = "T_FileInfo";
SelSqlFileInfo.ExecuteSQL = " SELECT * FROM T_FileInfo WHERE intProjectNo = @intProjectNo ";
SelSqlFileInfo.Parameters.Add(new DataBaseAccess.SQLPara("@intProjectNo", "1"));
lstSelect.Add(SelSqlFileInfo);
// SQLを実行し、結果をDataSetで取得する
DataSet ds = dataBaseAccess.GetDatas(path, lstSelect);
// 取得したファイルを別名で出力する
System.IO.File.WriteAllBytes(@"C:\test\output.txt", (byte[])ds.Tables["T_File"].Rows[0]["bloFile"]);
// △△△ データ登録処理 △△△
}
/// <summary>
/// データベースアクセス関連
/// </summary>
public class DataBaseAccess
{
#region Public Method
/// <summary>
/// 新規ファイル作成時のSQL
/// </summary>
/// <param name="strFilePath"></param>
public void CreateDataBase_NewFile(string strFilePath)
{
ExecuteSQL_DDL(strFilePath, _GetCreateTableSQL_V0100());
}
/// <summary>
/// SQL実行処理
/// </summary>
/// <param name="strFilePath"></param>
/// <param name="ExecutionSQL"></param>
/// <returns></returns>
public DataSet GetDatas(string strFilePath, ExecutionSQL ExecutionSQL)
{
List<ExecutionSQL> lst = new List<ExecutionSQL>();
lst.Add(ExecutionSQL);
return GetDatas(strFilePath, lst);
}
/// <summary>
/// SQL実行処理
/// </summary>
public DataSet GetDatas(string strFilePath, List<ExecutionSQL> ExecutionSQLs)
{
var sqlConnectionSb = new SQLiteConnectionStringBuilder { DataSource = strFilePath };
DataSet dataSet = new DataSet();
try
{
using (var cn = new SQLiteConnection(sqlConnectionSb.ToString()))
{
cn.Open();
using (var cmd = new SQLiteCommand(cn))
{
foreach (var param in ExecutionSQLs)
{
// ↓↓↓ チェック ↓↓↓
if (param.Type != ExecutionSQL.SqlType.SELECT) { throw new Exception("GetDatasメソッドはSELECT構文のみが実行可能です。SqlTypeを確認してください。"); }
// ↑↑↑ チェック ↑↑↑
cmd.CommandText = param.ExecuteSQL;
// SQLパラメータを設定
cmd.Parameters.Clear();
foreach (var item in param.Parameters) { cmd.Parameters.Add(new SQLiteParameter(item.Name, item.Value)); }
// SQLを実行し、データを取得する
using (var reader = cmd.ExecuteReader())
{
DataTable dt = new DataTable(param.TableName);
DataRow dr;
for (int i = 0; i < reader.FieldCount; i++)
{
dt.Columns.Add(new DataColumn(reader.GetName(i), reader.GetFieldType(i)));
}
while (reader.Read() == true)
{
dr = dt.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
{
dr[i] = reader[i];
}
dt.Rows.Add(dr);
}
dataSet.Tables.Add(dt);
reader.Close();
}
}
}
}
}
catch (Exception)
{
throw;
}
return dataSet;
}
/// <summary>
/// SQL実行時の設定内容
/// </summary>
public class ExecutionSQL
{
public ExecutionSQL(SqlType sqlType)
{
this.Parameters = new List<SQLPara>();
this.Type = sqlType;
}
public enum SqlType
{
INSERT, UPDATE, DELETE, SELECT
}
/// <summary>
/// SELECT 構文の時には必須
/// INSERT,UPDATE,DELETE 構文の時には不要
/// </summary>
public string TableName;
/// <summary>
/// 実行するSQL文
/// </summary>
public string ExecuteSQL;
/// <summary>
/// SQLのパラメータ
/// </summary>
public List<SQLPara> Parameters;
public SqlType Type;
}
/// <summary>
/// SQLパラメータ
/// </summary>
public class SQLPara
{
public SQLPara(string name, object value)
{
this.Name = name;
this.Value = value;
}
public string Name;
public object Value;
}
/// <summary>
/// 登録・更新・削除のSQLを実行するためのメソッド
/// </summary>
public void ExecuteSQL_InsUpdDel(string strFilePath, List<ExecutionSQL> ExecutionSQLs)
{
var sqlConnectionSb = new SQLiteConnectionStringBuilder { DataSource = strFilePath };
try
{
using (var cn = new SQLiteConnection(sqlConnectionSb.ToString()))
{
cn.Open();
using (var cmd = new SQLiteCommand(cn))
{
using (SQLiteTransaction trans = cn.BeginTransaction())
{
foreach (var param in ExecutionSQLs)
{
// ↓↓↓ チェック ↓↓↓
if (param.Type == ExecutionSQL.SqlType.SELECT) { throw new Exception("ExecuteSQL_InsUpdDelメソッドはSELECT構文以外のみが実行可能です。SqlTypeを確認してください。"); }
// ↑↑↑ チェック ↑↑↑
cmd.CommandText = param.ExecuteSQL;
// SQLパラメータを設定
cmd.Parameters.Clear();
foreach (var item in param.Parameters) { cmd.Parameters.Add(new SQLiteParameter(item.Name, item.Value)); }
cmd.ExecuteNonQuery();
}
trans.Commit();
}
}
}
}
catch (Exception)
{
throw;
}
}
#endregion
#region Private Method 1st.
/// <summary>
/// DDL構文（Create、Drop）系列のSQLを実行する
/// </summary>
/// <param name="strFilePath">ファイルパス</param>
/// <param name="strSQL">実行SQL</param>
private void ExecuteSQL_DDL(string strFilePath, string strSQL)
{
var sqlConnectionSb = new SQLiteConnectionStringBuilder { DataSource = strFilePath };
using (var cn = new SQLiteConnection(sqlConnectionSb.ToString()))
{
cn.Open();
using (var cmd = new SQLiteCommand(cn))
{
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
}
}
}
#region Private Method 2st.
/// <summary>
/// テーブル作成処理（バージョン01.00）
/// </summary>
/// <returns></returns>
private string _GetCreateTableSQL_V0100()
{
string strSQL = "";
strSQL = " create table T_File ( "
+ " intProjectNo integer "
+ " , intCardNo integer"
+ " , txtCommentNo TEXT "
+ " , intFileNo integer"
+ " , bloFile BLOB "
+ " , constraint T_File_PKC primary key (intProjectNo,intCardNo,txtCommentNo,intFileNo) "
+ " ); "
+ " "
+ " create table T_FileInfo ( "
+ " intProjectNo integer"
+ " , intCardNo integer"
+ " , txtCommentNo TEXT "
+ " , intFileNo integer"
+ " , txtFileName TEXT "
+ " , constraint T_FileInfo_PKC primary key (intProjectNo,intCardNo,txtCommentNo,intFileNo) "
+ " ); ";
return strSQL;
}
#endregion
#endregion
}
↑↑↑↑↑ サンプルソース ↑↑↑↑↑
