How can I bind an SQLite database table as a data source and perform CRUD operations in SfDataGrid?

The .NET MAUI DataGrid control can be bound to an external data source to display data in a tabular format. It supports data sources such as List, IEnumerable, and more. The SfDataGrid.ItemsSource property helps to bind this control with a collection of objects.

Refer to the following code example, which illustrates how to bind an SQLite database table as a data source and perform CRUD operations in SfDataGrid.

xaml:

<StackLayout>
   <Button Text="Add Row in DataBase" Clicked="AddRowButtonClicked"/>
   <Button Text="Remove Row in DataBase" Clicked="RemoveButtonClicked"/>
   <dataGrid:SfDataGrid x:Name="dataGrid"
                     SelectionMode="Single" NavigationMode="Cell">
</dataGrid:SfDataGrid>
</StackLayout>

xaml.cs:

public static SampleDemoDatabase sampleDemoDatabase;
public static SampleDemoDatabase SampleDemoDatabase
{
   get
   {
       if (sampleDemoDatabase == null)
           sampleDemoDatabase = new SampleDemoDatabase();
       return sampleDemoDatabase;
   }
}
public MainPage()
{
   InitializeComponent();
   dataGrid.ItemsSource = SampleDemoDatabase.OrderItemsDataSource;

}

// To add the row in the database and get the updated data in the data grid
public void AddRowButtonClicked(object sender, EventArgs e)
{
   SampleDemoDatabase.AddItem(new OrderItem { OrderID = 1004, Name = "Testing", TokenNo = 2023, BillStatus = "PAID" });
   
   // To get the updated data from the database
   SampleDemoDatabase.GetItems();

   // Set the updated data in the DataGrid
   dataGrid.ItemsSource = SampleDemoDatabase.OrderItemsDataSource;
}

// To remove the row from the database and get the updated data in the data grid
public void RemoveButtonClicked(object sender, EventArgs e)
{
   // To get the last row ID from the database
   int DeleteID = SampleDemoDatabase.OrderItemsDataSource.Last().ID;

   // To remove the last row from the database
   SampleDemoDatabase.DeleteItem(DeleteID);

   // To get the updated data from the database
   SampleDemoDatabase.GetItems();

   // Set the updated data in the DataGrid
   dataGrid.ItemsSource = SampleDemoDatabase.OrderItemsDataSource;
}

ViewModel:

private ObservableCollection<OrderItem> orderItemsDataSource;
public ObservableCollection<OrderItem> OrderItemsDataSource
{
   get => this.orderItemsDataSource;
   set
   {
       this.orderItemsDataSource = value;
       
   }
}
SQLiteConnection database;

public const string DatabaseFilename = "TestDataBase.db3";
      
public SampleDemoDatabase()
{
   string dbPath = Path.Combine(FileSystem.AppDataDirectory, DatabaseFilename);

   // Get the SQLite database connection
   database = new SQLiteConnection(dbPath);

   // Create the table if it doesn't exist
   database.CreateTable<OrderItem>();

   // Insert sample data into the database
   InsertSampleData();

   // Get the sample data from the database
   GetItems();

}

public void InsertSampleData()
{
   // Sample data
   var sampleItems = new List<OrderItem>
   {
       new OrderItem { OrderID = 1001, Name = "Patient01", TokenNo = 1501, BillStatus = "PAID" },
       new OrderItem { OrderID = 1002, Name = "Patient02", TokenNo = 1502, BillStatus = "NOT PAID" },
       new OrderItem { OrderID = 1003, Name = "Patient03", TokenNo = 1503, BillStatus = "PAID" },
       // Add more sample items as needed
   };

   foreach (var item in sampleItems)
   {
       try
       {
           database.Insert(item);
       }
       catch (SQLiteException ex)
       {
           // Handle exceptions, e.g., duplicate insertions
           Console.WriteLine($"SQLiteException: {ex.Message}");
       }
   }
}

// To add an item to the databaseb 
public void AddItem(OrderItem item)
{
   database.Insert(item);
}

// To delete an item from the database
public void DeleteItem(int id)
{
   database.Delete(new OrderItem { ID =id});
}

public void GetItems()
{
       var table = database.Table<OrderItem>().ToList();
       OrderItemsDataSource= new ObservableCollection<OrderItem>(table);
}

Download the complete sample from GitHub

Conclusion

I hope you enjoyed learning how to bind an SQLite database table as a data source and perform CRUD operations in SfDataGrid.

You can refer to our .NET MAUI DataGrid feature tour page to learn about its other groundbreaking feature representations. Explore our .NET MAUI DataGrid Documentation to understand how to present and manipulate data.
For current customers, check out our .NET MAUI components on the License and Downloads page. If you are new to Syncfusion®, try our 30-day free trial to explore our .NET MAUI DataGrid and other .NET MAUI components.

Please let us know in the comments section if you have any queries or require clarification. You can also contact us through our support forums, Direct-Trac or feedback portal, or the feedback portal. We are always happy to assist you!