public mdl_TransferRequests CollectTransferRequestInsert(DataSet ds, string prj, string vre, string rt,
string rq, string rqn, string rv, DateTime rd, string rvn)
{
int rtID = ds.Tables["tlkTransferRequestTypes"].AsEnumerable()
.Where(x => x.Field <string>("RequestTypeLabel") == rt)
.Select(x => x.Field <int>("RequestTypeID")).ToList().FirstOrDefault();
int rqID = ds.Tables["tblUser"].AsEnumerable()
.Where(x => rq == x.Field <string>("LastName") + ", " + x.Field <string>("FirstName"))
.Select(x => x.Field <int>("UserNumber")).ToList().Distinct().FirstOrDefault();
int rvID = ds.Tables["tblUser"].AsEnumerable()
.Where(x => rv == x.Field <string>("LastName") + ", " + x.Field <string>("FirstName"))
.Select(x => x.Field <int>("UserNumber")).ToList().Distinct().FirstOrDefault();
mdl_TransferRequests tr = new mdl_TransferRequests
{
Project = prj,
VreNumber = vre,
RequestType = rtID,
RequestedBy = rqID,
RequesterNotes = rqn,
ReviewedBy = rvID,
ReviewDate = rd,
ReviewNotes = rvn
};
return(tr);
}
public bool PutTransferRecords(DataSet ds, string prj, string vre, string rt, string rq, string rqn,
string rv, DateTime rd, string rvn,
DataGridView assets, DataGridView files, string vreDir, string repoDir,
string tm, string tf, string tt, string dsa, DataGridView rej)
{
bool[] success = new bool[4];
SqlConnection conn = new SqlConnection();
conn.ConnectionString = SQL_Stuff.conString;
conn.Credential = SQL_Stuff.credential;
using (conn)
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
// tblTransferRequests insert
mdl_TransferRequests tr = CollectTransferRequestInsert(ds, prj, vre, rt, rq, rqn, rv, rd, rvn);
string trQry = @"
INSERT INTO dbo.tblTransferRequests (Project, VreNumber, RequestType, RequestedBy, RequesterNotes,
ReviewedBy, ReviewDate, ReviewNotes)
OUTPUT INSERTED.RequestID
VALUES (@Project, @VreNumber, @RequestType, @RequestedBy, @RequesterNotes, @ReviewedBy,
@ReviewDate, @ReviewNotes)";
using (SqlCommand cmd = new SqlCommand(cmdText: trQry, connection: conn, transaction: trans))
{
cmd.Parameters.Add("@Project", SqlDbType.VarChar, 5).Value = tr.Project;
cmd.Parameters.Add("@VreNumber", SqlDbType.VarChar, 5).Value = tr.VreNumber;
cmd.Parameters.Add("@RequestType", SqlDbType.Int).Value = tr.RequestType;
cmd.Parameters.Add("@RequestedBy", SqlDbType.Int).Value = tr.RequestedBy;
cmd.Parameters.Add("@RequesterNotes", SqlDbType.VarChar, int.MaxValue).Value = tr?.RequesterNotes ?? (object)DBNull.Value;
cmd.Parameters.Add("@ReviewedBy", SqlDbType.Int).Value = tr.ReviewedBy;
cmd.Parameters.Add("@ReviewDate", SqlDbType.DateTime).Value =
tr.ReviewDate.HasValue ? tr.ReviewDate.Value.Date : (object)DBNull.Value;
cmd.Parameters.Add("@ReviewNotes", SqlDbType.VarChar, int.MaxValue).Value = tr?.ReviewNotes ?? (object)DBNull.Value;
// Execute insert and get the newly created ID
tr.RequestID = (int)cmd.ExecuteScalar();
}
success[0] = tr.RequestID > 0;
// tblAssetGroups insert
List <mdl_AssetGroups> ag = CollectAssetGroupsInsert(ds, prj, assets);
if (ag.Any())
{
int agRows = SQL_Stuff.insertBulk(ag.ToDataTable(), "dbo.tblAssetGroups", conn, trans);
success[1] = agRows == ag.Count();
// Query tblAssetGroups again to get all new IDs for use in further inserts
GetAssetGroups(ds, conn, trans);
}
else
{
success[1] = true;
}
// tblAssetsRegister insert
List <mdl_AssetsRegister> ar = CollectAssetsRegisterInsert(prj, files, vreDir, repoDir);
// If there are any assets, join asset ID to ar before doing bulk insert
if (ag.Any())
{
// Join asset IDs to the assets DGV
var assetsWithID = from a1 in assets.Rows.OfType <DataGridViewRow>()
join a2 in ds.Tables["tblAssetGroups"].AsEnumerable()
on a1.Cells["AssetName"].Value.ToString() equals a2.Field <string>("AssetName")
select new
{
FileName = a1.Cells["FileName"].Value.ToString(),
AssetName = a1.Cells["AssetName"].Value.ToString(),
AssetID = a2.Field <int>("AssetID")
};
// Join assets data to the new assets register records
IEnumerable <mdl_AssetsRegister> arTmp =
from a1 in ar
join a2 in assetsWithID on a1.DataFileName equals a2.FileName into FilesWithAssetInfo
from a3 in FilesWithAssetInfo.DefaultIfEmpty()
select new mdl_AssetsRegister
{
Project = a1.Project,
DataFileName = a1.DataFileName,
VreFilePath = a1.VreFilePath,
DataRepoFilePath = a1.DataRepoFilePath,
AssetID = a3?.AssetID
};
//ar = arTmp.GroupBy(x => x.DataFileName, (key, grp) => grp.First()).ToList();
ar = arTmp.ToList();
}
int arRows = SQL_Stuff.insertBulk(ar.ToDataTable(), "dbo.tblAssetsRegister", conn, trans);
success[2] = arRows == ar.Count();
// Get new FileIDs by re-querying the tbl
GetAssetsRegister(ds, conn, trans);
// tblAssetsChangeLog insert
List <mdl_AssetsChangeLog> cl = CollectAssetsChangeLogInsert(ds, tr.RequestID, files, rej, tm, tf, tt, dsa);
int clRows = SQL_Stuff.insertBulk(cl.ToDataTable(), "dbo.tblAssetsChangeLog", conn, trans);
success[3] = clRows == cl.Count();
trans.Commit();
}
catch (Exception ex)
{
MessageBox.Show(
"Failed to add new file transfer record(s):" + ex.GetType() + "\n\n" +
ex.Message + "\n\n" +
ex.StackTrace
);
try
{
trans.Rollback();
}
catch (Exception ex2)
{
MessageBox.Show(
"Failed to roll back transaction:" + ex2.GetType() + "\n\n" +
ex2.Message + "\n\n" +
ex2.StackTrace
);
}
}
}
return(!success.Contains(false));
}
