MS Sql Server saklı yordamlarına (stored procedure) çoklu parametre geçmek
Bu yazıda Microsoft Sql Server ™ üzerinde yazılmış prosedürlere (stored procedure) çoklu parametre geçmenin yollarından birini inceleyeceğiz. T-Sql diline ve temel programcılık bilgisine hakim değilseniz bu yazı sizin için karmaşık olabilir.
Bildiğimiz gibi Microsoft Sql Server en yeni sürümü olan 2005 dahil olmak üzere bütünleşik bir dizi (array) desteği getirmemektedir. Bu nedenle veriyle iletişimizi sağlayan prosedürlerimizde dizi veya benzeri bir veri tipini kullanmamız mümkün değil. Böyle olmasaydı uygulamamız içinde yaratacağımız diziyi prosedürümüze geçer ve bu yazıyı okumak yerine arkamıza yaslanır tv izlerdik. Bu yazıda bu zorluğu aşmak için xml metodunun nasıl kullanılacağını göstereceğim.
Vaktiyle üzerinde çalıştığım bir emlak portalı projesinde, kullanıcılara seçtikleri ilanları kıyaslamalarını sağlayabilecek bir sistem geliştiriyordum. Kullanıcı sınırsız sayıda ilan seçebilecek ve seçtiği ilanların tüm detayları veritabanından alınarak birebir kıyaslanacaktı. Bu durumda daha önce yazmış olduğum ve sadece bir ilana ait üm detayları getiren prosedür yeterli olmayacaktı.
Elimdeki mevcut prosedür şöyleydi :
CREATE PROCEDURE [dbo].[SelectAdvertisement]
(@AdvertisementIndex bigint,
.... )
AS ....
Bu prosedürü baz alarak şu şekilde yeni bir tane yazdım :CREATE PROCEDURE [dbo].[SelectAdvertisementMultiple]
(
@AdvertisementIndexListXml varchar(1000)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @DocXml int
EXEC sp_xml_preparedocument @DocXml OUTPUT, @AdvertisementIndexListXml
SELECT * FROM dbo.Advertisements AS a WITH (NOLOCK) INNER JOIN
OPENXML (@DocXml, '/advertisementlist/adv', 1) WITH (AdvertisementIndex int) AS b
ON a.AdvertisementIndex = b.AdvertisementIndex
WHERE a.Active=1
EXEC sp_xml_removedocument @DocXml
END
Prosedürün çalıştırılması ise şöyle olmalı :
EXEC dbo.[SelectAdvertisementMultiple]
'<advertisementlist>
<adv advertisementindex="24343" />
<adv advertisementindex="536332" />
<adv advertisementindex="72224" />
</advertisementlist>
'
Gördüğünüz gibi geçeceğimiz parametreleri bir xml metin haline getirip prosedüre geçiyoruz.
Prosedür aldığı xml metni OPENXML ile parse ederek (parse : işlemek, ayrıştırmak) AdvertisementIndex'leri ayrıştırmakta ve ayrıştırılan AdvertisementIndex'ler inner join ile ilgili tabloyla eşlenmektedir.
OPENXML ile ilgili ayrıntılar için
http://technet.microsoft.com/en-us/library/ms186918.aspx
adresini ziyaret edebilirsiniz.Uygulamanız içerisinden Xml oluşturmak için .NET classlarını kullanabilirsiniz. System.Xml altında bulunan classları başka bir yazıda ayrıntılı olarak anlatacağım.
Yine de konuyu tamamlamak açısından uygulama tarafında basit bir örnek vermek istiyorum :
using System.Xml ; // Xml sınıflarını (class) barındıran namespace (uzay)
............
private long[] AdvertisementIndexList = new long[5];
// kullanıcıdan gelen ilan idleri al, işle ve AdvertisementIndexList dizisine al.
// bu yazıyı okuduğunuza göre bu kısmı kodlamak sizin için çocuk işi :).
.............
XmlDocument xmldoc = new XmlDocument();
XmlNode advertisementListNode = xmldoc.CreateElement("AdvertisementList");
xmldoc.AppendChild(advertisementListNode);
for (int i = 0; i < this.AdvertisementIndexList.Length; i++)
{
XmlNode advNode = xmldoc.CreateElement("Adv");
XmlAttribute advNodeAttribute = xmldoc.CreateAttribute("AdvertisementIndex");
advNodeAttribute.Value = this.AdvertisementIndexList[i].ToString();
advNode.Attributes.Append(advNodeAttribute);
advertisementListNode.AppendChild(advNode);
}
string xmlString = xmldoc.InnerXml;
.....
// bağlantı (connection), komut (command) objelerini oluştur..
// xml komut objesine ekle.
cmd.Parameters.Add("@AdvertisementIndexListXml", SqlDbType.VarChar,1000).Value = xmlString;
.......
// örnekte data reader kullanıyorum ama DataSet de kullanabilirsiniz.
SqlDataReader reader = cmd.ExecuteReader();
Gördüğünüz gibi esasında xml yöntemi ile prosedürlere "dizi" geçmek oldukça kolay. Farklı yöntemler de mevcut olmasına rağmen , kişisel olarak bu yöntemi kullanıyorum.
Diğer yöntemlere gelince :
1- Dinamik sql kullanmak. (Yanlış kullanım sql injection davetiyesi çıkarır.)
2- Diziyi CSV formatına getirip prosedüre geçmek ve "123,456,789,743,244,4223" şeklindeki veriyi işlemek.
3-Yukarıdaki işleme işini yapan ve tablo tipi veri döndüren bir UDF (user defined function : kullanıcı tanımlı fonksiyon) yazmak. Prosedürümüzü table veri tipi alır hale getirmek ve UDF'in çıktısını prosedüre vermek.
Xml'i tercih ettiğimi tekrarlıyorum.