Tag Archives: MSSQL

This is a small example of how to collect the contact (in this case a table of Microsoft Dynamics Navision MSSQL) in a public folder Exchange contacts “test”,to do this there are several methods, in Exchange 2007 and Exchange 2010 sp1 is better to use EWS (Exchange Web sevices), but Exchange 2003 does not have that feature, alternative ways: (WEBDAV, COM, DLL from third parties, etc. .. .)
The easiest way I found is using the COM reference Microsoft Outlook 12.0 within the proposed visual studio 2008 in c #, not if it is the best and the fastest, the advantage is that it is compatible with all versions of Exchange.
 

try
{
	timer1.Enabled = false;
	int a,rpcCount;
	lblmis.Text = "Sincronizando Contactos...";
	Microsoft.Office.Interop.Outlook.Application oApp = new Microsoft.Office.Interop.Outlook.Application();
	NameSpace oNS = oApp.GetNamespace("MAPI");
	Registre reg = new Registre();
	MAPIFolder oAllPublicFolders; //what it says on the tin
	MAPIFolder oPublicFolders; // as above
	MAPIFolder objContacts; //as above
	//search the folder test in public folders
	oPublicFolders = oNS.Folders["Carpetas públicas"];
	oAllPublicFolders = oPublicFolders.Folders["Todas las carpetas públicas"];
	objContacts = oAllPublicFolders.Folders["test"];
	 
	Items oItems = objContacts.Items;
	 
	a = oItems.Count;
	progressBar1.Maximum = a;
	progressBar1.Value = 0;
	// search contacts from a table from MSSQL :
	string sql;
	SqlDataAdapter da;
	DataTable DTsql = new DataTable();
	DataSet ds;
	ds = new DataSet();
	string dsn = "server=" + servidor + ";database=" + bbdd + ";uid=" + usuari + ";pwd=" + password;
	SqlConnection myconnection = new SqlConnection(dsn);
	myconnection.Open();
	sql = "SELECT * FROM [" + empresa + "$Contact] ";
	da = new SqlDataAdapter(sql, myconnection);
	da.Fill(DTsql);
	 
	//delete non existents ( the primary key of the contact is "CustomerID") in my case
	lblmis.Text = "Deleting non existents..... ";
	DTsql.PrimaryKey = new DataColumn[] { DTsql.Columns["No_"] };
	progressBar1.Maximum = a;
	a = 0;
	rpcCount = 0;
	progressBar1.Value = 0;
	foreach (ContactItem coi in oItems)
	{
		if (coi.CustomerID.ToString().Trim() != "")
		{
			DataRow resul= DTsql.Rows.Find(coi.CustomerID.ToString());
			if (resul == null)
			{
				lblmis.Text = "deleting: " + coi.FirstName.ToString();
				coi.Delete();
			}
		resul = null;
		}
		else
		{
			coi.Delete();
		}
		rpcCount++;
		// the next is necessary if you use outllook in cached mode ( to solve the error only 250 items can be open error)
		if (rpcCount > 50)
		{
			lblmis.Text = "Borrant no existents..... ";
			rpcCount = 0;
			//force garbage collection and see if that decrements open message counter on exchange server.  We have used both a countdown loop as shown here, and also during each single loop.
			GC.Collect();
			GC.WaitForPendingFinalizers();
			GC.Collect();
			GC.WaitForPendingFinalizers();  //tried a second one also based on the articles
		}
		System.Windows.Forms.Application.DoEvents();
		lbltemps.Text = progressBar1.Value.ToString();
		progressBar1.Value = progressBar1.Value + 1;
	}
	//update or Add existents ( the primary key of the contact is "CustomerID") in my case
	a = 0;
	rpcCount = 0;
	if (DTsql.Rows.Count > 0)
	{
		progressBar1.Maximum = DTsql.Rows.Count;
		progressBar1.Value = 0;
		// Do for each row in the sql result
		foreach (DataRow dr in DTsql.Rows)
		{
			lbltemps.Text = progressBar1.Value.ToString();
			lblmis.Text = dr["Name"].ToString();
			System.Windows.Forms.Application.DoEvents();
			// set the first and last name to search for
			string sFirstName = dr["No_"].ToString();
			// field name to search in [] and the value to search in ''.
			string sSearch = String.Format("[CustomerID]='{0}' ", sFirstName);
			//busquem:
			ContactItem contact = (ContactItem)oItems.Find(sSearch);
			 
			if (contact != null)
			{
			// Contact found, update
			if (altes.Trim() != "1")
				{
				contact.FirstName = dr["Name"].ToString().Replace("'", "´").ToString();
				contact.LastName = "";
				contact.Email1Address = dr["E-Mail"].ToString();
				contact.MailingAddressStreet = dr["Address"].ToString().Replace("'", "´").ToString() + "\n " + dr["Address 2"].ToString().Replace("'", "´").ToString();
				contact.MailingAddressCity = dr["City"].ToString();
				contact.MailingAddressPostalCode = dr["Post Code"].ToString();
				contact.PrimaryTelephoneNumber = dr["Phone No_"].ToString();
				contact.HomeFaxNumber = dr["Fax No_"].ToString();
				contact.TelexNumber = dr["Telex No_"].ToString();
				contact.Email2Address = dr["E-Mail 2"].ToString();
				contact.MailingAddressState = dr["County"].ToString();
				contact.CompanyName = dr["Company Name"].ToString();
				System.Windows.Forms.Application.DoEvents();
				contact.Save();
				 
				}
			}
			else
			{
				//add contact
				ContactItem newContact = (ContactItem)objContacts.Items.Add(OlItemType.olContactItem);
				newContact.FirstName = dr["Name"].ToString().Replace("'", "´").ToString();
				newContact.LastName = "";
				newContact.Email1Address = dr["E-Mail"].ToString();
				newContact.MailingAddressStreet = dr["Address"].ToString().Replace("'", "´").ToString() + "\n " + dr["Address 2"].ToString().Replace("'", "´").ToString(); ;
				newContact.MailingAddressCity = dr["City"].ToString();
				newContact.MailingAddressPostalCode = dr["Post Code"].ToString();
				newContact.PrimaryTelephoneNumber = dr["Phone No_"].ToString();
				newContact.HomeFaxNumber = dr["Fax No_"].ToString();
				newContact.TelexNumber = dr["Telex No_"].ToString();
				newContact.Email2Address = dr["E-Mail 2"].ToString();
				newContact.MailingAddressState = dr["County"].ToString();
				newContact.CompanyName = dr["Company Name"].ToString().Replace("'", "´").ToString() ;
				newContact.CustomerID = dr["No_"].ToString();
				System.Windows.Forms.Application.DoEvents();
				newContact.Save();
				//newContact.Close(OlInspectorClose.olDiscard );
				 
				//newContact.Display(true);
			}
			a = a + 1;
			progressBar1.Value = a;
			rpcCount++;
			//the next is necessary if you use outllook in cached mode ( to solve the error only 250 items can be open error)
			if (rpcCount > 50)
			{
				rpcCount = 0;
				//force garbage collection and see if that decrements open message counter on exchange server.  We have used both a countdown loop as shown here, and also during each single loop.
				GC.Collect();
				GC.WaitForPendingFinalizers();
				GC.Collect();
				GC.WaitForPendingFinalizers();  //tried a second one also based on the articles
			}
	 
		}
	}
	 
	progressBar1.Value = 0;
	segons = reg.Read("segons").ToString();
	lblmis.Text = "Esperant...";
	this.Close();
}
catch (SqlException er)
{
	MessageBox.Show(er.Message, "SQL Error");
	this.Close();
}
catch (System.Exception  er)
{
	MessageBox.Show(er.Message, "General Error");
	this.Close();
 
}