Example T424210
Visible to All Users

Data Access Library - How to Create Data Sources at Runtime

This code example includes code snippets that shows how to create various data sources at runtime.

Files to Look At

Documentation

More Examples

Does this example address your development requirements/objectives?

(you will be redirected to DevExpress.com to submit your response)

Example Code

EFDataSourceSnippets/Code.cs(vb)
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; } } }
ExcelDataSourceSnippets/Code.cs(vb)
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; } } }
ObjectDataSourceSnippets/Code.cs(vb)
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; } } } }
SqlDataSourceSnippets/Code.cs(vb)
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; } } }
JsonDataSourceSnippets/Code.cs(vb)
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(); } } }
FederationDataSourceSnippets/Code.cs(vb)
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(); } } }

Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.