RSS

Monthly Archives: July 2012

Resolve : Issue in reading CVS file in Silverlight


Hi,
While reading CSV file, in each line first character is missing from the file.the solution of this problem below

 

PhoneNumber  ClientName	Status
334455	     sajjad ahmed Closed

Problem:
every record missing phonenumber first digit miss

Befor Solution:

                    StreamReader lObjStreamReader = new StreamReader(dlgOpen.File.OpenRead());

                    while (( lObjStreamReader.ReadLine()) != null)
                    {
                         string= lObjStreamReader.ReadLine();

After Solution:

 StreamReader lObjStreamReader = new StreamReader(dlgOpen.File.OpenRead());

                    bool lBlnIsColumnRow = true;
                    string lStrLine;
                    while ((lStrLine = lObjStreamReader.ReadLine()) != null)
                    {
                        // lStrLine = lObjStreamReader.ReadLine();


Fully Solution Code:

create list of class object for pass to the webservices as array

        List<SaveCampaignLeads> lArrObjclsGridLoadExcelData = new List<SaveCampaignLeads>();


    private void btnUpload_Click(object sender, RoutedEventArgs e)
        {

            try
            {
                lFnLoadCSVDataInGrid();
              //  tblead = lArrObjclsGridLoadExcelData.ToList();
            
            }

            catch (Exception)
            {
                throw;

            }


Function Upload File:



        public void lFnLoadCSVDataInGrid()
        {
            try
            {
               
                lArrObjclsGridLoadExcelData.Clear();
                int j = 1;
               
                OpenFileDialog dlgOpen = new OpenFileDialog();
                dlgOpen.Filter = "CSV Files (*.csv)|*.csv";
                bool? res = dlgOpen.ShowDialog();

                if (res == true)
                {


                    txtCSV.MaskedText = dlgOpen.File.Name;
                    txtCSV.IsReadOnly = true;

                    StreamReader lObjStreamReader = new StreamReader(dlgOpen.File.OpenRead());

                    bool lBlnIsColumnRow = true;
                    string lStrLine;
                    while ((lStrLine = lObjStreamReader.ReadLine()) != null)
                    {
                        // lStrLine = lObjStreamReader.ReadLine();

                        if (lBlnIsColumnRow)
                        {
                            lBlnIsColumnRow = false;
                            continue;
                        }

                        if (lStrLine == null)
                            break;

                        if (lStrLine.Trim() == "")
                            continue;

                        string[] lArrStrCells = null;

                        lArrStrCells = lStrLine.Split(",".ToCharArray());

                        if (lArrStrCells == null)
                            continue;

                        if (!(lArrStrCells.Length == 11))
                        {
                            MessageBox.Show("CSV file Column greater than required column!","Column Error",MessageBoxButton.OK);
                            continue;
                        }
                       
                      

                        SaveCampaignLeads lObjclsGridLoadExcelData = new SaveCampaignLeads();


                        lObjclsGridLoadExcelData.Leadidpk = j;
                        lObjclsGridLoadExcelData.Listidfk = j;

                        lObjclsGridLoadExcelData.Phonecode = Convert.ToSByte(lArrStrCells[0]);
                        lObjclsGridLoadExcelData.Phonenumber = lArrStrCells[1];
                        lObjclsGridLoadExcelData.Title = lArrStrCells[2];
                        lObjclsGridLoadExcelData.Firstname = lArrStrCells[3];
                        lObjclsGridLoadExcelData.Lastname = lArrStrCells[4];
                        lObjclsGridLoadExcelData.Surename = lArrStrCells[5];
                        lObjclsGridLoadExcelData.Address1 = lArrStrCells[6];
                        lObjclsGridLoadExcelData.Address2 = lArrStrCells[7];
                        lObjclsGridLoadExcelData.City = lArrStrCells[8];
                        lObjclsGridLoadExcelData.State = lArrStrCells[9];
                        lObjclsGridLoadExcelData.Zip = lArrStrCells[10];
                        lObjclsGridLoadExcelData.Datetimecallback = DateTime.Now;
                        lObjclsGridLoadExcelData.Comments = "";
                        lObjclsGridLoadExcelData.Agent = "";
                        lObjclsGridLoadExcelData.Attempts = 0;
                        lObjclsGridLoadExcelData.Lastupdate = DateTime.Now;

                        lArrObjclsGridLoadExcelData.Add(lObjclsGridLoadExcelData);



                        j++;
                    }

                    lObjStreamReader.Close();
                    lObjStreamReader.Dispose();


                }
                //  return lArrObjclsGridLoadExcelData;
            }
            catch (Exception ex)
            {
                txtCSV.Value = "";
              MessageBox.Show(ex.Message.ToString(),"File Upload",MessageBoxButton.OK);
                // return null;
            }
        }


Pass To Web Service upload csv file:

 private void btnSave_Click(object sender, RoutedEventArgs e)
        {

            if (isvalidate())
            {
                this.rbiActivity.IsBusy = true;
                SetMyLeadsWebServiceSoapClient ServiceClient = manager.ServiceClient;
                ServiceClient.ListLeadsSaveCompleted += new EventHandler<ListLeadsSaveCompletedEventArgs>(ServiceClient_ListLeadsSaveCompleted);
                ServiceClient.ListLeadsSaveAsync(this.listpk, this.CampaignId, Convert.ToString(txtName.Value), lArrObjclsGridLoadExcelData.ToArray(), manager.User.Login, manager.User.Password);
                 
            }


            else
            {
                MessageBox.Show(Validation, "Required", MessageBoxButton.OK);

            }
        }
       
        void ServiceClient_ListLeadsSaveCompleted(object sender, ListLeadsSaveCompletedEventArgs e)
        {
            if (e.Error == null)
            {
               
               
                    this.rbiActivity.IsBusy = false;
                 
                    MessageBox.Show(e.Result);
                    this.DialogResult = true;
                    this.Close();
               
            }
        }

        private bool isvalidate()
        {
            List<string> lstError = new List<string>();
            List<string> lstFileError = new List<string>();
            if (Convert.ToString(txtName.Value).Trim().Length == 0)
            {
                lstError.Add("Title field is empty");
            }
            if (Convert.ToString(txtCSV.MaskedText).Trim().Length == 0)
            {
                    lstFileError.Add("File is Empty");
            }

            if (lArrObjclsGridLoadExcelData.Count > 0)
            {
                for (int i = 0; i < lArrObjclsGridLoadExcelData.Count; i++)
                {
                    if(lArrObjclsGridLoadExcelData[i].Phonenumber.Length < 10)
                    {
                        lstFileError.Add("Phone Number should contain at least 10 digits");
                    }
                }
            }
        
            string ErrorMsg = "";
            if (lstError.Count > 0 || lstFileError.Count > 0 )
            {
                if (lstError.Count > 0)
                {
                
                ErrorMsg = "Following are required field.\n\n";
                    foreach (string s in lstError)
                    {
                        ErrorMsg += s + "\n";
                    }
                }
                if (lstFileError.Count > 0)
                {

                    ErrorMsg += "\nUpload File Errors.\n\n";
                    foreach (string s in lstFileError)
                    {
                        ErrorMsg += s + "\n";
                    }
                }

            this.Validation = ErrorMsg;
                return false;
                // 

            }
            else
            {

                return true;
            }

        }


Read in Web Services csv File data to insert db:


 public string ListLeadsSave(int PKListId, int campaignid, string description, List<SaveCampaignLeads> listleads, string Key, string Secret)
        {
            Duplicate = 0;
            SucessfulSave = 0;
            bool result = false;
           

            int listid = 0;
            string message = string.Empty;
            if (Validatuser(Key, Secret))
            {
                bool isNewRecord = false;
                TransactionMgr tx = TransactionMgr.ThreadTransactionMgr();



                try
                {
                    tx.BeginTransaction();
                    tblleads lead = new tblleads();
                    tbllists lst = new tbllists();
                    //user objUser = new user();
                    if (PKListId > 0)
                    {
                        isNewRecord = false;
                        lst.LoadByPrimaryKey(PKListId);
                        lst.Modifieddate = DateTime.Now;

                    }
                    else
                    {

                        isNewRecord = true;
                        lst.AddNew();


                    }

                    lst.Modifieddate = DateTime.Now;
                    lst.Createdate = DateTime.Now;
                    lst.Listdescription = description;
                    lst.Campaignidfk = campaignid;
                    lst.Liststatus = Convert.ToString(Status.Pending);
                    lst.Save();
                    listid = lst.Listidpk;
                    tx.CommitTransaction();


                    if (isNewRecord == true)
                    {
                        for (int j = 0; j < listleads.Count(); j++)
                        {
                            if (MatchPhoneNumber(listleads[j].Phonenumber, campaignid) == false)
                            {
                                result = true;
                                lead.AddNew();
                                SucessfulSave = SucessfulSave + 1;
                                lead.Listidfk = listid;
                                lead.Phonecode = listleads[j].Phonecode;
                                lead.Phonenumber = listleads[j].Phonenumber;
                                lead.Title = listleads[j].Title;
                                lead.Firstname = listleads[j].Firstname;
                                lead.Lastname = listleads[j].Lastname;
                                lead.Surename = listleads[j].Surename;
                                lead.Address1 = listleads[j].Address1;
                                lead.Address2 = listleads[j].Address2;
                                lead.City = listleads[j].City;
                                lead.State = listleads[j].State;
                                lead.Zip = listleads[j].Zip;
                                //  lead.Agent = listleads[j].Agent;
                                lead.Comments = listleads[j].Comments;
                                lead.Datetimecallback = listleads[j].Datetimecallback;
                                lead.Disposition = "1";
                                lead.Attempts = 0;
                                lead.Save();


                            }



                        }
                        if (result == false)
                        {

                            tx.RollbackTransaction();
                            lead.Leadidpk = -1;
                            lead.Save();

                            //message = ("Total Candidated:" + listleads.Count() + ",Success:" + SucessfulSave + ",Duplicate:" + Duplicate);

                        }


                    }

                    if (result == true)
                    {
                        tx.CommitTransaction();
                        message = ("Total Candidate:" + listleads.Count() + ",Success:" + SucessfulSave + ",Duplicate:" + Duplicate);


                    }





                }
                catch (Exception ex)
                {

                    message = ("Total Candidate:" + listleads.Count() + ",Success:" + SucessfulSave + ",Duplicate:" + Duplicate);

                    tx.RollbackTransaction();
                }
            }
            return message;
        }


Match Phone Number already Exist:


        public bool MatchPhoneNumber(string PhNo, int campnid)
        {
            tblleads ld = new tblleads();
            ld.GetleadsByPhoneNumber(PhNo, campnid);
            if (ld.RowCount > 0)
            {
                Duplicate = Duplicate + 1;
                return true;
            }
            else
            {
                return false;
            }
        }



Save Campaign Lead CS File becoz with create this class we can't pass to webservice and give Error.we are using mygeneration software for auto generated class;


namespace SetMyLeads.LOB.MyGeneration.BLL
{
    [Serializable]
    public class SaveCampaignLeads
    {
       


        public   int Leadidpk { get; set; }
        public int Listidfk { get; set; }
        public sbyte Phonecode { get; set; }
        public   string Phonenumber { get; set; }
        public   string Title { get; set; }
        public   string Firstname { get; set; }
        public   string Lastname { get; set; }
        public   string Surename { get; set; }
        public   string Address1 { get; set; }
        public   string Address2 { get; set; }
        public   string City { get; set; }
        public   string State { get; set; }
        public   string Zip { get; set; }
        public DateTime Datetimecallback { get; set; }
        public   string Comments { get; set; }
        public string Agent { get; set; }
        //public   string Disposition { get; set; }
        public int Attempts { get; set; }
        public   DateTime Lastupdate { get; set; }
    }
}



 
Leave a comment

Posted by on July 31, 2012 in Silverlight

 

Tags: , ,

create a SQL stored procedure with Mulitple parameters


Overview
This  stored procedures is the ability to pass Multiple parameters and have the stored procedure handle the differing requests that are made.

Explanation
This Sp specially develop for web site Searching Criteria .in this sp use multiple parameter  that get data according to your input and also use date parameter.

if date parameter empt then pass null to sp

stored procedure
ALTER Procedure [dbo].[mp_tkg_SearchingInquirywithparameters]
(
@name varchar(50)= null,
@email varchar(50)= null,
@contactreson varchar(50)= null,
@status varchar(2)= null,
@startdate datetime=null,
@enddate datetime=null

)
AS
BEGIN

If @name=null and Len(@name )=0 Set @name = ”
If @email=null and Len(@email )=0 Set @email = ”
If @status=null   and Len(@status )=0 Set @status = ”
If @contactreson=null and Len(@contactreson )=0 Set @contactreson = ”
if(@startdate !=null and @enddate !=null)
begin
select * from dbo.tkg_Inquiry where

( (tkg_Inquiry.FirstName LIKE ‘%’ + ISNULL(@name , tkg_Inquiry.FirstName) + ‘%’) or

(tkg_Inquiry.LastName LIKE ‘%’ + ISNULL(@name , tkg_Inquiry.FirstName) + ‘%’)) and

(tkg_Inquiry.Email LIKE ‘%’ + ISNULL(@email , tkg_Inquiry.Email) + ‘%’)and
(tkg_Inquiry.ContactReason LIKE ‘%’ + ISNULL(@contactreson , tkg_Inquiry.ContactReason) + ‘%’) and
(tkg_Inquiry.Processed LIKE ‘%’ + ISNULL(@status , tkg_Inquiry.Processed) + ‘%’) and
(Convert(varchar(10), dbo.tkg_Inquiry.DateCreated, 101) >= Convert(varchar(10), @startdate, 101)
and Convert(varchar(10), dbo.tkg_Inquiry.DateCreated, 101)<=Convert(varchar(10), @enddate, 101))
end
else
begin
select * from dbo.tkg_Inquiry where

( (tkg_Inquiry.FirstName LIKE ‘%’ + ISNULL(@name , tkg_Inquiry.FirstName) + ‘%’) or

(tkg_Inquiry.LastName LIKE ‘%’ + ISNULL(@name , tkg_Inquiry.FirstName) + ‘%’)) and

(tkg_Inquiry.Email LIKE ‘%’ + ISNULL(@email , tkg_Inquiry.Email) + ‘%’)and
(tkg_Inquiry.ContactReason LIKE ‘%’ + ISNULL(@contactreson , tkg_Inquiry.ContactReason) + ‘%’) and
(tkg_Inquiry.Processed LIKE ‘%’ + ISNULL(@status , tkg_Inquiry.Processed) + ‘%’)

end
END

Pass Parameter To SP

public static DataSet SearchingInquiry(string name, string email, string contactReason, string status, string startdate, string enddate)
{
if (startdate != “”)
{
enddate = startdate;
}

DataSet ds=new DataSet();
SqlParameterHelper sph = new SqlParameterHelper(GetWriteConnectionString(), “mp_tkg_SearchingInquirywithparameters”, 6);

sph.DefineSqlParameter(“@name”, SqlDbType.VarChar, 100, ParameterDirection.Input, name);
sph.DefineSqlParameter(“@Email”, SqlDbType.VarChar, 150, ParameterDirection.Input, email);
sph.DefineSqlParameter(“@contactreson”, SqlDbType.VarChar, 50, ParameterDirection.Input, contactReason);
sph.DefineSqlParameter(“@status”, SqlDbType.VarChar, 20, ParameterDirection.Input, status);
if (startdate == “”)
{

sph.DefineSqlParameter(“@startdate”, SqlDbType.DateTime, ParameterDirection.Input,null);

}
else
{

sph.DefineSqlParameter(“@startdate”, SqlDbType.DateTime, ParameterDirection.Input,  DateTime.Parse(startdate));

}
if (enddate == “”)
{
sph.DefineSqlParameter(“@enddate”, SqlDbType.DateTime, ParameterDirection.Input,null);

}
else
{

sph.DefineSqlParameter(“@enddate”, SqlDbType.DateTime, ParameterDirection.Input, DateTime.Parse(enddate));

}

ds = sph.ExecuteDataset();
return ds;

}

 

 
Leave a comment

Posted by on July 20, 2012 in SQL Query