public void LoadUFL()
{
db myDb = new db();
SqlDataReader reader = null;
SqlParameter uflDetailIdParam = new SqlParameter("@UFLDetailId", SqlDbType.Int);
uflDetailIdParam.Value = UFLDetailId;
myDb.Connect();
SqlCommand command = new SqlCommand("SpExcelLoadUFLDetail", myDb.Connection);
command.CommandType = CommandType.StoredProcedure;
try
{
command.Parameters.Add(uflDetailIdParam);
reader = command.ExecuteReader();
}
catch (Exception e)
{
string dummy = e.Message;
}
while (reader.Read())
{
if (reader["UFLDetailId"].ToString() != "")
{
UFLDetailId = int.Parse(reader["UFLDetailId"].ToString());
}
PiTowerName = reader["PiTowerName"].ToString();
FileMask = reader["FileMask"].ToString();
Folder = reader["Folder"].ToString();
if (reader["Recursive"].ToString() != String.Empty)
{
Recursive = (bool)reader["Recursive"];
}
else
{
Recursive = false;
}
if (reader["Encrypted"].ToString() != String.Empty)
{
Encrypted = (bool)reader["Encrypted"];
}
else
{
Encrypted = false;
}
DataType = reader["DataType"].ToString();
Delimiter = reader["Delimitor"].ToString();
DateTimeFormat = reader["DateTimeFormat"].ToString();
DateFormat = reader["DateFormat"].ToString();
TimeFormat = reader["TimeFormat"].ToString();
if (reader["HeaderMarkerRow"].ToString() != "")
{
HeaderMarkerRow = int.Parse(reader["HeaderMarkerRow"].ToString());
}
HeaderAdditionalDetailRows.Clear();
HeaderAdditionalDetailRows.AddRange((reader["HeaderAdditionalDetail"].ToString()).Split('|'));
if (reader["PiTagRow"].ToString() != "")
{
PiTagRow = int.Parse(reader["PiTagRow"].ToString());
}
PiTagList.Clear();
PiTagList.AddRange((reader["PiTagList"].ToString()).Split('|'));
}
reader.Close();
myDb.Connection.Close();
}
public void UpdateFileFormatDetails()
{
int affected = 0;
db myDb = new db();
SqlParameter fileFormatIdParam = new SqlParameter("@FileFormatId", SqlDbType.Int);
fileFormatIdParam.Value = UFLDetailId; // Use the same id for FileFormatId as the UFLDetailId
SqlParameter delimiterParam = new SqlParameter("@Delimitor", SqlDbType.VarChar, 5);
delimiterParam.Value = Delimiter;
SqlParameter headerRowNumberParam = new SqlParameter("@HeaderRowNumber", SqlDbType.Int);
headerRowNumberParam.Value = HeaderMarkerRow - UFL_DETAIL_HEADER_SIZE; // We use 15 extra lines for our UFL Details header
SqlParameter headerRowContentsParam = new SqlParameter("@HeaderRowContents", SqlDbType.VarChar, 1000);
headerRowContentsParam.Value = String.Join(DelimiterCode, HeaderMarkerRowContents.ToArray());
SqlParameter piTowerNameParam = new SqlParameter("@PiTowerName", SqlDbType.VarChar, 50);
piTowerNameParam.Value = PiTowerName;
SqlParameter folderParam = new SqlParameter("@Folder", SqlDbType.VarChar, 1000);
folderParam.Value = Folder;
SqlParameter fileMaskParam = new SqlParameter("@FileMask", SqlDbType.VarChar, 100);
fileMaskParam.Value = FileMask;
SqlParameter recurseParam = new SqlParameter("@Recurse", SqlDbType.Bit);
recurseParam.Value = Recursive;
SqlParameter encryptedParam = new SqlParameter("@Encrypted", SqlDbType.Bit);
encryptedParam.Value = Encrypted;
myDb.Connect();
SqlCommand command = new SqlCommand("SpExcelUpdateFileFormatDetails", myDb.Connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(fileFormatIdParam);
command.Parameters.Add(delimiterParam);
command.Parameters.Add(headerRowNumberParam);
command.Parameters.Add(headerRowContentsParam);
command.Parameters.Add(piTowerNameParam);
command.Parameters.Add(folderParam);
command.Parameters.Add(fileMaskParam);
command.Parameters.Add(recurseParam);
command.Parameters.Add(encryptedParam);
try
{
affected = command.ExecuteNonQuery();
}
catch (Exception e)
{
string dummy = e.Message;
Error frmError = new Error();
frmError.ErrorMsg = String.Format("An error occoured: {0}", e.Message);
frmError.ShowDialog();
}
// now store additional details content
SqlParameter fileFormatIdMLParam = new SqlParameter("@FileFormatId", SqlDbType.Int);
SqlParameter headerAdditioanlDetailRowNumberParam = new SqlParameter("@HeaderRowNumber", SqlDbType.Int);
SqlParameter headerAdditioanlDetailRowContentsParam = new SqlParameter("@HeaderRowContents", SqlDbType.VarChar, 1000);
SqlParameter deleteExistingParam = new SqlParameter("@DeleteExisting", SqlDbType.Bit);
SqlCommand commandML = new SqlCommand("SpExcelUpdateMultiLineHeaderRow", myDb.Connection);
commandML.CommandType = CommandType.StoredProcedure;
fileFormatIdMLParam.Value = UFLDetailId;
commandML.Parameters.Add(fileFormatIdMLParam);
commandML.Parameters.Add(headerAdditioanlDetailRowNumberParam);
commandML.Parameters.Add(headerAdditioanlDetailRowContentsParam);
commandML.Parameters.Add(deleteExistingParam);
for (int i = 0; i < HeaderAdditionalDetailRows.Count; i++)
{
// insert
headerAdditioanlDetailRowNumberParam.Value = int.Parse(HeaderAdditionalDetailRows[i]) - UFL_DETAIL_HEADER_SIZE; // We use 15 extra lines for our UFL Details header
headerAdditioanlDetailRowContentsParam.Value = HeaderAdditionalDetailRowsContents[i];
deleteExistingParam.Value = (i == 0); // Only delete existing records when we insert the first line
try
{
affected = commandML.ExecuteNonQuery();
}
catch (Exception e)
{
string dummy = e.Message;
Error frmError = new Error();
frmError.ErrorMsg = String.Format("An error occoured: {0}", e.Message);
frmError.ShowDialog();
}
}
}
public void SaveUFL()
{
SqlDataReader reader = null;
SqlParameter uflDetailIdParam = new SqlParameter("@UFLDetailId", SqlDbType.Int);
uflDetailIdParam.Value = UFLDetailId;
SqlParameter piTowerNameParam = new SqlParameter("@PiTowerName", SqlDbType.VarChar, 50);
piTowerNameParam.Value = PiTowerName;
SqlParameter fileMaskParam = new SqlParameter("@FileMask", SqlDbType.VarChar, 50);
fileMaskParam.Value = FileMask;
SqlParameter folderParam = new SqlParameter("@Folder", SqlDbType.VarChar, 255);
folderParam.Value = Folder;
SqlParameter recursiveParam = new SqlParameter("@Recursive", SqlDbType.Bit);
recursiveParam.Value = Recursive;
SqlParameter encryptedParam = new SqlParameter("@Encrypted", SqlDbType.Bit);
encryptedParam.Value = Encrypted;
SqlParameter dataTypeParam = new SqlParameter("@DataType", SqlDbType.Char, 3);
dataTypeParam.Value = DataType;
SqlParameter delimiterParam = new SqlParameter("@Delimitor", SqlDbType.VarChar, 10);
delimiterParam.Value = Delimiter;
SqlParameter dateTimeFormatParam = new SqlParameter("@DateTimeFormat", SqlDbType.VarChar, 30);
dateTimeFormatParam.Value = DateTimeFormat;
SqlParameter dateFormatParam = new SqlParameter("@DateFormat", SqlDbType.VarChar, 20);
dateFormatParam.Value = DateFormat;
SqlParameter timeFormatParam = new SqlParameter("@TimeFormat", SqlDbType.VarChar, 20);
timeFormatParam.Value = TimeFormat;
SqlParameter headerMarkerRowParam = new SqlParameter("@HeaderMarkerRow", SqlDbType.Int);
headerMarkerRowParam.Value = HeaderMarkerRow;
SqlParameter headerAdditionalDetailParam = new SqlParameter("@HeaderAdditionalDetail", SqlDbType.VarChar, 2000);
headerAdditionalDetailParam.Value = String.Join("|", HeaderAdditionalDetailRows.ToArray());
SqlParameter piTagRowParam = new SqlParameter("@PiTagRow", SqlDbType.Int);
piTagRowParam.Value = PiTagRow;
SqlParameter piTagListParam = new SqlParameter("@PiTagList", SqlDbType.VarChar, 2000);
piTagListParam.Value = String.Join("|", PiTagList.ToArray());
db myDb = new db();
myDb.Connect();
SqlCommand command = new SqlCommand("SpExcelSaveUFLDetail", myDb.Connection);
command.CommandType = CommandType.StoredProcedure;
try
{
command.Parameters.Add(uflDetailIdParam);
command.Parameters.Add(piTowerNameParam);
command.Parameters.Add(fileMaskParam);
command.Parameters.Add(folderParam);
command.Parameters.Add(recursiveParam);
command.Parameters.Add(encryptedParam);
command.Parameters.Add(dataTypeParam);
command.Parameters.Add(delimiterParam);
command.Parameters.Add(dateTimeFormatParam);
command.Parameters.Add(dateFormatParam);
command.Parameters.Add(timeFormatParam);
command.Parameters.Add(headerMarkerRowParam);
command.Parameters.Add(headerAdditionalDetailParam);
command.Parameters.Add(piTagRowParam);
command.Parameters.Add(piTagListParam);
reader = command.ExecuteReader();
}
catch (Exception e)
{
string dummy = e.Message;
}
while (reader.Read())
{
if (reader["UFLDetailId"].ToString() != "")
{
UFLDetailId = int.Parse(reader["UFLDetailId"].ToString());
}
}
reader.Close();
myDb.Connection.Close();
}