C# Programming

MSDE を使う (コンソールバージョン）

1. 目次
2. はじめに
3. 参考
4. SQL の２つの認証方法について
5. 非接続型のデータベース参照(コンソールバージョン) 
6. 非接続型のデータベースの更新( コンソールバージョン）
7. DataSetを使用しない直接的な操作（コンソールバージョン）
8. ストアドの呼び出し
9. 出力パラメータや戻り値のあるストアドの呼び出し
10. SqlExceptionの扱い

ＭＳの説明が SQL を理解していることを前提に書かれているので、データベースを知らない人にとっては最悪ですね。
忘れないようにメモっておきます。

（１）　Microsoft MSDN ADO.NET の概要
ADO.NETについて解説している。一通り目を通したほうがいいでしょう。

参考

コンソール版でデータベースアプリを作ろうと思うと、デザイナーを使えないので、コネクションストリングを作るのに苦労します。 コンソール版のアプリケーションを作る場合は、Windows.Forms 版でコネクションストリングを作ってから、それを参考に作ると楽です。

以下のソースコードは、マイクロソフトのチュートリアルをベースにしています。
MSDE との接続できさえすれば、チュートリアルどおりなので、説明はマイクロソフトのチュートリアルを見てください。

注意

ユーザー'sa'のログインに失敗しました。 理由：SQL Serverの信頼関係接続に関連付けられていません。 いうエラーが出て、SQL Server につながらない場合は、SQL サーバの認証方法を確認してみてください。 ＳＱＬサーバには、２つの認証（Windows 認証とSQL認証）があります。 この認証方法にマッチしたコネクションストリングでないと、このエラーが出る可能性があります。 NT 4.0, Win 2000, XP の場合、デフォルトでインストールすると Windows 認証になります。 信頼関係接続に関連付けられていません。というエラーメッセージもたいしたもんですね。 何の事だかさっぱりわ〜〜らん。もう少し使う立場になってエラーメッセージを出せよ！と言いたい。

MSDE は、MS SQL Serverを買えない人のためのおまけ程度にしか考えてないようなので、
管理ツール(EnterpriseManager)もなければ、説明資料も非常にプアーです。

ここでは、SqlDataAdapter とDataSet を使います。
まずは、Connection String を正しくセットしないと接続できません。
string myConnString = "data source=UCHUKAMEN\\VSdotNET;initia.....
となっていますが、UCHUKAMEN のところは、サーバー名に置き換えてください。

次に SqlDataAdapter をインスタンス化します。
このときに、ＳＱＬサーバに渡すパラメータを第１引数に渡します。
ここでは、単に参照するだけなので、"SELECT * FROM Books" という簡単な SQL 文を渡します。
次に、SqlDataAdapter で、DataSet にデータベースのデータを Fill() します。
これで、データベースとは独立したデータ構造を DataSet に持つことができます。
あとは、データセットの中身を書き出しているだけです。

コネクションストリングの SSPI とは？

string myConnString = "data source=xxxx\\VSdotNET;initial catalog=Books;integrated security=SSPI...." とありますが、このSSPI とは、Security Support Provider Interface　で、RPC のようなトランスポートレベルのアプリケーション間での通信のためのインターフェースで、Lan Manager や Kerberos のようなセキュリティパッケージを使い、信頼できる接続を提供するためのもので、要は Windows 認証を使うということです。

　

ソースコード

using System; using System.Data; using System.Data.SqlClient; namespace Database1 { /// <summary> /// Class1 の概要の説明です。 /// </summary> class Class1 { /// <summary> /// アプリケーションのメイン エントリ ポイントです。 /// </summary> [STAThread] static void Main(string[] args) { DataSet myDS = new DataSet(); string myConnString = "data source=UCHUKAMEN\\VSdotNET;initial catalog=test;integrated security=SSPI"; SqlDataAdapter myAdapter = new SqlDataAdapter("select * from Books", myConnString); try { myAdapter.Fill(myDS, "Books"); foreach(DataRow myTitle in myDS.Tables["Books"].Rows) Console.WriteLine("ID = "+ myTitle["ID"] + " Title =" + myTitle["Title"]); } catch (System.Data.SqlClient.SqlException e) { Console.WriteLine(e.Message); } Console.ReadLine(); } } }

　

実行結果

さて、データベースの読み出しができたので、次は更新ですね。

SqlCommandBuilder build = new System.Data.SqlClient.SqlCommandBuilder(myAdapter);

というように、SqlCommandBuilderのコンストラクター引数にSqlDataAdapter を指定することにより、
SqlDataAdapter に適切なUPDATE文、DELETE文、INSERT文が生成されるようになります。
そして、

myDS.Tables["Books"].Rows[0]["Title"] = "１００人の開発者の村のお話";

というように、DataSetのテーブルを変更し、

myAdapter.Update(myDS, "Books");

で、データセットをアップデートします。これで、データベースが更新されます。

ヒント

'System.InvalidOperationException' のハンドルされていない例外が system.data.dll で発生しました。 追加情報 : UpdateCommand の動的 SQL 生成は、キーである列情報を返さない SelectCommand に対してサポートされていません。 という例外が上がった場合は、テーブルに主キーを設定しているか確認してみましょう。 主キーがないと、一意にテーブルにデータを更新できないので、この例外があがります。

　

ソースコード

using System; using System.Data; using System.Data.SqlClient; namespace Database2 { /// <summary> /// Class1 の概要の説明です。 /// </summary> class Class1 { /// <summary> /// アプリケーションのメイン エントリ ポイントです。 /// </summary> [STAThread] static void Main(string[] args) { DataSet myDS = new DataSet(); string myConnString = "data source=UCHUKAMEN\\VSdotNET;initial catalog=test;integrated security=SSPI"; SqlDataAdapter myAdapter = new SqlDataAdapter("select * from Books", myConnString); try { myAdapter.Fill(myDS, "Books"); SqlCommandBuilder build = new System.Data.SqlClient.SqlCommandBuilder(myAdapter); myDS.Tables["Books"].Rows[0][0] = 1; myDS.Tables["Books"].Rows[0]["Title"] = "１００人の開発者の村のお話"; foreach(DataRow myTitle in myDS.Tables["Books"].Rows) Console.WriteLine("ID = "+ myTitle["ID"] + " Title =" + myTitle["Title"]); myAdapter.Update(myDS, "Books"); } catch (System.Data.SqlClient.SqlException e) { Console.WriteLine(e.Message); } Console.ReadLine(); } } }

　

実行結果

もう１つの方法は、SqlCommand を使う方法で、SQL文を直接たたくので直感的ですが、その分SQLを文字列で渡すことによるデバッグのしづらさがあります。

SqlCommandBuilder build = new System.Data.SqlClient.SqlCommandBuilder(myAdapter);

INSERT, UPDATE, DELETE は、ExecuteNonQuery();

SELECT は、ExecuteReader()により、SqlDataReader のインスタンスを取り出し、それ経由でデータにアクセスします。
この例では、レコードを１つ INSERT 文により追加しています。
　

ソースコード

using System; using System.Data; using System.Data.SqlClient; namespace Database3 { /// <summary> /// Class1 の概要の説明です。 /// </summary> class Class1 { /// <summary> /// アプリケーションのメイン エントリ ポイントです。 /// </summary> [STAThread] static void Main(string[] args) { string connectionString = "data source=UCHUKAMEN\\VSdotNET;initial catalog=test;integrated security=SSPI"; SqlConnection sqlConn = new SqlConnection( connectionString ); SqlCommand sqlCmdInsert = new SqlCommand("insert into Books(ID, Title) VALUES(99, '１００人の物語り終わり')" ); SqlCommand sqlCmdRefer = new SqlCommand("select * from Books"); sqlConn.Open(); sqlCmdInsert.Connection = sqlConn; sqlCmdInsert.ExecuteNonQuery(); sqlCmdRefer.Connection = sqlConn; SqlDataReader sqlDataReader = sqlCmdRefer.ExecuteReader(); while (sqlDataReader.Read()) Console.WriteLine("\t{0}\t{1}", sqlDataReader.GetInt32(0), sqlDataReader.GetString(1)); sqlConn.Close(); Console.ReadLine(); } } }

　

実行結果

　

Northwind の CustOrderHistストアドプロシージャを呼び出す例　
　

CustOrderHist ストアドプロシージャ

ALTER PROCEDURE dbo.CustOrderHist @CustomerID nchar(5) AS SELECT ProductName, Total=SUM(Quantity) FROM Products P, [Order Details] OD, Orders O, Customers C WHERE C.CustomerID = @CustomerID AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID GROUP BY ProductName

ソースコード

SqlConnection conn = new SqlConnection(this.sqlConnection1.ConnectionString); SqlCommand cmd = new SqlCommand("CustOrderHist", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@CustomerID", SqlDbType.NChar); cmd.Parameters["@CustomerID"].Value = "ALFKI"; conn.Open(); SqlDataReader sqlDataReader = cmd.ExecuteReader(); while (sqlDataReader.Read()) Console.WriteLine(sqlDataReader.GetString(0) + ":" + sqlDataReader.GetInt32(1)); conn.Close();

CustOrderHist ストアドプロシージャ

ALTER PROCEDURE dbo.GetTotalCount ( @CustomerID nchar(5) ) AS SELECT * From Customers RETURN @@ROWCOUNT

ソースコード

SqlConnection conn = new SqlConnection(this.sqlConnection1.ConnectionString); SqlCommand cmd = new SqlCommand("GetTotalCount", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@CustomerID", SqlDbType.NChar); cmd.Parameters["@CustomerID"].Value = "ALFKI"; cmd.Parameters.Add("@TotalCount", SqlDbType.Int); cmd.Parameters["@TotalCount"].Direction = ParameterDirection.ReturnValue; conn.Open(); SqlDataReader sqlDataReader = cmd.ExecuteReader(); // 次の行は例外が発生する。 // Console.WriteLine(cmd.Parameters[1].Value.ToString()); // sqlDataReaderをクローズするまで、戻りパラメータは使えない。 sqlDataReader.Close(); // 戻りパラメータ値取得可能 Console.WriteLine(cmd.Parameters[1].Value.ToString()); conn.Close();

SqlConnection なででは、SqlExceptionがあがる可能性があります。
この扱いには注意が必要です。

SqlException.Class　エラーの重大度を示す１−２５の値
１−１０	情報メッセージ	ユーザが入力したデータのエラー
１１−１６	情報メッセージ	ユーザが入力し、修正できる
１７−１９	ソフトまたはハードエラー	続行可能の場合もあれば続行不可能な場合もある。
２０−２５	ソフトまたはハードエラー	SqlConnectionは閉じられる。

また、SqlException.Messageにエラーメッセージが入りますが、
SqlException.Errors に複数のエラーメッセージが返される場合があるので、
これも注意が必要です。このエラーを表示するには、次のようにfor, foreachでまわす必要があります。

ソースコード (MSDNより)

public void DisplaySqlErrors(SqlException myException) { for (int i=0; i < myException.Errors.Count; i++) { MessageBox.Show("Index #" + i + "\n" + "Error: " + myException.Errors[i].ToString() + "\n"); } }