🗄️ Data Management Examples

Real-world examples from BeepSharedFunctions.cs demonstrating direct data management patterns

📚 Table of Contents

Overview

This page showcases actual data management patterns from the Beep.WinForm.Sample.App project. These examples demonstrate how to work directly with the data management engine without complex service orchestration.

🎯 Focus

These examples show pure data management operations - creating connections, working with different data sources, and moving data between sources.

🔗 Connection Creation

Dynamic creation of database and file connections

⚙️ Driver Configuration

Automatic driver detection and configuration

📊 Data Source Management

Opening, closing, and working with multiple data sources

🔄 Entity Operations

Getting entity lists, structures, and data

🚀 Data Movement

Moving data between different source types

🖥️ UI Integration

Real Windows Forms integration patterns

🔗 Connection Creation Examples

Based on BeepSharedFunctions.cs - real working examples from the sample application.

SQLite Database Connection

public static bool CreateConnectionForSqlite()
{
    CloseConnections();
    
    // Get the connection drivers configuration
    ConnectionDriversConfig config = GetConnectionDriversConfig(DataSourceType.SqlLite);
    
    // Create a new connection configuration
    ConnectionProperties connectionProperties = new ConnectionProperties
    {
        ConnectionString = "Data Source=./Beep/dbfiles/northwind.db",
        ConnectionName = "northwind.db",
        DriverName = config.PackageName,
        DriverVersion = config.version,
        DatabaseType = DataSourceType.SqlLite,
        Category = DatasourceCategory.RDBMS
    };
    
    // Add to the list of connections
    dmeEditor.ConfigEditor.AddDataConnection(connectionProperties);
    
    // Create the connection
    Sqlite_SampleDB = (SQLiteDataSource?)dmeEditor.GetDataSource("northwind.db");
    
    // Open the connection
    Sqlite_SampleDB.Openconnection();

    if (Sqlite_SampleDB.ConnectionStatus == System.Data.ConnectionState.Open)
    {
        return true;
    }
    else
    {
        MessageBox.Show("Connection Failed");
        return false;
    }
}

Excel File Connection

public static bool CreateConnectionForXls()
{
    CloseConnections();
    
    // Get the connection drivers configuration
    ConnectionDriversConfig config = GetConnectionDriversConfig(DataSourceType.Xls);
    
    // Create a new connection configuration
    ConnectionProperties connectionProperties = new ConnectionProperties
    {
        FileName = "country.xls",
        FilePath = "./dbfiles",
        ConnectionName = "country.xls",
        DriverName = config.PackageName,
        DriverVersion = config.version,
        DatabaseType = DataSourceType.Xls,
        Ext = "xls",
        Category = DatasourceCategory.FILE
    };
    
    // Add to the list of connections
    dmeEditor.ConfigEditor.AddDataConnection(connectionProperties);
    
    // Create the connection
    XlsFile = (TxtXlsCSVFileSource?)dmeEditor.GetDataSource("country.xls");
    
    // Open the connection
    XlsFile.Openconnection();

    if (XlsFile.ConnectionStatus == System.Data.ConnectionState.Open)
    {
        return true;
    }
    else
    {
        MessageBox.Show("Connection Failed");
        return false;
    }
}

CSV File Connection

public static bool CreateConnectionForCSV()
{
    CloseConnections();
    
    // Get the connection drivers configuration
    ConnectionDriversConfig config = GetConnectionDriversConfig(DataSourceType.CSV);
    
    // Create a new connection configuration
    ConnectionProperties connectionProperties = new ConnectionProperties
    {
        FileName = "iris.csv",
        FilePath = "./dbfiles",
        ConnectionName = "iris.csv",
        DriverName = config.PackageName,
        DriverVersion = config.version,
        DatabaseType = DataSourceType.CSV,
        Ext = "csv",
        Category = DatasourceCategory.FILE
    };
    
    // Add to the list of connections
    dmeEditor.ConfigEditor.AddDataConnection(connectionProperties);
    
    // Create the connection
    CSVFile = (CSVDataSource?)dmeEditor.GetDataSource("iris.csv");
    
    // Open the connection
    CSVFile.Openconnection();

    if (CSVFile.ConnectionStatus == System.Data.ConnectionState.Open)
    {
        return true;
    }
    else
    {
        MessageBox.Show("Connection Failed");
        return false;
    }
}

Driver Configuration Helper

public static ConnectionDriversConfig GetConnectionDriversConfig(DataSourceType type)
{
    // Get the list of connection drivers
    return dmeEditor.ConfigEditor.DataDriversClasses
        .FirstOrDefault(p => p.DatasourceType == type);
}
✅ Connection Pattern Benefits
  • Dynamic Configuration: Automatically detects and configures drivers
  • Type Safety: Strongly typed data source references
  • Resource Management: Proper connection lifecycle management
  • Error Handling: Built-in connection status validation

💾 Data Operations

Entity Management

// Get all entities from a data source
var dataSource = dmeEditor.GetDataSource("northwind.db");
dataSource.Openconnection();

if (dataSource.ConnectionStatus == System.Data.ConnectionState.Open)
{
    // Get list of all entities (tables)
    var entities = dataSource.GetEntitesList();
    
    foreach (string entity in entities)
    {
        Console.WriteLine(entity);
    }
}
else
{
    MessageBox.Show("Connection Failed");
}

Data Retrieval

// Retrieving data using a SQL query
var dataSource = dmeEditor.GetDataSource("northwind.db");
dataSource.Openconnection();

if (dataSource.ConnectionStatus == System.Data.ConnectionState.Open)
{
    string sql = "SELECT * FROM Customers";
    var resultSet = dataSource.ExecuteSQL(sql);
    
    foreach (var row in resultSet)
    {
        Console.WriteLine(row["CustomerID"] + " - " + row["CompanyName"]);
    }
}
else
{
    MessageBox.Show("Connection Failed");
}

Data Insertion

// Inserting data into a table
var dataSource = dmeEditor.GetDataSource("northwind.db");
dataSource.Openconnection();

if (dataSource.ConnectionStatus == System.Data.ConnectionState.Open)
{
    string sql = "INSERT INTO Customers (CustomerID, CompanyName) VALUES ('ALFKI', 'Alfreds Futterkiste')";
    dataSource.ExecuteSQL(sql);
}
else
{
    MessageBox.Show("Connection Failed");
}

Data Update

// Updating data in a table
var dataSource = dmeEditor.GetDataSource("northwind.db");
dataSource.Openconnection();

if (dataSource.ConnectionStatus == System.Data.ConnectionState.Open)
{
    string sql = "UPDATE Customers SET ContactName = 'Alfred' WHERE CustomerID = 'ALFKI' ";
    dataSource.ExecuteSQL(sql);
}
else
{
    MessageBox.Show("Connection Failed");
}

Data Delete

// Deleting data from a table
var dataSource = dmeEditor.GetDataSource("northwind.db");
dataSource.Openconnection();

if (dataSource.ConnectionStatus == System.Data.ConnectionState.Open)
{
    string sql = "DELETE FROM Customers WHERE CustomerID = 'ALFKI' ";
    dataSource.ExecuteSQL(sql);
}
else
{
    MessageBox.Show("Connection Failed");
}
✅ Data Operation Benefits
  • Simplicity: Direct and easy-to-understand data manipulations
  • Flexibility: Works with various data sources and structures
  • Efficiency: Lightweight operations with minimal overhead
  • Integration: Seamlessly integrates with application logic

🔄 Shared Functions Pattern

BeepSharedFunctions Class Structure

public static class BeepSharedFunctions
{
    // Strongly typed data sources
    public static SQLiteDataSource Sqlite_SampleDB;
    public static TxtXlsCSVFileSource XlsFile;
    public static CSVDataSource CSVFile;
    public static IDMEEditor dmeEditor;

    // Generic data source references for operations
    public static IDataSource DestinationDataSource;
    public static IDataSource SourceDataSource;

    public static void CloseConnections()
    {
        if (Sqlite_SampleDB != null)
        {
            Sqlite_SampleDB.Closeconnection();
        }
        if (XlsFile != null)
        {
            XlsFile.Closeconnection();
        }
        if (CSVFile != null)
        {
            CSVFile.Closeconnection();
        }
    }
}

Usage of Shared Functions

// Using the shared functions for data operations
BeepSharedFunctions.CreateConnectionForSqlite();
var dataSource = BeepSharedFunctions.Sqlite_SampleDB;
✅ Shared Functions Pattern Benefits
  • Reusability: Common functions can be reused across the application
  • Maintainability: Centralized updates and maintenance for data operations
  • Readability: Clear and descriptive function names improve code readability
  • Efficiency: Reduces code duplication and improves performance

🖥️ UI Integration Examples

Examples of how data management operations are integrated into the UI layer of the application.

Form Data Binding

// Binding data to a WinForms DataGridView
var dataSource = dmeEditor.GetDataSource("northwind.db");
dataSource.Openconnection();

if (dataSource.ConnectionStatus == System.Data.ConnectionState.Open)
{
    string sql = "SELECT * FROM Customers";
    var resultSet = dataSource.ExecuteSQL(sql);
    
    dataGridView1.DataSource = resultSet;
}
else
{
    MessageBox.Show("Connection Failed");
}

Form Controls Update

// Updating form controls based on data source
var dataSource = dmeEditor.GetDataSource("northwind.db");
dataSource.Openconnection();

if (dataSource.ConnectionStatus == System.Data.ConnectionState.Open)
{
    string sql = "SELECT TOP 1 * FROM Customers";
    var row = dataSource.ExecuteSQL(sql).FirstOrDefault();
    
    if (row != null)
    {
        textBoxCustomerID.Text = row["CustomerID"].ToString();
        textBoxCompanyName.Text = row["CompanyName"].ToString();
    }
}
else
{
    MessageBox.Show("Connection Failed");
}

Data Submission

// Submitting data from a form to the database
var dataSource = dmeEditor.GetDataSource("northwind.db");
dataSource.Openconnection();

if (dataSource.ConnectionStatus == System.Data.ConnectionState.Open)
{
    string sql = "INSERT INTO Customers (CustomerID, CompanyName) VALUES (@CustomerID, @CompanyName)";
    var parameters = new { CustomerID = "NEWID", CompanyName = "New Company" };
    dataSource.ExecuteSQL(sql, parameters);
}
else
{
    MessageBox.Show("Connection Failed");
}

Loading Data Sources into ComboBoxes

public MovingData(IDMEEditor editor)
{
    dmeEditor = editor;
    InitializeComponent();
    
    BeepSharedFunctions.dmeEditor = dmeEditor;
    
    // Load file-based data sources
    foreach (var item in dmeEditor.ConfigEditor.DataConnections
        .Where(p => p.Category == DatasourceCategory.FILE))
    {
        SourceDataSourcescomboBox.Items.Add(item.ConnectionName);
    }
    
    // Load database data sources
    foreach (var item in dmeEditor.ConfigEditor.DataConnections
        .Where(p => p.Category != DatasourceCategory.FILE))
    {
        DestinationDataSourcecomboBox1.Items.Add(item.ConnectionName);
    }
}

Handling Data Source Selection

private void SourceDataSourcescomboBox_SelectedIndexChanged(object sender, EventArgs e)
{
    if (SourceDataSourcescomboBox.SelectedItem != null)
    {
        // Get the selected data source
        BeepSharedFunctions.SourceDataSource = dmeEditor
            .GetDataSource(SourceDataSourcescomboBox.SelectedItem.ToString());
        
        if (BeepSharedFunctions.SourceDataSource != null)
        {
            BeepSharedFunctions.SourceDataSource.Openconnection();
            
            if (BeepSharedFunctions.SourceDataSource.ConnectionStatus == 
                System.Data.ConnectionState.Open)
            {
                // Load entities for this data source
                LoadEntitiesForSource();
            }
        }
    }
}

Entity Operations in UI

private void DeleteButton_Click(object sender, EventArgs e)
{
    if (DestinationDataSourcecomboBox1.SelectedItem != null && 
        DestEntitiescomboBox.SelectedItem != null)
    {
        BeepSharedFunctions.DestinationDataSource = dmeEditor
            .GetDataSource(DestinationDataSourcecomboBox1.SelectedItem.ToString());
        
        if (BeepSharedFunctions.DestinationDataSource != null)
        {
            BeepSharedFunctions.DestinationDataSource.Openconnection();
            
            if (BeepSharedFunctions.DestinationDataSource.ConnectionStatus == 
                System.Data.ConnectionStatus.Open)
            {
                // Execute drop table command
                var result = BeepSharedFunctions.DestinationDataSource
                    .ExecuteSql($"Drop Table {DestEntitiescomboBox.SelectedItem}");
                
                if (result.Flag == Errors.Ok)
                {
                    LogtextBox.AppendText($"Table {DestEntitiescomboBox.SelectedItem} Deleted" + 
                        Environment.NewLine);
                    
                    // Refresh entity list
                    BeepSharedFunctions.DestinationDataSource.GetEntitesList();
                }
            }
        }
    }
}
✅ UI Integration Benefits
  • Seamless: Smooth integration between data and UI layers
  • Responsive: Immediate UI updates based on data changes
  • User-friendly: Intuitive interfaces that reflect real-time data
  • Efficient: Optimized data loading and submission processes

Best Practices

Recommendations and best practices for effective data management using the Beep Data Management Engine.

🔑 Key Recommendations
  • Use connection pooling for better performance
  • Always close connections to release resources
  • Prefer using transactions for batch operations
  • Validate and sanitize all external inputs
🛑 Important Considerations
  • Be aware of the security implications of data access
  • Monitor and optimize performance regularly
  • Ensure compatibility of data types across sources