Özgür Özvarış

Bir yazılımcının kırık dökük dünyası

MSSQL Çok satırlı delete

clock Mayıs 19, 2014 17:52 by author OzgurOzvaris

Merhaba arkadaşlar,

Hatırlarsanız daha önce benzer bir konuyu update için paylaşmıştık.

MSSQL Çok satırlı update

Aynı şekilde yine delete edilen her kayıdın loglanması, log şişmesine ve hdd nin dolmasına neden oluyor. Fakat bir önceki update sorunundan farklı bir durum ile karşı karşıyayız. Update sorununda id ler sıralı olduğu için id leri her 50000 de bir logları temizliyorduk ama bu sefer kayıtlar id'ye göre değil. Bu nedenle cursor yöntemi kullandık.

DECLARE @iPK_ID int

DECLARE @itmpCount int

set @itmpCount = 0


DECLARE CURGETTABLESIZE CURSOR FOR  SELECT  PK_ID FROM TableName  where TARIH < convert(datetime,'01/01/14',3)  

 OPEN CURGETTABLESIZE 
 
 FETCH NEXT FROM CURGETTABLESIZE  INTO @iPK_ID
 
 WHILE @@FETCH_STATUS = 0   BEGIN
 
 DELETE FROM TableName WHERE PK_ID = @iPK_ID
 
 Print cast ( @iPK_ID as varchar(5)) + '-' + cast ( @itmpCount as varchar(5))
 
 set @itmpCount = @itmpCount + 1 
 
 if (@itmpCount = 50000)
 begin
   set @itmpCount  = 0
   Print '------------------------------------------------------'
   DBCC SHRINKDATABASE (DB, 10); 
 end
 
 FETCH NEXT FROM CURGETTABLESIZE INTO @iPK_ID  
 
 END  
 
 CLOSE CURGETTABLESIZE   
 
 DEALLOCATE CURGETTABLESIZE

İyi çalışmalar



Sql Trace Log

clock Mayıs 19, 2014 13:40 by author OzgurOzvaris
Değerli arkadaşlar,

bu gün  sql den 10 sn den fazla süren sorguları loglamayı ve bunları sorgulamayı öğreneceğiz.

Kaynak :

http://msdn.microsoft.com/en-us/library/cc293613.aspx

http://msdn.microsoft.com/en-us/library/ms186265.aspx

Burada dikkat etmemiz gereken bir husus exec sp_trace_setevent @TraceID, 12, 15, @on sql fonksyonunda kullanılan değerler. Bunlar event codlar ve bu evente karşılık istediğimiz kolonlar.

declare @rc int 
declare @TraceID int 
declare @maxfilesize bigint 
set @maxfilesize = 20
exec @rc = sp_trace_create
@TraceID output, 
2, 
N'InsertFileNameHere', 
@maxfilesize, 
NULL
if (@rc != 0) goto finish 
-- Set the events 
declare @on bit 
set @on = 1 
exec sp_trace_setevent @TraceID, 12, 15, @on 
exec sp_trace_setevent @TraceID, 12, 16, @on 
exec sp_trace_setevent @TraceID, 12, 1, @on 
exec sp_trace_setevent @TraceID, 12, 9, @on 
exec sp_trace_setevent @TraceID, 12, 17, @on 
exec sp_trace_setevent @TraceID, 12, 6, @on 
exec sp_trace_setevent @TraceID, 12, 10, @on 
exec sp_trace_setevent @TraceID, 12, 14, @on 
exec sp_trace_setevent @TraceID, 12, 18, @on 
exec sp_trace_setevent @TraceID, 12, 11, @on 
exec sp_trace_setevent @TraceID, 12, 12, @on 
exec sp_trace_setevent @TraceID, 12, 13, @on 
-- Set the Filters 
declare @bigintfilter bigint 
set @bigintfilter = 10000 
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter 
-- Set the trace status to start 
exec sp_trace_setstatus @TraceID, 1 
-- display trace id for future references 
select TraceID=@TraceID 
finish: 
go

Gelelim bu trace logu sorgulamaya 

SELECT IDENTITY(int, 1, 1) AS RowNumber, * INTO temp_trc from fn_trace_gettable('trace path.trc', 1)

select * from temp_trc

 İyi çalışmalar.



Windows Forms Listbox

clock Mayıs 19, 2014 10:33 by author OzgurOzvaris

Değerli Arkadaşlar,

Eğer windows forms listbox nesnesi ile biraz daha ileri uygulamalar yazmak isterseniz bu yazı faydalı olacaktır.

Kaynaklar : http://stackoverflow.com/questions/867514/make-listbox-items-have-a-different-value-than-item-text

Data isimli text value fieldlı claasın list boxta listelenip değerinin alınması.

Önce class oluşturulur.

public class SomeData
{
    public string Value { get; set; };
    public string Text { get; set; };
}

Sonra class içine değerler doldurulur.

List<SomeData> data = new List<SomeData>();
data.Add(new SomeData() { Value = 1, Text= "Some Text"});
data.Add(new SomeData() { Value = 2, Text = "Some Other Text"});

Daha sonra listbox'a bağlanır

listBox1.DisplayMember = "Text";
listBox1.DataSource = data;

İstenen içerik alınır

int value = (listBox1.SelectedItem as SomeData).Value;

 

 



Web Service Soap Header Linkler

clock Mayıs 14, 2014 11:09 by author OzgurOzvaris

wcf soap Log

http://stackoverflow.com/questions/17620228/log-soap-messages-from-a-console-application

SoapExtension Walkthrough -- VS7 version

http://radio-weblogs.com/0105476/stories/2002/03/22/soapextensionWalkthroughVs7Version.html

Programmatically Insert SoapHeader into SOAP Request with ASMX SoapExtensions

http://blogs.msdn.com/b/kaevans/archive/2007/08/06/programmatically-insert-soapheader-into-soap-request-with-asmx-soapextensions.aspx

private void AddHeader(SoapMessage message)
{
    CustomSoapHeader header = new CustomSoapHeader();
    header.CastMemberID = "gsupike@hotmail.com";
    header.MustUnderstand = false;
    message.Headers.Add(header);
}

 How to: Define and Process SOAP Headers 

http://msdn.microsoft.com/en-us/library/8728chd5(v=vs.80).aspx

 How to obtain SOAP Request body in C# Web

http://desalasworks.com/article/how-to-obtain-soap-request-body-in-c-web-services/

using System;
using System.Collections.Generic;
using System.Web;
using System.Xml;
using System.IO;
using System.Text;
using System.Web.Services;
using System.Web.Services.Protocols;
 
namespace SoapRequestEcho
{
  [WebService(
  Namespace = "http://soap.request.echo.com/",
  Name = "SoapRequestEcho")]
  public class EchoWebService : WebService
  {
    [WebMethod(Description = "Echo Soap Request")]
    public XmlDocument EchoSoapRequest(int input)
    {
      // Initialize soap request XML
      XmlDocument xmlSoapRequest = new XmlDocument();
 
      // Get raw request body
      using (Stream receiveStream = HttpContext.Current.Request.InputStream)
      {
        // Move to begining of input stream and read
        receiveStream.Position = 0;
        using (StreamReader readStream = 
                               new StreamReader(receiveStream, Encoding.UTF8))
        {
          // Load into XML document
          xmlSoapRequest.Load(readStream);
        }
      }
      // Return
      return xmlSoapRequest;
    }
  }
}

 Add Request SOAP-Header to Response

We will develop a web service that would authenticate users for web sites. The web site needs to pass the user ID as well as the password for the user. Apart from this the web site also needs to pass the sites ID and password. This information is needed so that the web service can validate the site that have requested for the user authentication.

The user ID and the password will be passed through the parameters of an authenticate method exposed by the web service, while the sites ID and password will be passed using SOAP header. Since the data is very sensitive SOAP extension will be used to encrypt the data and decrypt them.

http://stackoverflow.com/questions/7321526/add-request-soap-header-to-response

http://wso2.com/library/articles/extending-axis2/

Using SOAP Header and SOAP Extensions in a Web Service

case SoapMessageStage.BeforeDeserialize:

case SoapMessageStage.BeforeDeserialize:
readStr = new StreamReader(outwardStream);
writeStr = new StreamWriter(inwardStream);
soapMsg1 = readStr.ReadToEnd();
if ( message is System.Web.Services.Protocols.SoapClientMessage)
{
  // this is executed at client side
  xDoc.LoadXml(soapMsg1);
  XmlNodeList xResult = xDoc.GetElementsByTagName("AuthenticateResult");
  xResult[0].InnerXml = decrypt(xResult[0].InnerXml);
}
else if( message is System.Web.Services.Protocols.SoapServerMessage)
{
  // this is executed at server side
  xDoc.LoadXml(soapMsg1);
  XmlNodeList xSiteID = xDoc.GetElementsByTagName("siteID");
  xSiteID[0].InnerXml = decrypt(xSiteID[0].InnerXml);
  XmlNodeList xSitePwd = xDoc.GetElementsByTagName("sitePwd");
  xSitePwd[0].InnerXml = decrypt(xSitePwd[0].InnerXml);
  XmlNodeList xUserID = xDoc.GetElementsByTagName("UserID");
  xUserID[0].InnerXml = decrypt(xUserID[0].InnerXml);
  XmlNodeList xPwd = xDoc.GetElementsByTagName("Password");
  xPwd[0].InnerXml = decrypt(xPwd[0].InnerXml);
}
soapMsg1 = xDoc.InnerXml;
writeStr.Write(soapMsg1);
writeStr.Flush();
inwardStream.Position = 0;

case SoapMessageStage.AfterSerialize: 

case SoapMessageStage.AfterSerialize:
inwardStream.Position = 0;
readStr = new StreamReader(inwardStream);
writeStr = new StreamWriter(outwardStream);
soapMsg1 = readStr.ReadToEnd();
if ( message is System.Web.Services.Protocols.SoapClientMessage)
{
  // this is executed at client side
  xDoc.LoadXml(soapMsg1);
  XmlNodeList xSiteID = xDoc.GetElementsByTagName("siteID");
  xSiteID[0].InnerXml = encrypt(xSiteID[0].InnerXml);
  XmlNodeList xSitePwd = xDoc.GetElementsByTagName("sitePwd");
  xSitePwd[0].InnerXml = encrypt(xSitePwd[0].InnerXml);
  XmlNodeList xUserID = xDoc.GetElementsByTagName("UserID");
  xUserID[0].InnerXml = encrypt(xUserID[0].InnerXml);
  XmlNodeList xPwd = xDoc.GetElementsByTagName("Password");
  xPwd[0].InnerXml = encrypt(xPwd[0].InnerXml);
}
else if( message is System.Web.Services.Protocols.SoapServerMessage)
{
  // this is executed at server side
  xDoc.LoadXml(soapMsg1);
  XmlNodeList xResult = xDoc.GetElementsByTagName("AuthenticateResult");
  xResult[0].InnerXml = encrypt(xResult[0].InnerXml);
}
soapMsg1 = xDoc.InnerXml;
writeStr.Write(soapMsg1);
writeStr.Flush();
break;

 

 

 



Hakkımızda  AboneOl 

Blog Yayınımıza Hoşgeldiniz.

Month List

RecentPosts

Sign In