【WindowsForm備忘録】SQLServerの操作

WindowsFormでデータベースの操作ができるので、次の就職先に向けた勉強を実施。
kintame.site

SQLServerでデータベース作成

WindowsFormの操作

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace TestDatabase
{
    public partial class Fm_SQLServerConnection : Form
    {
        public static string ServerName;
        public static string UserName;
        public static string Password;

        public Fm_SQLServerConnection()
        {
            InitializeComponent();
        }

        private void Fm_SQLServerConnection_Load(object sender, EventArgs e)
        {

        }

        private void radioButton1_CheckedChanged(object sender, EventArgs e)
        {
            UserNameTxtBox.ReadOnly = true;
            PasswordTxtBox.ReadOnly = true;
        }

        private void radioButton2_CheckedChanged(object sender, EventArgs e)
        {
            UserNameTxtBox.ReadOnly = false;
            PasswordTxtBox.ReadOnly = false;
        }

        //https://kintame.site/cardapp2/

        private void BtnConnection_Click(object sender, EventArgs e)
        {
            try
            {
                //Windows認証
                ServerName = ServerNameTxtBox.Text;
                //SQLServer認証
                if (radioButton2.Checked)
                {
                    UserName = UserNameTxtBox.Text;
                    Password = PasswordTxtBox.Text;
                }

                string sConnectionString = SQLMethod.SQLConnect(ServerName, UserName, Password); //※後ほど作成します。
                Console.WriteLine(sConnectionString);
                using (var conn = new SqlConnection(sConnectionString))
                {
                    conn.Open();
                }
                MessageBox.Show("接続が完了しました。");
                this.DialogResult = System.Windows.Forms.DialogResult.OK; //後ほど解説します。

            }
            catch (SqlException error)
            {
                MessageBox.Show(error.Message);
                MessageBox.Show("接続に失敗しました。");
            }
            catch (Exception error)
            {
                MessageBox.Show(error.Message);
                MessageBox.Show("予期せぬ例外が発生しました。");
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            textBox1.Text =  SQLMethod.SQLInsert("Cards_Table", "id,name", "'3','tomo1'");
        }

        private void button2_Click(object sender, EventArgs e)
        {
            textBox1.Text= SQLMethod.SQLUpdate("Cards_Table", "name='kana'", "id='3'");
        }

        private void button3_Click(object sender, EventArgs e)
        {
            SQLMethod.SQLSelect("*", "Cards_Table", "id='2'");
        }

        private void button4_Click(object sender, EventArgs e)
        {
            SQLMethod.SQLDelete("Cards_Table", "id='2'");
        }
    }
}

SQL操作用のクラス作成

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace TestDatabase
{
    class SQLMethod
    {
        public static string SQLConnect(string Source)
        {
            string dataSource = Source;
            string initCatalog = "Cards_DB";
            string connectionString = "";

            //Windows認証の接続文字列
            connectionString = @"Server=" + dataSource + @";Database="
                                    + initCatalog + ";Integrated Security=True;";

            return connectionString;
        }

        public static string SQLConnect(string Source, string UserName = "", string PW = "")
        {
            string dataSource = Source;
            string initCatalog = "Cards_DB";
            string connectionString = "";

            if (string.IsNullOrEmpty(PW))
            {
                //Windows認証の接続文字列
                connectionString = @"Server=" + dataSource + @";Database="
                                        + initCatalog + ";Integrated Security=True;";
            }
            else
            {
                //SQLServer認証の接続文字列
                string userId = UserName;
                string passWord = PW;

                connectionString = @"Server=" + dataSource + @";Database="
                                        + initCatalog + @";User ID="
                                        + userId + @";Password=" + passWord + @";";
            }
            return connectionString;
        }

        public static string SQLInsert(string sTable, string sField, string sValue)
        {
            try
            {
                using (var conn = new SqlConnection(SQLConnect(Fm_SQLServerConnection.ServerName, Fm_SQLServerConnection.UserName, Fm_SQLServerConnection.Password)))
                using (var command = conn.CreateCommand())
                {
                    //接続を確立
                    conn.Open();

                    //INSERT文の準備
                    command.CommandText =
                        string.Format("INSERT INTO {0} ({1}) VALUES ({2});", sTable, sField, sValue);

                    Console.WriteLine(command.CommandText);

                    //SQLの実行
                    command.ExecuteNonQuery();          
                    
                    conn.Close();                 

                    MessageBox.Show("名刺をデータベースに追加しました。");

                    return command.CommandText;
                }
            }
            catch (SqlException error)
            {
                MessageBox.Show(error.ToString());
                return "";
            }
            catch (Exception error)
            {
                MessageBox.Show(error.ToString());
                return "";
            }
        }

        public static string SQLSelect(string sField, string sTable, string sWhere = "", string sOrderby = "")
        {
            try
            {
                using (var conn = new SqlConnection(SQLConnect(Fm_SQLServerConnection.ServerName, Fm_SQLServerConnection.UserName, Fm_SQLServerConnection.Password)))
                using (var command = conn.CreateCommand())
                {
                    //接続を確立
                    conn.Open();

                    //INSERT文の準備
                    command.CommandText =
                                            string.Format("SELECT {0} FROM {1}", sField, sTable);
                    if (!sWhere.Equals(""))
                    {
                        command.CommandText += string.Format(" WHERE {0}", sWhere);
                    }
                    if (!sOrderby.Equals(""))
                    {
                        command.CommandText += string.Format(" ORDER BY {0}", sOrderby);
                    }

                    Console.WriteLine(command.CommandText);

                    //SQLの実行
                    command.ExecuteNonQuery();

                    SqlDataReader reader = command.ExecuteReader();
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine(reader["id"] + ":"
                                            + reader["name"] + ":"
                                            );
                        }
                    }

                    conn.Close();

                    return command.CommandText;
                }


            }
            catch (SqlException error)
            {
                MessageBox.Show(error.ToString());
                return "";
            }
            catch (Exception error)
            {
                MessageBox.Show(error.ToString());
                return "";
            }
        }

        public static string SQLUpdate(string sTable, string sField, string sWhere = "", string sOrderby = "")
        {
            try
            {
                using (var conn = new SqlConnection(SQLConnect(Fm_SQLServerConnection.ServerName, Fm_SQLServerConnection.UserName, Fm_SQLServerConnection.Password)))
                using (var command = conn.CreateCommand())
                {
                    //接続を確立
                    conn.Open();

                    //Update文の準備
                    command.CommandText =
                        string.Format("UPDATE {0} SET {1}", sTable, sField);
                    if (!sWhere.Equals(""))
                    {
                        command.CommandText += " WHERE " + sWhere.ToString();
                    }
                    if (!sOrderby.Equals(""))
                    {
                        command.CommandText += " ORDER BY " + sOrderby.ToString();
                    }

                    Console.WriteLine(command.CommandText);

                    //SQLの実行
                    command.ExecuteNonQuery();

                    conn.Close();

                    MessageBox.Show("登録内容を更新しました。");

                    return command.CommandText;
                }
            }
            catch (SqlException error)
            {
                MessageBox.Show(error.ToString());
                return "";
            }
            catch (Exception error)
            {
                MessageBox.Show(error.ToString());
                return "";
            }
        }

        public static void SQLDelete(string sTable, string sWhere = "", string sOrderby = "")
        {
            try
            {
                DialogResult dr = MessageBox.Show("本当に削除しますか？", "確認", MessageBoxButtons.YesNo);

                if (dr == DialogResult.Yes)
                {
                    using (var conn = new SqlConnection(SQLConnect(Fm_SQLServerConnection.ServerName, Fm_SQLServerConnection.UserName, Fm_SQLServerConnection.Password)))
                    using (var command = conn.CreateCommand())
                    {
                        //接続を確立
                        conn.Open();

                        //DELETE文の準備
                        command.CommandText =
                            string.Format("DELETE FROM {0} ", sTable);

                        if (!sWhere.Equals(""))
                        {
                            command.CommandText += string.Format("WHERE {0}", sWhere);
                        }
                        if (!sOrderby.Equals(""))
                        {
                            command.CommandText += string.Format("ORDER BY {0}", sOrderby);
                        }
                        //SQLの実行
                        command.ExecuteNonQuery();

                        conn.Close();

                        MessageBox.Show("名刺をデータベースに追加しました。");
                    }
                }
            }
            catch (SqlException error)
            {
                MessageBox.Show(error.ToString());
            }
            catch (Exception error)
            {
                MessageBox.Show(error.ToString());
            }
        }
    }

}