SQLの窓

2017年09月28日


jQuery を使用してサーバから取得した JSON データを元にテーブルを作成し、その後から列を加工する

HTML 部分は、タイトル部分と thead のみ作成し、JSON データを取り出す毎に作り直すようになっています。

$.each でループ処理している対象のデータは row です。これは JSON オブジェクトで、1行1行のデータを保持したデータセットの配列です。

▼ フォーマット
[
        {
            "社員コード": "0001",
            "氏名": "浦岡 友也",
            "フリガナ": "ウラオカ トモヤ",
            "所属": "0003",
            "性別": "0",
            "給与": "270000",
            "手当": "9000",
            "管理者": "",
            "生年月日": "2000-01-01 00:00:00",
            "性別2": "男",
            "所属名": "営業部第三",
            "手当2": "9000"
        },
        {
            "社員コード": "0002",
            "氏名": "山村 洋代",
            "フリガナ": "ヤマムラ ヒロヨ",
            "所属": "0003",
            "性別": "1",
            "給与": "300000",
            "手当": "",
            "管理者": "",
            "生年月日": "",
            "性別2": "女",
            "所属名": "営業部第三",
            "手当2": "0"
        }
]

※ number_format() は、String.prototype.number_format として定義済みです
※ text-right は、twitter-bootstrap で定義されています
	// テーブル表示リセット
	$("#data .row_data").remove();

	// データ用
	var row_data;
	var col_data;

	// 行のループ  ( <tr></tr> )
	$.each(row, function( index, obj ) {

		// テーブルに行を追加	
		row_data = $("<tr class=\"row_data\"></tr>").appendTo( "#data" );
	
		// 行に列を追加	
		col_data = $("<td></td>").appendTo( row_data );
		col_data.text( obj["社員コード"] );

		col_data = $("<td></td>").appendTo( row_data );
		col_data.text( obj["氏名"] );

		col_data = $("<td></td>").appendTo( row_data );
		col_data.text( obj["フリガナ"] );

		col_data = $("<td></td>").appendTo( row_data );
		col_data.text( obj["性別2"] );

		col_data = $("<td></td>").appendTo( row_data );
		col_data.text( obj["所属"] );

		col_data = $("<td></td>").appendTo( row_data );
		col_data.text( obj["所属名"] );

		col_data = $("<td></td>").appendTo( row_data );
		col_data.attr("class","text-right")
		// head-js.view で String.prototype に定義
		col_data.text( obj["給与"].number_format() );

		col_data = $("<td></td>").appendTo( row_data );
		col_data.attr("class","text-right")
		// head-js.view で String.prototype に定義
		col_data.text( obj["手当2"].number_format() );

		col_data = $("<td></td>").appendTo( row_data );
		col_data.text( obj["生年月日"] != null ? obj["生年月日"].substr(0,10) : "" );
	
	});


1 列目をコード、2列目を氏名として、2列目のテキストにリンクを設定するには以下のよう記述します。
	$("#data .row_data").each(function(i){

		var code = $(this).find("td").eq(0).text();
		var name = $(this).find("td").eq(1).text();

		var link = '<a href="https://ドメイン/?code=' + code  +  '" target="_blank">' + name + '</a>';

		$(this).find("td").eq(1).html( link );

	});



TD の中の文字列にスタイルを適用してクリックした時のイベントを登録する(条件付)には、以下のように記述します

※ ここでは、特定の行のみ処理しています
	$("#data .row_data").each(function(i){

		var type = $(this).find("td").eq(1).text();
		var td = $(this).find("td").eq(0);

		if ( type == "特定の文字列" ) {
			td.css( 
				{ "text-decoration":"underline", "cursor":"pointer" }
			);
			td.on("click", function(){ 
				alert( $(this).text() );
			});
		}

	});



以下は対象となる HTML の記述です
<div id="req">
	<table id="data" class="table table-condensed table-hover">
	<thead>
		<tr>
		<th>コード</th>
		<th>氏名</th>
		<th>フリガナ</th>
		<th>性別</th>
		<th>所属</th>
		<th>所属名</th>
		<th class="text-right">給与</th>
		<th class="text-right">手当</th>
		<th>生年月日</th>
		</tr>
	</thead>
	</table>
</div>




posted by lightbox at 2017-09-28 20:18 | jQuery | このブログの読者になる | 更新情報をチェックする

Microsoft Access に対してSQLを入力してその結果を DataGridView に表示する最も簡単なコード



最初に .accdb か .mdb を選択しておいて、自由な SQL をタイプしてその結果をそのまま Excel のごとく DataGridView に表示します。

DataGridView に表示するデータは全て文字列扱いとして、ArrayList に随時行を追加して、Rows.Add に、ToArray() したものをセットします。

一応表示のみを想定しているので、以下の設定を最初にします


表示件数は多すぎると時間がかかりすきるので、固定で 100 件にしています。
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WinApp1 {
	public partial class Form1 : Form {

		private OpenFileDialog ofd = new OpenFileDialog();

		public Form1() {
			InitializeComponent();
		}

		private void button1_Click(object sender, EventArgs e) {

			using (OleDbConnection myCon = new OleDbConnection())
			using (OleDbCommand myCommand = new OleDbCommand()) {

				// *********************
				// 接続文字列の作成
				// *********************
				myCon.ConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};", ofd.FileName);
				Debug.WriteLine(myCon.ConnectionString);

				// *********************
				// 接続
				// *********************
				try {
					// 接続文字列を使用して接続
					myCon.Open();
					// コマンドオブジェクトに接続をセット
					myCommand.Connection = myCon;
					// コマンドを通常 SQL用に変更
					myCommand.CommandType = System.Data.CommandType.Text;
				}
				catch (Exception ex) {
					MessageBox.Show(this,ex.Message);
					return;
				}

				// *********************
				// 実行 SQL
				// *********************
				myCommand.CommandText = textBox1.Text;

				// *********************
				// レコードセット取得
				// *********************
				try {
					using (OleDbDataReader myReader = myCommand.ExecuteReader()) {

						// *********************
						// 列数
						// *********************
						int nCols = myReader.FieldCount;

						// DataGridView 初期化
						dataGridView1.Columns.Clear();
						dataGridView1.ColumnCount = nCols;		// カラム数

						Type fldType;
						ArrayList rowData = new ArrayList();

						// データ取得と表示
						int maxCount = 0;
						while (myReader.Read()) {

							// 行データをセットする ArrayList をクリア
							rowData.Clear();

							maxCount++;
							if ( maxCount > 100 ) {
								break;
							}

							// 初回のみ、タイトル文字列を設定
							if (maxCount == 1) {
								for (int idx = 0; idx < nCols; idx++) {
									dataGridView1.Columns[idx].Name = myReader.GetName(idx);
								}
							}

							// 行データの作成( ArrayList で作成して ToArray() したものを Rows.Add )
							for (int idx = 0; idx <nCols; idx++) {

								// NULL でない場合
								if (!myReader.IsDBNull(idx)) {
									// 列のデータ型を取得
									fldType = myReader.GetFieldType(idx);

									// 文字列
									if (fldType.Name == "String") {
										rowData.Add(myReader.GetValue(idx).ToString());
									}
									else if (fldType.Name == "Int32") {
										rowData.Add(myReader.GetInt32(idx).ToString());
									}
									else if (fldType.Name == "DateTime") {
										rowData.Add(myReader.GetDateTime(idx).ToString());
									}
									else {
										rowData.Add(myReader.GetValue(idx).ToString());
									}

								}
								else {
									rowData.Add("");
								}
							}

							// 行追加 ( BuildArray() は配列を作成する為のメソッド )
							dataGridView1.Rows.Add(rowData.ToArray());

						}

						// リーダを使い終わったので閉じる
						myReader.Close();
					}
				}
				catch (Exception ex) {
					myCon.Close();
					MessageBox.Show(this, ex.Message);
					return;
				}

				myCon.Close();

				// カラム幅の自動調整
				dataGridView1.AutoResizeColumns();

			}		// 最外の using の終わり

		}

		private void button2_Click(object sender, EventArgs e) {

			// *********************
			// ファイル選択
			// *********************
			ofd.Filter = "Excel(*.mdb;*.accdb)|*.mdb;*.accdb|すべてのファイル(*.*)|*.*";
			ofd.FilterIndex = 1;
			ofd.Title = "Access データベースを選択してください";
			ofd.RestoreDirectory = true;

			if (ofd.ShowDialog() != DialogResult.OK) {
				return;
			}


		}		// click イベントの終わり

	}		// class の終わり
}


※ テーブル・ビュー一覧は以下のようにして簡単に表示できます
			using (OleDbConnection myCon = new OleDbConnection())
			using (OleDbCommand myCommand = new OleDbCommand()) {

				// *********************
				// 接続文字列の作成
				// *********************
				myCon.ConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};", ofd.FileName);
				Debug.WriteLine(myCon.ConnectionString);

				// *********************
				// 接続
				// *********************
				try {
					// 接続文字列を使用して接続
					myCon.Open();
					// コマンドオブジェクトに接続をセット
					myCommand.Connection = myCon;
					// コマンドを通常 SQL用に変更
					myCommand.CommandType = System.Data.CommandType.Text;
				}
				catch (Exception ex) {
					MessageBox.Show(this,ex.Message);
					return;
				}

				DataTable dataTable = myCon.GetOleDbSchemaTable(
					OleDbSchemaGuid.Tables,
					new object[] { null, null, null, null }
				);
				// TABLE のみの場合は以下( 上記は 全て )
				//	new object[] { null, null, null, "TABLE" }

				dataGridView1.DataSource = dataTable;

				myCon.Close();

				// カラム幅の自動調整
				dataGridView1.AutoResizeColumns();

			}		// 最外の using の終わり

さらに SQL の実行結果は、データの途中加工が全くできなくなりますが、DataTable を使って以下のようにさらに短く記述する事ができます
				// *********************
				// 実行 SQL
				// *********************
				myCommand.CommandText = textBox1.Text;

				try
				{
					using (OleDbDataReader myReader = myCommand.ExecuteReader())
					{
						DataTable dataTable = new DataTable();

						dataTable.Load(myReader);

						dataGridView1.DataSource = dataTable;
		
						myReader.Close();
					}

				}
				catch (Exception ex)
				{
					myCon.Close();
					MessageBox.Show(this, ex.Message);
					return;
				}

DataSource プロパティを使用した場合、新たに直接データをセットしていきたい場合は、いったん DataSource プロパティに null を設定しておく必要があります。


Microsoft.ACE.OLEDB.12.0 がインストールされていない場合

Microsoft Access データベース エンジン 2010 再頒布可能コンポーネントをダウンロードしてインストールします。


( 32ビット版と64ビット版があるので選択してダウンロードします )



posted by lightbox at 2017-09-28 19:53 | VS(C#) | このブログの読者になる | 更新情報をチェックする

2017年09月26日


MDB 接続から、MySQL と SQLServer を外部参照として結合して、Group By を使用して複雑な SQL を作成して Excel にエクスポートする SQL 文



※ SQLの窓では、Microsoft.Jet.OLEDB.4.0 使用しているので、最新の Access( .accdb ) への外部参照はできません。( C#、VB、VBScript、PHP で Microsoft.ACE.OLEDB.12.0 を使用すれば .accdb と下位の MDB 両方にアクセスできます  )

関連する記事

MDB 接続で、MySQL と SQLServer の表を結合表示する
System.Data.OleDb で select into 構文を使って簡単に Microsoft Access のデータを他の データベースにエクスポートする

SQL文全体

この SQL 文の最も大きな目的は、得意先毎の金額の集計です。その為のベースとなる取引先データは、売上伝票のデータで、伝票番号毎に売り上げた商品と金額が複数記録されています。

取引データには、得意先名や担当者名が無いので他のテーブルから参照する為に結合していますが、group by で指定した取引先コード以外は結果に表示出来ないので( MySQL は可能なようですが、通常は不可 )、max 関数を使用して得意先名と担当者の情報と住所を表示しています。

全て、取引先コードに対して必ず一つの値しか無いのでこのような記述で取り出す事ができます。
select 
	取引先コード,
	max(得意先名) as 得意先名,
	sum(金額) as 売上金額, 
	max(社員コード) as 社員コード, 
	max(氏名) as 担当者, 
	left(max(住所1) & max(住所2),30) as 住所

from 
	([ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};server=localhost;database=lightbox;uid=root;pwd=password].取引データ left outer join [ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};server=localhost;database=lightbox;uid=root;pwd=password].得意先マスタ on 取引データ.取引先コード = 得意先マスタ.得意先コード)
	left outer join [ODBC;Driver={SQL Server Native Client 11.0};server=localhost;database=lightbox;uid=sa;pwd=password].社員マスタ on 得意先マスタ.担当者 = 社員マスタ.社員コード

where
	Format(取引日付, 'yyyy/MM/dd') between '2005/06/01' and '2005/06/30'

group by 取引先コード

having
	max(得意先名) like '%商店%'




Microsoft Access ベースの記述

ここでは、SQL で使える関数は、Microsoft Access のルールに従う必要があるので、left 関数や、文字列の連結と日付との比較の為に Format 関数を使用しています。

このような SQL は、Microsoft の仕様に基づいて実行されて動作するので、from の 前に into [外部参照].表名 と書く事で、ある特定の RDBMS に対するエクスポートを容易に実行する事ができます。

ここでは、解りやすく Excel に出力する書式として以下のようになります
select 
	取引先コード,
	max(得意先名) as 得意先名,
	sum(金額) as 売上金額, 
	max(社員コード) as 社員コード, 
	max(氏名) as 担当者, 
	left(max(住所1) & max(住所2),30) as 住所

into [Excel 8.0;DATABASE=c:\tmp\集計関数結果出力.xls].取引先別売上

from 
	([ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};server=localhost;database=lightbox;uid=root;pwd=password].取引データ left outer join [ODBC;Driver={MySQL ODBC 5.3 Unicode Driver};server=localhost;database=lightbox;uid=root;pwd=password].得意先マスタ on 取引データ.取引先コード = 得意先マスタ.得意先コード)
	left outer join [ODBC;Driver={SQL Server Native Client 11.0};server=localhost;database=lightbox;uid=sa;pwd=password].社員マスタ on 得意先マスタ.担当者 = 社員マスタ.社員コード

where
	Format(取引日付, 'yyyy/MM/dd') between '2005/06/01' and '2005/06/30'

group by 取引先コード

having
	max(得意先名) like '%商店%'




出力先のテーブルが既に存在している場合は、前もって削除しておいて下さい。Excel の場合は、テーブル名はシート名となり、ブックが無い場合は新規に作成されます。

SQL を MDB のビューとして登録

into によるエクスポートが無い SQL 文の場合は ビューとして登録が可能です。トンカチ + V のアイコンで、ビューの名前を入力するダイアログが表示されるので、その時に接続している MDB の中にビューが登録されて、後から容易に実行する事ができます。

SQL を 各 RDBMS のビューとして登録

例えば、MySQL で今回の SQL を記述すると以下のようになります。
select 
	取引先コード,
	得意先名,
	sum(金額) as 売上金額, 
	社員コード, 
	氏名 as 担当者, 
	left(concat(max(住所1),max(住所2)),30) as 住所

from 
	取引データ left outer join 得意先マスタ on 取引先コード = 得意先コード
	left outer join 社員マスタ on 担当者 = 社員コード

where
	取引日付 between '2005/06/01' and '2005/06/30'

group by 取引先コード

having
	得意先名 like '%商店%'

これを MySQL に ビューとして登録してしまえば、SQLの窓では『処理』メニューからエクスポートが各 RDBMS に対して容易に実行できるようになっています。


posted by lightbox at 2017-09-26 17:48 | SQLの窓 Build C++ | このブログの読者になる | 更新情報をチェックする
Seesaa の各ページの表示について
Seesaa の 記事がたまに全く表示されない場合があります。その場合は、設定> 詳細設定> ブログ設定 で 最新の情報に更新の『実行ボタン』で記事やアーカイブが最新にビルドされます。

Seesaa のページで、アーカイブとタグページは要注意です。タグページはコンテンツが全く無い状態になりますし、アーカイブページも歯抜けページはコンテンツが存在しないのにページが表示されてしまいます。

また、カテゴリページもそういう意味では完全ではありません。『カテゴリID-番号』というフォーマットで表示されるページですが、実際存在するより大きな番号でも表示されてしまいます。

※ インデックスページのみ、実際の記事数を超えたページを指定しても最後のページが表示されるようです

対処としては、このようなヘルプ的な情報を固定でページの最後に表示するようにするといいでしょう。具体的には、メインの記事コンテンツの下に『自由形式』を追加し、アーカイブとカテゴリページでのみ表示するように設定し、コンテンツを用意するといいと思います。


※ エキスパートモードで表示しています

アーカイブとカテゴリページはこのように簡単に設定できますが、タグページは HTML 設定を直接変更して、以下の『タグページでのみ表示される内容』の記述方法で設定する必要があります

<% if:page_name eq 'archive' -%>
アーカイブページでのみ表示される内容
<% /if %>

<% if:page_name eq 'category' -%>
カテゴリページでのみ表示される内容
<% /if %>

<% if:page_name eq 'tag' -%>
タグページでのみ表示される内容
<% /if %>
この記述は、以下の場所で使用します
container 終わり

フリーフォントで簡単ロゴ作成
フリーフォントでボタン素材作成
フリーフォントで吹き出し画像作成
フリーフォントではんこ画像作成
ほぼ自由に利用できるフリーフォント
フリーフォントの書体見本とサンプル
画像を大きく見る為のウインドウを開くボタンの作成

CSS ドロップシャドウの参考デモ
BUTTONS (CSS でボタン)
イラストAC
ぱくたそ
写真素材 足成
フリーフォント一覧
utf8 文字ツール
右サイド 終わり
base 終わり