| static private GetSqlForStoredProcedures ( ) : string | ||
| return | string | |
public IEnumerable <string> GetStoredProcedureList(string connectionString)
{
var retval = new List <string>();
using (var tableReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlForStoredProcedures()))
{
while (tableReader.Read())
{
var newEntity = new StoredProc();
newEntity.Name = tableReader["name"].ToString();
retval.Add(newEntity.Name);
//newEntity.Schema = tableReader["schema"].ToString();
}
}
return(retval);
}
private static bool LoadStoredProcedures(Database database, string procName, string connectionString)
{
try
{
var dsSP = DatabaseHelper.ExecuteDataset(connectionString, SchemaModelHelper.GetSqlForStoredProcedures(procName));
var dsSPParameter = DatabaseHelper.ExecuteDataset(connectionString, SchemaModelHelper.GetSqlForStoredProceduresParameters());
//Add the Stored Procedures
StoredProc customStoredProcedure = null;
foreach (DataRow rowSP in dsSP.Tables[0].Rows)
{
var id = (int)rowSP["id"];
var name = (string)rowSP["name"];
var schema = (string)rowSP["schemaname"];
customStoredProcedure = database.StoredProcList.FirstOrDefault(x => x.Name == name);
if (customStoredProcedure == null)
{
customStoredProcedure = new StoredProc();
customStoredProcedure.Name = name;
customStoredProcedure.SQL = SchemaModelHelper.GetSqlForStoredProceduresBody(schema, name, connectionString);
customStoredProcedure.Schema = schema;
database.StoredProcList.Add(customStoredProcedure);
}
}
//Add the parameters
var sortOrder = 1;
foreach (DataRow rowSP in dsSPParameter.Tables[0].Rows)
{
if (!DatabaseHelper.IsValidSQLDataType((SqlNativeTypes)int.Parse(rowSP["xtype"].ToString())))
{
customStoredProcedure.InError = true;
customStoredProcedure.ParameterList.Clear();
customStoredProcedure.FieldList.Clear();
return(false);
}
var id = (int)rowSP["id"];
var spName = (string)rowSP["name"];
var name = (string)rowSP["ColName"];
var typeName = (string)rowSP["ColType"];
var dataType = DatabaseHelper.GetSQLDataType(rowSP["xtype"].ToString(), database.UserDefinedTypes);
var length = int.Parse(rowSP["length"].ToString());
var isOutput = ((int)rowSP["isoutparam"] != 0);
//The length is half the bytes for these types
if ((dataType == SqlDbType.NChar) ||
(dataType == SqlDbType.NVarChar))
{
length = length / 2;
}
if (customStoredProcedure != null)
{
var parameter = new Parameter();
parameter.Name = name.Replace("@", string.Empty);
parameter.SortOrder = sortOrder;
sortOrder++;
parameter.DataType = dataType;
parameter.Length = length;
parameter.Nullable = (int)rowSP["isnullable"] == 1 ? true : false;
parameter.IsOutputParameter = isOutput;
customStoredProcedure.ParameterList.Add(parameter);
}
}
//Try to get the columns
var errorItems = new List <string>();
foreach (var sp in database.StoredProcList)
{
try
{
DataSet dsSPColumn = null;
try
{
dsSPColumn = DatabaseHelper.ExecuteDataset(connectionString, SchemaModelHelper.GetSqlForStoredProceduresColumns(sp));
}
catch (Exception)
{
sp.ColumnFailure = true;
}
if ((dsSPColumn != null) && dsSPColumn.Tables.Count > 0)
{
var dt = dsSPColumn.Tables[0];
foreach (DataColumn column in dt.Columns)
{
var newColumn = new Field();
var dataType = Extensions.GetSqlDbType(column.DataType);
var length = newColumn.DataType.ValidateDataTypeMax(1000000);
newColumn.Name = column.ColumnName;
newColumn.DataType = dataType;
newColumn.Nullable = true;
newColumn.Length = length;
if (newColumn.DataType == SqlDbType.Decimal)
{
newColumn.Length = 18;
newColumn.Scale = 4;
}
if (newColumn.DataType == SqlDbType.DateTime2)
{
newColumn.Length = 7;
newColumn.Scale = 0;
}
if (newColumn.DataType == SqlDbType.VarChar)
{
newColumn.Length = 50;
}
sp.FieldList.Add(newColumn);
}
}
}
catch (Exception ex)
{
//Do Nothing - Skip to next
if (ex.Message.Contains("Invalid object name '#")) //this is a temp table. it cannot be run so there is nothing we can do
{
//Do Nothing
}
else
{
errorItems.Add(sp.Name);
}
}
}
return(true);
}
catch (Exception /*ignored*/)
{
throw;
}
}
