using System.Collections.Generic;

namespace EF_Sample08.DomainClasses
{
    public class Doctor
    {
        public int Id { set; get; }
        public string Name { set; get; }

        public virtual ICollection<Patient> Patients { set; get; }
    }
}

namespace EF_Sample08.DomainClasses
{
    public class Patient
    {
        public int Id { set; get; }
        public string Name { set; get; }

        public virtual Doctor Doctor { set; get; }
    }
}

using System.Data.Entity;
using EF_Sample08.DomainClasses;

namespace EF_Sample08.DataLayer.Context
{
    public class Sample08Context : DbContext
    {
        public DbSet<Doctor> Doctors { set; get; }
        public DbSet<Patient> Patients { set; get; }
    }
}

using System.Data.Entity.Migrations;
using EF_Sample08.DomainClasses;
using System.Collections.Generic;

namespace EF_Sample08.DataLayer.Context
{
    public class Configuration : DbMigrationsConfiguration<Sample08Context>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
            AutomaticMigrationDataLossAllowed = true;
        }

        protected override void Seed(Sample08Context context)
        {
            addData(context);
            addSP(context);
            addFn(context);
            base.Seed(context);
        }

        private static void addData(Sample08Context context)
        {
            var patient1 = new Patient { Name = "p1" };
            var patient2 = new Patient { Name = "p2" };
            var doctor1 = new Doctor { Name = "doc1", Patients = new List<Patient> { patient1, patient2 } };
            context.Doctors.Add(doctor1);
        }

        private static void addFn(Sample08Context context)
        {
            context.Database.ExecuteSqlCommand(
                @"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FindDoctorPatientsCount]') 
                   AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
                      DROP FUNCTION [dbo].[FindDoctorPatientsCount]");
            context.Database.ExecuteSqlCommand(
                @"CREATE FUNCTION FindDoctorPatientsCount(@Doctor_Id INT)
                    RETURNS INT
                    BEGIN
                     RETURN 
                     (
                         SELECT COUNT(*)
                         FROM   Patients
                         WHERE  Doctor_Id = @Doctor_Id
                     );
                    END");
        }

        private static void addSP(Sample08Context context)
        {
            context.Database.ExecuteSqlCommand(
                @"IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FindDoctorsStartWith]')
                  AND type in (N'P', N'PC'))
                       DROP PROCEDURE [dbo].[FindDoctorsStartWith]
                 ");
            context.Database.ExecuteSqlCommand(
                @"CREATE PROCEDURE FindDoctorsStartWith(@name NVARCHAR(400))
                  AS
                 SELECT *
                 FROM   Doctors
                 WHERE  [Name] LIKE @name + '%'");
        }
    }
}

using System;
using System.Data;
using System.Data.Entity;
using System.Data.Objects.SqlClient;
using System.Data.SqlClient;
using System.Linq;
using EF_Sample08.DataLayer.Context;
using EF_Sample08.DomainClasses;

namespace EF_Sample08
{
    class Program
    {
        static void Main(string[] args)
        {
            Database.SetInitializer(new MigrateDatabaseToLatestVersion<Sample08Context, Configuration>());

            using (var db = new Sample08Context())
            {
                runSp(db);
                runFn(db);
                usingSqlFunctions(db);
            }
        }

        private static void usingSqlFunctions(Sample08Context db)
        {
            var doctorsWithNumericNameList = db.Doctors.Where(x => SqlFunctions.IsNumeric(x.Name) == 1).ToList();
            if (doctorsWithNumericNameList.Any())
            {
                //do something
            }
        }

        private static void runFn(Sample08Context db)
        {
            var doctorIdParameter = new SqlParameter
            {
                ParameterName = "@doctor_id",
                Value = 1,
                SqlDbType = SqlDbType.Int
            };
            var patientsCount = db.Database.SqlQuery<int>("select dbo.FindDoctorPatientsCount(@doctor_id)", doctorIdParameter).FirstOrDefault();
            Console.WriteLine(patientsCount);
        }

        private static void runSp(Sample08Context db)
        {
            var nameParameter = new SqlParameter
            {
                ParameterName = "@name",
                Value = "doc",
                Direction = ParameterDirection.Input,
                SqlDbType = SqlDbType.NVarChar
            };
            var doctors = db.Database.SqlQuery<Doctor>("exec FindDoctorsStartWith @name", nameParameter).ToList();
            if (doctors.Any())
            {
                foreach (var item in doctors)
                {
                    Console.WriteLine(item.Name);
                }
            }
        }
    }
}

db.Database.SqlQuery<Doctor>("exec FindDoctorsStartWith "+ txtName.Text, nameParameter).ToList()

DECLARE @DriveBenchmark INT
DECLARE @MachineName NVARCHAR(1000)
DECLARE @DiskFreeSpace INT
DECLARE @DriveLetter CHAR(1)
DECLARE @AlertMessage NVARCHAR(MAX)
DECLARE @MailSubject NVARCHAR(MAX)
DECLARE @NewLine CHAR(2)

SET @NewLine = CHAR(13) + CHAR(10)

SET @DriveBenchmark = 2048 -- 2GB
SET @MailSubject = 'Free space is low on ' + @@SERVERNAME
SET @AlertMessage = ''

IF EXISTS (
     SELECT *
     FROM   tempdb..sysobjects
     WHERE  id = OBJECT_ID(N'[tempdb]..[#disk_free_space]')
 )
  DROP TABLE #disk_free_space
 
CREATE TABLE #disk_free_space
(
 DriveLetter  CHAR(1) NOT NULL,
 FreeMB       INTEGER NOT NULL
)
 
/* Populate #disk_free_space with data */
INSERT INTO #disk_free_space
EXEC MASTER..xp_fixeddrives
 
 
DECLARE DriveSpace CURSOR FAST_FORWARD
FOR
  SELECT DriveLetter,
         FreeMB
  FROM   #disk_free_space

 OPEN DriveSpace
 FETCH NEXT FROM DriveSpace INTO @DriveLetter, @DiskFreeSpace
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
  IF @DiskFreeSpace < @DriveBenchmark
  BEGIN
      SET @AlertMessage = @AlertMessage + 'Drive ' + @DriveLetter + ' on ' + @@SERVERNAME
          + ' has only ' + CAST(@DiskFreeSpace AS VARCHAR) + ' MB left.' + @NewLine
  END

  FETCH NEXT FROM DriveSpace INTO @DriveLetter, @DiskFreeSpace
END
 CLOSE DriveSpace 
 DEALLOCATE DriveSpace
  
 DROP TABLE #disk_free_space

IF @AlertMessage <> ''
BEGIN
  EXECUTE msdb.dbo.sp_send_dbmail
  @recipients = 'nasiri@site.net', -- Change This
  @copy_recipients = 'Administrator@site.net', -- Change This
  @Subject = @MailSubject,
  @Body = @AlertMessage
  ,@importance = 'High'
END

using FluentNHibernate;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHSample1.Mappings;

namespace ConsoleTestApplication
{
  class CDb
  {
      public static void CreateDb(IPersistenceConfigurer dbType)
      {
          var cfg = Fluently.Configure().Database(dbType);

          PersistenceModel pm = new PersistenceModel();
          pm.AddMappingsFromAssembly(typeof(CustomerMapping).Assembly);
          var sessionSource = new SessionSource(
                              cfg.BuildConfiguration().Properties,
                              pm);

          var session = sessionSource.CreateSession();
          sessionSource.BuildSchema(session, true);
      }
  }
}

CDb.CreateDb(
               MsSqlConfiguration
                  .MsSql2008
                  .ConnectionString("Data Source=(local);Initial Catalog=HelloNHibernate;Integrated Security = true")
                  .ShowSql());

using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate;
using NHSample1.Mappings;

namespace ConsoleTestApplication
{
  class Config
  {
      public static ISessionFactory CreateSessionFactory(IPersistenceConfigurer dbType)
      {
          return
             Fluently.Configure().Database(dbType
              ).Mappings(m => m.FluentMappings.AddFromAssembly(typeof(CustomerMapping).Assembly))
               .BuildSessionFactory();
      }
  }
}

using System;
using NHibernate;
using NHSample1.Domain;

namespace ConsoleTestApplication
{
  class CDbOperations
  {
      ISessionFactory _factory;

      public CDbOperations(ISessionFactory factory)
      {
          _factory = factory;
      }

      public int AddNewCustomer()
      {
          using (ISession session = _factory.OpenSession())
          {
              using (ITransaction transaction = session.BeginTransaction())
              {
                  Customer vahid = new Customer()
                  {
                      FirstName = "Vahid",
                      LastName = "Nasiri",
                      AddressLine1 = "Addr1",
                      AddressLine2 = "Addr2",
                      PostalCode = "1234",
                      City = "Tehran",
                      CountryCode = "IR"
                  };

                  Console.WriteLine("Saving a customer...");

                  session.Save(vahid);
                  session.Flush();//چندین عملیات با هم و بعد

                  transaction.Commit();

                  return vahid.Id;
              }
          }
      }

      public void DeleteCustomer(int id)
      {
          using (ISession session = _factory.OpenSession())
          {
              using (ITransaction transaction = session.BeginTransaction())
              {
                  Customer customer = session.Load<Customer>(id);
                  Console.WriteLine("Id:{0}, Name: {1}", customer.Id, customer.FirstName);

                  Console.WriteLine("Deleting a customer...");
                  session.Delete(customer);

                  session.Flush();//چندین عملیات با هم و بعد

                  transaction.Commit();
              }
          }
      }
  }
}

using System;
using FluentNHibernate.Cfg.Db;
using NHibernate;
using NHSample1.Domain;

namespace ConsoleTestApplication
{
  class Program
  {
      static void Main(string[] args)
      {
          //CDb.CreateDb(SQLiteConfiguration.Standard.ConnectionString("data source=sample.sqlite").ShowSql());          
          //return;

          //todo: Read ConnectionString from app.config or web.config
          using (ISessionFactory session = Config.CreateSessionFactory(
                MsSqlConfiguration
                   .MsSql2008
                   .ConnectionString("Data Source=(local);Initial Catalog=HelloNHibernate;Integrated Security = true")
                   .ShowSql()
              ))
          {
              CDbOperations db = new CDbOperations(session);
              int id = db.AddNewCustomer();
              Console.WriteLine("Loading a customer and delete it...");
              db.DeleteCustomer(id);
          }

          Console.WriteLine("Press a key...");
          Console.ReadKey();
      }
  }
}

Saving a customer...
NHibernate: select next_hi from hibernate_unique_key with (updlock, rowlock)
NHibernate: update hibernate_unique_key set next_hi = @p0 where next_hi = @p1;@p0 = 17, @p1 = 16
NHibernate: INSERT INTO [Customer] (FirstName, LastName, AddressLine1, AddressLine2, PostalCode, City, CountryCode, Id) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7);@p0 = 'Vahid', @p1 = 'Nasiri', @p2 = 'Addr1', @p3 = 'Addr2', @p4 = '1234', @p5 = 'Tehran', @p6 = 'IR', @p7 = 16016
Loading a customer and delete it...
NHibernate: SELECT customer0_.Id as Id2_0_, customer0_.FirstName as FirstName2_0_, customer0_.LastName as LastName2_0_, customer0_.AddressLine1 as AddressL4_2_0_, customer0_.AddressLine2 as AddressL5_2_0_, customer0_.PostalCode as PostalCode2_0_, customer0_.City as City2_0_, customer0_.CountryCode as CountryC8_2_0_ FROM [Customer] customer0_ WHERE customer0_.Id=@p0;@p0 = 16016
Id:16016, Name: Vahid
Deleting a customer...
NHibernate: DELETE FROM [Customer] WHERE Id = @p0;@p0 = 16016
Press a key...

CDb.CreateDb(SQLiteConfiguration.Standard.ConnectionString("data source=sample.sqlite").ShowSql());

          //todo: Read ConnectionString from app.config or web.config
          using (ISessionFactory session = Config.CreateSessionFactory(
                     SQLiteConfiguration.Standard.ConnectionString("data source=sample.sqlite").ShowSql()
              ))
          {
 ...