SPSecurity.RunWithElevatedPrivileges(delegate()
{
try
{
SPSite spSite = SPContext.Current.Site;
SPWeb spWeb = spSite.OpenWeb();
SPList spList = spWeb.Lists["Master List"];
SPListItemCollection spListItemCollection = spList.Items;
spWeb.AllowUnsafeUpdates = true;
int cntError = 0;
int cntSuccess = 0;
foreach (SPListItem spListItem in spListItemCollection)
{
string strConnection = @"Server=111.111.111.111;user id=username;password=userpassword;database=SQLDatabase;max pool size=300;pooling=true;";
DataTable sqlDataTable = new DataTable();
string strQuery = string.Empty;
strQuery = @"SELECT * FROM [SQLDatabase].[dbo].[tbl_SystemData] WHERE [CustomerName]=@CustomerName;";
SqlConnection sqlConnection = new SqlConnection(strConnection);
if (sqlConnection.State == ConnectionState.Closed)
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand(strQuery, sqlConnection);
SqlParameter sqlParameter = new SqlParameter();
sqlParameter.ParameterName = "@CustomerName";
sqlParameter.Value = spListItem["Title"].ToString();
sqlCommand.Parameters.Add(sqlParameter);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
DataSet sqlDataSet = new DataSet();
sqlDataAdapter.Fill(sqlDataSet);
sqlDataTable = sqlDataSet.Tables[0];
if (sqlDataTable.Rows.Count > 0)
{
if (sqlDataTable.Rows.Count > 1)
{
//multiple record in database
try
{
int cntType = 0;
int cntOD = 0;
string strType = string.Empty;
string strOD = string.Empty;
foreach (DataRow sqlDataRow in sqlDataTable.Rows)
{
if (sqlDataRow[3] != null)
spListItem["Identity"] = sqlDataRow[3].ToString();
if (sqlDataRow[4] != null)
spListItem["SystemStatusReview"] = sqlDataRow[4].ToString();
if (sqlDataRow[5] != null)
spListItem["Renewal"] = sqlDataRow[5].ToString();
if (!string.IsNullOrEmpty(sqlDataRow[6].ToString()))
spListItem["RatingDate"] = DateTime.Parse(sqlDataRow[6].ToString()).ToString("dd-MMMM-yyyy");
if (sqlDataRow[7] != null)
spListItem["Rating"] = sqlDataRow[7].ToString();
if (sqlDataRow[8] != null)
{
if (cntType < 2)
{
if (string.IsNullOrEmpty(strType.ToString()))
strType = sqlDataRow[8].ToString();
else
{
if (strType.ToString() == "Type1" && strType.ToString() != sqlDataRow[8].ToString() || strType.ToString() == "Type2" && strType.ToString() != sqlDataRow[8].ToString())
strType = "Type1/Type2";
else
{
if (strType.ToString() == "Type1" && sqlDataRow[8].ToString() == "Type1")
strType = "Type1";
else
strType = "Type2";
**
**
cntType++;
**
**
if (sqlDataRow[9] != null)
{
if (cntOD < 1)
{
if (string.IsNullOrEmpty(strOD.ToString()))
strOD = sqlDataRow[9].ToString();
else
{
if (strOD.ToString() == "Category1" && strOD.ToString() != sqlDataRow[8].ToString() || strOD.ToString() == "Category2" && strOD.ToString() != sqlDataRow[8].ToString())
strOD = "Category1";
else
{
if (strOD.ToString() == "Category1" && sqlDataRow[8].ToString() == "Category1")
strOD = "Category1";
else
strOD = "Category2";
**
**
cntOD++;
**
**
if (!string.IsNullOrEmpty(sqlDataRow[10].ToString()))
spListItem["LastReviewDate"] = DateTime.Parse(sqlDataRow[10].ToString()).ToString("dd-MMMM-yyyy");
if (!string.IsNullOrEmpty(sqlDataRow[12].ToString()))
{
string strTestMultipleUser = "domain\\" + sqlDataRow[12].ToString();
PickerEntity entity = new PickerEntity();
int pos = strTestMultipleUser.IndexOf('#');
strTestMultipleUser = strTestMultipleUser.Substring(pos + 1);
entity.Key = strTestMultipleUser;
SPMember spTestMember = SPContext.Current.Web.AllUsers[strTestMultipleUser.ToString()];
SPFieldUserValueCollection UserCollection = new SPFieldUserValueCollection();
SPFieldUserValue UserName = new SPFieldUserValue(spWeb, spTestMember.ID, spWeb.AllUsers.GetByID(Convert.ToInt32(spTestMember.ID)).LoginName);
UserCollection.Add(UserName);
spListItem["Officer"] = UserCollection;
**
if (!string.IsNullOrEmpty(sqlDataRow[13].ToString()))
spListItem["NextReviewDate"] = DateTime.Parse(sqlDataRow[13].ToString()).ToString("dd-MMMM-yyyy");
if (sqlDataRow[14] != null)
spListItem["MonthsOverdue"] = sqlDataRow[14].ToString() + " month";
if (sqlDataRow[15] != null)
spListItem["AccountNo"] = sqlDataRow[15].ToString();
if (sqlDataRow[16] != null)
spListItem["Facility"] = sqlDataRow[16].ToString();
if (sqlDataRow[17] != null)
spListItem["FullySecured"] = sqlDataRow[17].ToString();
if (sqlDataRow[18] != null)
spListItem["SystemStatus"] = sqlDataRow[18].ToString();
**
if (cntType > 0)
spListItem["Type"] = strType.ToString();
else
spListItem["Type"] = strType.ToString();
if (cntOD > 0)
spListItem["Category"] = "Category1";
else
spListItem["Category"] = strOD.ToString();
**
catch (Exception ex)
{
Response.Write("Error while trying to map <b>" + spListItem["Title"].ToString() + "</b> with multiple System data with error message : <br/>" + ex.ToString() + "<br/>");
cntError++;
**
finally
{
sqlConnection.Close();
**
**
else
{
//only single record in database
try
{
foreach (DataRow sqlDataRow in sqlDataTable.Rows)
{
if (sqlDataRow[3] != null)
spListItem["Identity"] = sqlDataRow[3].ToString();
if (sqlDataRow[4] != null)
spListItem["SystemStatusReview"] = sqlDataRow[4].ToString();
if (sqlDataRow[5] != null)
spListItem["Renewal"] = sqlDataRow[5].ToString();
if (!string.IsNullOrEmpty(sqlDataRow[6].ToString()))
spListItem["RatingDate"] = DateTime.Parse(sqlDataRow[6].ToString()).ToString("dd-MMMM-yyyy");
if (sqlDataRow[7] != null)
spListItem["Rating"] = sqlDataRow[7].ToString();
if (sqlDataRow[8] != null)
spListItem["Type"] = sqlDataRow[8].ToString();
if (sqlDataRow[9] != null)
spListItem["Category"] = sqlDataRow[9].ToString();
if (!string.IsNullOrEmpty(sqlDataRow[10].ToString()))
spListItem["LastReviewDate"] = DateTime.Parse(sqlDataRow[10].ToString()).ToString("dd-MMMM-yyyy");
if (!string.IsNullOrEmpty(sqlDataRow[12].ToString()))
{
string strTestMultipleUser = "domain\\" + sqlDataRow[12].ToString();
PickerEntity entity = new PickerEntity();
int pos = strTestMultipleUser.IndexOf('#');
strTestMultipleUser = strTestMultipleUser.Substring(pos + 1);
entity.Key = strTestMultipleUser;
SPMember spTestMember = SPContext.Current.Web.AllUsers[strTestMultipleUser.ToString()];
SPFieldUserValueCollection UserCollection = new SPFieldUserValueCollection();
SPFieldUserValue UserName = new SPFieldUserValue(spWeb, spTestMember.ID, spWeb.AllUsers.GetByID(Convert.ToInt32(spTestMember.ID)).LoginName);
UserCollection.Add(UserName);
spListItem["Officer"] = UserCollection;
**
if (!string.IsNullOrEmpty(sqlDataRow[13].ToString()))
spListItem["NextReviewDate"] = DateTime.Parse(sqlDataRow[13].ToString()).ToString("dd-MMMM-yyyy");
if (sqlDataRow[14] != null)
spListItem["MonthsOverdue"] = sqlDataRow[14].ToString() + " month";
if (sqlDataRow[15] != null)
spListItem["AccountNo"] = sqlDataRow[15].ToString();
if (sqlDataRow[16] != null)
spListItem["Facility"] = sqlDataRow[16].ToString();
if (sqlDataRow[17] != null)
spListItem["FullySecured"] = sqlDataRow[17].ToString();
if (sqlDataRow[18] != null)
spListItem["SystemStatus"] = sqlDataRow[18].ToString();
**
**
catch (Exception ex)
{
Response.Write("Error while trying to map <b>" + spListItem["Title"].ToString() + "</b> with single System data with error message : <br/>" + ex.ToString() + "<br/>");
cntError++;
**
finally
{
sqlConnection.Close();
**
**
spListItem.Update();
**
cntSuccess++;
**
spWeb.AllowUnsafeUpdates = false;
**
catch (Exception ex)
{
Response.Write("Error while validate the data with error message : <br/>" + ex.ToString() + "<br/>");
**
finally
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Alert", "alert('Process completed, all data has been updated!');", true);
**
**);
