【筆記】ASP.NET 資料庫連結 (Insert/Update, Delete, Search Data)

最近的一直在重覆這些動作, 直接整理成 Function, 塞入 Command 就可以用比較方便

(1) Insert/Update

private void DB_InsertUpdateData(string DB, string Command)
{
    SqlConnection conn;
    SqlCommand com = new SqlCommand();
    SqlTransaction transaction;

    try
    {
        conn = new SqlConnection(get_connect_string(DB));
        conn.Open();
        com.Connection = conn;
        transaction = conn.BeginTransaction();
        com.CommandText = Command;
        try
        {
            com.Transaction = transaction;
            com.ExecuteNonQuery();
            transaction.Commit();
        }
        catch
        {
            com.Cancel();
            transaction.Rollback();
        }
        conn.Close();
        conn.Dispose();
    }
    catch
    {
        com.Cancel();
    }
}

(2)Delete

private void DB_DeleteData(string DB, string Command)
{
    SqlConnection conn;
    SqlCommand com = new SqlCommand();

    try
    {
        conn = new SqlConnection(get_connect_string(DB));
        conn.Open();

        com.Connection = conn;
        CommandText = Command;
        com.CommandText = CommandText;
        com.ExecuteNonQuery();

        conn.Close();
        conn.Dispose();
    }
    catch
    {
        com.Cancel();
    }
}

(3)Search

private void DB_SearchData(string DB,string SearchCommand, DataSet table)
{
    SqlConnection conn;
    SqlCommand com = new SqlCommand();
    SqlDataAdapter SqlAdapter = new SqlDataAdapter();

    try
    {
        conn = new SqlConnection(get_connect_string(DB));
        conn.Open();
        try
        {
            com.Connection = this.conn;
            com.CommandText = SearchCommand;
            SqlAdapter.SelectCommand = com;
            table.Locale = System.Globalization.CultureInfo.InvariantCulture;
            SqlAdapter.Fill(table);
        }
        catch
        {
            com.Cancel();
        }
    }
    finally
    {
        com.Cancel();
        conn.Close();
        conn.Dispose();
    }
}