Bulk Copy from JDE Table to SQL Table

//Write Jde Query to Bring Record.
//Float field should be handled by below code
//CAST(CAST(a.ABAN8 as  decimal(20,0)) as varchar)
//Map table

 var columnMapping = new List<string>();
                //columnMapping.Add("HashField,HashField,0");
                // columnMapping.Add("AddressId,AddressId");
                columnMapping.Add("ERPAddressId,ERPAddressId");
                columnMapping.Add("AddressName,AddressName");
                columnMapping.Add("BranchPlantForPricing,BranchPlantForPricing");
                columnMapping.Add("BranchPlantAvail,BranchPlantAvail");
                columnMapping.Add("BranchPlantAvailGroup,BranchPlantAvailGroup");
                columnMapping.Add("Lang,Lang");
                columnMapping.Add("Ordertype,Ordertype");
                columnMapping.Add("CreatedBy,CreatedBy");
                columnMapping.Add("DateCreated,DateCreated");
                columnMapping.Add("AddressLine1,AddressLine1");
                columnMapping.Add("AddressLine2,AddressLine2");
                columnMapping.Add("AddressLine3,AddressLine3");
                columnMapping.Add("AddressLine4,AddressLine4");
                columnMapping.Add("City,City");
                columnMapping.Add("State,State");
                columnMapping.Add("ZipCode,ZipCode");
                columnMapping.Add("County,County");
                columnMapping.Add("Country,Country");
                columnMapping.Add("MailingName,MailingName");
                columnMapping.Add("AdjustmentSchedule,AdjustmentSchedule");
                columnMapping.Add("BillingType,BillingType");
                columnMapping.Add("IsPORequired,IsPORequired");
                columnMapping.Add("AllowBackorder,AllowBackorder");
                columnMapping.Add("ParentAddressId,ParentAddressId");
                columnMapping.Add("ParentAddressName,ParentAddressName");
                columnMapping.Add("IsActive,IsActive");



Calling Function

  //Truncating Staging Address Table
                string TruncateQuery = "TRUNCATE TABLE ssAddressStagging";
                ExecuteNonQuery(TruncateQuery, E2wConnection);
                E2WLogWriter.Log("----------------------------------------------");
                ReturnMessage.AppendLine(Common.BulkImportData(queryJDE.ToString(), ConnStr, E2wConnection, "ssAddressStagging", columnMapping));


//Function
 public static string ExecuteNonQuery(string Sql, String ConString)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(ConString))
                {
                    SqlCommand cmd = new SqlCommand();
                    con.Open();
                    cmd.CommandTimeout = 0;
                    cmd.CommandText = Sql;
                    cmd.Connection = con;
                    int Result = cmd.ExecuteNonQuery();
                    return "Success!," + Result + " Records Affected!";
                }
            }
            catch (Exception ex)
            {
                return "Failed :" + ex.Message;
            }
        }
 private static void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            Console.WriteLine("Imported {0} Records...", e.RowsCopied);
        }

public static string BulkImportData(string SourceScript, string SourceConnectionString, string DestinationConnectionString, string DestinationTable, List<string> columnMapping)
        {
            string ReturnMessage = "";
            string CountScript = "Select count(*) from " + DestinationTable;

            try
            {
               
                using (SqlConnection sourceConnection = new SqlConnection(SourceConnectionString.Replace("Provider=SQLOLEDB;","")))
                {
                    sourceConnection.Open();
                    // Perform an initial count on the destination table.
                    SqlConnection DestinationConnection = new SqlConnection(DestinationConnectionString);
                    DestinationConnection.Open();
                    SqlCommand commandRowCount = new SqlCommand(CountScript, DestinationConnection);
                    commandRowCount.CommandTimeout = 0;
                    long countStart = System.Convert.ToInt32(commandRowCount.ExecuteScalar());
                    ReturnMessage +=
                    ReturnMessage += "Starting rows Count " + countStart + Environment.NewLine;

                    // Get data from the source table as a SqlDataReader.
                    SqlCommand commandSourceData = new SqlCommand(SourceScript, sourceConnection);
                    commandSourceData.CommandTimeout = 0;
                    SqlDataReader reader = commandSourceData.ExecuteReader();
                    using (SqlConnection destinationConnection = new SqlConnection(DestinationConnectionString))
                    {
                        destinationConnection.Open();
                        using (SqlBulkCopy bulkCopy =
                                   new SqlBulkCopy(destinationConnection))
                        {
                            bulkCopy.DestinationTableName = DestinationTable;
                            bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
                            bulkCopy.NotifyAfter = 5000; //For every 5000 records notify
                            bulkCopy.BulkCopyTimeout = 0;

                            try
                            {
                                foreach (var mapping in columnMapping)
                                {
                                    var split = mapping.Split(new[] { ',' });
                                    bulkCopy.ColumnMappings.Add(split[0], split[1]);
                                }

                                // Write from the source to the destination.
                                bulkCopy.WriteToServer(reader);
                            }
                            catch (Exception ex)
                            {

                                if (ex.Message.Contains("Received an invalid column length from the bcp client for colid"))
                                {
                                    string pattern = @"\d+";
                                    Match match = Regex.Match(ex.Message.ToString(), pattern);
                                    var index = Convert.ToInt32(match.Value) - 1;

                                    FieldInfo fi = typeof(SqlBulkCopy).GetField("_sortedColumnMappings", BindingFlags.NonPublic | BindingFlags.Instance);
                                    var sortedColumns = fi.GetValue(bulkCopy);
                                    var items = (Object[])sortedColumns.GetType().GetField("_items", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(sortedColumns);

                                    FieldInfo itemdata = items[index].GetType().GetField("_metadata", BindingFlags.NonPublic | BindingFlags.Instance);
                                    var metadata = itemdata.GetValue(items[index]);

                                    var column = metadata.GetType().GetField("column", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
                                    var length = metadata.GetType().GetField("length", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
                                    ReturnMessage+=(String.Format("Column: {0} contains data with a length greater than: {1}", column, length));
                                }                                                         
                               
                                ReturnMessage += ex.Message + Environment.NewLine;
                                //       Console.WriteLine(ex.Message);
                                throw ex;
                            }
                            finally
                            {
                                reader.Close();
                            }
                        }

                        // Perform a final count on the destination 
                        // table to see how many rows were added.
                        long countEnd = System.Convert.ToInt32(
                            commandRowCount.ExecuteScalar());

                        // System.IO.File.AppendAllText(sLogSummaryFileName,
                        //E2WLogWriter.WriteSummaryLog(sSummary.ToString(), sLogSummaryFileName);
                        ReturnMessage += "Ending rows Count " + countEnd + Environment.NewLine;
                        ReturnMessage += countEnd - countStart + " rows were added." + Environment.NewLine;
                        ReturnMessage = "Bulk Import was Successful!" + Environment.NewLine + ReturnMessage;
                        Console.WriteLine(ReturnMessage);
                        E2WLogWriter.Log(ReturnMessage);
                    }
                }
            }
            catch (Exception ex)
            {           
               
                ReturnMessage += "Bulk Import Failed!" + Environment.NewLine + ex.Message + Environment.NewLine;
                Console.WriteLine(ReturnMessage);
            }
            return ReturnMessage;
        }


//Updating Unique field with NextNumber appending AD retrieving it from another table

   string SourceScript = "";
                StringBuilder queryUpdate = new StringBuilder();

                //queryUpdate.Append("Update t1 Set t1.AddressID=t2.Addr From ssAddressStagging t1 inner join ");
                //queryUpdate.Append("(SELECT    Convert(varchar(50),(SELECT NextNumber  FROM ssNextNumber where Prefix='AD'))+ Convert(Varchar(50),ROW_NUMBER() OVER(ORDER BY x.ERPAddressid)) as Addr1,ERPAddressid,'AD'+ (Case When Len(Convert(varchar(50),(SELECT NextNumber  FROM ssNextNumber where Prefix='AD'))+ Convert(Varchar(50),ROW_NUMBER() OVER(ORDER BY x.ERPAddressid)) )<8 Then  REPLACE(STR( Convert(varchar(50),(SELECT NextNumber  FROM ssNextNumber where Prefix='AD'))+ Convert(Varchar(50),ROW_NUMBER() OVER(ORDER BY x.ERPAddressid)) , 8), SPACE(1), '0') ELSE  Convert(varchar(50),(SELECT NextNumber  FROM ssNextNumber where Prefix='AD'))+ Convert(Varchar(50),ROW_NUMBER() OVER(ORDER BY x.ERPAddressid))  end)  Addr");
                //queryUpdate.Append("  FROM    ssAddressStagging x    WHERE   x.ERPAddressid = x.ERPAddressid and x.ErpAddressid not in (Select ErpAddressId from ssAddress )) as t2 on t1.ErpAddressID=t2.ErpAddressID");
                queryUpdate.Append("Update t1 Set t1.AddressID=t2.Addr From ssAddressStagging t1 inner join ( ");
                queryUpdate.Append("SELECT   ( Convert(Varchar(100),CAST((SELECT NextNumber  FROM ssNextNumber where Prefix='AD') as int) + cast( ROW_NUMBER() OVER(ORDER BY x.ERPAddressid) as int))) as Addr1, ");
                queryUpdate.Append(" ERPAddressid,'AD'+ (Case When Len(Convert(varchar(50),CAST((SELECT NextNumber  FROM ssNextNumber where Prefix='AD') as int)  + CAST((ROW_NUMBER() OVER(ORDER BY x.ERPAddressid)) as int)) )<=8 Then  ");
                queryUpdate.Append(" REPLACE(STR( Convert(varchar(50),CAST((SELECT NextNumber  FROM ssNextNumber where Prefix='AD') as int)+ CAST((ROW_NUMBER() OVER(ORDER BY x.ERPAddressid))as int) , 2)), SPACE(3), '0') ");
                queryUpdate.Append(" ELSE Case when ");
                queryUpdate.Append("Len(Convert(varchar(50),CAST((SELECT NextNumber  FROM ssNextNumber where Prefix='AD') as int)  + CAST((ROW_NUMBER() OVER(ORDER BY x.ERPAddressid)) as int)) )>8 OR (Convert(varchar(50),CAST((SELECT NextNumber  FROM ssNextNumber where Prefix='AD') as int)  + CAST((ROW_NUMBER() OVER(ORDER BY x.ERPAddressid)) as int))=99999999) ");
                queryUpdate.Append("Then ");
                queryUpdate.Append("RIGHT(REPLACE(STR( Convert(varchar(50),CAST(('1') as int)+ CAST((ROW_NUMBER() OVER(ORDER BY x.ErpAddressid))as int) , 4)), SPACE(1), '0'), LEN(REPLACE(STR( Convert(varchar(50),CAST(('1') as int)+ CAST((ROW_NUMBER() OVER(ORDER BY x.ErpAddressid))as int) , 4)), SPACE(1), '0')) - 2) ");
                queryUpdate.Append("END ");
                queryUpdate.Append("END ");
                queryUpdate.Append(" ) Addr  FROM    ssAddressStagging x    WHERE   x.ERPAddressid = x.ERPAddressid and x.ErpAddressid not in (Select ErpAddressId from ssAddress )) as t2 on t1.ErpAddressID=t2.ErpAddressID ");
                E2WLogWriter.Log("----------------------------------------------");
                int Result = DBtools.ExecuteUpdate(queryUpdate.ToString(), E2wConnection + ";Provider=SQLOLEDB;");
                ReturnMessage.AppendLine(Convert.ToString(Result));


//END Update SsNextNumber with the last updated AddressId

 StringBuilder queryUpdateNextNumber = new StringBuilder();
                queryUpdateNextNumber.Append("update ssnextnumber SET NextNumber=");
                queryUpdateNextNumber.Append("(SELECT REPLACE(LTRIM(REPLACE((SELECT top 1 * from Split((select top 1 addressid from ssAddress order by addressid desc),'D') order by items asc), '0', ' ')),' ', '0'))   where Prefix='AD'");
                int ResultUpdateNextNumber = DBtools.ExecuteUpdate(queryUpdateNextNumber.ToString(), E2wConnection + ";Provider=SQLOLEDB;");
                ReturnMessage.AppendLine(Convert.ToString(ResultUpdateNextNumber));

jquery datetime picker in datagrid control c#,asp.net

//Add below javascript to header section on aspx page



 <script type="text/javascript">
 $(function () {
        var strdateFormat = "<%=ReqDateFormat %>";
        strdateFormat = strdateFormat.replace('MM', 'mm');
        strdateFormat = strdateFormat.replace('MMM', 'mmm');
        strdateFormat = strdateFormat.replace('yyyy', 'yy');
        //alert(strdateFormat);
          //    $("[id$=txtRequestedDate]").datepicker({
          //        showOn: 'button',
          //        buttonImageOnly: true,
          //        buttonImage: 'images/calendarbtn.gif',
          //        dateFormat: strdateFormat
          //    });
          //});
        var n = 0;
        $("[id*='txtRequestedDate']").each(function () {
            n++;
            tempField = this.id.split("-");
            this.id = tempField[0] + "-" + n;
            $("#" + this.id).datepicker({
                showOn: 'button',
                buttonImageOnly: true,
                buttonImage: 'images/calendarbtn.gif',
                dateFormat: strdateFormat
            });
        });
    });
    </script>

 <asp:DataGrid ID="DataGrid1" runat="server" AutoGenerateColumns="False" >
                    <Columns>
  <asp:TemplateColumn>
                            <ItemStyle Width="150px" />
                            <ItemTemplate>
                                <asp:TextBox ID="txtitem" Width="100px" runat="server" MaxLength="25"></asp:TextBox>&nbsp;&nbsp;
                                <asp:HiddenField ID="hdnTxtItem" runat="server" />
                                <asp:ImageButton ID="img_search" runat="server" Width="20px" Height="20px" ImageAlign="AbsBottom"
                                    ImageUrl="~/images/magnifying-glass.png"></asp:ImageButton>
                            </ItemTemplate>
                        </asp:TemplateColumn>

 <asp:TemplateColumn>
                            <ItemStyle CssClass="centerAlignGridColumn" Width="55px" />
                            <ItemTemplate>
                                <asp:TextBox ID="txtRequestedDate" Width="120px" MaxLength="12"  runat="server"></asp:TextBox>
                              <%--  <ajaxToolkit:FilteredTextBoxExtender ID="tbqtynumeric" runat="server" TargetControlID="txtQty"
                                     Enabled="true" />--%>
                            </ItemTemplate>
                        </asp:TemplateColumn> </Columns>
                </asp:DataGrid>

//.cs page bind the datagrid with some data
//You can find the control as shown below
foreach (DataGridItem dgi in DataGrid1.Items)
                {
 TextBox TReq = (TextBox)dgi.FindControl("txtRequestedDate");
}

Key Value pair to store and read value using Dictionary in C#

// Declare Dictionary  variable as shown below
 var ListLeadTimeDate = new Dictionary<string, string>();

//Add value against any key



 ListLeadTimeDate.Add("1", "hello");

//Retrieve it using below code

 string Reqstr = ListLeadTimeDate["1"];

Javascript/Jquery to Hide link button: Asp.net C#

##Add Script to the aspx page as shown below,get value from Session,if available then disable or remove the reference link.


/* .aspx page */
 
<script type="text/javascript">

  jQuery(document).ready(function ($) {
        var value = '<%=Session["SomeValue"]%>';
       // alert("hi1");
        if (value) {
          //  alert("hi");

            //$("#ctl00__lnkCart").attr("disabled", "disabled");
            $("#ctl00__lnkCart")[0].href = "javascript:void(0)"; //Remove url
        }
        else {
        
           $("#ctl00__lnkCart")[0].href="www.google.com";// pass url
        }

    });

</script>

  <asp:HyperLink ID="lnkCart" runat="server" >

/* .aspx.cs page */

   Session["SomeValue"] = "SomeValue";

####Cart button is disabled based on session value.