Dynamic Excel Data Management in C# windows application

Codebehind C#:

 private void btnBrowse_Click(object sender, EventArgs e)
        {
            try
            {
                OpenFileDialog fdlg = new OpenFileDialog();

                fdlg.Title = "Select file";

                fdlg.InitialDirectory = @"c:\";

                fdlg.FileName = txtFileName.Text;

                fdlg.Filter = "Excel Sheet(*.xls)|*.xls|All Files(*.*)|*.*";

                fdlg.FilterIndex = 1;

                fdlg.RestoreDirectory = true;

                if (fdlg.ShowDialog() == DialogResult.OK)
                {

                    txtFileName.Text = fdlg.FileName;

                    Import();

                    Application.DoEvents();

                }
            }
            catch (Exception ex)
            {
                ErrorLog.WriteErrorLog(ErrorLog.GetLogFilePath(), ex);
            }
        }

        private void Import()
        {
            if (txtFileName.Text.Trim() != string.Empty)
            {
                try
                {
                    string[] strTables = GetTableExcel(txtFileName.Text);

                    frmSelectTables objSelectTable = new frmSelectTables(strTables);
                    objSelectTable.ShowDialog(this);
                    objSelectTable.Dispose();
                    if ((SelectedTable != string.Empty) && (SelectedTable != null))
                    {

                        dt = GetDataTableExcel(txtFileName.Text, SelectedTable);

                        // Initialize the DataGridView.
                        dgvUsers.DataSource = null;
                        dgvUsers.Rows.Clear();
                        dgvUsers.Columns.Clear();
                        dgvUsers.AutoGenerateColumns = false;
                        dgvUsers.AutoSize = true;
                        dgvUsers.DataSource = dt.DefaultView;
                        lblMsg.Text = "Total Number :" + dt.Rows.Count;
                        count = dt.Rows.Count;
                        lblMsg.Visible = true;
                        if (count > 240)
                        {
                            txtserialnos.Text = "0-240";
                        }
                        else
                        {
                            txtserialnos.Text = "0-" + (count).ToString();
                        }

                        for (int i = 0; i <= dt.Columns.Count; i++)
                        {
                            if (i == 0)
                            {
                                DataGridViewCheckBoxColumn CBColumn = new DataGridViewCheckBoxColumn();
                                CBColumn.HeaderText = "";
                                CBColumn.FalseValue = "0";
                                CBColumn.TrueValue = "1";
                                dgvUsers.Columns.Insert(0, CBColumn);
                            }
                            else
                            {
                                // Initialize and add a text box column.
                                DataGridViewColumn column = new DataGridViewTextBoxColumn();
                                column.DataPropertyName = dt.Columns[i - 1].ColumnName;
                                column.Name = dt.Columns[i - 1].ColumnName;
                                dgvUsers.Columns.Insert(i, column);
                            }
                        }

                       
                    }
                }
                catch (Exception ex)
                {
                    ErrorLog.WriteErrorLog(ErrorLog.GetLogFilePath(), ex);
                }
            }
        }

        public static DataTable GetDataTableExcel(string strFileName, string Table)
        {
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 12.0 Xml;HDR=Yes;IMEX=1\";");
            conn.Open();
            string strQuery = "SELECT * FROM [" + Table + "]";
            //
            OleDbDataAdapter adapter = new OleDbDataAdapter(strQuery, conn);
            System.Data.DataSet ds = new System.Data.DataSet();
            // DataTable dt = new DataTable();
            adapter.Fill(ds);

            return ds.Tables[0];
        }


        public static string[] GetTableExcel(string strFileName)
        {
            string[] strTables = new string[100];
            Catalog oCatlog = new Catalog();
            ADOX.Table oTable = new ADOX.Table();
            ADODB.Connection oConn = new ADODB.Connection();
            oConn.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 12.0 Xml;HDR=Yes;IMEX=1\";", "", "", 0);
            oCatlog.ActiveConnection = oConn;
            if (oCatlog.Tables.Count > 0)
            {
                int item = 0;
                foreach (ADOX.Table tab in oCatlog.Tables)
                {
                    if (tab.Type == "TABLE")
                    {
                        strTables[item] = tab.Name;
                        item++;
                    }
                }
            }
            return strTables;
        }

        private void ExcelLabel_Load(object sender, EventArgs e)
        {

            this.reportViewer1.RefreshReport();
        }

        private void dgvUsers_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                Point cur = new Point(e.ColumnIndex, e.RowIndex);

                // Change the diagonal checkbox to the opposite state            
                if ((bool)(dgvUsers[cur.X, cur.Y].Selected) == true && (dgvUsers[cur.X, cur.Y].Value.ToString() != "0"))
                {
                    dgvUsers[cur.X, cur.Y].Selected = true;
                    dgvUsers[cur.X, cur.Y].Value = true;
                    flag = true;
                }
                else
                {
                    dgvUsers[cur.X, cur.Y].Selected = false;
                    dgvUsers[cur.X, cur.Y].Value = false;
                    flag = true;
                }
            }
            catch (Exception ex)
            {
                ErrorLog.WriteErrorLog(ErrorLog.GetLogFilePath(), ex);
            }
        }

        private void dgvUsers_CurrentCellDirtyStateChanged(object sender, EventArgs e)
        {
            try
            {
                if (dgvUsers.IsCurrentCellDirty)
                {
                    dgvUsers.CommitEdit(DataGridViewDataErrorContexts.Commit);
                }
            }
            catch (Exception ex)
            {
                ErrorLog.WriteErrorLog(ErrorLog.GetLogFilePath(), ex);
            }
        }

        private void btnReport_Click(object sender, EventArgs e)
        {
            try
            {

               
                DataTable dt1 = null;
                dt1 = dt.Clone();
                DataRow dr = null;
                int ii = 0;
                int jj = 0;
                dt1.Rows.Clear();

                if (chkSerialNos.Checked == true)
                {
                    if (txtserialnos.Text != "")
                    {
                        string[] Nos = txtserialnos.Text.Split('-');

                        for (ii = int.Parse(Nos[0]); ii <= int.Parse(Nos[1]) - 1; ii++)
                        {
                            if (flag == false)
                            {
                                dr = dt1.NewRow();
                                for (jj = 0; jj <= dgvUsers.Columns.Count - 2; jj++)
                                {
                                    dr[jj] = dgvUsers.Rows[ii].Cells[jj + 1].Value;
                                }
                                dt1.Rows.Add(dr);
                            }
                            else
                            {
                                if (dgvUsers.Rows[ii].Cells[0].Value != null)
                                {
                                    if (dgvUsers.Rows[ii].Cells[0].Value.ToString() != "0")
                                    {
                                        if ((bool)dgvUsers.Rows[ii].Cells[0].Value == true)
                                        {
                                            dr = dt1.NewRow();
                                            for (jj = 0; jj <= dgvUsers.Columns.Count - 2; jj++)
                                            {
                                                dr[jj] = dgvUsers.Rows[ii].Cells[jj + 1].Value;
                                            }
                                            dt1.Rows.Add(dr);
                                        }
                                    }
                                }
                            }
                        }
                        if ((int.Parse(Nos[1]) + 240) > count)
                        {
                            if (int.Parse(Nos[1]) + 1 < count)
                            {
                                txtserialnos.Text = (int.Parse(Nos[1]) + 1).ToString() + "-" + count.ToString();
                            }
                        }                      
                        else
                        {
                            txtserialnos.Text = (int.Parse(Nos[1]) + 1).ToString() + "-" + (int.Parse(Nos[1]) + 240).ToString();
                        }
                    }
                }
                else
                {
                    for (ii = 0; ii <= dgvUsers.Rows.Count - 1; ii++)
                    {
                        if (flag == false)
                        {
                            dr = dt1.NewRow();
                            for (jj = 0; jj <= dgvUsers.Columns.Count - 2; jj++)
                            {
                                dr[jj] = dgvUsers.Rows[ii].Cells[jj + 1].Value;
                            }
                            dt1.Rows.Add(dr);
                        }
                        else
                        {
                            if (dgvUsers.Rows[ii].Cells[0].Value != null)
                            {
                                dr = dt1.NewRow();
                                for (jj = 0; jj <= dgvUsers.Columns.Count - 2; jj++)
                                {
                                    dr[jj] = dgvUsers.Rows[ii].Cells[jj + 1].Value;
                                }
                                dt1.Rows.Add(dr);
                            }
                        }
                    }

                 
                }

                dt1.Columns[0].ColumnName = "COL1";
                dt1.Columns[1].ColumnName = "COL2";
                dt1.Columns[2].ColumnName = "COL3";
                dt1.Columns[3].ColumnName = "COL4";
                dt1.Columns[4].ColumnName = "COL5";
                dt1.Columns[5].ColumnName = "COL6";
                dt1.Columns[6].ColumnName = "COL7";
                dt1.Columns[7].ColumnName = "COL8";
                dt1.AcceptChanges();

                FillReport(dt1);
              
            }
            catch (Exception ex)
            {
                ErrorLog.WriteErrorLog(ErrorLog.GetLogFilePath(), ex);
            }
        }

        private void FillReport(DataTable dt)
        {
            reportViewer1.Clear();
            reportViewer1.ProcessingMode = ProcessingMode.Local;
            reportViewer1.LocalReport.ReportPath = System.IO.Path.GetDirectoryName(
                System.Reflection.Assembly.GetExecutingAssembly().Location) + "\\ExcelLabels.rdlc";
            reportViewer1.LocalReport.DataSources.Clear();
            reportViewer1.LocalReport.DataSources.Add(new ReportDataSource(reportViewer1.LocalReport.GetDataSourceNames()[0], dt));
            this.reportViewer1.RefreshReport();
            tabControl1.SelectTab("tabPage2");
            tabControl1.SelectedIndex = 1;
        }

        private void chkAll_CheckedChanged(object sender, EventArgs e)
        {
            try
            {
                if (chkAll.Checked == true)
                {
                    for (int ii = 0; ii <= dgvUsers.Rows.Count - 1; ii++)
                    {
                        dgvUsers[0, ii].Selected = true;
                        dgvUsers[0, ii].Value = true;
                    }
                }
                else
                {
                    for (int ii = 0; ii <= dgvUsers.Rows.Count - 1; ii++)
                    {
                        dgvUsers[0, ii].Selected = false;
                        dgvUsers[0, ii].Value = false;
                    }
                }
            }
            catch (Exception ex)
            {
                ErrorLog.WriteErrorLog(ErrorLog.GetLogFilePath(), ex);
            }
        }

Error Log in asp.net

            try
            {
              
            }
            catch (Exception ex)
            {
                ErrorLog.WriteErrorLog(ErrorLog.GetLogFilePath(), ex);
            }



public class ErrorLog
    {
        protected static string strLogFilePath = string.Empty;
        private static StreamWriter sw = null;
        /// <summary>
        /// Setting LogFile path. If the logfile path is null then it will update error info into LogFile.txt under
        /// application directory.
        /// </summary>
        public static string LogFilePath
        {
            set
            {
                strLogFilePath = value;
            }
            get
            {
                return strLogFilePath;
            }
        }
        /// <summary>
        /// Empty Constructor
        /// </summary>
        public ErrorLog() { }
        /// <summary>
        /// Write error log entry for window event if the bLogType is true. Otherwise, write the log entry to
        /// customized text-based text file
        /// </summary>
        /// <param name="bLogType"></param>
        /// <param name="objException"></param>
        /// <returns>false if the problem persists</returns>
        public static bool ErrorRoutine(bool bLogType, Exception objException)
        {
            try
            {
                //Check whether logging is enabled or not
                bool bLoggingEnabled = false;
                bLoggingEnabled = CheckLoggingEnabled();

                //Don't process more if the logging
                if (false == bLoggingEnabled)
                    return true;

                //Write to Windows event log
                if (true == bLogType)
                {
                    string EventLogName = "ErrorSample";

                    if (!EventLog.SourceExists(EventLogName))
                        EventLog.CreateEventSource(objException.Message, EventLogName);

                    // Inserting into event log
                    EventLog Log = new EventLog();
                    Log.Source = EventLogName;
                    Log.WriteEntry(objException.Message, EventLogEntryType.Error);
                }
                //Custom text-based event log
                else
                {
                    if (true != CustomErrorRoutine(objException))
                        return false;
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        /// <summary>
        /// Check Logginstatus config file is exist. If exist read the value set the loggig status
        /// </summary>
        public static bool CheckLoggingEnabled()
        {
            string strLoggingStatusConfig = string.Empty;

            strLoggingStatusConfig = GetLoggingStatusConfigFileName();

            //If it's empty then enable the logging status
            if (strLoggingStatusConfig.Equals(string.Empty))
            {
                return true;
            }
            else
            {

                //Read the value from xml and set the logging status
                bool bTemp = GetValueFromXml(strLoggingStatusConfig);
                return bTemp;
            }
        }
        /// <summary>
        /// Check the Logginstatus config under debug or release folder. If not exist, check under
        /// project folder. If not exist again, return empty string
        /// </summary>
        /// <returns>empty string if file not exists</returns>
        public static string GetLoggingStatusConfigFileName()
        {
            string strCheckinBaseDirecotry = AppDomain.CurrentDomain.BaseDirectory + "LoggingStatus.Config";

            if (File.Exists(strCheckinBaseDirecotry))
                return strCheckinBaseDirecotry;
            else
            {
                string strCheckinApplicationDirecotry = GetApplicationPath() + "LoggingStatus.Config";

                if (File.Exists(strCheckinApplicationDirecotry))
                    return strCheckinApplicationDirecotry;
                else
                    return string.Empty;
            }
        }
        /// <summary>
        /// Read the xml file and getthe logging status
        /// </summary>
        /// <param name="strXmlPath"></param>
        /// <returns></returns>
        public static bool GetValueFromXml(string strXmlPath)
        {
            try
            {
                //Open a FileStream on the Xml file
                FileStream docIn = new FileStream(strXmlPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);

                XmlDocument contactDoc = new XmlDocument();
                //Load the Xml Document
                contactDoc.Load(docIn);

                //Get a node
                XmlNodeList UserList = contactDoc.GetElementsByTagName("LoggingEnabled");

                //get the value
                string strGetValue = UserList.Item(0).InnerText.ToString();

                if (strGetValue.Equals("0"))
                    return false;
                else if (strGetValue.Equals("1"))
                    return true;
                else
                    return false;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// If the LogFile path is empty then, it will write the log entry to LogFile.txt under application directory.
        /// If the LogFile.txt is not availble it will create it
        /// If the Log File path is not empty but the file is not availble it will create it.
        /// </summary>
        /// <param name="objException"></param>
        /// <returns>false if the problem persists</returns>
        public static bool CustomErrorRoutine(Exception objException)
        {
            string strPathName = string.Empty;
            if (strLogFilePath.Equals(string.Empty))
            {
                //Get Default log file path "LogFile.txt"
                strPathName = GetLogFilePath();
            }
            else
            {

                //If the log file path is not empty but the file is not available it will create it
                if (false == File.Exists(strLogFilePath))
                {
                    if (false == CheckDirectory(strLogFilePath))
                        return false;

                    FileStream fs = new FileStream(strLogFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                    fs.Close();
                }
                strPathName = strLogFilePath;

            }

            bool bReturn = true;
            // write the error log to that text file
            if (true != WriteErrorLog(strPathName, objException))
            {
                bReturn = false;
            }
            return bReturn;
        }
        /// <summary>
        /// Write Source,method,date,time,computer,error and stack trace information to the text file
        /// </summary>
        /// <param name="strPathName"></param>
        /// <param name="objException"></param>
        ///
        /// <returns>false if the problem persists</returns>
        public static bool WriteErrorLog(string strPathName, Exception objException)
        {
            bool bReturn = false;
            string strException = string.Empty;
            try
            {
                sw = new StreamWriter(strPathName, true);
                sw.WriteLine("Source        : " + objException.Source.ToString().Trim());
                sw.WriteLine("Method        : " + objException.TargetSite.Name.ToString());
                sw.WriteLine("Date        : " + DateTime.Now.ToLongTimeString());
                sw.WriteLine("Time        : " + DateTime.Now.ToShortDateString());
                sw.WriteLine("Computer    : " + Dns.GetHostName().ToString());
                sw.WriteLine("Error        : " + objException.Message.ToString().Trim());
                sw.WriteLine("Stack Trace    : " + objException.StackTrace.ToString().Trim());
                sw.WriteLine("^^-------------------------------------------------------------------^^");
                sw.Flush();
                sw.Close();
                send_email("Membership Log", strPathName);
                bReturn = true;
            }
            catch (Exception)
            {
                bReturn = false;
            }
            return bReturn;
        }

        /// <summary>
        /// Send mail error log text file
        /// </summary>
        /// <returns>Mail send</returns>
        public static void send_email(string body, string file)
        {

            MailMessage msg = new MailMessage();
            msg.IsBodyHtml = true;
            msg.From = new MailAddress("xxxx@gmail.com", "xxxx");
            msg.To.Add(new MailAddress("xxxx@gmail.com"));
            msg.To.Add(new MailAddress("xxxx@gmail.com"));


            msg.Subject = "Membership Error Log";
            msg.Body = body;
            msg.DeliveryNotificationOptions = DeliveryNotificationOptions.OnFailure;
            Attachment at = new Attachment(file);
            msg.Attachments.Add(at);
            SmtpClient smtp = new SmtpClient("smtp.gmail.com");
            smtp.Credentials = new System.Net.NetworkCredential("xxxxx@gmail.com", "xxxxxx");
            smtp.EnableSsl = true;
            smtp.Send(msg);

        }

        /// <summary>
        /// Check the log file in applcation directory. If it is not available, creae it
        /// </summary>
        /// <returns>Log file path</returns>
        public static string GetLogFilePath()
        {
            try
            {
                // get the base directory
                string baseDir = AppDomain.CurrentDomain.BaseDirectory + AppDomain.CurrentDomain.RelativeSearchPath;

                // search the file below the current directory
                string retFilePath = baseDir + "//" + "LogFile.txt";

                // if exists, return the path
                if (File.Exists(retFilePath) == true)
                    return retFilePath;
                //create a text file
                else
                {
                    if (false == CheckDirectory(retFilePath))
                        return string.Empty;

                    FileStream fs = new FileStream(retFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                    fs.Close();
                }

                return retFilePath;
            }
            catch (Exception)
            {
                return string.Empty;
            }
        }
        /// <summary>
        /// Create a directory if not exists
        /// </summary>
        /// <param name="strLogPath"></param>
        /// <returns></returns>
        public static bool CheckDirectory(string strLogPath)
        {
            try
            {
                int nFindSlashPos = strLogPath.Trim().LastIndexOf("\\");
                string strDirectoryname = strLogPath.Trim().Substring(0, nFindSlashPos);

                if (false == Directory.Exists(strDirectoryname))
                    Directory.CreateDirectory(strDirectoryname);

                return true;
            }
            catch (Exception)
            {
                return false;

            }
        }

        public static string GetApplicationPath()
        {
            try
            {
                string strBaseDirectory = AppDomain.CurrentDomain.BaseDirectory.ToString();
                int nFirstSlashPos = strBaseDirectory.LastIndexOf("\\");
                string strTemp = string.Empty;

                if (0 < nFirstSlashPos)
                    strTemp = strBaseDirectory.Substring(0, nFirstSlashPos);

                int nSecondSlashPos = strTemp.LastIndexOf("\\");
                string strTempAppPath = string.Empty;
                if (0 < nSecondSlashPos)
                    strTempAppPath = strTemp.Substring(0, nSecondSlashPos);

                string strAppPath = strTempAppPath.Replace("bin", "");
                return strAppPath;
            }
            catch (Exception)
            {
                return string.Empty;
            }
        }
    }

Create Identity Card for like Membership in C# windows application

 










 Codebehind C#:


public static  Bitmap CreateFirstCard(string Name,string MembNo,string Mobile,string Phone,
          Bitmap objBmpImage, Image img ,int Width,int Height )
      {
          //Bitmap objBmpImage = new Bitmap(pbPhoto.Width, pbPhoto.Height);

          int intWidth = Width;
          int intHeight = Height;

          // Create the Font object for the image text drawing.
          System.Drawing.Font objFont = new System.Drawing.Font("Arial", 13, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Pixel);

          // Create a graphics object to measure the text's width and height.
          Graphics objGraphics = Graphics.FromImage(objBmpImage);

       

          // Create the bmpImage again with the correct size for the text and font.
          //objBmpImage = new Bitmap(objBmpImage, new Size(intWidth, intHeight));


          // Add the colors to the new bitmap.
          objGraphics = Graphics.FromImage(objBmpImage);

          // Set Background color

          //objGraphics.Clear(System.Drawing.Color.White);
          objGraphics.SmoothingMode = SmoothingMode.HighQuality;



          objGraphics.TextRenderingHint = System.Drawing.Text.TextRenderingHint.SystemDefault;

          objGraphics.DrawString(Name, objFont, new SolidBrush(System.Drawing.Color.Black), 95, 75, StringFormat.GenericDefault);

          objGraphics.DrawString(MembNo, objFont, new SolidBrush(System.Drawing.Color.Black), 157, 95, StringFormat.GenericDefault);

          objGraphics.DrawString(Phone, objFont, new SolidBrush(System.Drawing.Color.Black), 95, 120, StringFormat.GenericDefault);

          objGraphics.DrawString(Mobile, objFont, new SolidBrush(System.Drawing.Color.Black), 105, 140, StringFormat.GenericDefault);


          System.Drawing.Rectangle rec = new System.Drawing.Rectangle(310, 70, 102, 94);
          objGraphics.DrawImage(img, rec);

          objGraphics.Flush();

          return (objBmpImage);
      }

      public static Bitmap CreateSecondCard(string Address,string DOB,string BG,
          Bitmap objBmpImage, Image img, int Width, int Height)
      {
          //Bitmap objBmpImage = new Bitmap(pbPhoto.Width, pbPhoto.Height);

          int intWidth = Width;
          int intHeight = Height;

          // Create the Font object for the image text drawing.
          System.Drawing.Font objFont = new System.Drawing.Font("Arial", 13, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Pixel);

          // Create a graphics object to measure the text's width and height.
          Graphics objGraphics = Graphics.FromImage(objBmpImage);

      

          // Create the bmpImage again with the correct size for the text and font.
          //objBmpImage = new Bitmap(objBmpImage, new Size(intWidth, intHeight));


          // Add the colors to the new bitmap.
          objGraphics = Graphics.FromImage(objBmpImage);

          // Set Background color

          //objGraphics.Clear(System.Drawing.Color.White);
          objGraphics.SmoothingMode = SmoothingMode.HighQuality;



          objGraphics.TextRenderingHint = System.Drawing.Text.TextRenderingHint.SystemDefault;

          objGraphics.DrawString(Address.TrimStart(), objFont, new SolidBrush(System.Drawing.Color.Black), 157, 32, StringFormat.GenericDefault);

          objGraphics.DrawString(DOB, objFont, new SolidBrush(System.Drawing.Color.Black), 157, 72, StringFormat.GenericDefault);

          objGraphics.DrawString(BG, objFont, new SolidBrush(System.Drawing.Color.Black), 157, 100, StringFormat.GenericDefault);


          objGraphics.Flush();

          return (objBmpImage);
      }

private void btnSubmit_Click(object sender, EventArgs e)
        {
            try
            {
                SqlCommand cmd;

                if (con.con.State == ConnectionState.Closed)
                {
                    con.con.Open();
                }

                System.IO.FileInfo imageInfo;

               

                da = new SqlDataAdapter("select * from MembershipCard where CardNO='" + cmbMemberNo.Text + "'", con.con);
                ds = new DataSet();
                da.Fill(ds);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    int.TryParse(ds.Tables[0].Rows[0]["Id"].ToString(), out Id);
                    Photocontent = (byte[])ds.Tables[0].Rows[0]["Photo"];
                    ThumbPhotocontent = (byte[])ds.Tables[0].Rows[0]["ThumbPhoto"];

                    pbImage.Image = CommonFunctions.byteArrayToImage(Photocontent);

                    pbPhoto.Image = Properties.Resources.ID_card_3;
                    pbPhoto2.Image = Properties.Resources.ID_card_4;

                    da = new SqlDataAdapter("select SlNo,TITLE,MEMBNO,NAME,ADDR1,ADDR2,ROAD,CITY,PIN,BLOODGRP,PHONE,MOBILE from " +
                                                                               "Members where MEMBNO='" + cmbMemberNo.Text + "'", con.con);
                    ds = new DataSet();
                    da.Fill(ds);

                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        lblMembNo.Text = cmbMemberNo.Text;
                        lblName.Text = ds.Tables[0].Rows[0]["TITLE"].ToString() + " " + ds.Tables[0].Rows[0]["NAME"].ToString();
                        lblAddress.Text = ds.Tables[0].Rows[0]["ADDR1"].ToString() + "\r\n" +
                                          ds.Tables[0].Rows[0]["ADDR2"].ToString() +
                                          ds.Tables[0].Rows[0]["CITY"].ToString() + "\r\n";
                        lblAddress.Text = ds.Tables[0].Rows[0]["ROAD"].ToString() == "" ? lblAddress.Text + ds.Tables[0].Rows[0]["PIN"].ToString() :
                                            lblAddress.Text + ds.Tables[0].Rows[0]["ROAD"].ToString() + " - " + ds.Tables[0].Rows[0]["PIN"].ToString();

                        lblBg.Text = ds.Tables[0].Rows[0]["BLOODGRP"].ToString() == "" ? "" : ds.Tables[0].Rows[0]["BLOODGRP"].ToString();
                        lblMobile.Text = ds.Tables[0].Rows[0]["MOBILE"].ToString();
                        lblPhone.Text = ds.Tables[0].Rows[0]["PHONE"].ToString();
                        lbldob.Text = "";
                    }

                }
                else
                {
                    Id = 0;
                }

                if (txtFileName.Text == "")
                {
                    if (Photocontent.Length == 0)
                    {
                        imageInfo = new System.IO.FileInfo(Path.GetDirectoryName(
                                                                            System.Reflection.Assembly.GetExecutingAssembly().Location) + "\\face_outline.gif");

                        Photocontent = new byte[imageInfo.Length];
                        imagestream = imageInfo.OpenRead();
                        imagestream.Read(Photocontent, 0, Photocontent.Length);

                        ThumbPhotocontent = CommonFunctions.createThumnail(imagestream, 50, 50);
                        pbImage.Image = CommonFunctions.byteArrayToImage(Photocontent);
                    }
                    else if (clearflag == true)
                    {
                        imageInfo = new System.IO.FileInfo(Path.GetDirectoryName(
                                                                           System.Reflection.Assembly.GetExecutingAssembly().Location) + "\\face_outline.gif");

                        Photocontent = new byte[imageInfo.Length];
                        imagestream = imageInfo.OpenRead();
                        imagestream.Read(Photocontent, 0, Photocontent.Length);

                        ThumbPhotocontent = CommonFunctions.createThumnail(imagestream, 50, 50);
                        pbImage.Image = CommonFunctions.byteArrayToImage(Photocontent);
                    }
                    //imagestream.Close();
                }
                else
                {
                    imageInfo = new System.IO.FileInfo(txtFileName.Text);

                    Photocontent = new byte[imageInfo.Length];
                    imagestream = imageInfo.OpenRead();
                    imagestream.Read(Photocontent, 0, Photocontent.Length);

                    pbImage.Image = CommonFunctions.byteArrayToImage(Photocontent);

                    ThumbPhotocontent = CommonFunctions.createThumnail(imagestream, 50, 50);

                    //imagestream.Close();
                }

               

                //contain our picturebox image bytes.
                MemoryStream stream = new MemoryStream();
                //through the instruction below, we save the
                //image to byte in the object "stream".
                //pbPhoto.Image.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg);

                //pbPhoto.Image.Save(@"E:\Members\Member1.jpg", System.Drawing.Imaging.ImageFormat.Jpeg);

                Bitmap bmp = new Bitmap(pbPhoto.Image, pbPhoto.Width, pbPhoto.Height);

                string AppPath = System.IO.Path.GetDirectoryName(
                                 System.Reflection.Assembly.GetExecutingAssembly().Location);

                bool isExists = System.IO.Directory.Exists(AppPath + @"\Members");

                if (!isExists)
                    System.IO.Directory.CreateDirectory(AppPath + @"\Members");

                bmp.Save(AppPath + @"\Members\Member1.bmp");

                Bitmap bmp1 = CommonFunctions.CreateFirstCard(lblName.Text,lblMembNo.Text,lblMobile.Text,lblPhone.Text,
                    bmp, pbImage.Image,pbPhoto.Width,pbPhoto.Height);
               
                bmp1.Save(AppPath + @"\Members\" + lblName.Text  + "1.bmp");

                Bitmap bmp2 = new Bitmap(pbPhoto2.Image, pbPhoto2.Width, pbPhoto2.Height);

                bmp2.Save(AppPath + @"\Members\Member2.bmp");

                Bitmap bmp3 = CommonFunctions.CreateSecondCard(lblAddress.Text,lbldob.Text,lblBg.Text,
                    bmp2, pbImage.Image,pbPhoto2.Width,pbPhoto2.Height);

                bmp3.Save(AppPath + @"\Members\" + lblName.Text + "2.bmp");

                imageInfo = new System.IO.FileInfo(AppPath + @"\Members\" + lblName.Text + "1.bmp");
                Cardcontent = new byte[imageInfo.Length];
                imagestream = imageInfo.OpenRead();
                imagestream.Read(Cardcontent, 0, Cardcontent.Length);


                imageInfo = new System.IO.FileInfo(AppPath + @"\Members\" + lblName.Text + "2.bmp");
                Cardcontent2 = new byte[imageInfo.Length];
                imagestream = imageInfo.OpenRead();
                imagestream.Read(Cardcontent2, 0, Cardcontent2.Length);


                //if (cmbStatus.Text != "Select")
                {

                    cmd = new SqlCommand("MembershipCard_Insert_SP", con.con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (Id != 0)
                    {


                        cmd.Parameters.Add("@Opt", SqlDbType.BigInt).Value = 2;
                        cmd.Parameters.Add("@Id", SqlDbType.BigInt).Value = Id;

                    }
                    else
                    {
                        cmd.Parameters.Add("@Opt", SqlDbType.BigInt).Value = 1;
                        cmd.Parameters.Add("@Id", SqlDbType.BigInt).Value = 1;
                    }


                    cmd.Parameters.Add("@CardNO", SqlDbType.NVarChar, 50).Value = cmbMemberNo.Text;
                    cmd.Parameters.Add("@Photo", SqlDbType.Image).Value = Photocontent;
                    cmd.Parameters.Add("@ThumbPhoto", SqlDbType.Image).Value = ThumbPhotocontent;
                    cmd.Parameters.Add("@MembershipCard", SqlDbType.Image).Value = Cardcontent;
                    cmd.Parameters.Add("@MembershipCard2", SqlDbType.Image).Value = Cardcontent2;
                    cmd.Parameters.Add("@CardStatus", SqlDbType.NVarChar, 50).Value = "Card ready";// cmbStatus.Text;

                    cmd.ExecuteNonQuery();

                    MessageBox.Show("Data saved successfully.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                //string template = "Your Membershipcard is ready. Please collect from Office." +
                //                   "\r\n " +
                //                   "General Secretary, Ernakulam Karayogam.";

                //if (con.con.State == ConnectionState.Closed)
                //{
                //    con.con.Open();
                //}

                //cmd = new SqlCommand("select SmsText from SmsMaster where Id=5", con.con);
                //string template = cmd.ExecuteScalar().ToString();

                //string Message = template;

                //System.Diagnostics.Process proc = new System.Diagnostics.Process();
                //proc.EnableRaisingEvents = false;
                //proc.StartInfo.FileName = "iexplore";
                //proc.StartInfo.Arguments = "http://bulksmsalert.in/SendSms.aspx?username=ekmkgm&password=ekmkgm25&to=" + txtMobile.Text + "&from=ekmkgm&message=" + Message + "";
                //proc.Start();

                clearflag = false;
                cmbMemberNo.Text = "";
                txtFileName.Text = "";
                lblName.Text = "";
                lblMembNo.Text = "";
                lblPhone.Text = "";
                lblMobile.Text = "";
                lblAddress.Text = "";
                lbldob.Text = "";
                lblBg.Text = "";
                pbImage.Image = null;
                con.con.Close();


            }
            catch (Exception ex)
            {
                ErrorLog.WriteErrorLog(ErrorLog.GetLogFilePath(), ex);
            }
        }