VB.NETでdatagridviewをGUIで作成するとき
クエリに
select iif(bit型カラム=0,’男’,’女’) as 性別,…from sqlserverTable
と書くとエラー
select 性別=case when bit型カラム=0 then ‘男’ else ‘女’ end,…
と書くと大丈夫
VBAのレコードセットも癖が強いがVB.NETも一筋縄では行かない気がする
ちょっとベンチ
バックエンドSQLserverフロントエンドAccessVBA VS VB.NET
‘VBA
Public Function Queryspeedtest()
Dim T1 As Double
Dim T2 As Double
T1 = Timer
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.ConnectionString = “OLEDB接続文字”
cn.Open
rs.cursorlocation=adUseClient
rs.Open “select A.code,B.codename from TableA as A inner join TableB as B on(A.code=B.code)”, cn, adOpenStatic, adLockOptimistic
rs.Close
cn.Close
T2 = Timer
Debug.Print T2 – T1
End Function
‘VB.NET
Public Shared Function Queryspeedtest()
Dim SW = New System.Diagnostics.Stopwatch()
SW.Start()
Dim cn = New System.Data.OleDb.OleDbConnection(OLEDB接続文字)
cn.Open()
Dim cmd = New System.Data.OleDb.OleDbCommand(
“select A.code,B.codename from TableA as A inner join TableB as B on(A.code=B.code)”, cn
)
Dim DT = New DataTable(“test”)
Dim adp = New System.Data.OleDb.OleDbDataAdapter(cmd)
adp.Fill(DT)
adp.Dispose()
cn.Close()
SW.Stop()
Debug.WriteLine(SW.Elapsed)
End Function
tableAは20万件、TableBは1万件弱のマスターファイル
TableAのレコードのcodeからTableBのcodenameを得るよくあるクエリ
もちろん主キーは両方あり
単純なselect column from tableは若干VBが勝利
上記のクエリはVBA 0.4s NET 0.8s
倍とはいえ差はコンマ数秒なのでまあいいかと思うがdatagridviewでtextbox表示とかcomboboxに入れたりするとちょっと・・
Accessフォームの方が速い、VB.NETではちょっと工夫が要りそうな
vbaでcursolocationがクライアントの時はcursoltype、locktypeによる差はない
サーバサイドでは最速と覚えていたadopenforwardonlyよりadopendynamicの方がなぜか早かった
ADODBとADO.NETはまったく別物なので比較するなと言われそうだけど・・
でもVSのエディタはすばらしい、VBAエディターが残念に思えるほど
datatable のクエリ
が遅い
sqlserverテーブルのシンプルな選択クエリ。
フォームに表示が遅いのではなく デザイナーで作成途中の確認の段階で遅い。
accessから移行中のクエリだがaccessのリンクテーブルのクエリの方がはるかに速い
ADODBのデータソースレス接続の様にコードにクエリを書いてdataadapter作成したほうが速いのか？
ODBC接続のaccessクエリに ADO.NETが速さで劣る？
どこか間違えているのかも、明日落ち着いて見直そう
新元号
新元号は令和
Accessには和暦はあるのでそのうちupdateあるでしょう
SQLserverとVB.NETにはない → 失礼、VB.NETにはあります
SQLserverのスカラー関数を
前略
DECLARE @ResultVar nvarchar(10),@VarYear int,@VarDate nvarchar(10),@VarDateInt int
select @VarYear=cast(format(@paramDate,’yyyy’) as int)
select @VarDate=format(@paramDate,’MM/dd’)
select @VarDateInt=cast(format(@paramDate,’yyyyMMdd’) as int)
— Add the T-SQL statements to compute the return value here
select @ResultVar=(case
when @VarDateint>20190430 then
concat(‘R’,format((@VarYear-2018),’00’),’/’,@VarDate) –令和
when @VarDateInt>19890107 then
concat(‘H’,format((@varyear-1988),’00’),’/’,@VarDate) –平成
when @VarDateInt>19261224 then
concat(‘S’,format((@varyear-1925),’00’),’/’,@VarDate) –昭和
when @VarDateInt>19120729 then
concat(‘T’,format((@varyear-1911),’00’),’/’,@VarDate) –大正
when @VarDateInt>18680124 then
concat(‘M’,format((@varyear-1867),’00’),’/’,@VarDate) –明治
else ‘0’
end
)
— Return the result of the function
RETURN @ResultVar
以下略
これで良いはず
SQLserverにあればVSはなんとかなるでしょう
VB.NETには和暦あります。省略型はないですが。
複数ステップのOLEDBエラー nvarchar(max) 改行
SQLserverテーブルのnvrachar(max)のカラムに対して
ODBC接続のレコードセット取って
略
Str=”文字” & vbCrLf
rs!カラム=Str
rs.update
など更新や追加行うと複数ステップの・・・エラー
OLEDB接続では起きない
nvarchar(max)をmaxを500など変更すれば起きない
cn.execute “insert into・・・values・・・” では起きない
サロゲートペア SQLserverの場合
以前もMySQLで書いたけど
AccessからSQLServerへサロゲート文字保存について
AccessはVBエディターに表示はできないけどテーブル、フォームに表示保存可能
SQLserverも保存できる
接続文字を書いてexecuteメソッドで
insert into Table(column) values(“サロゲートペア文字”)
とVBAから更新クエリ発行すると保存できない
OLEDBはだめ
oledbはhttps://docs.microsoft.com/ja-jp/sql/connect/oledb/features/utf-16-support-in-oledb-driver-for-sql-server?view=sql-server-2017
難しいけどだめだと書いてあるようです
ODBCならといろいろ試したけどこれも今のところだめです
ODBCからリンクテーブルにしてフォーム作って入力すると可能なのだがなぜ？
接続文字列にオプションか何かあるのかな？引き続き要調査
OLE DB
詳しくはわかりませんが何年か前から
Microsoft OLE DB Provider for SQL Server（SQLOLEDB)
はもうサポートしないから新規開発に使用するのはやめてほしい
とMicrosoftがアナウンスしているが今でも使えています
でも突然消えると困るので調べてみると
代わりに
Microsoft OLE DB Driver for SQL Server（MSOLEDBSQL）
をリリースしてるようです
問題はSQLOLEDBはOSに付属してるので何も考えずに良かったけどMSOLEDBは別途インスト必要らしいです
挙動も少し違うようで安全性は向上する、計算列の操作が今まで甘かったけど今度は厳しく行きますよ という感じのことがアナウンスされてた
今日開発機にインストして使ってみましたがaccdbは特に問題なく動きました。
OSは64bitですがAccessが32なのでx86をインストだろうと思ったらX64でした
接続文字もSQLOLEDBをMSOLEDBSQLに書き換えるだけで良いようです
アプリ配布先にはインストされてないのでパッケージ作るとき困るけどSQLOLEDB消えてから考えよう
update case文とトリガー
テーブルにupdateトリガーを仕込んで更新日時カラムをcurrent_timestampにupdateさせている状態で
update TableName set TargetColumn=case when OtherColumn=’abc’ then ‘D’ else TargetColumn end
で更新すると更新日時カラムは全レコード現在時刻に変更される
更新クエリの前に
Alter table TableName disable trigger TriggerName
クエリの後に
Alter table TableName enable trigger TriggerName
トリガーは楽な反面、仕込んでいるのを忘れてしまう
今日見直すとこの更新クエリ結局全部更新？
where句でupdate の方が断然速い
なんでもcase で条件分岐すれば良いというわけではないらしい
VBAからSQL ServerのIdentity取得
これは時々必要になるのですが
scope_identity() , ident_current(‘Table’) , @@Identity
の３つの方法があるが他のセッションの影響を受けない scope_identity() が推奨と検索すると出てきます。
しかしVBAからADO ADODBでレコードセットを取って
前略
cn.execute “insert into table・・・”
rs.open “select scope_identity() as ID”,cn,adopendynamic,adlockoptimistic
としても取得してくれないのでしかたなく
rs.open “select ident_current(‘Table’) as ID”,cn,adopendynamic,adlockoptimistic
で取得していました
cursorlocationがserverサイドでdynamicを要求した場合だけ取得できないのに気がつきました
ほかの組み合わせは大丈夫のようです
しかしこのレコードセットのcursortypeは何を要求しようと結局forwardonlyに調整されます
テーブルやPK等で自動調節？
ならばなぜdynamic要求の場合だけＸなのでしょう。動的だから？
集計クエリ カウント
重複は除外してカウント、重複あっても全カウント 両方出したいとき
SQLserver、MySQLなら
select GroupColumn,Count(distinct TargetColumn),Count(TargetColumn) from Table group by GroupColumn
と簡単に出る
ACCESSでは？
このクエリはACCESSではできないようなのでいろいろ試したが今のところクエリ一文ではできていない
一度クエリでカウントして保存してさらにこれをソースにカウントしたカラムをカウントする のイメージで….
select GroupColumn as Group1,Count(TargetColumn) as Count1 from Table group by GroupColumn,TargetColumn
これをQ_1で保存して
select Group1,Count(Count1) as CountDistinct from Q_1 group by Group1
これでdistinct countができる
もう一つ全カウント
select GroupColumn as Group1,Count(TargetColumn) as CounAll from Table group by GroupColumn
のクエリQ_2を保存して
Q_1、Q_2 の連結クエリを作成する の２段構え
もっといい方法があるのかもしれないが今のところこれしか思いつかない
バックエンドがSQLserverやMySQLならACCESSから一番上のパススルークエリの方が楽かな？