The problem:
I have a xml variable Xdocument in C# like this:
I want to do a “group by” all the same records:
Category Archives: c#
Hi guys!
I have developed this simple dll for the barcode printing / representation, the use is very simple :
this is the code of the sample form:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using BarcodeFree;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Code128 text = new Code128();
this.textBox2.Text = text.Encode( textBox1.Text);
ImageBarcode barcode, barcode2, barcode3, barcode4;
//parameters to call ImageBarcode:
//public ImageBarcode(int width, int height, string value, bool Landscape = false, Color? color = null,Color? colorFore=null )
barcode = new ImageBarcode(pictureBox1.Width, pictureBox1.Height, textBox2.Text);
barcode2 = new ImageBarcode(pictureBox2.Width, pictureBox2.Height, textBox2.Text, false, Color.Red, Color.Yellow );
barcode3 = new ImageBarcode(pictureBox3.Width, pictureBox3.Height, textBox2.Text, true);
barcode4 = new ImageBarcode(pictureBox4.Width, pictureBox4.Height, textBox2.Text, false, Color.Turquoise , Color.LightGray);
// barcode = new ImageBarcode(250, 112, textBox2.Text);
this.pictureBox1.Image = barcode.GenerateImage();
this.pictureBox2.Image = barcode2.GenerateImage();
this.pictureBox3.Image = barcode3.GenerateImage();
this.pictureBox4.Image = barcode4.GenerateImage();
// this.pictureBox1.Refresh();
}
}
}
And Finally the DLL( for demo test):
Some documentation about barcodes:
http://www.keyence.com/ss/products/auto_id/barcode_lecture/basic/code128/
http://www.jtbarton.com/Barcodes/BarcodeStringBuilderExample.aspx
http://www.jtbarton.com/Barcodes/BarcodeOverview.aspx
http://www.gs1.org/docs/barcodes/GS1_General_Specifications.pdf
especially thanks to:
http://grandzebu.net/informatique/codbar-en/code128.htm
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();
}
An example of a project in c # where I use different ways to acquire data from a MSSQL database, using a “dades.xsd” and with direct connections.
a little to see the different ways of connection to the database:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
// references added :
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace comandes_Web
{
public partial class _Default : System.Web.UI.Page
{
private string cadena;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btacceptar_Click(object sender, EventArgs e)
{
// with this line we take configuration of "web.config" file at value: ConnectionStrings
cadena = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString ;
//using dades.xsd
dades.tenUsersDataTable res1;
res1 = new dades.UsersDataTable();
dadesTableAdapters.UsersTableAdapter TenTA;
TenTA = new dadesTableAdapters.UsersTableAdapter();
TenTA.Fill(res1);
//ussing direct metods
SqlDataAdapter daSql;
SqlConnection conSql;
DataSet res2;
DataTable res3;
conSql = new SqlConnection(cadena); // using the connection string dades.xsd
daSql = new SqlDataAdapter("SELECT Codigo, Password FROM Users", conSql);
//with dataset
res2 = new DataSet() ;
daSql.Fill(res2, "Users");
//with datatable
res3 = new DataTable();
daSql.Fill(res3);
//see the number or files 3 diferent ways:
lblresul.Text = res1.Count.ToString();
lblresul.Text = res2.Tables["Users"].Rows.Count.ToString() ;
lblresul.Text = res3.Rows.Count.ToString() ;
//see rows content 3 diferent ways:<
lblresul.Text = res1[0]["Codigo"].ToString() + " - " + res1[0]["Password"].ToString();
lblresul.Text = res2.Tables["Users"].Rows[0]["Codigo"].ToString() + " - " + res2.Tables["Users"].Rows[0]["Password"].ToString();
lblresul.Text = res3.Rows[0]["Codigo"].ToString() + " - " + res3.Rows [0]["Password"].ToString();
//more complex!!
res3.Clear();
res3 = res2.Tables["Users"];
lblresul.Text = res3.Rows[0]["Codigo"].ToString() + " - " + res3.Rows[0]["Password"].ToString();
int resultat;
//call scalar query with dades.xsd
dadesTableAdapters.QueriesTableAdapter queryTA;
queryTA = new dadesTableAdapters.QueriesTableAdapter();
resultat= (int) queryTA.ComprovarUsuari("user", "password");
lblresul.Text = resultat.ToString ();
//a query only returns one value an can do INSERTS UPDATES AND DELETES
//call a query (direct metoth) ( no parameters)
SqlCommand querysql;
string resuls;
querysql = new SqlCommand("SELECT Codigo,Password FROM Users ", conSql);
conSql.Open();
// 2 ways to do ( one value , first row, first column )
resuls = (string)querysql.ExecuteScalar();
resuls = querysql.ExecuteScalar().ToString();
lblresul.Text = resuls ;
//execute query with datareader
SqlDataReader sqldatar;
sqldatar = querysql.ExecuteReader();
//loop to see files content
while (sqldatar.Read())
{
lblresul.Text = sqldatar[0].ToString ();
lblresul.Text = sqldatar["Codigo"].ToString();
}
sqldatar.Close();
sqldatar.Dispose();
//execute query , only return files affected
int files;
files = querysql.ExecuteNonQuery();
lblresul.Text = files.ToString();
conSql.Close();
}
}