public void AppendColumnHeader(ColumnObject columnObject)
{
PreviousColumnObject.NextColumnObject = columnObject;
columnObject.NextColumnObject = this;
columnObject.PreviousColumnObject = PreviousColumnObject;
PreviousColumnObject = columnObject;
}
public DataObject(ColumnObject listHeader, int rowIndex)
{
Left = Right = Up = Down = this;
ListHeader = listHeader;
RowIndex = rowIndex;
listHeader?.AddDataObject(this);
}
public override bool Equals(object obj)
{
if (obj is ColumnObject)
{
ColumnObject col = (ColumnObject)obj;
return(string.Equals(this.ColumnName, col.ColumnName));
}
return(false);
}
public void SendMail(Config _config, string file)
{
try
{
string connString = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={file};Extended Properties=Excel 8.0;";
if (file.TrimEnd().ToLower().EndsWith("x"))
{
connString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{file}';Extended Properties='Excel 12.0 Xml;HDR=YES;'";
}
using (var conn = new OleDbConnection(connString))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand($" select * from [{_config.Sheet}] ", conn);
OleDbDataReader reader = cmd.ExecuteReader();
var tpParser = new TemplateParser(_config.TemplatePath);
var sm = new SendMail(_config);
while (reader.Read())
{
var row = new List <ColumnObject>();
string email = "";
foreach (ColumnConfig c in _config.ConfigColumns)
{
var cc = new ColumnObject();
cc.Value = reader.GetValue(c.Position).ToString();
cc.ReplaceKey = c.ReplaceKey;
cc.IsEmail = c.IsMailColumn;
if (c.IsMailColumn)
{
email = cc.Value;
}
row.Add(cc);
}
sm.Send(email, tpParser.GetBodyMail(row), tpParser.GetReplaceText(row, _config.EmailSubject), _config.TemplateIsHtml);
}
}
}
catch (Exception ex)
{
_log?.Invoke(ex.ToString());
}
}
public DbObject BuildDbObject(string connectionString)
{
DbObject dbObj = new DbObject();
try
{
List <TableObject> tables = new List <TableObject>();
DataTable dtAllObjects = GetTable(connectionString, Crud.GetSchema_Table_Columns_DataTypes_IdentityCol());
TableObject tblObj = null;
foreach (DataRow row in dtAllObjects.Rows)
{
tblObj = null;
tblObj = new TableObject {
Name = string.Format("{0}", row["TABLE_NAME"]), Schema = string.Format("{0}", row["TABLE_SCHEMA"])
};
if (tables.Contains(tblObj) == false)
{
tables.Add(tblObj);
}
}
tblObj = null;
DataRow[] rows = null;
ColumnObject colObj;
List <ColumnObject> Columns = null;
for (int tableCounter = 0; tableCounter < tables.Count; tableCounter++)
{
tblObj = null;
tblObj = tables[tableCounter];
Columns = new List <ColumnObject>();
rows = null;
rows = dtAllObjects.Select(string.Format("TABLE_NAME='{0}' AND TABLE_SCHEMA='{1}'", tblObj.Name, tblObj.Schema));
foreach (DataRow rw in rows)
{
colObj = new ColumnObject()
{
ColumnName = string.Format("{0}", rw["COLUMN_NAME"]), ColumnDataType = string.Format("{0}", rw["DATA_TYPE"])
};
if (Columns.Contains(colObj) == false)
{
Columns.Add(colObj);
}
}
tblObj.Columns = Columns;
rows = null;
rows = dtAllObjects.Select(string.Format("TABLE_NAME='{0}' AND TABLE_SCHEMA='{1}' AND IS_IDENTITY=1", tblObj.Name, tblObj.Schema));
foreach (DataRow rw in rows)
{
tblObj.IdColumn = string.Format("{0}", rw["COLUMN_NAME"]).Replace(" ", "");
}
tables[tableCounter] = tblObj;
}
dbObj.Tables = tables;
}
catch (Exception)
{
throw;
}
return(dbObj);
}
public static List <TableObject> GenerarListado(string connectionString, string query)
{
List <TableObject> tableList = new List <TableObject>();
List <ColumnObject> columnList = new List <ColumnObject>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
if (!tableList.Any(x => x.Name.Equals(reader["Table_Name"])))
{
TableObject table = new TableObject
{
Name = reader["Table_Name"].ToString(),
Columns = new List <ColumnObject>()
};
if (table.Name != "sysdiagrams")
{
tableList.Add(table);
}
}
ColumnObject column = new ColumnObject
{
TableName = reader["Table_Name"].ToString(),
Name = reader["Column_Name"].ToString(),
ColumnType = reader["Data_Type"].ToString(),
Size = reader["Size"].ToString(),
Nullable = reader["Is_Nullable"].ToString() == "True" ? "NULL" : "NOT NULL"
};
if (column.ColumnType != "sysname" && column.TableName != "sysdiagrams")
{
columnList.Add(column);
}
}
foreach (ColumnObject column in columnList)
{
foreach (TableObject table in tableList)
{
if (column.TableName.Equals(table.Name))
{
table.Columns.Add(column);
}
}
}
}
finally
{
// Always call Close when done reading.
reader.Close();
}
return(tableList);
}
}
