How to use SQLBulkCopy to load data

8 May 2021 In: .net
-Using a stored procedure: 37 seconds
-Using concatenated inline SQL: 45 seconds
-Using Entity Framework: 45 minutes
-Using the SQLBulkCopy class: 4.5 seconds
 
 
1.
 DataTable table = new DataTable();
table.TableName = "LogBulkLoad";
table.Columns.Add("IpAddress", typeof(string));
table.Columns.Add("Identd", typeof(string));
table.Columns.Add("RemoteUser", typeof(string));
table.Columns.Add("LogDateTime", typeof(System. DateTimeOffset));
table.Columns.Add("Method", typeof(string));
table.Columns.Add("Resource", typeof(string));
table.Columns.Add("Protocol", typeof(string));
table.Columns.Add("QueryString", typeof(string));
table.Columns.Add("StatusCode", typeof(int));
table.Columns.Add("Size", typeof(long));
table.Columns.Add("Referer", typeof(string));
table.Columns.Add("UserAgent", typeof(string));
 
 
2.
foreach (var log in logData)
{
DataRow row = table.NewRow();
row["IpAddress"] = log.IpAddress;
row["Identd"] = log.Identd;
row["RemoteUser"] = log.RemoteUser;
row["LogDateTime"] = log.LogDateTime;
row["Method"] = log.Method;
row["Resource"] = log.Resource;
row["Protocol"] = log.Protocol;
row["QueryString"] = log.QueryString;
row["StatusCode"] = log.StatusCode;
row["Size"] = log.Size;
row["Referer"] = log.Referer;
row["UserAgent"] = log.UserAgent;
table.Rows.Add(row);
}
 
 
3.
using (SqlConnection conn = new SqlConnection(Configu rationManager.ConnectionStrings["LogParserContext"]. ConnectionString))
{
conn.Open();
using (SqlBulkCopy s = new SqlBulkCopy(conn))
{
s.DestinationTableName = "LogBulkLoad";
s.ColumnMappings.Add("IpAddress", "IpAddress");
s.ColumnMappings.Add("Identd", "Identd");
s.ColumnMappings.Add("RemoteUser", "RemoteUser");
s.ColumnMappings.Add("LogDateTime", "LogDateTime");
s.ColumnMappings.Add("Method", "Method");
s.ColumnMappings.Add("Resource", "Resource");
s.ColumnMappings.Add("Protocol", "Protocol");
s.ColumnMappings.Add("QueryString", "QueryString");
s.ColumnMappings.Add("StatusCode", "StatusCode");
s.ColumnMappings.Add("Size", "Size");
s.ColumnMappings.Add("Referer", "Referer");
s.ColumnMappings.Add("UserAgent", "UserAgent");
s.WriteToServer((DataTable)table);
}
}
 
 


        public static string CreateInsert(DataTable table, string tablenamesi,int u)
        {
            string sqlsc = "", sqlvl = "";
            sqlsc = "INSERT INTO "+tablenamesi+" (";
            for (int i = 0; i < table.Columns.Count; i++)
            {
                sqlsc += "[" + table.Columns[i].ColumnName + "],";
                if (table.Columns[i].DataType.ToString() == "System.String")
                    sqlvl += "'" + table.Rows[u][i].ToString() + "',";
                else
                if (table.Columns[i].DataType.ToString() == "System.DateTime")
                {
                    if(table.Rows[u][i]!=System.DBNull.Value)
                    sqlvl += "'" + ((DateTime)table.Rows[u][i]).ToString("yyy.MM.dd HH:mm:ss") + "',";
                    else
                        sqlvl += "NULL,";
                }
                else
                    sqlvl += "'" + (table.Rows[u][i].ToString() == "" ? "0" :
                    table.Rows[u][i].ToString().Replace(",", ".")) + "',";
            }
            if (sqlsc.EndsWith(",")) sqlsc = sqlsc.Substring(0, sqlsc.Length - 1);
            if (sqlvl.EndsWith(",")) sqlvl = sqlvl.Substring(0, sqlvl.Length - 1);
            sqlsc = sqlsc + ") VALUES (" + sqlvl + ")";
            return sqlsc;
        }
 
 
 
 
 
 
  public static string CreateTABLE(string tableName, DataTable table)
        {
            string sqlsc;
            sqlsc = "CREATE TABLE " + tableName + "(";
            for (int i = 0; i < table.Columns.Count; i++)
            {
                sqlsc += "\n [" + table.Columns[i].ColumnName + "] ";
                string columnType = table.Columns[i].DataType.ToString();
                switch (columnType)
                {
                    case "System.Int32":
                        sqlsc += " int ";
                        break;
                    case "System.Int64":
                        sqlsc += " bigint ";
                        break;
                    case "System.Int16":
                        sqlsc += " smallint";
                        break;
                    case "System.Byte":
                        sqlsc += " tinyint";
                        break;
                    case "System.Decimal":
                        sqlsc += " decimal ";
                        break;
                    case "System.DateTime":
                        sqlsc += " datetime ";
                        break;
                    case "System.String":
                    default:
                        sqlsc += string.Format(" nvarchar({0}) ", table.Columns[i].MaxLength == -1 ? "max" : table.Columns[i].MaxLength.ToString());
                        break;
                }
                if (table.Columns[i].AutoIncrement)
                    sqlsc += " IDENTITY(" + table.Columns[i].AutoIncrementSeed.ToString() + "," + table.Columns[i].AutoIncrementStep.ToString() + ") ";
                if (!table.Columns[i].AllowDBNull)
                    sqlsc += " NOT NULL ";
                sqlsc += ",";
            }
            return sqlsc.Substring(0, sqlsc.Length - 1) + "\n)";
        } 
 

ASP.Net JSON post with basic auth

25 Ağu 2020 In: .net
string url = "https://zkapigw.xbank.com.tr:8443/api/accountService";

                User user = new xbank.User()
                {
                    associationCode = "195000_XYX_TICARET",
                    startDate = "2019-09-28T00:00:00",
                    endDate = "2019-10-05T00:00:00"
                };
                var json = JsonConvert.SerializeObject(user);

                HttpWebRequest http = (HttpWebRequest)WebRequest.Create(new Uri(url));
                http.Credentials = CredentialCache.DefaultCredentials;
                http.ContentType = "application/json";
                http.Headers["Authorization"] = "Basic " + Convert.ToBase64String(Encoding.Default.GetBytes(username + ":" + password));
                http.AllowAutoRedirect = true;
                http.Method = "POST";

                ASCIIEncoding encoding = new ASCIIEncoding();
                Byte[] bytes = encoding.GetBytes(json);

                Stream newStream = http.GetRequestStream();
                newStream.Write(bytes, 0, bytes.Length);
                newStream.Close();

                var response = http.GetResponse();
                var stream = response.GetResponseStream();
                var sr = new StreamReader(stream);
                var responseText = sr.ReadToEnd();

                Hareketler distance = JsonConvert.DeserializeObject<Hareketler>(responseText);

Clear your text for URL Request

12 Mar 2019 In: .net
public static string CleanForURL(string m)
        {
            m = m.Replace("\\", " ").Replace("/", " ").Replace("&"," ").Replace("?"," ").Replace("%", " ");
            char[] s = m.Where(c => (char.IsLetterOrDigit(c) || char.IsWhiteSpace(c) || c == '-')).ToArray();
            return (new string(s)).Trim();
        }

Delete row from Datatable with an iteration

1 Mar 2018 In: .net
List<DataRow> toDelete = new List<DataRow>();
                foreach (DataRow dr in dt_kimaylmt.Rows)
                {
                    if (dr["DepartmentId"].ToString() == "" || dr["FleetId"].ToString().Length == 0)
                    {
                        toDelete.Add(dr);
                    }
                }

                foreach (DataRow dr in toDelete)
                {
                    dt_kimaylmt.Rows.Remove(dr);
                }
               

                dt_kimaylmt.AcceptChanges();

Repeater Footer Summary

1 Ağu 2017 In: .net
        protected void rpt_kayitlar_ItemDataBound(object sender, RepeaterItemEventArgs e)
        {
            if (e.Item.ItemType == ListItemType.Header) { Session["ktp"] = 0m; }
            if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem) { Session["ktp"] = (decimal)Session["ktp"] + (decimal)DataBinder.Eval(e.Item.DataItem, "Tutar");  }
            if (e.Item.ItemType == ListItemType.Footer) { ((Label)e.Item.FindControl("lbl_t")).Text = ((decimal)Session["ktp"]).ToString("N2"); }
        }

Transfer HTTP to HTTPS on IIS

1 Haz 2017 In: .net, ipucu

We have 2 methods for this purpose:

1. You can add this line to your index.html/default.aspx web page

if (location.protocol !== "https:"){
location.replace(window.location.href.replace("http:","https:"));
}

 

2. If you are using higher version of IIS 6.1, just put this lines to web.config

<system.webServer>
<rewrite>
<rules>
<rule name="HTTPyi HTTPSye Yonlendirme" stopProcessing="true">
<match url="(.*)" />
<conditions>
<add input="{HTTPS}" pattern="off" ignoreCase="true" />
</conditions>
<action type="Redirect" redirectType="Permanent" url="https://{HTTP_HOST}/{R:1}" />
</rule>
</rules>
</rewrite>
</system.webServer>

 

 

RadioButton group name solution for repater

25 Şub 2017 In: .net, ipucu

I got a templated control (a repeater) listing some text and other markup. Each item has a radiobutton associated with it, making it possible for the user to select ONE of the items created by the repeater.

The repeater writes the radiobutton setting its ID and NAME generated with the default ASP.NET naming convention making each radiobutton a full 'group'. This means all radiobuttons are independent on each other, which again unfortunately means I can select all radiobuttons at the same time. The radiobutton has the clever attribute 'groupname' used to set a common name, so they get grouped together and thus should be dependant (so I can only select one at a time). The problem is - this doesn't work - the repeater makes sure the id and thus the name (which controls the grouping) are different.
 
With this JS method, you can set name with a constant name. And that s solved :) 
 
    function set_radio_name() {
        $("[type=radio]").each(function (i) {
            var name = $(this).attr("name");
            var splitted = name.split("$");
            $(this).attr("name", "sec");
        });
    };

    $(document).ready(function () {
        set_radio_name();
    });

Ben Kimim ?

Celiker BahceciMerhabalar, ben Çeliker BAHÇECİ. 2004 den beri özel sektörde bilgisayar mühendisligi ve egitmenlik yapıyorum. Yine aynı yılın Ekim ayından beri sitemde .Net ile programlama ve hayat görüşüm ile ilgili makalelerimi yayınlıyorum. Blogum dışında Yazgelistir.com, mobilnedir.com gibi ineta kapsamındaki bir çok siteye Microsoft teknolojileri ile ilgili yazılar yazmaktayım.
Bu site ile sizinde hayatınızı anlamlandırmanızda bir parça katkımın olması dilegiyle...