C#でADO.NETでCRUD
この回はC#の講座でデータベースをやってみようとしたらいい教科書が見当たらず、困ったあげくに秀和システムの「作って覚える Visual C# 2008 Express Edition入門」の中の最後にCSVを扱う部分があったので、それを書き換えてSQL Serverに接続してCRUDする流れをやった時の資料です。
.net frameworkのData Setを使うプログラム
C#の.net frameworkでのCRUD
Visual Basicでしばらく飯を食ってきたので残念なのですが、Visual Basicはこれ以上UPGradeしないことがアナウンスされました。Visual Basic 言語の戦略についての説明。そこでC#で昔通りのADO.NETでのデータベースアクセスをやってみます。
ADO.NETとは
MicrosoftはいまやADO.NETを推奨していません。Entity FrameWorkにしなさいといいます。しかしまだ慣れないのでいつもの流れのADO.NETでやってみたいと思います。
ADO.NETは.net frameworkでしか動きませんので、開始するプロジェクトを「Windowsフォームアプリケーション(.NET Framework)」でプロジェクトを作ってください。「Windowsフォームアプリ」では作成できませんので注意してください。
ADO.NETはDataSetというローカルのメモリーにオフラインでデータベース格納場所を作って、そことのデータのやり取りを行ういろいろなクラスを提供してプログラムとデータのやり取りをする部分を分ける試みです。
Form(DataGridViewなどのコントロール) <-> DataSet(DataTable)<->DataBase(SQL Server等)
上のように 画面<->メモリー中のデータ<->データベース とデータがつながっています
画面とDataSetの間のデータのやり取りは「BindingNavigator(DataSetを操作するコントロール)」と「BindingSource(DataSetの中のテーブルとの結合)」で行っています。
またDataSetとデータベースのデータのやり取りは「TableAdapterManager(TableAdapterの操作を行う)」と「TableAdapter(データベースのテーブルに対応するDataSet内のDataTableとのやり取り)」で組みあがっています。
ただ命令は簡単で、ただ呼び出せはあとはやってくれる感じなので、文字で見たほど難しくありません
またコーディングも、自動でコーディングされるので、ウイザードに答えていけば出来上がります。前の章でやっているのと大差ありません。
SQL Serverに作るデータ
これもVisual Basicのサンプルで使用したデータを使います。ただし、SQL Serverに入れますので以下のようなテーブルを作ってください
//IDは自動増番にする
create table moneyTable (
ID int IDENTITY (1,1),
日付 date not null,
分類 varchar(30) not null,
品名 varchar(30),
金額 int,
備考 varchar(50),
primary key(ID) );
create table categoryTable (
分類 varchar(10) not null,
入出金分類 varchar(10),
primary key(分類));
SQL Serverの設定とテーブルの作り方はC#とデータベースにありますので参考にしてください。
リレーションでつなぐテーブルも用意します
データセットの作成
データベースにテーブルを作ったら、メモリー中にもその受け皿のデータセットを作ります
ソリューション エクスプローラーのプロジェクト名上で右クリックして追加でクラスを選びます。
クラス等のアイコン一覧がでるので、ここでDataSetを選びます
名前を「MoneyDataSet.xsd」とします。このデータセット中にデータテーブルを作ります。
自動で作る方法もありますが、余分なものまで作るので今回は手作りします
列を挿入してテーブルと同じものを作っていきます。Typeは右下のプロパティで設定します。
| フィールド名 | 型 | メモ |
|---|---|---|
| ID | System.Int32 | 主キー |
| 日付 | System.DateTime | |
| 分類 | System.String | |
| 品名 | System.String | |
| 金額 | System.Int32 | |
| 備考 | System.String |
同様に「CategoryDataSet」中に「CategoryTable」を作ります
| フィールド名 | 型 |
|---|---|
| 分類 | System.String |
| 入出金分類 | System.String |
同様に「SummaryDataSet」中に「SumDataTable」を作ります
| フィールド名 | 型 |
|---|---|
| 日付 | System.DateTime |
| 入金合計 | System.Int32 |
| 出金合計 | System.Int32 |
で、データセットができ上ったら、かならずここで「ソリューションのビルド」しておきましょう。
フォームの作成
C#のフォームを作成します。Form1のソースファイル名ですが、nameはformViewに変更しておきます。
分かりづらいので githubにソースを入れておきます
MyHousekeepingSQLC#のMyHousekeepingSQL配下のMyHousekeepingBookのForm1.Designer.csにあります。
コンテナーのTabControlを貼り付け、一つは「一覧表示」一つは「集計表」とします。まだDatagridViewは貼り付けていません。後からやります
ボタンを4つ張ります。追加(buttonAdd)、変更(buttonChange)、削除()buttonDelete、終了(buttonEnd)です。メニューは必須ではありません。
そしてもう一つフォームを追加します。ソリューションエクスプローラーから追加で「ItemForm.cs」を追加します。
MonthCalendar(monCalendar)、CombBox(cmbbCategory-DropDownList)、Textboxが２つ(txtItem,txtRemarks)と金額はMaskedTextBox(mtxtMoney-999999-Right)とボタンが２つ、登録(buttonOK)とキャンセル(buttonCancel)です
登録は「DialogResult」をOK、キャンセルは「DailogResult」をCancelにします。
また、ボタン以外はすべて「Modifiers」を「Public」にして、一覧画面から見えるようにしておきます
データテーブルとのBind
ではいよいよ、バインドです。さきほど作ったデータテーブルとフォーム上のコントロールをバインドして同期させます。
Tabで作った一覧表を開き、そこにツールボックスからDataGridViewを貼り付けます。大きさを調整してください。
そして、DataGRidViewの右肩にある小さな矢印をクリックして画面デザイナーを開きます。そしてデータソースのツリーを展開して「他のデータソース」->「プロジェクトデータソース」->「MoneyDataSet」->「moneyDataTable」を選択します。これで先ほど作ったフィールド名が画面に表示されると思います。ならない時は修正してください。
これで自動的にmoneyDataTableBindingSourceが作成されます
つづいて、ItemFormを開きます。
ItemFormのコンボボックスの右肩の矢印をクリックして画面デザイナを表示します。そしてデータソースに「他のデータソース」->「プロジェクトデータソース」->「CategoryDataSet」->「CategoryDataTable」を選択します。
これで自動的に、categoryDataTableBindingSourceが作成されます
Form1のデータ読み込みプログラミング
最初にプログラムするのはDataGridViewに表示する部分です。とはいっても、DataGridViewに直接読み込むことはしません。さきほどDatTableとDataGridViewをBindしました。なのでデータベースからDataTableにデータを読み込むと自動的に画面に表示されるようになっています。なので、SQL Serverに接続して、DataTableに読み込みを行う部分を作成していきましょう。
Formの名前はformViewなので、初期化処理は formView_Loadで行うことになります。Form1のデザインの表示をしてフォームのタイトルバーをダブルクリックするとformView_Loadが開きます。
で、moneyDataTableの読み込みは何度もすることになるのでLoadData()というサブルーチンを作っておいて、なんでも呼べるようにしておきましょう。なのでまずはformView_LoadではLoadData()をコールするだけにしておきます。
LoadData()でSQL Serverから読み込むにはまずSQL Serverに接続しないといけません。まずはその接続子を記述しておきます。
using System.Data.SqlClient;
namespace MyHousekeepingBook
{
public partial class formView : Form
{
public string connString = "Data Source=localhost\\SQLEXPRESS;" +
"Initial Catalog=mydb;" +
"Integrated Security=SSPI;" +
"TrustServerCertificate=true;";
public formView()
{
InitializeComponent();
}
まず usingで System.Data.SqlClient;を宣言してSQL呼び出しの準備をします。formViewのクラスの先頭に connString という名前で宣言します。localhostはパソコン自分自身のことで、ここでは無料版のSQLServerに接続するので SQLEXPRESSにつなぎます。\はエスケープするため２つ書きます
接続するデータベース名はMyDbですが、これは変更できます。そしてサーバーを信頼して接続するのでID,PASSはありません。
private void formView_Load(object sender, EventArgs e)
{
LoadData();
}
LoadData()で先の接続子を作ってSQLServerにつないで、DataReaderで１行づつ読み込み、DataTableに追加すればよい
private void LoadData()
{
string sqlStr = "Select * from moneyTable";
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand(sqlStr, conn);
conn.Open();
//一度データを消して再読み込みする
moneyDataSet.moneyDataTable.Clear();
using (SqlDataReader dr = cmd.ExecuteReader())
{
DataRow drow;
while (dr.Read())
{
drow = moneyDataSet.moneyDataTable.NewRow();
for(int i = 0;i<dr.FieldCount;i++)
{
drow[i] = dr[i];
}
moneyDataSet.moneyDataTable.Rows.Add(drow);
}
}
}
}
SqlConnectionを使ってSQLServerと接続します。そしてmoneyTableへSelect文を投げます。テーブルの１行を表すDataRowを準備してSqlDataReaderから１行づつ読み込んでDataTableに入れていきます
DataTableに入れればDataGridViewと同期しているので画面に出てきます。
追加はItemFormを使ってデータ入力
追加ボタンが押されたらItemFormの画面を使用してデータの追加を行います
追加ボタンが押されたらbuttonAddを呼び出します
private void buttonAdd_Click(object sender, EventArgs e)
{
AddData();
}
private void AddData()
{
//カテゴリーデータをコンボボックスに渡す
ItemForm frmItem = new ItemForm(categoryDataSet1);
DialogResult drRet = frmItem.ShowDialog();
if (drRet == DialogResult.OK)
{
DateTime dt =
frmItem.monCalendar.SelectionRange.Start;
string br = frmItem.cmbCategory.Text;
string hm = frmItem.txtItem.Text;
int kg = int.Parse(frmItem.mtxtMoney.Text);
string bk = frmItem.txtRemarks.Text;
//SQLでSQLserverに追加する
SaveData(dt, br, hm, kg, bk);
//再表示する
LoadData();
}
}
ItemFormを呼び出せば良いのですが、実は問題があります。ItemFormのコンボボックスでcategoryDataTableを使いますが、ItemFormを使うたびにcategoryを呼び出していたら時間がもったいないので、mainであるForm1で初めに読んでおいて渡してあげた方が効率がよさそうです。
そこでitemFormのコンストラクターにcategoryDataを追加しておいて、渡すようにします。すると渡すための入れ物が必要になるので、Form1のデザインを開いておいて左上のコンポーネントからCategoryDataSetをDataGridViewの上にドロップします
すると入れ物であるcategoryDataSet1がForm1側にも作成されるので、これに先ほどの_load()のタイミングで読み込んでおいて、ItemFormを呼ぶときに渡してあげます。
private void formView_Load(object sender, EventArgs e)
{
//カテゴリーデータを最初に一度だけ読み込む
string strSql = "select * from categoryTable;";
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand(strSql, conn);
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
DataRow drow;
while (dr.Read())
{
drow = categoryDataSet1.CategoryDataTable.NewRow();
for (int i = 0; i < dr.FieldCount; i++)
{
drow[i] = dr[i];
}
categoryDataSet1.CategoryDataTable.Rows.Add(drow);
}
}
}
//moneyTableを読んで再表示する
LoadData();
なので、ItemFormのコンストラクターを書き換えておきます。
namespace MyHousekeepingBook
{
public partial class ItemForm : Form
{
public ItemForm(CategoryDataSet dsCategory)
{
InitializeComponent();
categoryDataSet.Merge(dsCategory);
this.Text = "新規";
}
コンストラクターでcategoryのデータを受けたらItemForm側のcategoryDataSetにマージします。これで親で１度読み込んだcategoryを流用できます。
AddData()は新規データの作成ですからItemFormではまっさらの状態で表示し、コンボボックスにはSQLServerのカテゴリが表示されます。
そして、登録ボタンが押されると、親から呼び出されたダイアログボックスからOKで戻ってきます。ItemFormの入力データはMidifiersがpublicにしてありますので、親にコントロールが戻ってきていても読み取ることができます。なのでfrmItem.monCalendar.SelectionRange.Startでカレンダーの設定日などを読むことができます
このようにItemFormからデータを読み込んだら SaveData()を読んでSQLServerにInsert文を発行します
private void SaveData(DateTime dt,string br, string hm, int kg, string bk)
{
//auto incrementなのでIDは書かない
string strSql = "INSERT INTO [moneyTable] ([日付],[分類],[品名],[金額],[備考]) VALUES (@日付, @分類, @品名,@金額,@備考)";
using (SqlConnection conn = new SqlConnection(connString))
{
try
{
SqlCommand cmd = new SqlCommand(strSql, conn);
conn.Open();
cmd.CommandType = CommandType.Text;
//strSQLの@の部分にパラメータを展開する
cmd.Parameters.Clear();
SqlParameter param = cmd.CreateParameter();
param = cmd.CreateParameter();
param.ParameterName = "@日付";
param.SqlDbType = SqlDbType.Date;
param.Direction = ParameterDirection.Input;
param.Value = dt;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.ParameterName = "@分類";
param.SqlDbType = SqlDbType.VarChar;
param.Size = 30;
param.Direction = ParameterDirection.Input;
param.Value = br;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.ParameterName = "@品名";
param.SqlDbType = SqlDbType.VarChar;
param.Size = 30;
param.Direction = ParameterDirection.Input;
param.Value = hm;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.ParameterName = "@金額";
param.SqlDbType = SqlDbType.Int;
param.Direction = ParameterDirection.Input;
param.Value = kg;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.ParameterName = "@備考";
param.SqlDbType = SqlDbType.VarChar;
param.Direction = ParameterDirection.Input;
param.Size = 30;
param.Value = bk;
cmd.Parameters.Add(param);
//リターンのないSQLを実行する
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("書き込み失敗");
}
}
}
LoadDataと同じようにそのたびごとにコネクションからやり直します。SqlcommandにInsert文を宣言しておき、Parameterで各データをストアします。最後にExecuteNonQuery()で書き込みます。
SaveDataをすればデータベースが書き換わっていますのでまたLoadDataをします。
編集もItemFormを使ってデータ編集
追加の流れと同様に編集も行います。違いは追加はまっさらですが、編集は現在の行のデータを持ってItemDataに渡り、そこでの編集結果を書き戻すことです。
編集ボタンが押されたらbuttonChangeを呼び出します
private void buttonChange_Click(object sender, EventArgs e)
{
UpdateData();
}
private void UpdateData()
{
//データグリッドビューのデータを変更する
int nowRow = dgv.CurrentRow.Index;
//oldIDを取っておいて、書き戻す
int oldID = int.Parse(dgv.Rows[nowRow].Cells[0].Value.ToString());
DateTime oldDate =
DateTime.Parse(dgv.Rows[nowRow].Cells[1].Value.ToString());
string oldCategory = dgv.Rows[nowRow].Cells[2].Value.ToString();
string oldItem = dgv.Rows[nowRow].Cells[3].Value.ToString();
int oldMoney = int.Parse(dgv.Rows[nowRow].Cells[4].Value.ToString());
string oldRemarks = dgv.Rows[nowRow].Cells[5].Value.ToString();
ItemForm frmItem = new ItemForm(categoryDataSet1,
oldDate,
oldCategory,
oldItem,
oldMoney,
oldRemarks);
DialogResult drRet = frmItem.ShowDialog();
//OKならば
if (drRet == DialogResult.OK)
{
DateTime dt =
frmItem.monCalendar.SelectionRange.Start;
string br = frmItem.cmbCategory.Text;
string hm = frmItem.txtItem.Text;
int kg = int.Parse(frmItem.mtxtMoney.Text);
string bk = frmItem.txtRemarks.Text;
//同じIDのところにUPDATEで書き戻す
UpData(oldID, dt, br, hm, kg, bk);
//再読み込みをして書き直す
LoadData();
}
}
編集の場合もItemFormを呼びますが、今回は現在選択されている行のデータを持ってItemFormを呼びます。なのでまずは、DataGRidViewの現在の行を見て、データを取得します
編集なのでIDは保持しておいて上書きします。
ItemFormのコンストラクターを編集用に追加します。
public ItemForm(CategoryDataSet dsCategory,
DateTime nowDate,
string category,
string item,
int money,
string remarks)
{
InitializeComponent();
categoryDataSet.Merge(dsCategory);
monCalendar.SetDate(nowDate); //カレンダー
cmbCategory.Text = category; //カテゴリー
txtItem.Text = item; //品名
mtxtMoney.Text = money.ToString();//金額
txtRemarks.Text = remarks; //備考
this.Text = "変更";
}
追加ではSaveDataでInsertしましたが、今回はUPdateでupdate文を使います
private void UpData(int id, DateTime dt, string br, string hm, int kg, string bk)
{
string strSql = "UPDATE [moneyTable] SET [日付]=@日付,[分類]=@分類,[品名]=@品名,[金額]=@金額,[備考]=@備考 WHERE [ID]=@ID";
using (SqlConnection conn = new SqlConnection(connString))
{
try
{
SqlCommand cmd = new SqlCommand(strSql, conn);
conn.Open();
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
SqlParameter param = cmd.CreateParameter();
param = cmd.CreateParameter();
param.ParameterName = "@日付";
param.SqlDbType = SqlDbType.Date;
param.Direction = ParameterDirection.Input;
param.Value = dt;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.ParameterName = "@分類";
param.SqlDbType = SqlDbType.VarChar;
param.Size = 30;
param.Direction = ParameterDirection.Input;
param.Value = br;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.ParameterName = "@品名";
param.SqlDbType = SqlDbType.VarChar;
param.Size = 30;
param.Direction = ParameterDirection.Input;
param.Value = hm;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.ParameterName = "@金額";
param.SqlDbType = SqlDbType.Int;
param.Direction = ParameterDirection.Input;
param.Value = kg;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.ParameterName = "@備考";
param.SqlDbType = SqlDbType.VarChar;
param.Direction = ParameterDirection.Input;
param.Size = 30;
param.Value = bk;
cmd.Parameters.Add(param);
//primary key
param = cmd.CreateParameter();
param.ParameterName = "@ID";
param.SqlDbType = SqlDbType.Int;
param.Direction = ParameterDirection.Input;
param.Value = id;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("変更失敗");
}
}
}
UpdateではIDは変わらないのでUpdate文で上書きします。
削除は行のIDでDelete
編集ボタンが押されたらbuttonChangeを呼び出します
private void buttonDelete_Click(object sender, EventArgs e)
{
DeleteData();
}
private void DeleteData()
{
//現在行のIDを取得して削除する
int nowRow = dgv.CurrentRow.Index;
int ID = int.Parse(dgv.Rows[nowRow].Cells[0].Value.ToString());
string strSql = "DELETE FROM [moneyTable] WHERE [ID]=@ID";
using (SqlConnection conn = new SqlConnection(connString))
{
try
{
SqlCommand cmd = new SqlCommand(strSql, conn);
conn.Open();
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
SqlParameter param = cmd.CreateParameter();
//primary key
param = cmd.CreateParameter();
param.ParameterName = "@ID";
param.SqlDbType = SqlDbType.Int;
param.Direction = ParameterDirection.Input;
param.Value = ID;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
LoadData();
}
catch (Exception ex)
{
MessageBox.Show("削除失敗");
}
}
}
現在行からIDを取得してDelete文で削除します。データベースが変わるのでLoadData()をします。
実は集計などがまだあるのですが、データベースの主題から外れるのでここまでにします。GitHubには全ソースが入っていますので、DataGridViewを張って、データソースにSumDataTableを選んで集計ができますので、やれる方はやってみてください