public static Dictionary <string, Data_SqlSPEntry> Action_AutoLoadingAllSPS(SqlConnection ActiveConnection, string SPType)
{
if (ActiveConnection != null)
{
Data_SqlDataHelper obj = new Data_SqlDataHelper();
obj.ActiveConnection = ActiveConnection;
string sql_getallsps = "select * from sys.all_objects where (type = 'P') AND (is_ms_shipped = 0)";
DataTable activeSPSDT = new DataTable();
Dictionary <string, Data_SqlSPEntry> result = new Dictionary <string, Data_SqlSPEntry>();
if (obj.Action_ExecuteForDT(sql_getallsps, out activeSPSDT))
{
foreach (DataRow activeRow in activeSPSDT.Rows)
{
Data_SqlSPEntry newSPEntry = new Data_SqlSPEntry();
string spName = "";
obj.Static_GetColumnData(activeRow, "name", out spName);
if (SPType != "")
{
if (SPType == Data_SqlSPEntryType.SelectAction)
{
if (!spName.StartsWith(Data_SqlSPEntryNameFiler.StartNamed_SelectAction))
{
continue;
}
}
else if (SPType == Data_SqlSPEntryType.UpdateAction)
{
if (!spName.StartsWith(Data_SqlSPEntryNameFiler.StartNamed_Update))
{
continue;
}
}
}
newSPEntry.SPName = spName;
newSPEntry.KeyName = spName;
string spObjectID = "";
obj.Static_GetColumnData(activeRow, "object_id", out spObjectID);
string sql_paramters = "select * from sys.all_parameters where object_id = " + spObjectID;
DataTable activeSPParametersDT = new DataTable();
string sql_paramstype = "select * from sys.types";
DataTable paramstypeDT = new DataTable();
if (!obj.Action_ExecuteForDT(sql_paramstype, out paramstypeDT))
{
return(null);
}
if (obj.Action_ExecuteForDT(sql_paramters, out activeSPParametersDT))
{
foreach (DataRow activeParamterRow in activeSPParametersDT.Rows)
{
string activeSystemType_ID = "";
obj.Static_GetColumnData(activeParamterRow, "system_type_id", out activeSystemType_ID);
string activeUserType_ID = "";
obj.Static_GetColumnData(activeParamterRow, "user_type_id", out activeUserType_ID);
string activeParamsMaxLength = "";
obj.Static_GetColumnData(activeParamterRow, "max_length", out activeParamsMaxLength);
string activeParamsName = "";
obj.Static_GetColumnData(activeParamterRow, "name", out activeParamsName);
string activeIsOutPut = "";
obj.Static_GetColumnData(activeParamterRow, "is_output", out activeIsOutPut);
string max_length = "";
obj.Static_GetColumnData(activeParamterRow, "max_length", out max_length);
string activeDBType = "";
DataRow[] dbtyps = paramstypeDT.Select("system_type_id=" + activeSystemType_ID + " and user_type_id=" + activeUserType_ID);
if (dbtyps.Length > 0)
{
obj.Static_GetColumnData(dbtyps[0], "name", out activeDBType);
Data_SqlSPEntry.AddSPParameter(ref newSPEntry, activeParamsName, Data_SqlSPHelper.Static_GetDbTypeFromConfigStr(activeDBType), ParameterDirection.Input, int.Parse(max_length), null);
}
else
{
continue;
}
}
}
else
{
continue;
}
result.Add(newSPEntry.KeyName, newSPEntry);
}
}
return(result);
}
else
{
return(null);
}
}
