T4 Template For Generate Deploy Manage Access

<#@ template language="C#" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="System.Data.DataSetExtensions" #>
<#@ assembly name="System.Xml" #>
<#@ assembly name="System.Configuration" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Xml" #>
<#@ import namespace="System.Data.Common" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Text.RegularExpressions" #>
<#@ import namespace="System.Configuration"#>
<#@ import namespace="System.Globalization"#>
<#@ output extension="sql" #>
<#

const string ConnectionString = @"Data Source=10.70.215.115; Initial Catalog=GPL; User Id=CPFB_User; Password=admin1234; MultipleActiveResultSets=True";

const string CommonNamespace = "PortalProj.Gpl.Common";
const string ContextNamespace = "PortalProj.Gpl.Data";
const string PocoNamespace = "PortalProj.Gpl.Entities";

const string DbContext = "GplDbContext";
const string DbContextVariable = "gplDB";

// That's it, nothing else to configure ***********************************************************************************************

string tableName = "";
string modelName="";
string lowermodelName="";

DAManager DaManager=new DAManager(ConnectionString);
DataTable allTables= DaManager.LoadTables();
DataTable table=null;
List<string> Storelist= DaManager.LoadStoreProcdure();
string modelScheme="";
string StoreName="";
DataColumn[] primaryKeys = null;
List<string> oldField = new List<string>() { "VERSION_NO"};
#>
 
USE GPL

   <#foreach (DataRow dataRow in allTables.Rows)
	{ 
		tableName=dataRow["TABLE_NAME"].ToString();
		table= DaManager.GetScheme(tableName);
		modelName=DaManager.GetModelName(tableName);
		modelScheme=modelName+"Schema";
		lowermodelName=modelName.ToLower();
		primaryKeys = table.PrimaryKey;
    #>
		GRANT SELECT, INSERT,Update ON [dbo].<#=tableName #> TO  [GplAdminApp];				
	<#}#>

	<#foreach (var item in Storelist)
	{ 
		StoreName=item;
    #>		
		GRANT EXECUTE ON [dbo].<#=StoreName #> TO  [GplAdminApp];				
	<#}#>


	<#foreach (DataRow dataRow in allTables.Rows)
	{ 
		tableName=dataRow["TABLE_NAME"].ToString();
		table= DaManager.GetScheme(tableName);
		modelName=DaManager.GetModelName(tableName);
		modelScheme=modelName+"Schema";
		lowermodelName=modelName.ToLower();
		primaryKeys = table.PrimaryKey;
    #>	
		GRANT SELECT, INSERT,Update ON [dbo].<#=tableName #> TO [GplApp];	
	<#}#>
	<#foreach (var item in Storelist)
	{ 
		StoreName=item;
    #>				
		GRANT EXECUTE ON [dbo].<#=StoreName #> TO  [GplApp];	
	<#}#>


<#+ public class DAManager
	{
		
		private string _connectionString = "";
		private string _providerName = "";
		private string _configFilePath = "";

		public DAManager(string connectionString)
        {
			_connectionString = connectionString;
		}

		public DataTable GetScheme(string tableName)
		{
			//_connectionString = GetConnectionString();
			using (SqlConnection conn = new SqlConnection(_connectionString))
			{
				SqlCommand comm = new SqlCommand();
				comm.CommandText = string.Format("select * from {0} where 1<>1",tableName);
				comm.Connection = conn;
				SqlDataAdapter da = new SqlDataAdapter(comm);
				DataSet ds = new DataSet();
				DataTable[] dt=da.FillSchema(ds, SchemaType.Mapped);
				return dt[0];
				conn.Close();
			}
		}

		public DataTable LoadTables()
		{
			//_connectionString = GetConnectionString();
			using (SqlConnection conn = new SqlConnection(_connectionString))
			{
				conn.Open();
				DataTable dtAllTables = conn.GetSchema("Tables");
				conn.Close();
				return dtAllTables;
			}
		}

		public List<string> LoadStoreProcdure()
		{
			using (SqlConnection conn = new SqlConnection(_connectionString))
			{
				conn.Open();
				List<string> list=new List<string>();
				SqlCommand comm = new SqlCommand();
				comm.CommandText = "select name from sysobjects where type='P'";
				comm.Connection = conn;
				SqlDataReader reader = comm.ExecuteReader();
				while (reader.Read())
				{
					for (int i = 0; i < reader.FieldCount; i++)
					{
						string s=reader.GetValue(i).ToString();
						list.Add(s);
					}					
				}							
				conn.Close();
				return list;
			}
		}

		public string GetModelName(string tableName)
		{
			tableName = tableName.ToLower();
			tableName = Regex.Replace(tableName, "^t_", string.Empty);
			return System.Globalization.CultureInfo.CurrentCulture.TextInfo.ToTitleCase(tableName).Replace("_", string.Empty);
		}
	}
#>