This code example includes code snippets that shows how to create various data sources at runtime.
Files to Look At
- EFDataSourceSnippets/Code.cs (VB: EFDataSourceSnippets/Code.vb)
- ExcelDataSourceSnippets/Code.cs (VB: ExcelDataSourceSnippets/Code.vb)
- ObjectDataSourceSnippets/Code.cs (VB: ObjectDataSourceSnippets/Code.vb)
- SqlDataSourceSnippets/Code.cs (VB: SqlDataSourceSnippets/Code.vb)
- JsonDataSourceSnippets/Code.cs (VB: JsonDataSourceSnippets/Code.vb)
- FederationDataSourceSnippets/Code.cs (VB: FederationDataSourceSnippets/Code.vb)
Documentation
More Examples
- Reporting for Web Forms - How to Register Data Sources for Use in the Web Report Designer
- How to Create a Report Bound to the SQL Data Source
- Reporting for ASP.NET Core - Inject Data from the Entity Framework Core DbContext into a Report Using the Object Data Source
- How to Use the Object Data Source in Web Reporting Applications
- How to Create a Report Bound to the ObjectDataSource with Constructor Parameters Linked to Report Parameters
- Dashboard for ASP.NET Core - How to create new JSON data sources at runtime
- Dashboard for WinForms - How to Bind a Dashboard to the JSON Data Source at Runtime
- Dashboard for ASP.NET Core - How to Register a Federated Data Source
Does this example address your development requirements/objectives?
(you will be redirected to DevExpress.com to submit your response)
Example Code
C#using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DevExpress.DataAccess.EntityFramework;
namespace EFDataSourceSnippets
{
public class Test
{
EFDataSource DataSource { get; set; }
public Test()
{
DataSource = InitializeEFDataSource();
}
EFDataSource InitializeEFDataSource()
{
return GenerateEFDataSource();
}
const string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=pwd;MultipleActiveResultSets=True;Application Name=EntityFramework";
private EFDataSource GenerateEFDataSource()
{
var connectionParameters = new DevExpress.DataAccess.EntityFramework.EFConnectionParameters(typeof(NorthwindEntities), "EntityConnection", connectionString);
var efDataSource = new DevExpress.DataAccess.EntityFramework.EFDataSource(connectionParameters);
((System.ComponentModel.ISupportInitialize)(efDataSource)).BeginInit();
efDataSource.StoredProcedures.Add(GetStoredProcedure());
((System.ComponentModel.ISupportInitialize)(efDataSource)).EndInit();
return efDataSource;
}
private EFStoredProcedureInfo GetStoredProcedure()
{
var efParameter1 = new DevExpress.DataAccess.EntityFramework.EFParameter("CustomerID", typeof(string), "ALFKI");
var efStoredProc = new DevExpress.DataAccess.EntityFramework.EFStoredProcedureInfo("CustOrderHist", new[] { efParameter1 });
return efStoredProc;
}
}
}
C#using DevExpress.DataAccess.Excel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ExcelDataSourceSnippets
{
public class Code
{
void ExcelDataSourceBindingToCSV() {
// Create a new Excel data source.
ExcelDataSource excelDataSource = new ExcelDataSource();
excelDataSource.FileName = "Northwind.csv";
// Specify import settings.
CsvSourceOptions csvSourceOptions = new CsvSourceOptions();
csvSourceOptions.DetectEncoding = true;
csvSourceOptions.DetectNewlineType = true;
csvSourceOptions.DetectValueSeparator = true;
excelDataSource.SourceOptions = csvSourceOptions;
}
void ExcelDataSourceBindingToXLS() {
// Create a new Excel data source.
ExcelDataSource excelDataSource = new ExcelDataSource();
excelDataSource.FileName = "Northwind.xlsx";
// Select a required worksheet.
ExcelWorksheetSettings excelWorksheetSettings = new ExcelWorksheetSettings();
excelWorksheetSettings.WorksheetName = "Sheet_Categories";
// Specify import settings.
ExcelSourceOptions excelSourceOptions = new ExcelSourceOptions();
excelSourceOptions.ImportSettings = excelWorksheetSettings;
excelSourceOptions.SkipHiddenRows = false;
excelSourceOptions.SkipHiddenColumns = false;
excelDataSource.SourceOptions = excelSourceOptions;
}
public static ExcelDataSource CreateExcelDataSource()
{
var excelDataSource = new ExcelDataSource() { Name = "Excel_Products" };
excelDataSource.FileName = "Products.xlsx";
excelDataSource.SourceOptions = new ExcelSourceOptions()
{
ImportSettings = new ExcelWorksheetSettings("Sheet"),
};
excelDataSource.RebuildResultSchema();
return excelDataSource;
}
}
}
C#using DevExpress.DataAccess.ObjectBinding;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ObjectDataSourceSnippets
{
public class BindingToParametrizedConstructor
{
void ObjectDataSourceInitialization()
{
ObjectDataSource objds = new ObjectDataSource();
objds.Name = "ObjectDataSource1";
objds.BeginUpdate();
objds.DataMember = "Items";
objds.DataSource = typeof(BusinessObject);
objds.EndUpdate();
//this line of code allows passing a parameter value to a parametrized constructor of an underlying data source object
var parameter = new DevExpress.DataAccess.ObjectBinding.Parameter("p1", typeof(int), 3);
objds.Constructor = new DevExpress.DataAccess.ObjectBinding.ObjectConstructorInfo(parameter);
}
public class BusinessObject
{
public SampleItems Items { get; set; }
public BusinessObject(int p1)
{
Items = new SampleItems(p1);
}
}
public class SampleItems : List<SampleItem>
{
public SampleItems() : this(10) { }
public SampleItems(int itemNumber)
{
for (int i = 0; i < itemNumber; i++)
{
Add(new SampleItem() { Name = i.ToString() });
}
}
}
public class SampleItem
{
public string Name { get; set; }
}
}
public class BindingToMethod
{
void ObjectDataSourceInitialization()
{
DevExpress.DataAccess.ObjectBinding.ObjectDataSource objds = new DevExpress.DataAccess.ObjectBinding.ObjectDataSource();
objds.Name = "ObjectDataSource1";
objds.BeginUpdate();
objds.DataMember = "GetData";
objds.DataSource = typeof(SampleItem);
objds.EndUpdate();
var parameter = new DevExpress.DataAccess.ObjectBinding.Parameter("value", typeof(int), 3);
objds.Parameters.Add(parameter);
//this line of code is required to obtain the data source object schema
objds.Fill();
}
public class SampleItem
{
public string Name { get; set; }
public static List<SampleItem> GetData(int value)
{
List<SampleItem> items = new List<SampleItem>();
for (int i = 0; i < value; i++)
{
items.Add(new SampleItem() { Name = i.ToString() });
}
return items;
}
}
}
}
C#using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.Sql;
namespace SqlDataSourceSnippets
{
public class Code
{
SqlDataSource DataSource { get; set; }
void SqlDataSourceInitialization()
{
//1)
var myConnectionName = "Northwind_Connection";
SqlDataSource DataSource = new SqlDataSource(myConnectionName);
//2)
//MsSqlConnectionParameters connectionParameters = new MsSqlConnectionParameters(".", "NWind", null, null, MsSqlAuthorizationType.Windows);
//SqlDataSource ds1 = new SqlDataSource(connectionParameters);
}
void SelectQueryCreation()
{
SelectQuery query = SelectQueryFluentBuilder
.AddTable("Categories")
.SelectColumn("CategoryName")
.GroupBy("CategoryName")
.Join("Products", "CategoryID")
.SelectColumn("ProductName", AggregationType.Count, "ProductCount")
.SortBy("ProductName", AggregationType.Count, System.ComponentModel.ListSortDirection.Descending)
.GroupFilter("[ProductCount] > 7")
.Build("Categories");
DataSource.Queries.Add(query);
QueryParameterInitialization(query);
}
void StoredProcedureInitialization()
{
StoredProcQuery spQuery = new StoredProcQuery("StoredProcedure", "stored_procedure_name");
spQuery.Parameters.Add(new QueryParameter("@First", typeof(int), 0));
spQuery.Parameters.Add(new QueryParameter("@Second", typeof(string), "Value"));
spQuery.Parameters.Add(new QueryParameter("@Third", typeof(string), "Value"));
DataSource.Queries.Add(spQuery);
}
void QueryParameterInitialization(SelectQuery query)
{
QueryParameter parameter = new QueryParameter()
{
Name = "catID",
Type = typeof(DevExpress.DataAccess.Expression),
Value = new DevExpress.DataAccess.Expression("[Parameters.catID]", typeof(System.Int32))
};
query.Parameters.Add(parameter);
query.FilterString = "CategoryID = ?catID";
}
void CustomSqlQueryInitialization()
{
CustomSqlQuery query = new CustomSqlQuery();
query.Name = "CustomQuery";
query.Sql = "Select top 10 * from Products";
DataSource.Queries.Add(query);
}
void RelationshipInitialization()
{
SelectQuery categories = SelectQueryFluentBuilder.AddTable("Categories").SelectAllColumns().Build("Categories");
SelectQuery products = SelectQueryFluentBuilder.AddTable("Products").SelectAllColumns().Build("Products");
DataSource.Queries.AddRange(new SqlQuery[] { categories, products });
DataSource.Relations.Add(new MasterDetailInfo("Categories", "Products", "CategoryID", "CategoryID"));
}
public static SqlDataSource CreateSqlDataSource()
{
MsSqlConnectionParameters connectionParameters = new MsSqlConnectionParameters(".", "NorthWind", null, null, MsSqlAuthorizationType.Windows);
var sqlDataSource = new SqlDataSource(connectionParameters) { Name = "Sql_Categories" };
var categoriesQuery = SelectQueryFluentBuilder.AddTable("Categories").
SelectAllColumnsFromTable().
Build("Categories");
sqlDataSource.Queries.Add(categoriesQuery);
sqlDataSource.RebuildResultSchema();
return sqlDataSource;
}
}
}
C#using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace JsonDataSourceSnippets
{
public class Code
{
void GenerateJsonDataSource()
{
DevExpress.DataAccess.Json.JsonDataSource jsonDataSource = new DevExpress.DataAccess.Json.JsonDataSource();
Uri sourceUri = new Uri("https://raw.githubusercontent.com/DevExpress-Examples/DataSources/master/JSON/customers.json");
jsonDataSource.JsonSource = new DevExpress.DataAccess.Json.UriJsonSource(sourceUri);
jsonDataSource.Fill();
}
}
}
C#using DevExpress.DataAccess.DataFederation;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace FederationDataSourceSnippets
{
public class Code
{
void CreateFederationDataSource()
{
var sqlDataSource = SqlDataSourceSnippets.Code.CreateSqlDataSource();
var excelDataSource = ExcelDataSourceSnippets.Code.CreateExcelDataSource();
// Create the federated query's SQL and Excel sources.
Source sqlSource = new Source(sqlDataSource.Name, sqlDataSource, "Categories");
Source excelSource = new Source(excelDataSource.Name, excelDataSource, "");
// Create a federated query.
var selectNode = sqlSource.From()
// Select the "CategoryName" column from the SQL source.
.Select("CategoryName")
// Join the SQL source with the Excel source based on the "CategoryID" key field.
.Join(excelSource, "[Excel_Products.CategoryID] = [Sql_Categories.CategoryID]")
// Select columns from the Excel source.
.Select("CategoryID", "ProductName", "UnitPrice")
// Specify the query's name and build it.
.Build("CategoriesProducts");
// Create a federated data source and add the federated query to the collection.
var federationDataSource = new FederationDataSource();
federationDataSource.Queries.Add(selectNode);
// Build the data source schema to display it in the Field List.
federationDataSource.RebuildResultSchema();
}
}
}