chaitanyapallavi: 2013

Friday, March 15, 2013

To insert and update the data from xml files into db with transaction scope and the mail alert for the failed xml files as attachements

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Transactions;
using System.Net.Mail;
using System.Net.Mime;
using System.Net;
namespace ReadXML
{
class Program
{
static void Main(string[] args)
{
string path = "C:\\AXXML\\HIMS\\IN";
//string Filename = "";
//String FileNam = "";
DirectoryInfo dir = new DirectoryInfo(path);
string[] files = Directory.GetFiles(path, "*.xml");
//foreach (FileInfo flInfo in dir.GetFiles("*.xml"))
//{
foreach (string Filename in files)
{

try
{
SqlCommand cmd1 = null;
object obj1 = null;
//Filename = flInfo.FullName;
//FileNam = flInfo.Name;
//DataSet dsData = new DataSet();
//dsData.ReadXml(Filename);

XmlDataDocument xmldoc = new XmlDataDocument();
xmldoc.Load(Filename);
XmlElement root = xmldoc.DocumentElement;
bool flag = false;

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
{
try
{
string strConnection = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ToString();
SqlConnection connection = new SqlConnection(strConnection);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
for (int i = 0; i < root.ChildNodes.Count; i++)
{
if (root.ChildNodes.Item(i).Name.ToString() == "Record")
{
string Name = root.ChildNodes.Item(i).FirstChild.Name;
string Value = root.ChildNodes.Item(i).FirstChild.InnerText;
string query = "SELECT COUNT(*) FROM " + root.Name + " WHERE " + Name + "=" + Value;
SqlCommand cmd = new SqlCommand(query, connection);
object obj = cmd.ExecuteScalar();

if (obj.ToString() == "0")
{
string InsQuery = "INSERT INTO " + root.Name + " (";
string InsFileds = "";
string InsFiledValues = "";
for (int j = 0; j < root.ChildNodes.Item(i).ChildNodes.Count; j++)
{
string ElName = root.ChildNodes.Item(i).ChildNodes.Item(j).Name;
string ELValue = root.ChildNodes.Item(i).ChildNodes.Item(j).InnerText.Replace("'", "''");
InsFileds = InsFileds + ElName + ", ";
InsFiledValues = InsFiledValues + "'" + ELValue + "', ";
}
InsFileds = InsFileds.Substring(0, InsFileds.Length - 2);

InsQuery = InsQuery + InsFileds + ") VALUES (" + InsFiledValues.Substring(0, InsFiledValues.Length - 2) + ")";

cmd1 = new SqlCommand(InsQuery, connection);
obj1 = cmd1.ExecuteNonQuery();
if (obj1.ToString() == "0")
{
flag = true;
}
}
else
{
string UpdQuery = "UPDATE " + root.Name + " SET ";
string UpdFileds = "";
string UpdFiledValues = "";
for (int j = 0; j < root.ChildNodes.Item(i).ChildNodes.Count; j++)
{
string ElName = root.ChildNodes.Item(i).ChildNodes.Item(j).Name;
string ELValue = root.ChildNodes.Item(i).ChildNodes.Item(j).InnerText;
if (ElName != Name)
{
UpdFileds = UpdFileds + ElName + " = '" + ELValue + "', ";
}
}
UpdQuery = UpdQuery + UpdFileds.Substring(0, UpdFileds.Length - 2) + " WHERE " + Name + "='" + Value + "'";

cmd1 = new SqlCommand(UpdQuery, connection);
obj1 = cmd1.ExecuteNonQuery();
if (obj1.ToString() == "0")
{
flag = true;

}
}
}
}
if (connection.State == ConnectionState.Open)
{
connection.Close();
connection.Dispose();
}
if (flag == true)
{
scope.Dispose();
System.IO.File.Move(Filename, "C:\\AXXML\\HIMS\\ErrorIN\\" + Filename.Substring(17));
}
else
{
scope.Complete();
}
}
catch (Exception e)
{
scope.Dispose();
System.IO.File.Move(Filename, "C:\\AXXML\\HIMS\\ErrorIN\\" + Filename.Substring(17));
}
}
}
catch (Exception es)
{
throw es;
}

}
Program objProgram = new Program();
objProgram.MailAlert();
}
public void MailAlert()
{
MailMessage msgMail = new MailMessage();
msgMail.From = new MailAddress("HMSTeam@dhanushinfotech.net", "HMS Team");

msgMail.To.Add("pallavikumaridasam.c@dhanushinfotech.net");
msgMail.IsBodyHtml = true;
msgMail.Subject = "Failed XML Files";
msgMail.Body = "<html>" +
"<body> " +
"Dear, " +
"The attached files are failed to insert the data into db" +

"Best Regards, " +
"Team HMS" +
"</body>" +
" </html>";
string path = "C:\\AXXML\\HIMS\\ErrorIN";

DirectoryInfo dir = new DirectoryInfo(path);
string[] XMLfile = Directory.GetFiles(path, "*.xml");

foreach (string file in XMLfile)
{
// Create the file attachment for this e-mail message.
Attachment data = new Attachment(file, MediaTypeNames.Application.Octet);
// Add time stamp information for the file.
ContentDisposition disposition = data.ContentDisposition;
disposition.CreationDate = System.IO.File.GetCreationTime(file);
disposition.ModificationDate = System.IO.File.GetLastWriteTime(file);
disposition.ReadDate = System.IO.File.GetLastAccessTime(file);
// Add the file attachment to this e-mail message.
msgMail.Attachments.Add(data);
}
SmtpClient smtpC = new SmtpClient();
smtpC.Host = "smtpout.secureserver.net";

smtpC.Credentials = new System.Net.NetworkCredential("pallavikumaridasam.c@dhanushinfotech.net", "123456aA");
smtpC.Send(msgMail);

}
}
}

Monday, March 11, 2013

To insert and update the data from XML files into database

To insert and update the data into database from XML files which are placed in a folder

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace ReadXML
{
class Program
{
static void Main(string[] args)
{
string path = "D:";
string Filename = "";
String FileNam = "";
DirectoryInfo dir = new DirectoryInfo(path);

foreach (FileInfo flInfo in dir.GetFiles("*.xml"))
{
try
{
SqlCommand cmd1 = null;
object obj1=null;
Filename = flInfo.FullName;
FileNam = flInfo.Name;

//xml data into Dataset
// DataSet dsData = new DataSet();
// dsData.ReadXml(Filename);

XmlDataDocument xmldoc = new XmlDataDocument();
xmldoc.Load(Filename);
XmlElement root = xmldoc.DocumentElement; // to know the root tag

// To check the data in db whether it exist or not
for (int i = 0; i < root.ChildNodes.Count; i++)
{
if (root.ChildNodes.Item(i).Name.ToString() == "Record")
{
string Name = root.ChildNodes.Item(i).FirstChild.Name;
string Value = root.ChildNodes.Item(i).FirstChild.InnerText;
string query = "SELECT COUNT(*) FROM " + root.Name + " WHERE " + Name + "=" + Value;
string strConnection = ConfigurationManager.ConnectionStrings["HMSConnectionString"].ToString(); // To read Connection String from app.config
SqlConnection connection = new SqlConnection(strConnection);
connection.Open();
SqlCommand cmd = new SqlCommand(query, connection);
object obj = cmd.ExecuteScalar();
connection.Close();
// To insert the xml data into db
if (obj.ToString() == "0")
{
string InsQuery = "INSERT INTO " + root.Name + " (";
string InsFileds = "";
string InsFiledValues = "";
for (int j = 0; j < root.ChildNodes.Item(i).ChildNodes.Count; j++)
{
string ElName = root.ChildNodes.Item(i).ChildNodes.Item(j).Name;
string ELValue = root.ChildNodes.Item(i).ChildNodes.Item(j).InnerText;
InsFileds = InsFileds + ElName + ", ";
InsFiledValues = InsFiledValues +"'"+ ELValue + "', ";
}
InsFileds = InsFileds.Substring(0,InsFileds.Length-2);

InsQuery = InsQuery + InsFileds + ") VALUES (" + InsFiledValues.Substring(0, InsFiledValues.Length - 2) + ")";
connection.Open();
cmd1 = new SqlCommand(InsQuery, connection);
obj1 = cmd1.ExecuteScalar();
connection.Close();
}

// To update the xml data into db
else
{
string UpdQuery = "UPDATE " + root.Name + " SET ";
string UpdFileds = "";
string UpdFiledValues = "";
for (int j = 0; j < root.ChildNodes.Item(i).ChildNodes.Count; j++)
{
string ElName = root.ChildNodes.Item(i).ChildNodes.Item(j).Name;
string ELValue = root.ChildNodes.Item(i).ChildNodes.Item(j).InnerText;
if (ElName != Name)
{
UpdFileds = UpdFileds + ElName + " = '" + ELValue + "', ";
}
}
UpdQuery = UpdQuery + UpdFileds.Substring(0, UpdFileds.Length - 2) + " WHERE " + Name + "='" + Value + "'";
connection.Open();
cmd1 = new SqlCommand(UpdQuery, connection);
obj1 = cmd1.ExecuteScalar();
connection.Close();
}
}
}
}
catch (Exception es)
{
throw es;
}

}
}

}
}

Sunday, January 6, 2013

To disable the particular value in rad combo box

long SchemID = 0;
                int Status = 0;
                foreach (Schema obj in lstSchemas)
                {
                    SchemID = obj.SchemaID;
                    Status = obj.Status;
                    if (Status == 0)
                    {
                        RadComboBoxItemitem=drp_InsuranceCompScheme.FindItemByValue(SchemID.ToString());
                        item.Enabled = false;
                    }
                }

chaitanyapallavi