
<%@ CodeTemplate Language="C#" ClassName="TableInfoSql" TargetLanguage="C#" Debug="False" Description="" encoding="GB2312" %>
<%@ Property Name="databaseProjectName" Type="System.String" Default="SpSampleDb" Optional="True" Category="Context" Description="" %>
<%@ Property Name="nameSpace" Type="System.String" Default="sql" Optional="True" Category="Context" Description="" %>

/*******************************************************/
/*Create table */ 
/*******************************************************/
<%
DbEntity db = Template.GetDataBase(databaseProjectName);
foreach(Table tb in db.Tables)
{
	OpenResponse(databaseProjectName+"\\TableInfoSql\\"+tb.ModuleName+"\\"+tb.Name+".sql");
	string pkParmList = "";
	foreach(Field fd in tb.Fields)
	{
		if(fd.IsPK == true)
			pkParmList += fd.DataType.DotNetType+" "+fd.MemberName+",";
	}
	pkParmList = pkParmList.Trim(',');
	

	string tablename=tb.TableSpace.ToLower()  +tb.MemberName.ToLower();
	Response.WriteLine("create table " + tablename+" (" );
	
	int icount=0;
	foreach(Field fd in tb.Fields)
	{ 
	  
	    
	   if(icount>0){
	     Response.WriteLine(" ," );
	   }
	    string filename=fd.MemberName;
	  
	     Response.Write("   "+ filename+" "+ fd.DataType.FullName );
	    
	     if(fd.Identity.ToString()!=""){
	         Response.Write( " IDENTITY(1,1)  primary key" );
	     }else{
	     
	     if(fd.IsPK == true){
	        Response.Write( " primary key " );
	     }}
	    
	      if(fd.DefaultValue != ""){
	        Response.Write( " default '"+fd.DefaultValue+"' " );
	     }
	     if(fd.IsNullable  == true){
	        Response.Write( " null " );
	     }else{
	      Response.Write( " not null " );
	     }
	     
	    icount++;
	   
      
	} 
	Response.WriteLine("");
	%>
	);

---/* View*/
go
CREATE View V_<%=tablename%>  
as 
  select * from <%=tablename%>;
go	
	<% 
	 
	foreach(Field fd in tb.Fields)
	{ 
	  %>
	   execute sp_addextendedproperty 'MS_Description','<%=fd.CnName%>','user','dbo','table','<%=tablename%>','column','<%=fd.MemberName%>';   
	 <%
	   
      
	} 
	Response.WriteLine("");
	 
	%>
	
	
/*******************************************************/
/* tableinfo  */
/*******************************************************/
<% 

        string sqldelete="delete from cb_TableInfo where tablename='" +tablename+"';";
        Response.WriteLine(sqldelete);
	    string sql1="insert into cb_TableInfo (tablename,classname,pk,updatetime,module,remark,reserved,data,index_size,sumrows,unused,default1) values(";
	    Response.WriteLine(sql1);
	    Response.WriteLine("'"+tablename+"',");
	    Response.WriteLine("'"+tb.PropertyName+"',");
	    Response.Write("'");
        foreach(Field fd in tb.Fields)
	   { 
	     if( fd.IsPK ==true )
	     {
	      Response.Write( fd.MemberName );
	     }
	    }
	    Response.WriteLine("\',");
        Response.WriteLine("\'" +DateTime.Now.ToString()+ "\',");
        Response.WriteLine("\'" + tb.ModuleName+ "\',");
        Response.WriteLine("\'" + tb.CnName+ "\',");
       
        Response.WriteLine("\'0\',");
        Response.WriteLine("\'0\',");
        Response.WriteLine("\'0\',");
        Response.WriteLine("\'0\',");
        Response.WriteLine("\'0\',");
       
        Response.WriteLine("\'\'");
            
        Response.WriteLine(");");
       
	 
%>

	<%
	sqldelete="delete from cb_TableField where tablename='"+tablename+"';";
    Response.WriteLine(sqldelete);
	foreach(Field fd in tb.Fields)
	{ 
	  
			
	    string sql ="insert into cb_TableField  (tablename,fieldname,datatype,fieldlength,fieldidentity,fieldkey,notnullable,defaults,remark) values(";
        Response.WriteLine(sql);
        Response.WriteLine("'"+tablename +"',");
        Response.WriteLine("\'" + fd.MemberName+ "\',");
        string datatype=fd.DataType.FullName.ToLower();
        int index = datatype.IndexOf("(");
       if (index > 1) { 
           int endindex=datatype.IndexOf(")");
          
           string length = datatype.Substring(index+1, endindex - index-1);
           datatype = datatype.Substring(0,index);
           Response.WriteLine("\'" + datatype+ "\',");
           Response.WriteLine("\'"+length+"\',");
        }else{
           Response.WriteLine("\'" + datatype+ "\',");
           Response.WriteLine("\'\',");
        }
        if(fd.Identity.ToString()=="(1,1)"){
           Response.WriteLine("\'1\',");
        }else{
          Response.WriteLine("\'0\',");
        }
       if( fd.IsPK ==false )
	   {
	     Response.WriteLine("\'0\',");
	   }
	   else{
	     Response.WriteLine("\'1\',");
	   }
	    Response.WriteLine("\'" + !fd.IsNullable  + "\',");
	    Response.WriteLine("\'\',");
	    string remark=fd.CnName;
	    if(remark==null|| remark==""){
	       remark=fd.MemberName;
	    }
	    Response.WriteLine("\'" + remark+ "\'");
        Response.WriteLine(");");
       
	}
%>


<%
	CloseResponse();
}
%>