Try this sample, tested on SQLEXPRESS 2008, AutoCAD 2014
public static List<object[]> GetRoomRecords()
{
List<object[]> records = new List<object[]>();
//custom connection string
string connStr =
"Data Source=.\\SQLEXPRESS;Initial Catalog=MySQLAutoCAD;Integrated Security=True;";
string sqlQuery =
@"SELECT [ID]
,[postcode]
,[roomnumber]
,[description]
,[X]
,[Y]
FROM [MySqlAutoCAD].[dbo].[Rooms]
WHERE ([postcode]=99211 AND [roomnumber]> 99)";// change on your condition here
SqlDataAdapter sqlAdapter = null;
using (SqlConnection sqlCon = new SqlConnection(connStr))
{
try
{
sqlAdapter = new SqlDataAdapter(sqlQuery, sqlCon);
using (SqlCommand sqlCommand = new SqlCommand(sqlAdapter.SelectCommand.CommandText))
{
System.Data.DataSet dataset = new System.Data.DataSet();
sqlAdapter.Fill(dataset, "Table1");// default table name, do not you change it
System.Data.DataTable dt = dataset.Tables["Table1"];
foreach (System.Data.DataRow dr in dt.Rows)
{
object[] items = dr.ItemArray;
records.Add(items);
}
}
sqlCon.Close();
return records;
}
catch (System.Exception ex)
{
System.Diagnostics.Debug.Print(ex.Message);
return null;
}
}
}
[CommandMethod("msql")]
public static void AddRecordsFromSQL()
{
List<object[]> data = new List<object[]>();
try
{
data = GetRoomRecords();
}
catch
{
return;
}
if (data != null)
{
Document doc = Autodesk.AutoCAD.ApplicationServices.Application.DocumentManager.MdiActiveDocument;
Editor ed = doc.Editor;
Database db = doc.Database;
try
{
using (DocumentLock doclock = doc.LockDocument())
{
using (Transaction tr = doc.TransactionManager.StartTransaction())
{
doc.TransactionManager.EnableGraphicsFlush(true);
BlockTable bt = (BlockTable)tr.GetObject(doc.Database.BlockTableId, OpenMode.ForRead);
BlockTableRecord btr = (BlockTableRecord)tr.GetObject(bt[BlockTableRecord.ModelSpace], OpenMode.ForWrite);
foreach (object[] item in data)
{
int roomnumber = Convert.ToInt32(item[2], CultureInfo.CurrentCulture);
string description = Convert.ToString(item[3], CultureInfo.CurrentCulture);
double X = Convert.ToDouble(item[4], CultureInfo.CurrentCulture);
double Y = Convert.ToDouble(item[5], CultureInfo.CurrentCulture);
Point3d pt = new Point3d(X, Y, 0);
MText mtx = new MText();
mtx.SetDatabaseDefaults();
mtx.Attachment = AttachmentPoint.MiddleCenter;
mtx.Contents = string.Format("{0}\n\n{1}", roomnumber, description);
mtx.Normal = Vector3d.ZAxis;
mtx.Location = pt;
mtx.Location = mtx.Location;
ObjectId mtxId = btr.AppendEntity(mtx);
tr.AddNewlyCreatedDBObject(mtx, true);
tr.TransactionManager.QueueForGraphicsFlush();
}
ed.Regen();
doc.TransactionManager.FlushGraphics();
tr.Commit();
}
}
}
catch (Autodesk.AutoCAD.Runtime.Exception ex)
{
ed.WriteMessage(ex.StackTrace);
}
finally
{
}
}
}
