C#からSQL ServerのSQL文を実行するプログラムを作りました。
実行するSQL文はテーブル作成（CREATE）、テーブル削除（DROP）、テーブルのレコード追加（INSERT）、レコード検索（SELECT）、レコード更新（UPDATE）、レコード削除（DELETE）です。
これに伴ってSQL Serverに接続と解放、テーブルの存在有無の確認処理も作ります。
プログラムには作る人や仕様によって100人100通りありますが、今回はSQL Serverへのアクセスをまとめたクラスとそのクラスのプロパティやメソッドを呼び出すプログラムで構成しました。
開発にはVisual Studioを使用します。
Visual Studioで新規プロジェクトを作る
プロジェクト名をProjectSqlServerとして新たにプロジェクトを作成。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ProjectSqlServer
{
class Program
{
static void Main(string[] args)
{
}
}
}
新規にプロジェクを作るとこのようなコードが自動的に作られます。
usingで設定しているライブラリが無駄に多すぎるのとSQL Serverへのアクセスに必要なライブラリが有りません。
ライブラリ設定
using System;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace ProjectSqlServer
{
class Program
{
static void Main(string[] args)
{
}
}
}
ライブラリをこのようにします。
SQL Serverのアクセスに必要な「using System.Data.SqlClient;」を追加しました。
SQL Serveの処理をまとめたクラス作成
コードファイルSqlServerSSSPRODUCT.csを作詞しそこにSqlServerSSSPRODUCTクラスを作りました。
using System;
using System.Data.SqlClient;
using System.Collections.Generic;
// **************************************************************
// LocalhostのSQL Server(データベース:SSSPRODUCT)操作
// **************************************************************
class SqlServerSSSPRODUCT
{
}
usingは先ほどのProgram.csと同様です。
このSqlServerSSSPRODUCTクラスのSQL Server関連の処理を追加します。
定数宣言
データベースの名称、テーブルの名称、又は戻り値等をプログラムロジック中に書くのはあまり好ましくないので定数として予め宣言します。
// **************************************************************
// LocalhostのSQL Server(データベース:SSSPRODUCT)操作
// **************************************************************
class SqlServerSSSPRODUCT
{
public bool CONSTOK = true; // 正常
public bool CONSTNG = false; // 失敗
public string CONSTSERVER = @"localhost\SQLEXPRESS"; // サーバー
public string CONSTDBNAME = "SSSPRODUCT"; // データベース名
public string CONSTUSERID = "sa"; // ユーザーID
public string CONSTPASSWORD = "xxxxxxxx"; // パスワード
public string CONSTTBLTEST = "TBLTEST"; // テーブル名
// テーブルTBLTESTのカラム構成
public string CONSTTBLTESTCOLUMNS = "TBLTESTID DECIMAL(5,0) NOT NULL DEFAULT 0,TBLTESTNAMERUBY VARCHAR(50) NOT NULL DEFAULT '',TBLTESTNAME VARCHAR(50) NOT NULL DEFAULT ''";
public string CONSTTBLTESTPRIMARYKEY = "TBLTESTID"; // テーブルTBLTESTのプライマリーキー
想定できる定数を宣言しました。
必要に応じて追加します。
レコードレイアウト用クラス
後に使用するテーブルTBLTESTのレコードレイアウトイメージを作りました。
これはListクラスでも使用します。
// *********************************************
// テーブル(TBLTEST)レイアウト
// *********************************************
class TBLTESTRECORD
{
public decimal Id;
public string Ruby;
public string Name;
}
SQL Server接続・解放
SQL ServerのSQL文を実行するには先だってSQL Serverに接続する必要があります。
そして処理が終わったらSQL Serverを解放します。
SQL Server接続
// *********************************************
// Localhost SQL Server接続
// *********************************************
public bool Connect(SqlConnection Connection)
{
try
{
string Connectiontext = "";
Connectiontext = Connectiontext + "Data Source = " + CONSTSERVER + ";";
Connectiontext = Connectiontext + "Initial Catalog = " + CONSTDBNAME + ";";
Connectiontext = Connectiontext + "User ID = " + CONSTUSERID + ";";
Connectiontext = Connectiontext + "Password = " + CONSTPASSWORD + ";";
// SQL Server接続
Connection.ConnectionString = Connectiontext;
Connection.Open();
}
// 接続失敗
catch (SqlException)
{
return CONSTNG;
}
// 正常時
return CONSTOK;
}
引数でSqlConnection で受け取り、定数で宣言した情報でコネクションを開設します。
正常に接続できたら定数CONSTOKを返し失敗したら定数CONSTNGを返します。
SQL Server解放
// *********************************************
// SQL Server解放
// *********************************************
public void DeConnect(SqlConnection Connection)
{
Connection.Close();
}
引数でSqlConnection で受け取りそれを解放します。
テーブルの有無確認
// *********************************************
// テーブルの有無確認
// *********************************************
public bool TblExistChk(string TableName)
{
string StrSql;
bool TblExist;
SqlConnection Connection = new SqlConnection();
// SQL Server接続
if (Connect(Connection) == CONSTNG)
return CONSTNG;
// テーブル有り:OK
// テーブル無し:NG
StrSql = "";
StrSql = StrSql + "IF OBJECT_ID(N'" + TableName + "', N'U') IS NULL";
StrSql = StrSql + " SELECT CONVERT(BIT,'" + CONSTNG + "') AS TblExistChkRtn";
StrSql = StrSql + "ELSE ";
StrSql = StrSql + " SELECT CONVERT(BIT,'" + CONSTOK + "') AS TblExistChkRtn";
SqlCommand Command = new SqlCommand();
Command.CommandText = StrSql;
Command.Connection = Connection;
SqlDataReader Reader = Command.ExecuteReader();
Reader.Read();
TblExist = (bool)Reader.GetValue(0);
DeConnect(Connection); // SQL Server解放
return TblExist;
}
テーブルの有無を確認して判定を戻り値として返します。
後にテーブル作成時に同名のテーブルが存在したら削除してから作成する処理に使います。
テーブル作成
// *********************************************
// テーブル作成
// *********************************************
public bool TableCreate(string TableName, string Constitution, string PrimaryKey)
{
string StrSql;
SqlConnection Connection = new SqlConnection();
// SQL Server接続
if (Connect(Connection) == CONSTNG)
return CONSTNG;
// テーブル作成
try
{
StrSql = "";
StrSql = StrSql + "CREATE TABLE " + TableName;
StrSql = StrSql + "(";
StrSql = StrSql + " " + Constitution + ",";
StrSql = StrSql + " CONSTRAINT PK" + TableName + " PRIMARY KEY CLUSTERED";
StrSql = StrSql + " (";
StrSql = StrSql + " " + PrimaryKey;
StrSql = StrSql + " )";
StrSql = StrSql + ")";
SqlCommand Command = new SqlCommand();
Command.CommandText = StrSql;
Command.Connection = Connection;
Command.ExecuteNonQuery();
}
catch (SqlException)
{
DeConnect(Connection); // SQL Server解放
return CONSTNG;
}
DeConnect(Connection); // SQL Server解放
return CONSTOK;
}
引数としてテーブル名、カラム構成、プライマリーキーを受け取りCREATE 文でテーブルを作成します。
テーブル削除
// *********************************************
// テーブル削除
// *********************************************
public bool TableDelete(string TableName)
{
string StrSql;
SqlConnection Connection = new SqlConnection();
// SQL Server接続
if (Connect(Connection) == CONSTNG)
return CONSTNG;
// テーブル削除
try
{
StrSql = "";
StrSql = StrSql + "DROP TABLE " + TableName;
SqlCommand Command = new SqlCommand();
Command.CommandText = StrSql;
Command.Connection = Connection;
Command.ExecuteNonQuery();
}
catch (SqlException)
{
DeConnect(Connection); // SQL Server解放
return CONSTNG;
}
DeConnect(Connection); // SQL Server解放
return CONSTOK;
}
削除対象のテーブル名を受け取りDROP文を使ってテーブルを削除します。
行（レコード）追加
// *********************************************
// テーブル(TBLTEST)レコード追加
// *********************************************
public bool TBLTESTInsert(List<TBLTESTRECORD> ListTblTest)
{
string StrSql;
SqlConnection Connection = new SqlConnection();
// データの有無
if (ListTblTest.Count < 1)
return CONSTNG;
// SQL Server接続
if (Connect(Connection) == CONSTNG)
return CONSTNG;
// TBLTESTレコード追加
for (int i = 0; i < ListTblTest.Count; i++)
{
try
{
StrSql = "";
StrSql = StrSql + "INSERT INTO " + CONSTTBLTEST;
StrSql = StrSql + "(";
StrSql = StrSql + " TBLTESTID,";
StrSql = StrSql + " TBLTESTNAMERUBY,";
StrSql = StrSql + " TBLTESTNAME";
StrSql = StrSql + ")";
StrSql = StrSql + "VALUES";
StrSql = StrSql + "(";
StrSql = StrSql + " " + ListTblTest[i].Id + ",";
StrSql = StrSql + " '" + ListTblTest[i].Ruby + "',";
StrSql = StrSql + " '" + ListTblTest[i].Name + "'";
StrSql = StrSql + ")";
SqlCommand Command = new SqlCommand();
Command.CommandText = StrSql;
Command.Connection = Connection;
Command.ExecuteNonQuery();
}
catch (SqlException)
{
DeConnect(Connection); // SQL Server解放
return CONSTNG;
}
}
DeConnect(Connection); // SQL Server解放
return CONSTOK;
}
行の追加はINSERT文を使用します。
レコードレイアウトと同様のクラスTBLTESTRECORDをListクラスとした引数を受け取り、その内容をテーブルのレコードとして追加します。
⇩Listクラスについてはこちらを参照下さい。
C#を始めよう｜クラスをListクラスとして使う
データの一時保存等に便利なListクラスってものがあるのですが、レコードレイアウト的な他クラスをListクラスにして使ってみた。ベースのクラスusing System;using System.Collections.Generic;nam...
sssproduct.com
行（レコード）検索
// *********************************************
// テーブル(TBLTEST)IDでレコード検索
// *********************************************
public List<TBLTESTRECORD> TBLTESTSelect(int IdFrom, int IdTo)
{
string StrSql;
SqlConnection Connection = new SqlConnection();
List<TBLTESTRECORD> TblTestRec = new List<TBLTESTRECORD>();
// SQL Server接続
if (Connect(Connection) == CONSTNG)
return TblTestRec;
try
{
StrSql = "";
StrSql = StrSql + "SELECT ";
StrSql = StrSql + " TBLTESTID,";
StrSql = StrSql + " TBLTESTNAMERUBY,";
StrSql = StrSql + " TBLTESTNAME";
StrSql = StrSql + " FROM ";
StrSql = StrSql + " " + CONSTTBLTEST;
StrSql = StrSql + " WHERE ";
StrSql = StrSql + " TBLTESTID >= " + IdFrom + " AND ";
StrSql = StrSql + " TBLTESTID <= " + IdTo;
SqlCommand Command = new SqlCommand();
Command.CommandText = StrSql;
Command.Connection = Connection;
SqlDataReader Reader = Command.ExecuteReader();
while (Reader.Read())
{
TBLTESTRECORD Rec = new TBLTESTRECORD();
Rec.Id = (decimal)Reader.GetValue(0);
Rec.Ruby = (string)Reader.GetValue(1);
Rec.Name = (string)Reader.GetValue(2);
TblTestRec.Add(Rec);
}
}
catch (SqlException)
{
DeConnect(Connection); // SQL Server解放
return TblTestRec;
}
DeConnect(Connection); // SQL Server解放
return TblTestRec;
}
検索にはSELECT文を使ってWHERE句で条件を指定します。
引数で検索対象のプライマリーキーであるIdのFrom-Toを受け取り、検索結果をTBLTESTRECORDをListクラスとして戻り値として返しています。
行（レコード）の更新
// *********************************************
// テーブル(TBLTEST)IDでアップデート
// *********************************************
public bool TBLTESTUpdate(int IdFrom, int IdTo, string[] text)
{
string StrSql = "";
SqlConnection Connection = new SqlConnection();
// SQL Server接続
if (Connect(Connection) == CONSTNG)
return CONSTNG;
// TBLTEST UPDATE
try
{
StrSql = StrSql + string.Format("UPDATE {0}", CONSTTBLTEST);
if (text[0] != "")
StrSql = StrSql + string.Format(" SET TBLTESTNAMERUBY = '{0}'", text[0]);
if (text[1] != "")
{
if (text[0] == "")
StrSql = StrSql + string.Format(" SET TBLTESTNAME = '{0}'", text[1]);
else
StrSql = StrSql + string.Format(" ,TBLTESTNAME = '{0}'", text[1]);
}
StrSql = StrSql + string.Format(" WHERE ");
StrSql = StrSql + string.Format(" TBLTESTID >= {0} AND ", IdFrom);
StrSql = StrSql + string.Format(" TBLTESTID <= {0}", IdTo);
SqlCommand Command = new SqlCommand();
Command.CommandText = StrSql;
Command.Connection = Connection;
Command.ExecuteNonQuery();
}
catch (SqlException)
{
DeConnect(Connection); // SQL Server解放
return CONSTNG;
}
DeConnect(Connection); // SQL Server解放
return CONSTOK;
}
更新はUPDATE文を使用します。
引数で更新対象のIdのFrom-Toと変更後の値とするテキストの配列で受け取ます。
引数をもとにして更新します。
行（レコード）削除
// *********************************************
// テーブル(TBLTEST)IDでレコード削除
// *********************************************
public bool TBLTESTDelete(decimal IdFrom, decimal IdTo)
{
string StrSql = "";
SqlConnection Connection = new SqlConnection();
// SQL Server接続
if (Connect(Connection) == CONSTNG)
return CONSTNG;
// TBLTEST DALETE
try
{
StrSql = StrSql + string.Format("DELETE ");
StrSql = StrSql + string.Format(" FROM ");
StrSql = StrSql + string.Format(" {0} ", CONSTTBLTEST);
StrSql = StrSql + string.Format(" WHERE ");
StrSql = StrSql + string.Format(" TBLTESTID >= {0} AND ", IdFrom);
StrSql = StrSql + string.Format(" TBLTESTID <= {0}", IdTo);
SqlCommand Command = new SqlCommand();
Command.CommandText = StrSql;
Command.Connection = Connection;
Command.ExecuteNonQuery();
}
catch (SqlException)
{
DeConnect(Connection); // SQL Server解放
return CONSTNG;
}
DeConnect(Connection); // SQL Server解放
return CONSTOK;
}
}
行（レコード）の削除はUPDATE文を使います。
引数のIdのFrom-Toで更新対象行を絞ります。
引数の配列の値で更新します。
配列の0番目にはRuby、1場面目には名前が格納されています。
以上でSQL Serverの処理をまとめたクラスSqlServerSSSPRODUCTの完成です。
このクラスを呼び出すロジックを書いてみます。
SqlServerSSSPRODUCTクラスを使ったプログラム
using System;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace ProjectSqlServer
{
class Program
{
static void Main(string[] args)
{
// TBLTESTの有無確認
TblTestChk();
// TBLTEST作成
TblTestCreate();
// TBLテスト レコード追加
TblTestRecordAdd();
// TBLTEST抽出
TblTestSelect();
// TBLTESTアップデート
TblTestUpDate();
// TBLTESTデリート
TblTestDelete();
}
// **************************************************
// テーブル(TBLTEST)有無確認
// ***************************************************
static void TblTestChk()
{
SqlServerSSSPRODUCT SSSPRODUCT = new SqlServerSSSPRODUCT();
if ( SSSPRODUCT.TblExistChk(SSSPRODUCT.CONSTTBLTEST) == SSSPRODUCT.CONSTOK )
{
Console.WriteLine(string.Format("作成するテーブル{0}と同名のテーブルが存在します。",SSSPRODUCT.CONSTTBLTEST));
Console.WriteLine("何かキーを押すとプログラムを再開します。");
Console.ReadLine();
if (SSSPRODUCT.TableDelete(SSSPRODUCT.CONSTTBLTEST) == SSSPRODUCT.CONSTOK)
{
Console.WriteLine(string.Format("テーブル{0}を削除しました。", SSSPRODUCT.CONSTTBLTEST));
Console.WriteLine("何かキーを押すとプログラムを再開します。");
Console.ReadLine();
}
}
}
// **************************************************
// テーブル(TBLTEST)作成
// ***************************************************
static void TblTestCreate()
{
SqlServerSSSPRODUCT SSSPRODUCT = new SqlServerSSSPRODUCT();
// テーブル(TBLTEST)作成
if (SSSPRODUCT.TableCreate(SSSPRODUCT.CONSTTBLTEST, SSSPRODUCT.CONSTTBLTESTCOLUMNS, SSSPRODUCT.CONSTTBLTESTPRIMARYKEY) == SSSPRODUCT.CONSTOK)
Console.WriteLine("テーブル{0}を作成しました。", SSSPRODUCT.CONSTTBLTEST);
else
Console.WriteLine("テーブル{0}の作成に失敗しました。", SSSPRODUCT.CONSTTBLTEST);
Console.WriteLine("何かキーを押すとプログラムを再開します。");
Console.ReadLine();
}
// **************************************************
// テーブル(TBLTEST)レコード追加
// ***************************************************
static void TblTestRecordAdd()
{
SqlServerSSSPRODUCT SSSPRODUCT = new SqlServerSSSPRODUCT();
List<TBLTESTRECORD> TblTestRec = new List<TBLTESTRECORD>();
TblTestRec.Add(new TBLTESTRECORD { Id = 10, Ruby = "いちろう", Name = "一郎" });
TblTestRec.Add(new TBLTESTRECORD { Id = 20, Ruby = "じろう", Name = "二郎" });
TblTestRec.Add(new TBLTESTRECORD { Id = 30, Ruby = "さぶろう", Name = "三郎" });
if (SSSPRODUCT.TBLTESTInsert(TblTestRec) == SSSPRODUCT.CONSTOK)
{
for (int i = 0; i < TblTestRec.Count; i++)
Console.WriteLine("id={0} ruby={1} name={2}", TblTestRec[i].Id, TblTestRec[i].Ruby, TblTestRec[i].Name);
Console.WriteLine("テーブル{0}に{1}レコード追加しました。", SSSPRODUCT.CONSTTBLTEST, TblTestRec.Count);
}
else
Console.WriteLine("テーブル{0}のレコード追加に失敗しました。", SSSPRODUCT.CONSTTBLTEST);
Console.WriteLine("何かキーを押すとプログラムを再開します。");
Console.ReadLine();
}
// **************************************************
// テーブル(TBLTEST)レコード抽出
// ***************************************************
static void TblTestSelect()
{
SqlServerSSSPRODUCT SSSPRODUCT = new SqlServerSSSPRODUCT();
List<TBLTESTRECORD> TblTestRec = new List<TBLTESTRECORD>();
TblTestRec = SSSPRODUCT.TBLTESTSelect(0, 100);
if (TblTestRec.Count < 1)
Console.WriteLine("テーブル{0}に検索対象となるレコードは存在しませんでした。", SSSPRODUCT.CONSTTBLTEST);
else
{
for (int i = 0; i < TblTestRec.Count; i++)
Console.WriteLine("id={0} ruby={1} name={2}", TblTestRec[i].Id, TblTestRec[i].Ruby, TblTestRec[i].Name);
Console.WriteLine("テーブル{0}の検索されたレコードは{1}件です。", SSSPRODUCT.CONSTTBLTEST, TblTestRec.Count);
}
Console.WriteLine("何かキーを押すとプログラムを再開します。");
Console.ReadLine();
}
// **************************************************
// テーブル(TBLTEST)アップデート
// ***************************************************
static void TblTestUpDate()
{
SqlServerSSSPRODUCT SSSPRODUCT = new SqlServerSSSPRODUCT();
List<TBLTESTRECORD> TblTestRec = new List<TBLTESTRECORD>();
string[] text = { "しばけん", "柴犬" };
if (SSSPRODUCT.TBLTESTUpdate(10, 20, text) == SSSPRODUCT.CONSTOK)
{
TblTestRec = SSSPRODUCT.TBLTESTSelect(0, 100);
for (int i = 0; i < TblTestRec.Count; i++)
Console.WriteLine("id={0} ruby={1} name={2}", TblTestRec[i].Id, TblTestRec[i].Ruby, TblTestRec[i].Name);
Console.WriteLine("テーブル{0}のアップデート後の結果です。", SSSPRODUCT.CONSTTBLTEST);
}
else
Console.WriteLine("テーブル{0}のアップデートに失敗しました。", SSSPRODUCT.CONSTTBLTEST);
Console.WriteLine("何かキーを押すとプログラムを再開します。");
Console.ReadLine();
}
// **************************************************
// テーブル(TBLTEST)デリート
// ***************************************************
static void TblTestDelete()
{
SqlServerSSSPRODUCT SSSPRODUCT = new SqlServerSSSPRODUCT();
List<TBLTESTRECORD> TblTestRec = new List<TBLTESTRECORD>();
if (SSSPRODUCT.TBLTESTDelete(20, 20) == SSSPRODUCT.CONSTOK)
{
TblTestRec = SSSPRODUCT.TBLTESTSelect(0, 100);
for (int i = 0; i < TblTestRec.Count; i++)
Console.WriteLine("id={0} ruby={1} name={2}", TblTestRec[i].Id, TblTestRec[i].Ruby, TblTestRec[i].Name);
Console.WriteLine("テーブル{0}から対象レコードを削除した結果です", SSSPRODUCT.CONSTTBLTEST);
}
else
Console.WriteLine("テーブル{0}のレコード削除に失敗しました。", SSSPRODUCT.CONSTTBLTEST);
Console.WriteLine("何かキーを押すとプログラムを再開します。");
Console.ReadLine();
}
}
}
各処理無いでSqlServerSSSPRODUCT クラスをインスタンス化して使用しています。
単純に作成したSqlServerSSSPRODUCT のプロパティやメソッドを呼んでいるだけです。
コメント