SQL insert from select

2 May 2021 In:

Copy table data with table structure

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




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

SQL Update with Select command

16 Nis 2021 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 = '-_-_-' 
 
 

DropDownList with KeyValuePair

17 Ara 2020 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(); 

Autocomplete with dropdownlist

16 Ara 2020 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 + '');
 
            });

TextBox only numbers

1 Eki 2020 In:
onkeydown="return ( event.ctrlKey || event.altKey || (47<event.keyCode && event.keyCode<58 && event.shiftKey==false) || (95<event.keyCode && event.keyCode<106) || (event.keyCode==8) || (event.keyCode==9) || (event.keyCode>34 && event.keyCode<40) || (event.keyCode==46) )"

Kredi kartı textbox

25 Eyl 2020 In:
 <asp:TextBox ID="txt_kartno" runat="server" CssClass="form-control"  placeholder="0000 0000 0000 0000"
 MaxLength="19"
 onkeypress="return isNumberbosluklu(event);"></asp:TextBox> 
 
 
 
 function isNumberbosluklu(evt) {
            evt = (evt) ? evt : window.event;
            console.log(evt.keyCode);
            var charCode = (evt.which) ? evt.which : evt.keyCode;
            //if (charCode == 32) return true;//bosluk ka
            if (charCode > 31 && (charCode < 48 || charCode > 57)) {
                return false;
            }

            //cc için bosluk ekle her 4 karakterde
            var x = document.getElementById("txt_kartno");
            var index = x.value.lastIndexOf(" ");
            var test = x.value.substr(index + 1);
            if (test.length === 4)
                x.value = x.value + " ";

            return true;
        } 
 
 
 
 
 
 
 
 
 
 
/* ESKİSİ  */
 
 
 
 
 
  <asp:TextBox ID="txtLira" CssClass="form-control" type="number" MaxLength="6" Text="0" autocomplete="off" onfocus="tut(this);" onblur="ver(this);" runat="server" onkeypress="return isNumber(event);" Style="text-align: right"></asp:TextBox>  
 
 
 var eski = "";
        function tut(element) {
            eski = element.value;
            element.value = '';
        }

        function ver(element) {
            if ('' == element.value) {
                element.value = eski;
                //  element.style.background = '#FFFFFF';
            }
            // else
            //  element.style.background = '#A9F5BC';

        }
        function isNumber(evt) {
            evt = (evt) ? evt : window.event;
            var charCode = (evt.which) ? evt.which : evt.keyCode;
            if (charCode > 31 && (charCode < 48 || charCode > 57)) {
                return false;
            }
            return true;
        }
 

Call SOAP API with HttpWebRequest

28 Ağu 2020 In:

 
byte[] data = System.Text.Encoding.ASCII.GetBytes("id=1");

            System.Net.WebRequest request = System.Net.HttpWebRequest.Create(
            "http://localhost:54943/api.asmx/usersgetir");
            request.Method = "POST";
            request.ContentLength = data.Length;
            request.ContentType = "application/x-www-form-urlencoded";

            System.IO.Stream str = request.GetRequestStream();
            str.Write(data, 0, data.Length);
            str.Flush();
            System.Net.WebResponse response = request.GetResponse();
            System.IO.StreamReader reader = new System.IO.StreamReader(response.GetResponseStream());
            string result = reader.ReadToEnd();

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...