Friday, 28 December 2012

get excel sheet data


     private void btnCompare_Click(object sender, EventArgs e)
        {
            string filename1 = FileTxt1.Text;
            string filename2 = FileTxt2.Text;

            string file1_sheet = GetExcelSheets(filename1);
            string file2_sheet = GetExcelSheets(filename2);

            //String sConnectionString1 = "Provider=Microsoft.Jet.OLEDB.4.0;" +
            //"Data Source=" + filename1 + ";" +
            //"Extended Properties=Excel 8.0;";

            //String sConnectionString2 = "Provider=Microsoft.Jet.OLEDB.4.0;" +
            //"Data Source=" + filename2 + ";" +
            //"Extended Properties=Excel 8.0;";
            String sConnectionString1 = "Provider=Microsoft.ACE.OLEDB.12.0;" +
            "Data Source=" + filename1 + ";" +
            "Extended Properties=Excel 12.0;";

            String sConnectionString2 = "Provider=Microsoft.ACE.OLEDB.12.0;" +
            "Data Source=" + filename2 + ";" +
            "Extended Properties=Excel 12.0;";
            OleDbConnection objConn = new OleDbConnection(sConnectionString1);

            objConn.Open();
            OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + file1_sheet + "$]", objConn);
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
            objAdapter1.SelectCommand = objCmdSelect;
            DataSet objDataset1 = new DataSet();
            objAdapter1.Fill(objDataset1, "XLData");
            DataTable dt1 = objDataset1.Tables[0];
            objConn.Close();


            objConn = new OleDbConnection(sConnectionString2);
            objConn.Open();
            objCmdSelect = new OleDbCommand("SELECT * FROM [" + file2_sheet + "$]", objConn);
            objAdapter1 = new OleDbDataAdapter();
            objAdapter1.SelectCommand = objCmdSelect;
            objDataset1 = new DataSet();
            objAdapter1.Fill(objDataset1, "XLData");
            DataTable dt2 = objDataset1.Tables[0];
            objConn.Close();
            compareDataTables(dt1, dt2);

        }



   public string GetExcelSheets(string excelFileName)
        {
            Microsoft.Office.Interop.Excel.Application excelFileObject = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workBookObject = null;
            workBookObject = excelFileObject.Workbooks.Open(excelFileName, 0, true, 5, "", "", false,
            Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
            "",
            true,
            false,
            0,
            true,
            false,
            false);
            Excel.Sheets sheets = workBookObject.Worksheets;

            // get the first and only worksheet from the collection of worksheets
            Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
            //MessageBox.Show(worksheet.Name);
            return worksheet.Name;
        }

compare two datatables

 public DataTable CompareDataTables(DataTable first, DataTable second)
        {
            first.TableName = "FirstTable";
            second.TableName = "SecondTable";
            //Create Empty Table
            DataTable table = new DataTable("Difference");
            try
            {
                //Must use a Dataset to make use of a DataRelation object
                using (DataSet ds = new DataSet())
                {
                    //Add tables
                    ds.Tables.AddRange(new DataTable[] { first.Copy(), second.Copy() });
                    //Get Columns for DataRelation
                    DataColumn[] firstcolumns = new DataColumn[ds.Tables[0].Columns.Count];
                    for (int i = 0; i < firstcolumns.Length; i++)
                    {
                        firstcolumns[i] = ds.Tables[0].Columns[i];
                    }
                    DataColumn[] secondcolumns = new DataColumn[ds.Tables[1].Columns.Count];
                    for (int i = 0; i < secondcolumns.Length; i++)
                    {
                        secondcolumns[i] = ds.Tables[1].Columns[i];
                    }
                    //Create DataRelation
                    DataRelation r = new DataRelation(string.Empty, firstcolumns, secondcolumns, false);
                    ds.Relations.Add(r);
                    //Create columns for return table
                    for (int i = 0; i < first.Columns.Count; i++)
                    {
                        table.Columns.Add(first.Columns[i].ColumnName, first.Columns[i].DataType);
                    }
                    //If First Row not in Second, Add to return table.
                    table.BeginLoadData();
                    foreach (DataRow parentrow in ds.Tables[0].Rows)
                    {
                        DataRow[] childrows = parentrow.GetChildRows(r);
                        if (childrows == null || childrows.Length == 0)
                            table.LoadDataRow(parentrow.ItemArray, true);
                    }
                    table.EndLoadData();
                }
            }

        return table;
        }

Saturday, 15 December 2012

dynamic ip change in c#



                 ManagementBaseObject inPar = null;
                 ManagementBaseObject outPar = null;
                 ManagementClass mc = new
                 ManagementClass("Win32_NetworkAdapterConfiguration");
                 ManagementObjectCollection moc = mc.GetInstances();
                 try
                 {
                     foreach (ManagementObject mo in moc)
                     {
                         if (!(bool)mo["IPEnabled"])
                             continue;

                         inPar = mo.GetMethodParameters("EnableStatic");
                         inPar["IPAddress"] = new string[] {"10.57.245.184" };
                         inPar["SubnetMask"] = new string[] {"255.255.0.0" };
                         outPar = mo.InvokeMethod("EnableStatic", inPar,null);
                         //MonIP.Text = "10.59.245.186";
                     }
                 }
                 catch (Exception ex)
                 {
                     MessageBox.Show("Problème au changement d'IP (" +
                     ex.Message + ")", "erreur", MessageBoxButtons.OK,
                     MessageBoxIcon.Error);
                     return;
                 }

Thursday, 13 December 2012

Calender Control in asp.net


<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
    <link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.5/themes/base/jquery-ui.css"
        type="text/css" media="all" />
    <link rel="stylesheet" href="http://static.jquery.com/ui/css/demo-docs-theme/ui.theme.css"
        type="text/css" media="all" />
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js" type="text/javascript"></script>
    <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.5/jquery-ui.min.js"
        type="text/javascript"></script>
    <script type="text/javascript">
        $(function () {
            $("#<%= txtFrom.ClientID  %>").datepicker();
            $("#<%= txtTo.ClientID  %>").datepicker();
        });
       
    </script>
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">   
    <p>
        <asp:TextBox ID="txtFrom" runat="server"></asp:TextBox></p>
    <p>
        <asp:TextBox ID="txtTo" runat="server"></asp:TextBox></p>

</asp:Content>

Modify the gridview row data in asp.net


 protected void gvOpenings_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                DataRow row = ((DataRowView)e.Row.DataItem).Row;
                //int i=e.Row.RowIndex;
                for (int i = 0; i <8 ; i++)
                {
                    if (e.Row.Cells[i].Text.Length > 30)
                    {
                        string strStore = e.Row.Cells[i].Text;
                        e.Row.Cells[i].Text = strStore.Substring(0, 27) + "..";
                    }
                }
            }
        }

send mail with attachment


string strSMTPServer = ConfigurationManager.AppSettings["SMTPServer"];
                string strFromAddress = ConfigurationManager.AppSettings["FromAddress"];
                string strUserName = ConfigurationManager.AppSettings["MailCredential_UserName"];
                string strPassword = ConfigurationManager.AppSettings["MailCredential_Password"];
                int intSMTPPort = Convert.ToInt32(ConfigurationManager.AppSettings["SMTPPort"]);
                MailMessage mail = new MailMessage();
                SmtpClient SmtpServer = new SmtpClient(strSMTPServer);
                if (fileResume.HasFile == true)
                {
                    mail.Attachments.Add(new Attachment(fileResume.PostedFile.InputStream, fileResume.PostedFile.FileName));
                    mail.From = new MailAddress(strFromAddress);
                    mail.To.Add(ConfigurationManager.AppSettings["MailToAddress"]);
                    String strAppliedFor=Request.QueryString["jobid"]!=null?Request.QueryString["jobid"]:"General";
                    mail.Subject = "Applied For " + ddlApplyOne.SelectedValue;
                    mail.IsBodyHtml = true;
                    StringBuilder mailBody = new StringBuilder();
                    mailBody.AppendFormat("<div style='width:50px;backround-color:Green'><table border='10' bordercolor='red' cellpadding='10' cellspacing='10'  align='center'>");
                    mailBody.AppendFormat("<tr><td colspan='2' align='center'><h1>Application for the post of {0}</h1></td></tr>", ddlApply.SelectedValue);
                    mailBody.AppendFormat("<tr><td>Name of the Candidate:</td><td>{0}</td></tr>",txtName.Text);
                    mailBody.AppendFormat("<tr><td>EmailID:</td><td>{0}</td></tr>",txtEmail.Text);
                    mailBody.AppendFormat("<tr><td>Contact Ph:</td><td>{0}</td></tr>",txtContact.Text);
                    mailBody.AppendFormat("<tr><td>Educational Qualification:</td><td>{0}</td></tr>",txtEduQu.Text);
                    mailBody.AppendFormat("<tr><td>Technical Skils:</td><td>{0}</td></tr>",txtSkills.Text);
                    mailBody.AppendFormat("<tr><td>Work Experience:</td><td>{0}</td></tr>", ddlExp.SelectedValue);
                    mailBody.AppendFormat("<tr><td>Current Working Status:</td><td>{0}</td></tr>", txtWorkStat.Text);
                    mailBody.AppendFormat("</table></div>");
                    mail.Body = mailBody.ToString();
                    SmtpServer.Port = intSMTPPort;
                    SmtpServer.Credentials = new System.Net.NetworkCredential(strUserName, strPassword);
                    SmtpServer.EnableSsl = true;
                    SmtpServer.Send(mail);
                    txtEmail.Text = "";
                    txtName.Text = "";
                    txtSkills.Text = "";
                    txtWorkStat.Text = "";
                    txtContact.Text="";
                    txtEduQu.Text="";
                    txtWorkStat.Text="";
                    ddlApply.SelectedIndex=0;
                    ddlExp.SelectedIndex=0;
                    lblError.Text = "";
                    ScriptManager.RegisterStartupScript(this,this.GetType(),"MessageBox","alert('Your details are updated to our system. We will get back to you soon..Thank you');",true);

Messagebox in asp.net page

 ScriptManager.RegisterStartupScript(this,this.GetType(),"MessageBox","alert('Your details are updated to our system. We will get back to you soon..Thank you');",true);