SqlHandler

Klasik olarak kullandığımız SQL işlemleri yerine ürettiğim alternatif bir class

c# kodları

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
/// <summary>

/// ''' SQL İşlemleri - Son Düzenleme : 06.08.2020

/// ''' CodeBy : Faysal KARASOY

/// ''' </summary>
public class SqlHandler
{
    public static string ConnectionString
    {
        get
        {
            return ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ConnectionString;
        }
    }

    public void ExeCuteNonQuery(string SQL)
    {
        using (SqlConnection Baglanti = new SqlConnection(ConnectionString))
        {
            Baglanti.Open();
            using (SqlCommand Komut = new SqlCommand())
            {
                Komut.Connection = Baglanti;
                Komut.CommandText = SQL;
                Komut.ExecuteNonQuery();
            }
            Baglanti.Close();
        }
    }

    public void ExeCuteNonQuery(string SQL, string ParametreAdi, object ParametreDegeri)
    {
        using (SqlConnection Baglanti = new SqlConnection(ConnectionString))
        {
            Baglanti.Open();
            using (SqlCommand Komut = new SqlCommand())
            {
                Komut.Connection = Baglanti;
                Komut.CommandText = SQL;
                Komut.Parameters.AddWithValue(ParametreAdi, ParametreDegeri);
                Komut.ExecuteNonQuery();
                Komut.Parameters.Clear();
            }
            Baglanti.Close();
        }
    }

    public void ExeCuteNonQuery(string SQL, List<ISqlParameters> Parameters)
    {
        using (SqlConnection Baglanti = new SqlConnection(ConnectionString))
        {
            Baglanti.Open();
            using (SqlCommand Komut = new SqlCommand())
            {
                Komut.Connection = Baglanti;
                Komut.CommandText = SQL;
                foreach (ISqlParameters Para in Parameters)
                    Komut.Parameters.AddWithValue(Para.Name, Para.Value);
                Komut.ExecuteNonQuery();
                Komut.Parameters.Clear();
            }
            Baglanti.Close();
        }
    }

    public void ExeCuteNonQueryAsSp(string StroredProcedureName, string ParametersName, object ParametersValue)
    {
        using (SqlConnection Baglanti = new SqlConnection(ConnectionString))
        {
            Baglanti.Open();
            using (SqlCommand Komut = new SqlCommand())
            {
                Komut.Connection = Baglanti;
                Komut.CommandText = StroredProcedureName;
                Komut.CommandType = CommandType.StoredProcedure;
                Komut.Parameters.AddWithValue(ParametersName, ParametersValue);
                Komut.ExecuteNonQuery();
                Komut.Parameters.Clear();
            }
            Baglanti.Close();
        }
    }

    public void ExeCuteNonQueryAsSp(string StroredProcedureName, List<ISqlParameters> Parameters)
    {
        using (SqlConnection Baglanti = new SqlConnection(ConnectionString))
        {
            Baglanti.Open();
            using (SqlCommand Komut = new SqlCommand())
            {
                Komut.Connection = Baglanti;
                Komut.CommandText = StroredProcedureName;
                Komut.CommandType = CommandType.StoredProcedure;
                foreach (ISqlParameters Para in Parameters)
                    Komut.Parameters.AddWithValue(Para.Name, Para.Value);
                Komut.ExecuteNonQuery();
                Komut.Parameters.Clear();
            }
            Baglanti.Close();
        }
    }


    public object ExeCuteScalar(string SQL, List<ISqlParameters> Parametreler)
    {
        object gd;

        using (SqlConnection Baglanti = new SqlConnection(ConnectionString))
        {
            Baglanti.Open();
            using (SqlCommand Komut = new SqlCommand())
            {
                Komut.Connection = Baglanti;
                Komut.CommandText = SQL;
                foreach (var Param in Parametreler)
                    Komut.Parameters.AddWithValue(Param.Name, Param.Value);
                gd = Komut.ExecuteScalar();
            }
            Baglanti.Close();
        }

        return gd;
    }


    public object ExeCuteScalar(string SQL, string ParametreAdi, object ParametreDegeri)
    {
        object gd;

        using (SqlConnection Baglanti = new SqlConnection(ConnectionString))
        {
            Baglanti.Open();
            using (SqlCommand Komut = new SqlCommand())
            {
                Komut.Connection = Baglanti;
                Komut.CommandText = SQL;
                Komut.Parameters.AddWithValue(ParametreAdi, ParametreDegeri);
                gd = Komut.ExecuteScalar();
            }
            Baglanti.Close();
        }

        return gd;
    }
    public object ExeCuteScalar(string SQL)
    {
        object gd;

        using (SqlConnection Baglanti = new SqlConnection(ConnectionString))
        {
            Baglanti.Open();
            using (SqlCommand Komut = new SqlCommand())
            {
                Komut.Connection = Baglanti;
                Komut.CommandText = SQL;
                gd = Komut.ExecuteScalar();
            }
            Baglanti.Close();
        }

        return gd;
    }

    public object ExeCuteScalarAsSp(string StroredProcedureAdi)
    {
        object gd;

        using (SqlConnection Baglanti = new SqlConnection(ConnectionString))
        {
            Baglanti.Open();
            using (SqlCommand Komut = new SqlCommand())
            {
                Komut.Connection = Baglanti;
                Komut.CommandText = StroredProcedureAdi;
                Komut.CommandType = CommandType.StoredProcedure;
                gd = Komut.ExecuteScalar();
            }
            Baglanti.Close();
        }

        return gd;
    }

    public object ExeCuteScalarAsSp(string StroredProcedureAdi, string ParametreAdi, object ParametreDegeri)
    {
        object gd;

        using (SqlConnection Baglanti = new SqlConnection(ConnectionString))
        {
            Baglanti.Open();
            using (SqlCommand Komut = new SqlCommand())
            {
                Komut.Connection = Baglanti;
                Komut.CommandText = StroredProcedureAdi;
                Komut.CommandType = CommandType.StoredProcedure;
                Komut.Parameters.AddWithValue(ParametreAdi, ParametreDegeri);
                gd = Komut.ExecuteScalar();
            }
            Baglanti.Close();
        }

        return gd;
    }

    public object ExeCuteScalarAsSp(string StroredProcedureAdi, List<ISqlParameters> ParametreListesi)
    {
        object gd;

        using (SqlConnection Baglanti = new SqlConnection(ConnectionString))
        {
            Baglanti.Open();
            using (SqlCommand Komut = new SqlCommand())
            {
                Komut.Connection = Baglanti;
                Komut.CommandText = StroredProcedureAdi;
                Komut.CommandType = CommandType.StoredProcedure;
                foreach (var Param in ParametreListesi)
                    Komut.Parameters.AddWithValue(Param.Name, Param.Value);
                gd = Komut.ExecuteScalar();
            }
            Baglanti.Close();
        }

        return gd;
    }
}

public struct ISqlParameters
{
    /// <summary>
    ///     ''' Parametre Adı
    ///     ''' </summary>
    ///     ''' <remarks></remarks>
    public string Name;
    /// <summary>
    ///     ''' Parametre Değeri
    ///     ''' </summary>
    ///     ''' <remarks></remarks>
    public object Value;
}

Visual Basic Kodları

Imports System.Data
Imports System.Data.SqlClient
''' <summary>
''' SQL İşlemleri - Son Düzenleme : 06.08.2020
''' CodeBy : Faysal KARASOY
''' </summary>
Public Class SqlHandler
    Public Shared ReadOnly Property ConnectionString As String
        Get
            Return ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ConnectionString
        End Get
    End Property

    Public Sub ExeCuteNonQuery(SQL As String)
        Using Baglanti As New SqlConnection(ConnectionString)
            Baglanti.Open()
            Using Komut As New SqlCommand
                Komut.Connection = Baglanti
                Komut.CommandText = SQL
                Komut.ExecuteNonQuery()
            End Using
            Baglanti.Close()
        End Using
    End Sub

    Public Sub ExeCuteNonQuery(SQL As String, ParametreAdi As String, ParametreDegeri As Object)
        Using Baglanti As New SqlConnection(ConnectionString)
            Baglanti.Open()
            Using Komut As New SqlCommand
                Komut.Connection = Baglanti
                Komut.CommandText = SQL
                Komut.Parameters.AddWithValue(ParametreAdi, ParametreDegeri)
                Komut.ExecuteNonQuery()
                Komut.Parameters.Clear()
            End Using
            Baglanti.Close()
        End Using

    End Sub

    Public Sub ExeCuteNonQuery(SQL As String, Parameters As List(Of ISqlParameters))
        Using Baglanti As New SqlConnection(ConnectionString)
            Baglanti.Open()
            Using Komut As New SqlCommand
                Komut.Connection = Baglanti
                Komut.CommandText = SQL
                For Each Para As ISqlParameters In Parameters
                    Komut.Parameters.AddWithValue(Para.Name, Para.Value)
                Next
                Komut.ExecuteNonQuery()
                Komut.Parameters.Clear()
            End Using
            Baglanti.Close()
        End Using
    End Sub

    Public Sub ExeCuteNonQueryAsSp(StroredProcedureName As String, ParametersName As String, ParametersValue As Object)
        Using Baglanti As New SqlConnection(ConnectionString)
            Baglanti.Open()
            Using Komut As New SqlCommand
                Komut.Connection = Baglanti
                Komut.CommandText = StroredProcedureName
                Komut.CommandType = CommandType.StoredProcedure
                Komut.Parameters.AddWithValue(ParametersName, ParametersValue)
                Komut.ExecuteNonQuery()
                Komut.Parameters.Clear()
            End Using
            Baglanti.Close()
        End Using
    End Sub

    Public Sub ExeCuteNonQueryAsSp(StroredProcedureName As String, Parameters As List(Of ISqlParameters))
        Using Baglanti As New SqlConnection(ConnectionString)
            Baglanti.Open()
            Using Komut As New SqlCommand
                Komut.Connection = Baglanti
                Komut.CommandText = StroredProcedureName
                Komut.CommandType = CommandType.StoredProcedure
                For Each Para As ISqlParameters In Parameters
                    Komut.Parameters.AddWithValue(Para.Name, Para.Value)
                Next
                Komut.ExecuteNonQuery()
                Komut.Parameters.Clear()
            End Using
            Baglanti.Close()
        End Using

    End Sub


    Public Function ExeCuteScalar(SQL As String, Parametreler As List(Of ISqlParameters)) As Object
        Dim gd As Object

        Using Baglanti As New SqlConnection(ConnectionString)
            Baglanti.Open()
            Using Komut As New SqlCommand
                Komut.Connection = Baglanti
                Komut.CommandText = SQL
                For Each Param In Parametreler
                    Komut.Parameters.AddWithValue(Param.Name, Param.Value)
                Next
                gd = Komut.ExecuteScalar()
            End Using
            Baglanti.Close()
        End Using

        Return gd
    End Function


    Public Function ExeCuteScalar(SQL As String, ParametreAdi As String, ParametreDegeri As Object) As Object
        Dim gd As Object

        Using Baglanti As New SqlConnection(ConnectionString)
            Baglanti.Open()
            Using Komut As New SqlCommand
                Komut.Connection = Baglanti
                Komut.CommandText = SQL
                Komut.Parameters.AddWithValue(ParametreAdi, ParametreDegeri)
                gd = Komut.ExecuteScalar()
            End Using
            Baglanti.Close()
        End Using

        Return gd
    End Function
    Public Function ExeCuteScalar(SQL As String) As Object
        Dim gd As Object

        Using Baglanti As New SqlConnection(ConnectionString)
            Baglanti.Open()
            Using Komut As New SqlCommand
                Komut.Connection = Baglanti
                Komut.CommandText = SQL
                gd = Komut.ExecuteScalar()
            End Using
            Baglanti.Close()
        End Using

        Return gd
    End Function

    Public Function ExeCuteScalarAsSp(StroredProcedureAdi As String) As Object
        Dim gd As Object

        Using Baglanti As New SqlConnection(ConnectionString)
            Baglanti.Open()
            Using Komut As New SqlCommand
                Komut.Connection = Baglanti
                Komut.CommandText = StroredProcedureAdi
                Komut.CommandType = CommandType.StoredProcedure
                gd = Komut.ExecuteScalar()
            End Using
            Baglanti.Close()
        End Using

        Return gd
    End Function

    Public Function ExeCuteScalarAsSp(StroredProcedureAdi As String, ParametreAdi As String, ParametreDegeri As Object) As Object
        Dim gd As Object

        Using Baglanti As New SqlConnection(ConnectionString)
            Baglanti.Open()
            Using Komut As New SqlCommand
                Komut.Connection = Baglanti
                Komut.CommandText = StroredProcedureAdi
                Komut.CommandType = CommandType.StoredProcedure
                Komut.Parameters.AddWithValue(ParametreAdi, ParametreDegeri)
                gd = Komut.ExecuteScalar()
            End Using
            Baglanti.Close()
        End Using

        Return gd
    End Function

    Public Function ExeCuteScalarAsSp(StroredProcedureAdi As String, ParametreListesi As List(Of ISqlParameters)) As Object
        Dim gd As Object

        Using Baglanti As New SqlConnection(ConnectionString)
            Baglanti.Open()
            Using Komut As New SqlCommand
                Komut.Connection = Baglanti
                Komut.CommandText = StroredProcedureAdi
                Komut.CommandType = CommandType.StoredProcedure
                For Each Param In ParametreListesi
                    Komut.Parameters.AddWithValue(Param.Name, Param.Value)
                Next
                gd = Komut.ExecuteScalar()
            End Using
            Baglanti.Close()
        End Using

        Return gd
    End Function



End Class

Public Structure ISqlParameters
    ''' <summary>
    ''' Parametre Adı
    ''' </summary>
    ''' <remarks></remarks>
    Public Name As String
    ''' <summary>
    ''' Parametre Değeri
    ''' </summary>
    ''' <remarks></remarks>
    Public Value As Object
End Structure

Örnek Kullanım

'Tek parametreli execute
'VB.net
Dim sh As New SqlHandler
sh.ExeCuteNonQuery("UPDATE [TableHizmetler] SET [Silindi]=1 WHERE [IDno]=@IDno", "@IDno", IDno)


// Tek parametreli execute
//c#
SqlHandler sh = new SqlHandler();
sh.ExeCuteNonQuery("UPDATE [TableHizmetler] SET [Silindi]=1 WHERE [IDno]=@IDno", "@IDno", IDno);

'çok parametreli execute
'VB.net
Dim sh As New SqlHandler
Dim pList As New List(Of ISqlParameters) From {
    New ISqlParameters With {.Name = "@HizmetAdi", .Value = Adi},
    New ISqlParameters With {.Name = "@Resim", .Value = "/upload/" & ResimAdi},
    New ISqlParameters With {.Name = "@Aciklama", .Value = Metin}
    }

sh.ExeCuteNonQuery("INSERT INTO [dbo].[TableHizmetler] ([Silindi],[HizmetAdi],[Resim],[Aciklama]) VALUES (0,@HizmetAdi,@Resim,@Aciklama)", pList)




// çok parametreli execute
// c#
SqlHandler sh = new SqlHandler();

List<ISqlParameters> pList = new List<ISqlParameters>()
    {
        new ISqlParameters() { Name = "@HizmetAdi", Value = Adi },
        new ISqlParameters() { Name = "@Resim", Value = "/upload/" + ResimAdi },
        new ISqlParameters() { Name = "@Aciklama", Value = Metin }
    };

sh.ExeCuteNonQuery("INSERT INTO [dbo].[TableHizmetler] ([Silindi],[HizmetAdi],[Resim],[Aciklama]) VALUES (0,@HizmetAdi,@Resim,@Aciklama)", pList);