Ograničavanje broja konekcija na SQL Server

Više puta mi se pojavila potreba da kontrolišem broj konekcija iz aplikacije na bazu podataka, samim tim i da ograničim pristup u odredjenim situacijama.

Na primer, ukoliko pravite program koji registrujete na odredjeni/dozvoljni broj konekcija, ili jednostavno prateći poslovnu logiku želite da zabranite korisniku da bude prijavljen na više mesta, ovo je rešenje koje možete da iskoristite.

SELECT  sistem.loginame,
        sistem.hostname,
		sistem.program_name
FROM    master..sysprocesses AS sistem
WHERE	sistem.hostname <> ''

Ovo će nam dati sledeći rezultat

Ovim upitom vidimo aktivne konekcije na bazu podataka/server, što nam daje neke potrebne informacije za početak, ali definitivno nedovoljne da definišemo i ograničimo pristup po potrebama.

Kao što vidite na rezultatu upita, pročitali smo

  1. LoginName
  2. HostName
  3. ProgramName

E, sad, ono što je jedan od najbitnijih podataka u celoj ovoj priči je, koja je to aplikacija otvorila konekciju? To možemo da vidimo iz kolone “program_name”.
Naravno, da bismo mogli da razgraničimo šta je “naša” konekcija, a šta je neki sistemski proces, treba nam naziv programa koji je “otvorio” konekciju. Iz rezultata primera vidimo da dve konekcije “drži” MSSQL, i treća je otvorena od programa “GHC”.

Kako definisati naziv programa u konekciji? Lako!
Jednostavno je potrebno dodeliti property na kreiranju ConnectionString-a kao u primeru:

private string CreateConnectionString(string server, string database, string username, string password)
{
        SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder();
        cb.DataSource = server;
        cb.InitialCatalog = database;
        cb.MultipleActiveResultSets = true;
        cb.UserID = username;
        cb.Password = password;
        cb.Pooling = true;
        // Vrednost naziva ne sme biti NULL, pa obratite paznju ako ga prosledjujete kao parametar
        cb.ApplicationName = System.Reflection.Assembly.GetEntryAssembly().GetName().Name;
        return cb.ToString();
}
U primeru smo iskoristili Reflection da dobijemo ime programa, vi ga možete proslediti metodi kao dodatni parametar, ali vodite računa da nije NULL, jer ćete dobiti Exception za to, ApplicationName property ne može biti NULL!

Dobro, sada imamo naziv programa, server login i ime računara sa koga je konekcija kreirana, i dalje nedovoljno…

Ono što fali je da znamo da je to naš korisnik.

Mi smo to rešavali na sledeći način. Tačnije, logika po kojoj smo uvek pravili security na aplikaciji nam se poklopila sa ovakvim zahtevima.

Naime, ono što je važno je da naša baza korisnike “mapira” na server login-e, što u praksi znači da će za svakog korisnika aplikacije, biti kreiran Sql Server Login sa istim username-om.

Ne zbog ovog ograničavanja, to dalje širi priči i otvara dosta dobrih mogućnosti kasnije, kao npr. kreiranje rola na bazi i automatsko dodeljivanje user-a roli, samim tim postavlljanje predefinisanog seta privilegija na tabele, procedure, itd…

Dakle, kada smo ustanovili da imamo korisnike u našoj bazi paralelno sa SQL Server login-ima, ostaje nam da malo promenimo upit da nam vrati podatke o našim korisnicima.

Pretpostavimo da je bazi postoji tabela “User”, koja u sebi, izmedju ostalog, ima i kolonu “Username”, koju koristimo za identifikaciju korisnika svakako. Vrednost kolone “Username” odgovara SQL Server login-u sa istim “LoginName”-om. Sada upit izgleda ovako:

SELECT  u.FirstName,
        u.LastName,
        sistem.loginame,
        sistem.hostname,
		sistem.program_name
FROM    master..sysprocesses AS sistem INNER JOIN
        [User] as u ON u.Username COLLATE DATABASE_DEFAULT = sistem.loginame COLLATE DATABASE_DEFAULT
WHERE   hostname <> ''

I rezultat je ono što nam treba:

I za kraj još jedna mala napomena i objašnjenje.

Obratite pažnju na COLLATION servera i baze podataka. Ovim upitom su JOIN-ovane tabele iz “master” baze i vaše baze, čiji COLLATION-i mogu biti različiti, što će dovesti do ozbiljnih problema pri pravljenju upita koji u sebi imaju npr. WHERE klauzulu, jer server neće moći da uporedi string-ove na različitim COLLATION-ima.

Zato koristimo COLLATE DATABASE_DEFAULT u upitu, što rešava ove probleme i dozvoljava da se upit izvrši iako kolacije nisu iste.
Pogledajte blog Pinal Dave-a za više informacija o problemu sa COLLATION-ima (i svemu ostalom vezano za SQL Server), da ja ne pričam ispričane priče.

Ovako ćete u svakom momentu imati mogućnost da proverite koliko konekcija je otvoreno ka serveru i od strane kojih korisnika, ostaje Vam da pustite mašti na volju, da napravite npr. neku lepu formu koja će korisnika nakon prijave obavestiti da je negde već prijavljen ili da je broj dozvoljenih konekcija prekoračen i sl.

Advertisements

Kreiranje objekta sa RowMapper-om

Ideja je da kreiramo jedostavan, precizan i uniforman način za mapiranje/čitanje objekata iz baze, a da pritom ne koristimo gotove framework-e.

Idealan način, bar za mene, su RowMapper klase.

Ovo nije ništa novo, RowMapper-i se koriste u Javi kao podrazumevani, bar koliko sam ja uspeo da primetim.

Ono sto mi nije jasno, zašto ih ja nisam koristio ranije? Definitivno skraćuju vreme pisanja, osiguravaju precizan kod, klase su male i fokusirane, baš sve kako treba.

Za početak nam treba interface koji ćemo koristiti kako bismo “predali” određeni RowMapper klasi za čitanje podataka iz baze.

using System.Data;

namespace GHC.Data
{
    /// <summary>
    /// Map data reader values to a new instance of type T
    /// </summary>
    public interface IRowMapper
    {
        T Map(IDataRecord record);
    }
}

Konkretne stvari

Da bih najbolje objasnio o čemu se radi, uzeću za primer objekat City. Dakle, cilj je jasan, čitamo podatke o gradovima iz baze.

Evo ga model:

using System;

namespace GHC.Models
{
    public class City
    {
        public Guid ID { get; set; }
        public string Name { get; set; }
        public string PostalCode { get; set; }
        public override string ToString()
        {
            return this.PostalCode + " " + this.Name;
        }
    }
}

Za svaki model ćemo kreirati odgovarajuću RowMapper klasu koja će implementirati prethodno kreirani IRowMapper interfejs.

Možda Vam izgleda kao preterano, ali taj kod svakako pišete, samo što ga u ovom slučaju držimo u posebnim klasama. Da ponovim, zadržaćemo male, fokusirane klase, automatski smanjujemo mogućnost greške, “gubljenja” po kodu i pisanje čuvenog “špageti koda”

Ovako izgleda RowMapper klasa za model City:

using GHC.Data;
using GHC.Models;

namespace GHC.RowMappers
{
    public class CityRowMapper : IRowMapper
    {
        public City Map(System.Data.IDataRecord record)
        {
            return new City()
            {
                ID = record.GetGuid(0),
                Name = record["Name"].ToString(),
                PostalCode = record["PostalCode"].ToString(),
                State = new State() { ID = record.GetGuid(3) }
            };
        }
    }
}

Ono što se vidi i što stvari pojednostavljuje, je to sto implementacija IRowMapper-a po definiciji vraća zadati tip, tako da kasnije nema nikakvih dodatnih konvertovanja i suvišnog koda.

Connector – a gde su ti podaci?

Došli smo do još konkretnijih stvari.

Kreiramo klasu Connector koja će nam služiti za povezivanje sa bazom i vraćanje podataka preko RowMapper-a

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;

namespace GHC.Data
{
    public class Connector
    {
		private string _connectionString;

		public string Server { get; set; }
		public string Database { get; set; }
		public string Username { get; set; }
		public string Password { get; set; }

		public string ConnectionString
		{
			get
			{
				if (string.IsNullOrEmpty(_connectionString))
				{
					_connectionString = CreateConnectionString(this.Server, this.Database, this.Username, this.Password, null, 0);
				}

				return _connectionString;
			}
		}

		private string CreateConnectionString(string server, string database, string username, string password, int connectionTimeout)
		{
			SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder();
			cb.DataSource = server;
			cb.InitialCatalog = database;
			cb.MultipleActiveResultSets = true;
			cb.UserID = username;
			cb.Password = password;
			cb.Pooling = true;

			if (connectionTimeout >= 0)
			{
				cb.ConnectTimeout = connectionTimeout;
			}

			return cb.ToString();
		}

		///<summary>
		/// Execute reader and return first instance of object created with IRowMapper type to instantiate
        	/// </summary>
		/// Sql Query to retreive object from database
	        /// IRowMapper used to map object type from reader
        	/// List of T
        	public T Get<T>(string query, IRowMapper rowMapper)
        	{
            		object obj = null;

			using (SqlConnection Connection = new SqlConnection(ConnectionString))
			{
				try
				{
					Connection.Open();

					using (SqlCommand command = Connection.CreateCommand())
					{
						command.CommandText = query;

						SqlDataReader reader = command.ExecuteReader();

						e.Reader = reader;

						reader.Read();

						if (reader.HasRows)
						{
							obj = rowMapper.Map(reader);
						}

						reader.Close();
					}
				}
				catch (SqlException ex)
				{
					throw ex;
				}
				catch(Exception)
				{
					throw;
				}
			}

            		return (T)obj;
        	}

		///<summary>
		/// Execute reader and create list of provided type using IRowMapper interface
		/// </summary>
		/// Type of object to create
		/// Sql Query
		/// IRowMapper to map provided object
		/// List of provided object type
		public List<T> GetList<T>(string query, IRowMapper<T> rowMapper)
		{
			List list = new List();

			using (SqlConnection Connection = new SqlConnection(ConnectionString))
			{
				try
				{
					Connection.Open();

					using (SqlCommand command = Connection.CreateCommand())
					{
						command.CommandText = query;

						SqlDataReader reader = command.ExecuteReader();

						while (reader.Read())
						{
							list.Add((T)rowMapper.Map(reader));
						}

						reader.Close();
					}
				}
				catch (SqlException ex)
				{
					throw ex;
				}
				catch(Exception)
				{
					throw;
				}
			}

			return list;
		}
	}
}

U klasi Connector nema ništa komplikovano. To je ConnectionString koji nam svakako treba, koristimo DataReader jer je neuporedivo brži od popunjavanja npr. DataSet-ova, i mapiramo dati rezultat na neki RowMapper.
Ovde vidimo dve metode, jednu koja vraća jednu instancu datog tipa, i drugu koja vraca listu datih tipova.

public T Get<T>(string query, IRowMapper<T> rowMapper)
public List<T> GetList<T>(string query, IRowMapper<T> rowMapper)

Praktično, prva metoda će “direktno” vratiti zadati tip T, dok će druga vratiti listu tipa T, šta nam više treba u životu?

A evo i kako.

Podrazumevam malo lepše pisanje upita, ali za potrebe primera, biće sasvim dovoljni.

using System.Collections.Generic;
using GHC.Models;
using GHC.RowMappers;

namespace GHC.Services
{
    public class CityService
    {
        private Connector Connector
        {
            get
            {
                // Kreiranje Connector klase cete izdvojiti negde, naravno, ispisao sam ga ovde, tek toliko da imamo neki kompletan kod
                Connector connector = new Connector();
                connector.Server = "server";
                connector.Database = "database";
                connector.Username = "username";
                connector.Password = "password";

                return connector;
            }
        }

        public City GetCity()
        {
            return Connector.Get<City>("SELECT ID, Name, PostalCode FROM City WHERE CityID='00000000-0000-0000-0000-000000000001'", new CityRowMapper());
        }

        public List<City> GetCities()
        {
            return Connector.GetList<City>("SELECT ID, Name, PostalCode FROM City", new CityRowMapper());
        }
    }
}

I eto, sad možete da zaboravite na nepotrebna prepakivanja SQL result set-ova na modele gde zatrebaju.
Sa ovako kreiranim RowMapper-om za model, lista ili objekat je kasnije popunjen u jednoj liniji koda.