public void UpdateSeriesCatalog(string instanceIdentifier, string entityConnectionstring)
{
//var s = Ressources.EFMODELDEF_IN_CONNECTIONSTRING;
//var constring = "metadata=res://*/ODM_1_1_1EFModel.csdl|res://*/ODM_1_1_1EFModel.ssdl|res://*/ODM_1_1_1EFModel.msl;provider=System.Data.SqlClient;provider connection string=" Data Source=tcp:bhi5g2ajst.database.windows.net,1433;Initial Catalog=HydroServertest2;User ID=HisCentralAdmin@bhi5g2ajst; Password=f3deratedResearch; Persist Security Info=true; MultipleActiveResultSets=True;App=EntityFramework";
//var constring = "metadata=res://*/ODM_1_1_1EFModel.csdl|res://*/ODM_1_1_1EFModel.ssdl|res://*/ODM_1_1_1EFModel.msl;provider=System.Data.SqlClient;provider connection string="data source=mseul-cuahsi;initial catalog=HydroSample2;integrated security=true; MultipleActiveResultSets=True;App=EntityFramework";
//var cleanedConnectionString = entityConnectionstring.Replace(s, string.Empty);
BusinessObjectsUtils.UpdateCachedprocessStatusMessage(instanceIdentifier, CacheName, Ressources.IMPORT_STATUS_TIMESERIES);
var ec = new EntityConnectionStringBuilder(entityConnectionstring);
//clear SeriesCatolog table
var repo = new SeriesCatalogRepository();
repo.deleteAll(entityConnectionstring);
SqlConnection sqlConnection1 = new SqlConnection(ec.ProviderConnectionString);
var adp = new SqlDataAdapter();
var cmd = new SqlCommand();
var dataTable = new DataTable();
var sb = new StringBuilder();
sb.Append("SELECT dv.SiteID, s.SiteCode, s.SiteName, s.SiteType, dv.VariableID, v.VariableCode, v.VariableName, ");
sb.Append("v.Speciation, v.VariableUnitsID, u.UnitsName AS VariableUnitsName, v.SampleMedium, ");
sb.Append("v.ValueType, v.TimeSupport, v.TimeUnitsID, u1.UnitsName AS TimeUnitsName, v.DataType, ");
sb.Append(" v.GeneralCategory, dv.MethodID, m.MethodDescription, dv.SourceID, so.Organization, ");
sb.Append("so.SourceDescription, so.Citation, dv.QualityControlLevelID, qc.QualityControlLevelCode, dv.BeginDateTime, ");
sb.Append("dv.EndDateTime, dv.BeginDateTimeUTC, dv.EndDateTimeUTC, dv.ValueCount ");
sb.Append("FROM (");
sb.Append("SELECT SiteID, VariableID, MethodID, QualityControlLevelID, SourceID, MIN(LocalDateTime) AS BeginDateTime,");
sb.Append("MAX(LocalDateTime) AS EndDateTime, MIN(DateTimeUTC) AS BeginDateTimeUTC, MAX(DateTimeUTC) AS EndDateTimeUTC, ");
sb.Append("COUNT(DataValue) AS ValueCount ");
sb.Append("FROM DataValues ");
sb.Append("GROUP BY SiteID, VariableID, MethodID, QualityControlLevelID, SourceID) dv ");
sb.Append("INNER JOIN dbo.Sites s ON dv.SiteID = s.SiteID ");
sb.Append("INNER JOIN dbo.Variables v ON dv.VariableID = v.VariableID ");
sb.Append("INNER JOIN dbo.Units u ON v.VariableUnitsID = u.UnitsID ");
sb.Append("INNER JOIN dbo.Methods m ON dv.MethodID = m.MethodID ");
sb.Append("INNER JOIN dbo.Units u1 ON v.TimeUnitsID = u1.UnitsID ");
sb.Append("INNER JOIN dbo.Sources so ON dv.SourceID = so.SourceID ");
sb.Append("INNER JOIN dbo.QualityControlLevels qc ON dv.QualityControlLevelID = qc.QualityControlLevelID ");
sb.Append("GROUP BY dv.SiteID, s.SiteCode, s.SiteName, s.SiteType, dv.VariableID, v.VariableCode, v.VariableName, v.Speciation, ");
sb.Append("v.VariableUnitsID, u.UnitsName, v.SampleMedium, v.ValueType, v.TimeSupport, v.TimeUnitsID, u1.UnitsName, ");
sb.Append("v.DataType, v.GeneralCategory, dv.MethodID, m.MethodDescription, dv.SourceID, so.Organization, ");
sb.Append("so.SourceDescription, so.Citation, dv.QualityControlLevelID, qc.QualityControlLevelCode, dv.BeginDateTime, ");
sb.Append("dv.EndDateTime, dv.BeginDateTimeUTC, dv.EndDateTimeUTC, dv.ValueCount ");
sb.Append("ORDER BY dv.SiteID, dv.VariableID, v.VariableUnitsID");
cmd.CommandText = sb.ToString();
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;
adp.SelectCommand = cmd;
adp.Fill(dataTable);
//List<SeriesCatalogDBModel> list = AutoMapper.Mapper.DynamicMap<IDataReader, List<SeriesCatalogDBModel>>(dataTable.CreateDataReader());
//List<SeriesCatalogDBModel> list = dataTable.AsEnumerable().ToList();
// .Select(row=> new SeriesCatalogDBModel)
// open the destination data
using (SqlConnection destinationConnection =
new SqlConnection(ec.ProviderConnectionString))
{
// open the connection
destinationConnection.Open();
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(destinationConnection.ConnectionString, SqlBulkCopyOptions.KeepNulls | SqlBulkCopyOptions.CheckConstraints))
{
//bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsTransfer);
//bulkCopy.NotifyAfter = 100;
bulkCopy.BatchSize = 50;
foreach (DataColumn dc in dataTable.Columns)
{
bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
}
bulkCopy.DestinationTableName = "SeriesCatalog";
bulkCopy.WriteToServer(dataTable);
}
}
}
public JsonResult SeriesCatalogSearch(JQueryDataTablesModel jQueryDataTablesModel, string identifier)
{
int totalRecordCount;
int searchRecordCount;
//Get Connection string
var entityConnectionString = HydroServerToolsUtils.BuildConnectionStringForUserName(HttpContext.User.Identity.Name);
//var entityConnectionString = HydroServerToolsUtils.GetDBEntityConnectionStringByName(connectionName);
if (String.IsNullOrEmpty(entityConnectionString))
{
ModelState.AddModelError(String.Empty, Ressources.HYDROSERVER_USERLOOKUP_FAILED);
}
var repository = new SeriesCatalogRepository();
var items = repository.GetSeriesCatalog(entityConnectionString, startIndex: jQueryDataTablesModel.iDisplayStart,
pageSize: jQueryDataTablesModel.iDisplayLength, sortedColumns: jQueryDataTablesModel.GetSortedColumns(),
totalRecordCount: out totalRecordCount, searchRecordCount: out searchRecordCount, searchString: jQueryDataTablesModel.sSearch);
var result = from c in items
select new[] {
//c.SeriesID,
//c.SiteID,
c.SiteCode,
c.SiteName,
c.SiteType,
//c.VariableID,
c.VariableCode,
c.VariableName,
c.Speciation,
//c.VariableUnitsID,
c.VariableUnitsName,
c.SampleMedium,
c.ValueType,
c.TimeSupport,
//c.TimeUnitsID,
c.TimeUnitsName,
c.DataType,
c.GeneralCategory,
//c.MethodID,
c.MethodDescription,
//c.SourceID,
c.Organization,
c.SourceDescription,
c.Citation,
//c.QualityControlLevelID,
c.QualityControlLevelCode,
c.BeginDateTime,
c.EndDateTime,
//c.BeginDateTimeUTC,
//c.EndDateTimeUTC,
c.ValueCount
};
return this.DataTablesJson(items: result,
totalRecords: totalRecordCount,
totalDisplayRecords: searchRecordCount,
sEcho: jQueryDataTablesModel.sEcho);
}