DBLayer Wizard V 1.0

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
using System.Collections;
using System.Windows.Forms;
using System.Text;

namespace DBLayer.DBLayerEngine
{
	/// <summary>
	/// Summary description for DBSPCreator.
	/// </summary>
	public class DBSPCreator
	{

		ServerInfo _ServerInfo;
		string _DBName;

		public DBSPCreator(ServerInfo serverinfo,string DBName)
		{
			_ServerInfo=serverinfo;
			_DBName=DBName;
		}
		#region Meta Data Functions
		private ArrayList GetSPParameters(string SPName)
		{
			ArrayList result=new ArrayList(0);
			SqlConnection connection=new SqlConnection(Helper.GenerateConnectionString(_ServerInfo,_DBName));
			SqlCommand command=new SqlCommand(SPName,connection);
			command.CommandType=CommandType.StoredProcedure;
			connection.Open();
			SqlCommandBuilder.DeriveParameters(command);
			connection.Close();

			for(int i=0;i<command.Parameters.Count;i++)
			{
				SqlParameter OldParam=command.Parameters[i];
				//TODO:Fix IsNullable
				SqlParameter NewParam=new SqlParameter(OldParam.ParameterName,OldParam.SqlDbType,OldParam.Size,OldParam.Direction,false,OldParam.Precision,OldParam.Scale,OldParam.SourceColumn,OldParam.SourceVersion,OldParam.Value);
				result.Add(NewParam);
			}
			return result;
		}
		#endregion

		private string Header()
		{
			string result="using System;\n"+
"using System.Data;\n"+
"using System.Data.SqlClient;\n"+
"using System.IO;\n"+
"using System.Collections;\n"+

"namespace DBLayer\n"+
"{\n"+
	"/// <summary>\n"+
	"/// Summary description for DBSP.\n"+
	"/// </summary>\n"+
	"public class DBSP:DB\n"+
	"{\n"+
		"private DBSP()\n"+
		"{\n"+
			
		"}\n";
			return result;
		}
		private string Footer()
		{
			string result="\n}\n}";
			return result;
		}

		public string GetSPClass(ArrayList SPNames,bool genSPDataSet,bool genSPDataReader,bool genSPReturnValue,bool genSPDataSetwithReturnValue)
		{
			StringBuilder result=new StringBuilder();
			result.Append(Header());
			for(int i=0;i<SPNames.Count;i++)
			{
				result.Append(CreateProcedureMethods(SPNames[i].ToString(),genSPDataSet,genSPDataReader,genSPReturnValue,genSPDataSetwithReturnValue));
			}

			result.Append(Footer());
			return result.ToString();
		}

		public string CreateProcedureMethods(string SPName,bool genSPDataSet,bool genSPDataReader,bool genSPReturnValue,bool genSPDataSetwithReturnValue)
		{
			string result="";
			ArrayList Parameters=GetSPParameters(SPName);
			if(genSPDataSetwithReturnValue)
				result+=CreateSPReturnDataSet(SPName,Parameters,true);

			if(genSPDataSet)
				result+=CreateSPReturnDataSet(SPName,Parameters,false);

			if(genSPDataReader)
				result+=CreateSPReturnDataReader(SPName,Parameters);

			if(genSPReturnValue)
				result+=CreateSPReturnInt(SPName,Parameters);
			return result;
		}

		


		private string CreateSPReturnDataSet(string SPName,ArrayList Parameters,bool IncludeReturnValue)
		{
			string SPMethodName=SPName.Replace(" ","_");

			string result="";
			string MethodParametersString="";
			for(int i=0;i<Parameters.Count;i++)
			{
				SqlParameter Param=(SqlParameter)Parameters[i];
				if( (!IncludeReturnValue) && ( Param.Direction==ParameterDirection.ReturnValue) )
					continue;
				string outString="";
				if(Param.Direction==ParameterDirection.Output || Param.Direction==ParameterDirection.ReturnValue ||Param.Direction==ParameterDirection.InputOutput )
					outString="out ";
				MethodParametersString+=outString+ Helper.GetDotNetDataType(Param.SqlDbType.ToString()) + " " + Param.ParameterName.Remove(0,1)+",";
			}
			if(MethodParametersString.Length>0)
			MethodParametersString=MethodParametersString.Remove(MethodParametersString.Length-1,1);

			

			result+="\npublic static DataSet "+SPMethodName+"("+MethodParametersString+")";
			result+="\n"+"{";
			result+="\n\t"+"ArrayList Parameters=new ArrayList(0);";
			result+="\n";
			//add parameters to list
			for(int i=0;i<Parameters.Count;i++)
			{
				SqlParameter param=(SqlParameter)Parameters[i];
				if( (!IncludeReturnValue) && ( param.Direction==ParameterDirection.ReturnValue) )
					continue;
				string paramName=param.ParameterName.Remove(0,1);
				//TODO:Add All Parameter specifications
				result+="\n\t"+"SqlParameter "+paramName+"Parameter=new SqlParameter(\""+param.ParameterName+"\",SqlDbType."+param.SqlDbType.ToString()+");";
				result+="\n\t"+paramName+"Parameter.Size="+param.Size+";";
				if(param.Direction==ParameterDirection.Input)
				{
					result+="\n\t"+paramName+"Parameter.Value="+paramName+";";
				}
				else if(param.Direction==ParameterDirection.Output || param.Direction==ParameterDirection.InputOutput)
				{
					result+="\n\t"+paramName+"Parameter.Direction=ParameterDirection.Output;";
				}
				else if(param.Direction==ParameterDirection.ReturnValue)
				{
					result+="\n\t"+paramName+"Parameter.Direction=ParameterDirection.ReturnValue;";
				}

				result+="\n\t"+"Parameters.Add("+paramName+"Parameter);";
				result+="\n";
			}

			//execute Stored Procedure
			result+="\n\t"+" DataSet dsResult=ExecuteStoredProcedure(\"["+SPName+"]\",ref Parameters);";

			//define result values
			for(int i=0;i<Parameters.Count;i++)
			{
				SqlParameter param=(SqlParameter)Parameters[i];
				if( (!IncludeReturnValue) && ( param.Direction==ParameterDirection.ReturnValue) )
					continue;
				string paramName=param.ParameterName.Remove(0,1);
				if(param.Direction!=ParameterDirection.Input)
				{
					result+="\n\t"+paramName+"=("+Helper.GetDotNetDataType(param.SqlDbType.ToString())+")"+paramName+"Parameter.Value;";
				}
			}

			result+="\n";
			result+="\n\t"+"return dsResult;";
			result+="\n"+"}";

			return result;
		}


		private string CreateSPReturnDataReader(string SPName,ArrayList Parameters)
		{
			string SPMethodName=SPName.Replace(" ","_");
			string result="";
			string MethodParametersString="";
			for(int i=0;i<Parameters.Count;i++)
			{
				SqlParameter Param=(SqlParameter)Parameters[i];
				if(Param.Direction!=ParameterDirection.Input)
					continue;
				MethodParametersString+=Helper.GetDotNetDataType(Param.SqlDbType.ToString()) + " " + Param.ParameterName.Remove(0,1)+",";
			}
			if(MethodParametersString.Length>0)
			MethodParametersString=MethodParametersString.Remove(MethodParametersString.Length-1,1);

			result+="\npublic static SqlDataReader "+SPMethodName+"Reader("+MethodParametersString+")";
			result+="\n"+"{";
			result+="\n\t"+"ArrayList Parameters=new ArrayList(0);";
			result+="\n";
			//add parameters to list
			for(int i=0;i<Parameters.Count;i++)
			{
				SqlParameter param=(SqlParameter)Parameters[i];
				if( param.Direction==ParameterDirection.ReturnValue)
					continue;
				string paramName=param.ParameterName.Remove(0,1);
				//TODO:Add All Parameter specifications
				result+="\n\t"+"SqlParameter "+paramName+"Parameter=new SqlParameter(\""+param.ParameterName+"\",SqlDbType."+param.SqlDbType.ToString()+");";
				result+="\n\t"+paramName+"Parameter.Size="+param.Size+";";
				if(param.Direction==ParameterDirection.Input)
				{
					result+="\n\t"+paramName+"Parameter.Value="+paramName+";";
				}
				else if(param.Direction==ParameterDirection.Output || param.Direction==ParameterDirection.InputOutput)
				{
					result+="\n\t"+paramName+"Parameter.Direction=ParameterDirection.Output;";
				}

				result+="\n\t"+"Parameters.Add("+paramName+"Parameter);";
				result+="\n";
			}

			//execute Stored Procedure
			result+="\n\t"+" SqlDataReader readerResult=ExecuteStoredProcedureReader(\"["+SPName+"]\",ref Parameters);";

			//define result values
			for(int i=0;i<Parameters.Count;i++)
			{
				SqlParameter param=(SqlParameter)Parameters[i];
				if(param.Direction!=ParameterDirection.Input)
					continue;
				string paramName=param.ParameterName.Remove(0,1);
			}

			result+="\n";
			result+="\n\t"+"return readerResult;";
			result+="\n"+"}";

			return result;
		}

		private string CreateSPReturnInt(string SPName,ArrayList Parameters)
		{
			string SPMethodName=SPName.Replace(" ","_");
			string result="";
			string MethodParametersString="";
			string CallParameters="";
			for(int i=0;i<Parameters.Count;i++)
			{
				SqlParameter Param=(SqlParameter)Parameters[i];
				if( Param.Direction==ParameterDirection.ReturnValue )
					continue;
				string outString="";
				
				if(Param.Direction==ParameterDirection.Output ||Param.Direction==ParameterDirection.InputOutput )
					outString="out ";
				MethodParametersString+=outString+ Helper.GetDotNetDataType(Param.SqlDbType.ToString()) + " " + Param.ParameterName.Remove(0,1)+",";
				CallParameters+=outString+ " " + Param.ParameterName.Remove(0,1)+",";
			}
			if(MethodParametersString.Length>0)
			MethodParametersString=MethodParametersString.Remove(MethodParametersString.Length-1,1);
			if(CallParameters.Length>0)
				CallParameters=CallParameters.Remove(CallParameters.Length-1,1);

			result+="\npublic static int "+SPMethodName+"ReturnValue("+MethodParametersString+")";
			result+="\n"+"{";
			result+="\n\t"+"int Return_Value;";

			if(MethodParametersString.Length>0)
			result+="\n\t"+SPMethodName+"(out Return_Value,"+CallParameters+");";
			else
				result+="\n\t"+SPMethodName+"(out Return_Value"+CallParameters+");";
			result+="\n\t"+"return Return_Value;";

			result+="\n}";
			return result;
		}

	}
}