Genera Singleton

Declare @Proyecto varchar(50)
Declare @Entity varchar(50)
Declare @Clase varchar(50)
Declare @Capa varchar(50)

Set @Proyecto='Kaizen'
Set @Entity='Empleado'
Set @Clase='Connection'
Set @Capa='Data'

Declare @NameSpace varchar(250);
Declare @i int;

Set @NameSpace='WebService';

Set @i=1;

print 'using System;';
print 'using System.Collections.Generic;'
print 'using System.Data.SqlClient;'
print 'using System.Linq;'
print 'using System.Text;'
print 'using System.Threading.Tasks;'
print ''
print 'namespace ' + @NameSpace + '.' + @Capa + '.' + @Entity;
print '{'
print '    public class ' + @Clase
print '    {'
print '        private SqlConnection sqlConnection = null;'
print '        private SqlCommand sqlCommand = null;'
print '        private SqlDataReader sqlReader = null;'
print '        private object sqlScalar = null;'
print '        private static ' + @Clase + ' instance = null;'
print ''       
print '        public static ' + @Clase + ' Instance'
print '        {'
print '            get'
print '            {'
print '                if (instance == null)'
print '                {'
print '                    instance = new ' + @Clase + '();'
print '                }'
print '                else'
print '                {'
print '                    if (instance.sqlConnection.State.Equals(System.Data.ConnectionState.Closed))'
print '                    {'
print '                        instance.sqlConnection.Open();'
print '                    }'
print '                }'
print '                return instance;'
print '            }'
print '        }'
print ''
print '        public ' + @Clase + '()'
print '        {'
print '            if (sqlConnection == null)'
print '            {'
print '                sqlConnection = new SqlConnection();'
print '                sqlConnection.ConnectionString = ConnectionString();'
print '                sqlConnection.Open();'
print '            }'
print '            else'
print '            {'
print '                if (sqlConnection.State.Equals(System.Data.ConnectionState.Closed))'
print '                {'
print '                    sqlConnection.Open();'
print '                }'
print '            }'
print '        }'
print ''
print '        private string ConnectionString()'
print '        {'
print '            string connectionstring = string.Empty;'
print '            connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["Integra.Services.Data.QProcess"].ToString();'
print '            return connectionstring;'
print '        }'
print ''
print '        public SqlDataReader ExcuteSP(string nameProcedure, List<SqlParameter> parameters)'
print '        {'
print '            sqlCommand = new SqlCommand();'
print '            sqlCommand.Connection = this.sqlConnection;'
print '            sqlCommand.CommandTimeout = 36000;//3600;'
print '            sqlCommand.CommandText = nameProcedure;'
print ''
print '            if (parameters != null && parameters.Count > 0)'
print '            {'
print '                sqlCommand.Parameters.AddRange(parameters.ToArray());'
print '            }'
print ''
print '            sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;'
print '            sqlReader = sqlCommand.ExecuteReader();'
print '            return sqlReader;'
print '        }        '
print ''
print '        public object ExcuteScalar(string nameProcedure, List<SqlParameter> parameters)'
print '        {'
print '            sqlCommand = new SqlCommand();'
print '            sqlCommand.Connection = this.sqlConnection;'
print '            sqlCommand.CommandTimeout = 3600;'
print '            sqlCommand.CommandText = nameProcedure;'
print ''
print '            if (parameters != null && parameters.Count > 0)'
print '            {'
print '                sqlCommand.Parameters.AddRange(parameters.ToArray());'
print '            }'
print '            sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;'
print '            sqlScalar = sqlCommand.ExecuteScalar();'
print ''
print '            return sqlScalar;'
print '        }'
print '    }'
print '}'
print ''
print ''
print '////Colocar esto al inicio de cada Data para usar la clase connection'
print '//List<Entities.'+ @Proyecto+'.'+@Entity+'> ' + lower(@Entity)+'Collection = null;'
print '//Entities.'+ @Proyecto+'.'+@Entity+' ' + lower(@Entity)+' = null;'
print '//SqlDataReader dataReader = null;'
print '//'
print '//public ' + @Clase + ' connection = null;'
print '//'
print '//public '+ @Entity +'()'
print '//{'
print '//    this.connection = ' + @Clase + '.Instance;'
print '//}'
print '//'
print '//public '+ @Entity +'(bool isTransaction)'
print '//{'
print '//    this.connection = new ' + @Clase + '();'
print '//}'
print '//'
print '//public '+ @Entity +'(' + @Clase + ' connection)'
print '//{'
print '//    this.connection = connection;'
print '//}'