Ben Siewert Ben Siewert - 3 months ago 15
C# Question

C# Use datatable in a new function

I have a datatable that is defined and filled from data in a spreadsheet. Then, in another function I want to access that data table to cast those values as text into an attribute. How do I use that same datatable in tht DrawCircuits function?

public static System.Data.DataTable ReadExcelToTable(string path)
{
string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
System.Data.DataSet set = new DataSet();
using (OleDbConnection conn = new OleDbConnection(connstring))
{
conn.Open();
System.Data.DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
string firstSheetName = sheetsName.Rows[0][2].ToString();
string sql = string.Format("SELECT * FROM [{0}]", firstSheetName);
OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
ada.Fill(set);
conn.Close();
}
return set.Tables[0];
}

//this command can insert a block and fill out attributes (text)
[CommandMethod("DrawCircuits")]
public void DrawCircuits(string name, double x, double y, double z)
{
Database db = Autodesk.AutoCAD.ApplicationServices.Application.DocumentManager.MdiActiveDocument.Database;
using (Transaction myT = db.TransactionManager.StartTransaction())
{
//Get the block definition
string blockName = name;
BlockTable bt =
db.BlockTableId.GetObject(OpenMode.ForRead) as BlockTable;
BlockTableRecord blockDef =
bt[blockName].GetObject(OpenMode.ForRead) as BlockTableRecord;
//Also open paper space - we'll be adding our BlockReference to it
BlockTableRecord ps =
bt[BlockTableRecord.PaperSpace].GetObject(OpenMode.ForWrite)
as BlockTableRecord;
//Create new BlockReference, and link it to our block definition
Point3d point = new Point3d(x, y, z);
using (BlockReference blockRef =
new BlockReference(point, blockDef.ObjectId))
{
//Add the block reference to paper space
ps.AppendEntity(blockRef);
myT.AddNewlyCreatedDBObject(blockRef, true);
//Iterate block definition to find all non-constant
// AttributeDefinitions
foreach (ObjectId id in blockDef)
{
DBObject obj = id.GetObject(OpenMode.ForRead);
AttributeDefinition attDef = obj as AttributeDefinition;
if ((attDef != null) && (!attDef.Constant))
{
//This is a non-constant AttributeDefinition
//Create a new AttributeReference
using (AttributeReference attRef = new AttributeReference())
{
attRef.SetAttributeFromBlock(attDef, blockRef.BlockTransform);
// below is where I want to access the
// datatable loaded into memory in the other
// function. I want the att.Ref.TextString
// value to start from row 0 column 0 of the
// datatable
if (attRef.Tag == "TAG1")
{
attRef.TextString = "";
}
if (attRef.Tag == "XXX-NNNN+")
{
attRef.TextString = "";
}
//Add the AttributeReference to the BlockReference
blockRef.AttributeCollection.AppendAttribute(attRef);
myT.AddNewlyCreatedDBObject(attRef, true);
}
}
}
}
//Our work here is done
myT.Commit();
}
}


***8-18-2016 update!
With help, I have been able to use the datatable I loaded into memory from an Excel spreadsheet in a new function by way of calling in in the parameter of the DrawCircuits() function. I think this is the best method for what I am trying to achieve. However, I am now, once again, trying to use this datatable in another function where I tied everything together that I am trying to use, Test3(). Everything works fine and the attRef.TextString = dr.Table.Rows[1][0].ToString(); statement from the DrawCircuits() function works properly by populating the value from the datatable as a string for the attribute.

The problem that I am having is that now, in another function, Test3(), I cannot get the for loop of Test3() to populate the next group of attributes with the next row of the datatable. All of the attributes get populated with the same row values from the datatable. I have tried many attempts at this on my own. Let me know if any of you have any ideas about the right solution.

[CommandMethod("Test3")]
public void Test3()
{
string Path = SelectSpreadsheet();
System.Data.DataTable table = ReadExcelToTable(Path);

InsertBlocks();
DrawModule("AI-1756-IF16H-SHEET1");
for (int i = 0; i < 8; i++)
{
DrawCircuits("AI-AIT-CIRCUIT", 24, 17 - (i * 1), 0, table.Rows[i]);
}

}

public static System.Data.DataTable ReadExcelToTable(string path)
{
string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
System.Data.DataSet set = new DataSet();
using (OleDbConnection conn = new OleDbConnection(connstring))
{
conn.Open();
System.Data.DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
string firstSheetName = sheetsName.Rows[0][2].ToString();
string sql = string.Format("SELECT * FROM [{0}]", firstSheetName);
OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
ada.Fill(set);
conn.Close();
}
return set.Tables[0];
}


[CommandMethod("DrawCircuits")]
public void DrawCircuits(string name, int x, int y, int z, System.Data.DataRow dr)
{
Database db = Autodesk.AutoCAD.ApplicationServices.Application.DocumentManager.MdiActiveDocument.Database;
using (Transaction myT = db.TransactionManager.StartTransaction())
{
//Get the block definition
string blockName = name;
BlockTable bt =
db.BlockTableId.GetObject(OpenMode.ForRead) as BlockTable;
BlockTableRecord blockDef =
bt[blockName].GetObject(OpenMode.ForRead) as BlockTableRecord;
//Also open paper space - we'll be adding our BlockReference to it
BlockTableRecord ps =
bt[BlockTableRecord.PaperSpace].GetObject(OpenMode.ForWrite)
as BlockTableRecord;
//Create new BlockReference, and link it to our block definition
Point3d point = new Point3d(x, y, z);
using (BlockReference blockRef =
new BlockReference(point, blockDef.ObjectId))
{
//Add the block reference to paper space
ps.AppendEntity(blockRef);
myT.AddNewlyCreatedDBObject(blockRef, true);
//Iterate block definition to find all non-constant
// AttributeDefinitions


foreach (ObjectId id in blockDef)
{

DBObject obj = id.GetObject(OpenMode.ForRead);
AttributeDefinition attDef = obj as AttributeDefinition;
if ((attDef != null) && (!attDef.Constant))
{
//This is a non-constant AttributeDefinition
//Create a new AttributeReference


using (AttributeReference attRef = new AttributeReference())
{
attRef.SetAttributeFromBlock(attDef, blockRef.BlockTransform);


if (attRef.Tag == "TAG1")
{

attRef.TextString = dr.Table.Rows[1][0].ToString();
}
if (attRef.Tag == "XXX-NNNN+")
{
attRef.TextString = dr.Table.Rows[1][1].ToString();
}

//Add the AttributeReference to the BlockReference
blockRef.AttributeCollection.AppendAttribute(attRef);
myT.AddNewlyCreatedDBObject(attRef, true);
}


}
}


}
//Our work here is done
myT.Commit();
}
}

RAM RAM
Answer
public static System.Data.DataTable ReadExcelToTable(string path)
{
    //Method codes
    ...

    // Return the data table
    return set.Tables[0];
}    

public void DrawCircuits(string name, double x, double y, double z,DataTable dt)
{
     ...
     attRef.TextString = dt.Rows[0][0];
     ...
}

public DataTable ChangeDt(DataTable dt)
{
    // Change dt codes
    ...
    // Return changed dt
    return dt;
}

public void Use(DataTable dt)
{
    var myDt = ChangeDt(dt);

    for(i=0; i<=10; i++)
    {
       DrawCircuits("name", 1, 2, i, myDt);
    }
}

Or a Use method without parameter:

public void Use()
{
    var dt = ReadExcelToTable("....The Path....");
    var myDt = ChangeDt(dt);
    for(i=0; i<=10; i++)
    {
       DrawCircuits("name", 1, 2, i, dt);
    }
}