SecurityProtocol Setting in .Net, SSL/TLS Error Fixes

 #region SecurityProtocol Setting

                try

                { //try TLS 1.3

                    ServicePointManager.SecurityProtocol = (SecurityProtocolType)12288

                                                         | (SecurityProtocolType)3072

                                                         | (SecurityProtocolType)768

                                                         | SecurityProtocolType.Tls;

                }

                catch (NotSupportedException)

                {

                    try

                    { //try TLS 1.2

                        ServicePointManager.SecurityProtocol = (SecurityProtocolType)3072

                                                             | (SecurityProtocolType)768

                                                             | SecurityProtocolType.Tls;

                    }

                    catch (NotSupportedException)

                    {

                        try

                        { //try TLS 1.1

                            ServicePointManager.SecurityProtocol = (SecurityProtocolType)768

                                                                 | SecurityProtocolType.Tls;

                        }

                        catch (NotSupportedException)

                        { //TLS 1.0

                            ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls;

                        }

                    }

                }

                #endregion

Received an invalid column length from the bcp client for colid Error while Bulk Upload in aspx c#

 I have created sample code to know the route cause of this issue.

So we can write code like this.

Sample code:-

#region bulkCopy

if (MyConnection.State == ConnectionState.Closed) MyConnection.Open();

DataTable dataTableName = GetDataTabletFromCSVFile(filenamestr1);

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(MyConnection))

{

bulkCopy.DestinationTableName = "Destination_TBL_My_Name_Temp";

try

{

bulkCopy.WriteToServer(dataTableName);

}

catch (Exception ex)

{

WriteErrorLog_Debug("My_FileExtractService " + ex.Message.ToString() + " " + ex.StackTrace.ToString() + " / " + RefNo, "testTimer_Elapsed1");

string message = "";

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);

message = (String.Format("Column: {0} contains data with a length greater than: {1}", column, length));

}

string aa =  message;

WriteErrorLog_Debug("My_FileExtractService Detailed Exception " + message.ToString() + " " + ex.StackTrace.ToString() + " / " + RefNo, "testTimer_Elapsed1");

}

}

#endregion

Get Web Request Error 500,400 Exception details in c#

WebException is a good approch to get the Real cause of 500 error,  

catch (WebException ex)

{
    using (var stream = ex.Response.GetResponseStream())
    using (var reader = new StreamReader(stream))
    {
        Console.WriteLine(reader.ReadToEnd());
    }
}
catch (Exception ex)
{
    // Something more serious happened
    // like for example you don't have network access
    // we cannot talk about a server exception here as
    // the server probably was never reached
}

Some Key to Help for Sql Optimization

 Below having some command used to get/analysis the Sql Query for optimization.

Add Below list of command at top pf the Query with ON and at the end again reset with OFF

Ex: Set NoCount ON

--Query

Set NoCount OFF

----Set Of Commands--------

Set NoExec OFF

Set ParseOnly OFF

Set Concat_Null_Yields_NULL OFF

Set Arithabort OFF

--Set Showplan_Text OFF

Set Statistics Time OFF

Set Statistics IO OFF

Set NOCount OFF

-----Mainly Used below three command only-----

SET NOCOUNT ON

SET STATISTICS TIME ON

set statistics io on