http://blog.tameryigit.com/2017/08/sql-stored-procedure-ve-cursor-kullanimi.html bağlantıda oluşturduğumuz Stored Procedure'ü, Sql Server'ın Job özelliğini kullanarak günde 1 kez otomatik çalıştırmak istedik.

Bunun için öncelikle Microsoft SQL Server Management Studio'da SQL Server Agent'ı aktif ettik.

Jobs bölümünden New Job diyerek Job tanımlama ekranını doldurduk.



Steps bölümünde yapılacak işleri, Schedules bölümünde ise bu işlerin hangi zamanlarda yapılacağını tanımladık.




Örnekte tanımladığımız Job, her gece saat 01:00'da EXEC TeklifOlustur komutunu yani prosedürümüzü çalıştırıyor.

Aşağıdaki örnekte TeklifOlustur adında bir prosedür oluşturduk.

Prosedürün yapacağı iş, birkaç tablodan UNION ALL ile birleştirilerek alınmış sorgu sonucunu stored procedure içinde tanımlanmış @TABLO adında bir tabloya yazmak.

Daha sonra kursor adıyla tanımlanmış CURSOR vasıtasıyla bu tablo üzerinde satır satır dolaşarak SELECT, SUM gibi çeşitli işlemler yaptıktan sonra bulduğu bilgileri TEKLIF tablosuna eklemek.

USE [BOSFOR_WEB_YONETIM_FIDE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[TeklifOlustur]
AS

DECLARE @ILKTARIH NVARCHAR(100) = GETDATE() --güncel zaman
DECLARE @SONTARIH NVARCHAR(100) = DATEADD(yy,10,GETDATE()) --güncel zamana 10 yıl ekledik

 --procedure içinde kürsörde kullanmak üzere bir tablo tanımlıyoruz

DECLARE @TABLO TABLE
(
 FIRMA NVARCHAR(50),
 SIPARISNO NVARCHAR(50),
 EKIMNO NVARCHAR(50),
 FIDEADI NVARCHAR(250),
 VARYANT NVARCHAR(50),
 SEEDITEMREF NVARCHAR(50),
 TOHUMADI NVARCHAR(250),
 PLANLANANEKIMTARIHI NVARCHAR(50),
 TESLIMTARIHI NVARCHAR(50),
 SIPARISMIKTARI NVARCHAR(50),
 PLNMIKTAR INT,
 EKILENMIKTAR NVARCHAR(50),
 BEKLEYENMIKTAR NVARCHAR(50),
 TEKLIFMIKTARI NVARCHAR(50),
      KALANGUN NVARCHAR(250)
)

INSERT @TABLO

   
SELECT TMP2.*
FROM (
SELECT
TMP.*
,KALANGUN=(SELECT DATEDIFF(day, GETDATE(), PLANLANANEKIMTARIHI))
FROM (
SELECT * FROM FITAP.[dbo].[FF_220_TOHUMBILGI](@SONTARIH,@ILKTARIH)
UNION ALL
SELECT * FROM FITAP.[dbo].[FF_230_TOHUMBILGI](@SONTARIH,@ILKTARIH)
UNION ALL
SELECT * FROM FITAP.[dbo].[FF_240_TOHUMBILGI](@SONTARIH,@ILKTARIH)
UNION ALL
SELECT * FROM FITAP.[dbo].[FF_250_TOHUMBILGI](@SONTARIH,@ILKTARIH)
UNION ALL
SELECT * FROM FITAP.[dbo].[FF_260_TOHUMBILGI](@SONTARIH,@ILKTARIH)
UNION ALL
SELECT * FROM FITAP.[dbo].[FF_270_TOHUMBILGI](@SONTARIH,@ILKTARIH)
) AS TMP
) AS TMP2
WHERE TOHUMADI IS NOT NULL
AND KALANGUN<=5
ORDER BY PLANLANANEKIMTARIHI

SELECT * FROM @TABLO

--CURSOR BAŞLIYOR
DECLARE @TOHUMAD NVARCHAR(250)
DECLARE @PLANLANANMIKTAR INT

DECLARE kursor CURSOR FOR
--TABLOYU GRUPLANDIRARAK ALDIK
SELECT TOHUMADI FROM @TABLO GROUP BY TOHUMADI
OPEN kursor
FETCH NEXT FROM kursor
INTO @TOHUMAD
WHILE @@FETCH_STATUS = 0
BEGIN

  --Burada TOHUMADI'na göre filtreleme yap ve PLNMIKTAR alanlarındaki veriyi toplayıp @PLANLANANMIKTAR değişkenine at
  --TOHUM ADINA GÖRE BAK
  SELECT @PLANLANANMIKTAR=SUM(PLNMIKTAR) FROM @TABLO WHERE TOHUMADI=@TOHUMAD
 
 --KAYIT EKLE
 INSERT INTO TEKLIF (TohumAd,KayitTarihi,Miktar) VALUES (@TOHUMAD,(SELECT GETDATE()),@PLANLANANMIKTAR)

FETCH NEXT FROM kursor INTO @TOHUMAD
END
CLOSE kursor
DEALLOCATE kursor

Bu prosedür otomatik olarak belirli aralıklarla çalışsın istenirse Sql Server'da Job oluşturulabilir.
http://blog.tameryigit.com/2017/08/sql-serverda-job-zamanlanmis-gorev.html

protected void DosyaIslem(string islemYapilacakDosyaId)
        {
            try
            {
                VeriMsSQL.Islem islem = new VeriMsSQL.Islem();
                DataTable dt = islem.DosyaBilgi(islemYapilacakDosyaId);
                if (dt.Rows.Count > 0)
                {
                    DataRow dr = dt.Rows[0];
                    string dosya = dr["Dosya"].ToString();
                    string dosyaAd = dr["DosyaAd"].ToString();
                    byte[] bytes = (byte[])dr["Dosya"];
                    Response.Clear();
                    Response.AddHeader("content-disposition", "attachment;filename=" + dosyaAd + ".zip");
                    Response.ContentType = "application/zip";
                    Response.BinaryWrite(bytes);
                    Response.End();
                }
                dt.Dispose();
            }
            catch (Exception ex)
            {
                lblIslemSonuc.Text = ex.Message;
                lblIslemSonuc.CssClass = "islemHatali";
                lblIslemSonuc.Visible = true;
            }
        }

C#'ta dosyayı zip'e çevirme ve ziplenmiş dosyayı varbinary tipinde sql de tutma 

C# ile bir dosyayı .zip formatına çevirmek için çeşitli dll dosyayları bulunmakta. Buradaki örnekte Ionic DotNetZip kullanıldı. https://dotnetzip.codeplex.com/

   protected void DosyaYukleSql()
        {
            try
            {
                Guid guId = Guid.NewGuid();
                string dosyaAd = System.IO.Path.GetFileName(fileDosya.FileName);
                string dosyaUzanti = System.IO.Path.GetExtension(dosyaAd);
                dosya = guId + dosyaUzanti;  //dosya adını Guid ile belirledik              
                    using (ZipFile zip = new ZipFile())
                    {
                        zip.AddEntry(dosya, fileDosya.PostedFile.InputStream);
                        zip.Save(Server.MapPath("/upload/zip/") + guId + ".zip");
                        //zipi binary yap
                        byte[] byteZip = File.ReadAllBytes(Server.MapPath("/upload/zip/") + guId + ".zip");
//veritabanına ekle
                        VeriMsSQL.Islem islem = new VeriMsSQL.Islem();
                        islem.DosyaEkle(guId.ToString(), byteZip, kayitId: dosyaKayitId);
                    }
                    File.Delete(Server.MapPath("/upload/zip/") + guId + ".zip"); //klasördeki zipli dosyayı sil
            }
            catch (Exception ex)
            {
                lblIslemSonuc.Text = ex.Message;
                lblIslemSonuc.CssClass = "islemHatali";
                lblIslemSonuc.Visible = true;
            }
        }

SQL'de varbinary tipinde tutulan ziplenmiş dosyayı indirme

Bunun için LinkedServer yöntemi kullanılabilir.

Microsoft SQL Management Studio'da Server Objects başlığının altında Linked Servers seçeneğine sağ click yapılıp New Linked Servers ile aşağıdaki tanımlama penceresine ulaşılır.

Linked Server yazan yer sorgulamalarda kullanılacak isim
Provider: SQL Server için Microsoft OLE DB Provider for SQL Server
Product Name: SQL
Data Source: Bağlanacağımız Sql sunucunun adı veya IP adresi
Catalog: Üzerinde çalışacağımız veritabanı



Security bölümünde;

Be made using this security context seçilip

Sql sunucu kullanıcı bilgileri girilir.


Server Options bölümünde;

örnekteki alanları true olarak ayarladıktan sonra OK diyerek Linked Server tanımlama işlemini bitirmiş oluyoruz.

Artık aşağıdaki şekilde sorgularda kullanılabilir.

SELECT * FROM ETICARET_DB.dbo.SOZLESME SOZLESME,LOGOSERVER.LKSDB.dbo.LG_073_CLCARD CLCARD
WHERE SOZLESME.CariId=CLCARD.LOGICALREF

ETICARET isimli veritabanımızın üzerindeki SOZLESME isimli tablo
Linked Server olarak tanımlamış olduğumuz LOGOSERVER isimli sunucumuzun LKSDB isimli veritabanının üzerindeki LG_073_CLCARD isimli tablo


Aynı Sql sunucu üzerinde iki farklı veritabanından ortak sorgulama yapmak için

Aşağıdaki şekilde veritabanı isimleri belirtilerek kullanılabilir.

SELECT * FROM ETICARET_DB.dbo.SOZLESME SOZLESME,LKSDB.dbo.LG_073_CLCARD CLCARD
WHERE SOZLESME.CariId=CLCARD.LOGICALREF

ETICARET isimli veritabanımızın üzerindeki SOZLESME isimli tablo
LKSDB isimli veritabanının üzerindeki LG_073_CLCARD isimli tablo


İki farklı adresteki Sql Serverdan ortak sorgulama yapmak için

Management Studio'da Management klasörü altında Database Mail üzerinde yeni bir mail yapılandırması oluşturulur.

Buradaki bilgilere gönderici mail adresi bilgileri girilir.

Profil name olarak verilen isim, trigger içinde @profile_name = '***' alanında kullanılacak.

İşlem tamamlandıktan sonra mail gönderim yapılandırmasının doğru yapılıp yapılmadığı Database Mail üzerinde sağ click yapılıp Send Test Mail ile kontrol edilebilir.

Daha sonra üzerinde hareket olduğunda bildirilmesini istediğimiz tablo hangisi ise o tablo üzerinde yeni bir trigger oluşturulur.

Bunun için aşağıdaki kodlar kullanılabilir.

USE [VERITABANI_ADI]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TRIGGER_ADI] ON [dbo].[KAYIT_EKLENEN_TABLO]
FOR INSERT --yalnızca eklendiğinde mail gönderecek
AS
      BEGIN
     
          EXEC msdb.dbo.sp_send_dbmail
            @profile_name = '***', --database mail üzerinde oluşturduğumuz mail profil adı
            @recipients = 'tamer@tameryigit.com', --gönderilecek mail adresi
            @body = 'Yeni kayıt eklendi. Onaylamak için http://www.tameryigit.com', --mail içeriği
            @subject = 'Onay bekleyen kayıtlar var.' --mail başlığı        
      END

Not: SQL SERVER 2012 üzerinde test ettim. Sql Agent aktif olmasa da mail gönderimi sorunsuz olarak gerçekleşti.

MsSQL veritabanında son eklenen kaydın Id'sini almak için aşağıdaki kod sql sorgu cümlesinin devamına eklenebilir.

SELECT @@IDENTITY 

Sadece ilgili connection için son eklenen id isteniyorsa

SELECT SCOPE_IDENTITY()

Örnek Kullanım;

int sonEklenenSoruId = islem.KayitEkle("INSERT INTO SinavSorulari (Soru,Cevap) VALUES ('Sorumuz','Cevabımız') SELECT @@IDENTITY"); 

SELECT @@IDENTITY ile ExecuteScalar kullanılıyor.