Celiker BAHCECI

How to use SQLBulkCopy to load data

8 May 2021 Author: Admin 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

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

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

}

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

}

0 Yorumlar

SQL insert from select

2 May 2021 Author: Admin In:

Copy table data with table structure

SELECT *
INTO DEPARTMENTS_20210502
FROM DEPARTMENTS
WHERE CDate >= '2021.05.02';

0 Yorumlar

Create SQL insert and table creation script from DataTable

30 Nis 2021 Author: celiker In: .net

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

}

0 Yorumlar

SQL Update with Select command

16 Nis 2021 Author: Admin In:

UPDATE

Table_A

SET

Table_A.YDSMaxPuanENG = (select max(yd.puan) from YDSPUAN yd where yd.UYELER_ID=Table_A.UYELER_ID and yd.Iptal=0 and (dil='İNGİLİZCE / ENGLISH' or OSYMHamVeri LIKE '%ENGLISH%'))

FROM

[DegerlemeList] AS Table_A

-----

UPDATE
Table_A
SET
Table_A.col1 = Table_B.col1,
Table_A.col2 = Table_B.col2
FROM
Some_Table AS Table_A
INNER JOIN Other_Table AS Table_B
ON Table_A.id = Table_B.id
WHERE

Table_A.col3 = '-_-_-'

0 Yorumlar

Get Random SipNo

1 Şub 2021 Author: Admin In: .net, ipucu

public string GetSipNo()

{

Random rn = new Random();

//yeni

int lenght = 6;

StringBuilder rs = new StringBuilder();

//string charPool = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

string chars = "ABCDEFGHIJKLMNPRSTUVYZ";

//string nums = "1234567890";

string nums = "123456789";

while (lenght > 0)

{

if (lenght == 6 || lenght == 5 || lenght == 2 || lenght == 1)

rs.Append(nums[(int)(rn.NextDouble() * nums.Length)]);

if (lenght == 3 || lenght == 4)

rs.Append(chars[(int)(rn.NextDouble() * chars.Length)]);

lenght--;

}

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr_b2b"].ConnectionString))

{

using (SqlCommand comm = new SqlCommand(@"SELECT COUNT (SIP_ID) FROM Siparisler WHERE SipNo='" + rs.ToString() + "'"))

{

comm.Connection = conn;

conn.Open();

if (comm.ExecuteScalar().ToString() != "0") return GetSipNo();

}

return rs.ToString();

}

0 Yorumlar

ASP.Net TreeView and JS functions

20 Oca 2021 Author: Admin In:

$(document).ready(function () {

$('[id^=ContentPlaceHolder1_trv_FakBoln]').change(function () {

AgacdaChkSecildimi();

chkbul();

});

function AgacdaChkSecildimi() {

var kimetiklandi = window.event.srcElement;

if (kimetiklandi.type == 'checkbox') {

var cocuklarinolduguDIVIDsi = kimetiklandi.id.replace('CheckBox', 'Nodes');

var div = document.getElementById(cocuklarinolduguDIVIDsi);

if (div == null) return;

var chks = div.getElementsByTagName('INPUT');

for (var i = 0; i < chks.length; i++) {

if (chks[i].type == 'checkbox')

chks[i].checked = kimetiklandi.checked;

}

function chkbul() {

var listemiz = "";

var inputs = document.querySelectorAll("input[type='checkbox']");

for (var i = 0; i < inputs.length; i++) {

if (inputs[i].checked) {

var icIDsi = inputs[i].id.replace("CheckBox", "").replace("ContentPlaceHolder1_trv_FakBoln","ContentPlaceHolder1_trv_FakBolt");

listemiz += "- "+document.getElementById(icIDsi).innerHTML + "<br>";

}

document.getElementById("div_secililer").innerHTML = listemiz;

}

function GetSelectedNodes() {

        var selected = "";

        //Reference the TreeView.

        var tree = document.getElementById("<%=TreeView1.ClientID %>");

        //Reference the CheckBoxes in TreeView.

        var checkboxes = tree.getElementsByTagName("INPUT");

        //Loop through the CheckBoxes.

        for (var i = 0; i < checkboxes.length; i++) {

            //If CheckBox is checked.

            if (checkboxes[i].checked) {

                //Fetch the Text from the adjacent SPAN element.

                var text = checkboxes[i].nextSibling.innerHTML;

                //Fetch the Value from the Title(ToolTip) of CheckBox.

                var value = checkboxes[i].title;

                selected += text + " " + value + "\n";

}

}

        if (selected != "") {

            alert(selected);

}

        return false;

}

0 Yorumlar

DropDownList with KeyValuePair

17 Ara 2020 Author: Admin In:

///<summary>

///FAT_ID ya ait asamaların isimlerini döner

///</summary>

public List<KeyValuePair<string, int>> FaturaAsamaIsimleriniGetir(int FAT_ID)

{

var asamalar_list = new List<KeyValuePair<string, int>>();

asamalar_list.Add(new KeyValuePair<string, int>("Bilinmiyor",0));

foreach(BLL.FatAsamalari i in BLL.FatAsamalari.VerList("Turu='Genel' and Cancelled=0 and FAT_ID=" + FAT_ID.ToString()+" order by sira"))

{

asamalar_list.Add(new KeyValuePair<string, int>(i.AsamaAdi, i.Sira??1));

}

asamalar_list.Add(new KeyValuePair<string, int>("Fatura Ödendi", 99));

asamalar_list.Add(new KeyValuePair<string, int>("Fatura İptal Edildi", 100));

asamalar_list.Add(new KeyValuePair<string, int>("Fatura eArşivde", 101));

return asamalar_list;

}

-----------------------------

ddl_filtre_asamalar.Items.Clear();

ddl_filtre_asamalar.Items.Insert(0, " Seçiniz");

ddl_filtre_asamalar.DataTextField = "Key";

ddl_filtre_asamalar.DataValueField = "Value";

ddl_filtre_asamalar.DataSource = new BLL.Ortak().FaturaAsamaIsimleriniGetir(Convert.ToInt32(ddl_FAT.SelectedValue));

ddl_filtre_asamalar.DataBind();

0 Yorumlar

Autocomplete with dropdownlist

16 Ara 2020 Author: Admin In:

$("#ContentPlaceHolder1_ddl_Ana").change(function () {

var v = $("[id$=ContentPlaceHolder1_ddl_Ana] option:selected").val();

// $('[id$=txt_r3_muellif]').autocomplete('Suggest.ashx?t=Ogrenciler&k=Muellifi&B=' + v + '&s=m');

var ana = ($("#ContentPlaceHolder1_ddl_Ana").val());

console.log(v);

$("[id*='txt_H']").autocomplete('destroy');

$("[id*='txt_H']").removeData('autocomplete');

$("[id*='txt_H']").unautocomplete();

$("[id*='txt_H']").autocomplete('Suggest.ashx?t=listeden&p=' + v + '');

});

0 Yorumlar

<< Önceki yazılar Sonraki yazılar >>

Yorumlarıda ara

Entries (RSS)

Ben Kimim ?

Celiker Bahceci Merhabalar, 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...

Son yorumlar
Son yazılar

Marka Şehir Gaziantep in Logosu ve Fontu (2)
Tahir yazdı: Teşekkürler abi çok faydalı bir paylaşım… [Fazlası]
Marka Şehir Gaziantep in Logosu ve Fontu (2)
zuma yazdı: çok müyük miktarlar harcandı bu log… [Fazlası]
T.C. Kimlik Numarasından Soy Agacı Çıkarma (47)
özgür yazdı: NAsıl yapacagım yardım edin lütfen [Fazlası]
Microsoft to turn .Net Micro Framework code, support over to the community (1)
yakup yazdı: [Fazlası]
Transfer your data from Excel to SQL Table (1)
Mehmet t yazdı: Navicat dbmanagement xok iyi bu konularda [Fazlası]

How to use SQLBulkCopy to load data

SQL insert from select

Create SQL insert and table creation script from DataTable

SQL Update with Select command

Get Random SipNo

ASP.Net TreeView and JS functions

DropDownList with KeyValuePair

Autocomplete with dropdownlist

Ben Kimim ?

Kategoriler

Arşiv

Takip ettiklerim