宇宙仮面の
C# Programming

 
Image

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

開発環境: Visual Studio 2003 

1.目次

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

2.はじめに


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

3.参考

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

4.SQL の2つの認証方法について


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

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

注意
ユーザー'sa'のログインに失敗しました。
理由:SQL Serverの信頼関係接続に関連付けられていません。

いうエラーが出て、SQL Server につながらない場合は、SQL サーバの認証方法を確認してみてください。
SQLサーバには、2つの認証(Windows 認証とSQL認証)があります。
この認証方法にマッチしたコネクションストリングでないと、このエラーが出る可能性があります。

NT 4.0, Win 2000, XP の場合、デフォルトでインストールすると Windows 認証になります。

信頼関係接続に関連付けられていません。というエラーメッセージもたいしたもんですね。
何の事だかさっぱりわ〜〜らん。もう少し使う立場になってエラーメッセージを出せよ!と言いたい。

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

5.非接続型のデータベース参照(コンソールバージョン)

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

次に SqlDataAdapter をインスタンス化します。
このときに、SQLサーバに渡すパラメータを第1引数に渡します。
ここでは、単に参照するだけなので、"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();
                }
        }
}

 

 

実行結果

Image

6.非接続型のデータベースの更新( コンソールバージョン)

さて、データベースの読み出しができたので、次は更新ですね。
SqlCommandBuilder build = new System.Data.SqlClient.SqlCommandBuilder(myAdapter);
というように、SqlCommandBuilderのコンストラクター引数にSqlDataAdapter を指定することにより、
SqlDataAdapter に適切なUPDATE文、DELETE文、INSERT文が生成されるようになります。
そして、
myDS.Tables["Books"].Rows[0]["Title"] = "100人の開発者の村のお話";
というように、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"] = "100人の開発者の村のお話";           
                                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();
                }
        }
}

 

 

実行結果

Image

7.DataSetを使用しない直接的な操作(コンソールバージョン)

もう1つの方法は、SqlCommand を使う方法で、SQL文を直接たたくので直感的ですが、その分SQLを文字列で渡すことによるデバッグのしづらさがあります。
SqlCommandBuilder build = new System.Data.SqlClient.SqlCommandBuilder(myAdapter);
INSERT, UPDATE, DELETE は、ExecuteNonQuery();

SELECT は、ExecuteReader()により、SqlDataReader のインスタンスを取り出し、それ経由でデータにアクセスします。
この例では、レコードを1つ 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, '100人の物語り終わり')" );
                        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();
                }
        }
}

 

 

実行結果

Image

 

8.ストアドの呼び出し

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();

9.出力パラメータや戻り値のあるストアドの呼び出し

Northwind の CustOrderHistストアドプロシージャを呼び出す例 
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();
 

10.SqlExceptionの扱い

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

SqlException.Class エラーの重大度を示す1−25の値 
1−10情報メッセージユーザが入力したデータのエラー
11−16情報メッセージユーザが入力し、修正できる
17−19ソフトまたはハードエラー続行可能の場合もあれば続行不可能な場合もある。
20−25ソフトまたはハードエラー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");
    }
 }
 


日付修正履歴
2002/5/28初期バージョン作成
2002/12/8改訂
2003/7/21改訂