Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I am not sure I understand your restrictions in using a DataTable, but one thing I've done in the past when I needed to "refresh" the data in a DataTable or repopulate it using different criteria is to create a new class derived from DataTable that includes a reference to a DataAdapter with the connection and selection information originally used to fill the DataTable.</p> <p>For example, the DataTable sub-class could look something like the <code>LazyDataTable</code> code below. Note that I have added several different methods of accessing the Rows. They might make more sense after taking a look at the <code>PermissionSource</code> and main <code>Program</code> code near the end of this post. <em>Also, please note that I have not included all of the details related to properly opening and closing database connections in every case. How you handle that will depend on your model for database access (e.g. connection pooling, shared connections etc).</em></p> <pre><code>//using System.Data.Common; public class LazyDataTable : DataTable { protected DbDataAdapter Adapter { get; set; } public LazyDataTable(DbDataAdapter a) { Adapter = a; } /// &lt;summary&gt; /// Save changes back to the database, using the DataAdapter /// &lt;/summary&gt; public void Update() { Adapter.Update(this); } /// &lt;summary&gt; /// Fill this datatable using the SelectCommand in the DataAdapter /// The DB connection and query have already been set. /// &lt;/summary&gt; public void Fill() { Adapter.Fill(this); } /// &lt;summary&gt; /// read and return one row at a time, using IEnumerable syntax /// (this example does not actually add the row to this table, /// but that can be done as well, if desired. /// &lt;/summary&gt; public IEnumerable&lt;DataRow&gt; LazyReadRows() { using (var reader = OpenReader()) { //Get the schema from the reader and copy it to this table. var schema = reader.GetSchemaTable(); var values = new object[schema.Columns.Count]; while (reader.Read()) { reader.GetValues(values); var row = schema.NewRow(); row.ItemArray = values; yield return row; } } } /// &lt;summary&gt; /// Fill one row at a time, and return the new row. /// &lt;/summary&gt; public DataRow ReadRow() { if (_reader == null || _reader.IsClosed) _reader = OpenReader(); //Get the schema from the reader and copy it to this table. if (this.Columns.Count == 0) this.Columns.AddRange(_reader.GetSchemaTable().Columns.Cast&lt;DataColumn&gt;().ToArray()); if (!_reader.Read()) { _reader.Dispose(); return null; } var values = new object[_reader.FieldCount]; _reader.GetValues(values); return this.Rows.Add(values); } private DbDataReader _reader = null; private DbDataReader OpenReader() { OpenConnect(); return Adapter.SelectCommand.ExecuteReader(); } private void OpenConnect() { var cn = Adapter.SelectCommand.Connection; if (cn.State == ConnectionState.Closed) cn.Open(); } /// &lt;summary&gt; /// Change a Parameter in the SelectCommand, to filter which rows to retrieve. /// &lt;/summary&gt; public void SetSelectParam(string name, object value) { var selparams = Adapter.SelectCommand.Parameters; selparams[name].Value = value; } } </code></pre> <p>Then your <code>PermissionSource</code> would create a <code>LazyDataTable</code> and set the DataAdapter (including the connection and SELECT command) appropriately. It wouldn't fill the DataTable, but would instead return it empty, to be filled later, by the application code. So your <code>PermissionSource</code> might something like the code below. I've used <code>System.Data.OleDb</code> data objects as an example, but you would use whatever ADO providers you want.</p> <pre><code>interface ISource { public DataTable Table { get; } string UniqueName { get; } } public class PermissionSource : ISource { /// &lt;summary&gt; /// Loads a DataTable with all of the information to load it lazily. /// &lt;/summary&gt; public DataTable Table { get { const string SELECT_CMD = "SELECT * FROM [Permissions] WHERE ([PermissionName] IS NULL OR [PermissionName]=@p1) AND [OtherProperty]=@p2"; var conn = new OleDbConnection("...ConnectionString..."); var selectCmd = new OleDbCommand(SELECT_CMD, conn); selectCmd.Parameters.AddWithValue("p1", "PermissionName"); selectCmd.Parameters.AddWithValue("p2", 0); var adapter = new OleDbDataAdapter(selectCmd); var builder = new OleDbCommandBuilder(adapter); //used to generate the UPDATE and DELETE commands... adapter.UpdateCommand = builder.GetUpdateCommand(); //etc. //Do NOT fill the table here. Instead, let the caller fill it. return new LazyDataTable(adapter); } } public string UniqueName { get { return "Permission"; } } } </code></pre> <p>Your main program code would use <code>PermissionSource</code> and <code>LazyDataTable</code> as follows:</p> <pre><code> static class Program { void GetPermissions() { ISource permissionSource = SourceManager.Sources. Where(s =&gt; "Permission".Equals(s.UniqueName)).First(); var table = permissionSource.Table as LazyDataTable; table.SetSelectParam("PermissionName", "Admin"); //If you want to fill ALL rows in one step: table.Fill(); // OR If you want to fill one row at a time, and add it to the table: DataRow row; while(null != (row = table.ReadRow())) { //do something with each individual row. Exit whenever desired. Console.WriteLine(row["PermissionName"]); } // OR If you prefer IEnumerable semantics: DataRow row = table.LazyReadRows().FirstOrDefault(someValue.Equals(row["columnname"])); //OR use foreach, etc. Rows are still ONLY read one at a time, each time IEnumerator.MoveNext() is called. foreach (var row in table.LazyReadRows()) if (row["someColumn"] == "someValue") DoSomething(row["anothercolumn"]); } } </code></pre> <p>You can certainly mix and match parts of the LazyDataTable shown here to achieve exactly what you want within your application constraints. It would of course be much better if you could switch to a different model of sharing data, but if you <em>MUST</em> return a DataTable from each Source, then at least you can return a more functional DataTable when necessary by subclassing it as I've demonstrated here. This allows you to pass back more information, which you can use to fill the table as you see fit. I would still encourage you to look into LinqToSQL as well as possibly trying to switch to simply passing back a DbDataReader or some other object similar to the LazyDataTable I've shown here that will allow you to both customize the original query (e.g. by using the <code>SetSelectParam</code> method) and also to read the data in one row at a time.</p> <p>Hope that helps!</p>
    singulars
    1. This table or related slice is empty.
    1. POExtend DataTable in C#
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USricovox
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload