/*
private static int Insert_ImplicitMapping(int idset, List<string> sdmxmlFields)
{
HashSet<string> cubeConcepts = new HashSet<string>();
Dictionary<string, Dictionary<string, object>> dict = new Dictionary<string, Dictionary<string, object>>();
ISTAT.DBDAL.DataAccess.Get_Dimensions(idset).ForEach(e => { cubeConcepts.Add(e.Id); });
ISTAT.DBDAL.DataAccess.Get_Attributes(idset).ForEach(e => { cubeConcepts.Add(e.Id); });
cubeConcepts.Add("OBS_VALUE");
if (!cubeConcepts.Contains("TIME_PERIOD"))
cubeConcepts.Add("TIME_PERIOD");
int pos = 1;
foreach (string field in sdmxmlFields)
{
if (cubeConcepts.Contains(field))
{
//MappingItem i = new MappingItem();
//i._a = pos;
//i._b = field;
//i._c = "Cln: " + pos + " : " + field;
//items.Add(i);
Dictionary<string, object> c = new Dictionary<string, object>();
c.Add(field, "Cln: " + pos + " : " + field);
dict.Add(pos.ToString(), c);
}
pos++;
}
int idSchema = -1;
if ((idSchema = ISTAT.DBDAL.DataAccess.Create_Mapping(
idset,
0,
"ImplicitSdmxMlMapping",
"Implicit SDMX-ML Mapping",
dict,
";",
true,
false,
"",
0)) > 0)
{
//OK
}
return idSchema;
}*/
public static QueryReport Insert_CSVData(string csvFilePath, char csvDelimiter, int mappingID, int idset = -1)
{
var configDebug = ConfigurationManager.AppSettings["DebugBulkUpload"];
bool debug = (configDebug == "true") ? true : false;
var bulkuploadTimeout = ConfigurationManager.AppSettings["BulkUploadTimeout"];
int timeout = 0;
Int32.TryParse(bulkuploadTimeout, out timeout);
QueryReport _report = new QueryReport();
_report.StartReport();
string min_time = string.Empty;
string max_time = string.Empty;
string temp_table = "temp_upload_" + Utility.RandomString(16);
int rowcount = 0;
int proc_result = 0;
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
if (!dtw.TestConnection())
return null;
CSVUploader uploader;
if (debug)
{
uploader = new CSVUploader((System.Data.SqlClient.SqlConnection)dtw.DBConnection);
uploader.CreateDestinationTable = true;
uploader.ConnectionTimeOut = timeout;
uploader.CSVDelimiter = (mappingID > 0) ? csvDelimiter : ';'; //Se il mappingID non è definito (implicit mapping) utilizzo sempre ';'
uploader.Upload(csvFilePath, temp_table);
dtw.DBConnection.Close();
}
dtw.DBConnection.Open();
IDbTransaction transaction = dtw.DBConnection.BeginTransaction();
try
{
if (mappingID <= 0 && idset <= 0)
{
throw new Exception("Ivalid call of Insert_CSVData with mappingID=" + mappingID +
" and idset=" + idset + ". Either a valid 'mappingID' or a valid 'idset' must be provided");
}
// Carico il file csv nel DB
if (!debug)
{
uploader = new CSVUploader((System.Data.SqlClient.SqlConnection)dtw.DBConnection, (System.Data.SqlClient.SqlTransaction)transaction);
uploader.CreateDestinationTable = true;
uploader.ConnectionTimeOut = timeout;
uploader.CSVDelimiter = (mappingID > 0) ? csvDelimiter : ';'; //Se il mappingID non è definito (implicit mapping) utilizzo sempre ';'
uploader.Upload(csvFilePath, temp_table);
}
// Lancio la SP per l'upload
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.Connection = dtw.DBConnection;
cmd.Transaction = transaction;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "proc_bulkupload_UPLOAD_DATA";
System.Data.IDbDataParameter _param_code = cmd.CreateParameter();
_param_code.DbType = System.Data.DbType.Int64;
_param_code.ParameterName = "@IDStructure";
_param_code.Value = (mappingID > 0) ? mappingID : idset; //Se il mappingID non è definito (implicit mapping) utilizzo l'idset
cmd.Parameters.Add(_param_code);
System.Data.IDbDataParameter _param_table_name = cmd.CreateParameter();
_param_table_name.DbType = System.Data.DbType.String;
_param_table_name.ParameterName = "@table_name";
_param_table_name.Value = temp_table;
cmd.Parameters.Add(_param_table_name);
System.Data.IDbDataParameter _param_rowcount = cmd.CreateParameter();
_param_rowcount.DbType = System.Data.DbType.Int32;
_param_rowcount.Direction = ParameterDirection.Output;
_param_rowcount.ParameterName = "@rowcount";
cmd.Parameters.Add(_param_rowcount);
System.Data.IDbDataParameter _param_drop = cmd.CreateParameter();
_param_drop.DbType = System.Data.DbType.Int32;
_param_drop.ParameterName = "@dropTable";
_param_drop.Value = (debug) ? 0 : 1;
cmd.Parameters.Add(_param_drop);
System.Data.IDbDataParameter _param_implicit_map = cmd.CreateParameter();
_param_implicit_map.DbType = System.Data.DbType.Int32;
_param_implicit_map.ParameterName = "@UseImplicitMapping";
_param_implicit_map.Value = (mappingID > 0) ? 0 : 1;
cmd.Parameters.Add(_param_implicit_map);
System.Data.IDataParameter _return_val = cmd.CreateParameter();
_return_val.DbType = System.Data.DbType.Int32;
_return_val.ParameterName = "@errorCode";
_return_val.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(_return_val);
//proc_result = cmd.ExecuteNonQuery();
cmd.CommandTimeout = timeout;
System.Data.IDataReader dataReader = cmd.ExecuteReader();
//Check for errors
int errors = 0;
while (dataReader.Read())
{
string csv_col = dataReader.GetString(dataReader.GetOrdinal("CSV_COLUMN"));
string code = dataReader.GetString(dataReader.GetOrdinal("CODE"));
string error = dataReader.GetString(dataReader.GetOrdinal("ERROR"));
errors++;
_report.Errors.Add("CONCEPT: " + csv_col + ", CODE: " + code + ", ERROR: " + error);
}
dataReader.Close();
rowcount = (Int32)_param_rowcount.Value;
proc_result = (Int32)_return_val.Value;
if (errors == 0 && proc_result == 0)
{
transaction.Commit();
_report.RecordCount = rowcount;
_report.RecordTargetCount = rowcount;
}
else
{
transaction.Rollback();
_report.Errors.Add("Errore " + proc_result);
_report.RecordCount = 0;
_report.RecordTargetCount = rowcount;
}
}
catch (Exception ex)
{
_report.Errors.Add(ex.Message);
try
{
transaction.Rollback();
}
catch { }
}
finally
{
_report.StopReport();
_report.MaxTime = max_time;
_report.MinTime = min_time;
dtw.DBConnection.Close();
}
return _report;
}
public static QueryReport Insert_AttributeData(string attributeFilePath, int idSet, string userPathDir)
{
QueryReport _report = new QueryReport();
_report.StartReport();
string userScartedFileDir = userPathDir + @"\scarted";
string userScartedFileName = attributeFilePath.Substring(attributeFilePath.LastIndexOf(@"\") + 1).Replace(".csv", "_scarted.csv");
string min_time = string.Empty;
string max_time = string.Empty;
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
if (!dtw.TestConnection())
return null;
dtw.DBConnection.Open();
try
{
// Carico il file csv nel DB
CSVUploader uploader = new CSVUploader(SQLConnString_DB.ConnectionString);
uploader.CreateDestinationTable = true;
uploader.ConnectionTimeOut = 3600;
uploader.Upload(attributeFilePath, "Temp_Attribute");
// Lancio la SP per l'update degli attributi
System.Data.IDbCommand cmd = dtw.DBConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "sp_AttributeMain";
System.Data.IDbDataParameter _param_code = cmd.CreateParameter();
_param_code.DbType = System.Data.DbType.String;
_param_code.ParameterName = "@IDSET";
_param_code.Value = idSet;
cmd.Parameters.Add(_param_code);
cmd.ExecuteNonQuery();
// Faccio una select sulla tabella degli scarti
// se il risultato non è null con i dati creo un file csv e restituisco il link
cmd = dtw.DBConnection.CreateCommand();
cmd.CommandText = "SELECT * FROM Temp_Attribute_Scarted";
System.Data.IDataReader dReader = cmd.ExecuteReader();
string sHeader = "", sRow = "";
bool bHeader = true;
if (!Directory.Exists(userPathDir))
Directory.CreateDirectory(userPathDir);
if (!Directory.Exists(userScartedFileDir))
Directory.CreateDirectory(userScartedFileDir);
File.Create(userScartedFileDir + @"\" + userScartedFileName).Close();
while (dReader.Read())
{
sRow = "";
using (System.IO.StreamWriter file = new System.IO.StreamWriter(userScartedFileDir + @"\" + userScartedFileName, true))
{
if (bHeader)
{
for (int i = 0; i < dReader.FieldCount; i++)
{
sHeader += dReader.GetName(i) + "|";
}
bHeader = false;
sHeader = sHeader.Substring(0, sHeader.Length - 1);
file.WriteLine(sHeader);
_report.ScartedFilePath = @"/scarted/" + userScartedFileName;
}
for (int i = 0; i < dReader.FieldCount; i++)
{
sRow += dReader[i].ToString() + "|";
}
sRow = sRow.Substring(0, sRow.Length - 1);
file.WriteLine(sRow);
}
}
}
catch (Exception ex)
{
_report.Errors.Add(ex.Message);
}
finally
{
_report.StopReport();
_report.MaxTime = max_time;
_report.MinTime = min_time;
}
return _report;
}
