public static void fetchResult(this StoredProcedure aSP, Config aConfig, DBHelper aHelper)
{
List <DBColumn> _result = new List <DBColumn>();
var paramaters = GetParamaters(aSP.Paramaters);
var query = NativeStoredProcedureQuery.GetSQLQuery(aSP.Name, paramaters);
using (SqlConnection con = new SqlConnection(aConfig.DataSource))
{
try
{
con.Open();
String q = query.Query;
q = "SET FMTONLY ON;" + q + ";SET FMTONLY OFF;";
using (SqlCommand Command = new SqlCommand(query.Query, con))
{
Command.Parameters.AddRange(query.Parameters.ToArray());
logger.Debug(q);
using (SqlDataReader aReader = Command.ExecuteReader())
{
DataTable _schema = aReader.GetSchemaTable();
if (_schema != null)
{
foreach (DataRow row in _schema.Rows)
{
_result.Add(new DBColumn
{
TABLE_NAME = "SP" + aSP.Name,
DATA_TYPE = row["DataType"].ToString(),
COLUMN_NAME = row["BaseColumnName"].ToString()
});
}
}
}
}
}
catch (Exception ex)
{
logger.Debug(ex);
con.Close();
_result.Add(new DBColumn
{
TABLE_NAME = "SP" + aSP.Name,
DATA_TYPE = typeof(DataTable).ToString(),
COLUMN_NAME = "Result"
});
var q = NativeQuery.SimpleQueryHelper("SET FMTONLY OFF;");
aHelper.Execute(delegate(DbSession aDBSession)
{
aDBSession.Execute(q, new DataReaderBinder());
});
}
var r = _result.Convert(false);
aSP.Result = r.Count() > 0 ? r.First() : null;
}
}
public List <DBStoredProcedureParamater> GetStoredProcedureParamaters()
{
var q = NativeQuery.SimpleQueryHelper("select r.SPECIFIC_SCHEMA, r.SPECIFIC_NAME, r.SPECIFIC_CATALOG, p.PARAMETER_NAME, p.DATA_TYPE from DataBank.INFORMATION_SCHEMA.ROUTINES as r inner JOIN information_schema.PARAMETERS as p ON p.SPECIFIC_NAME = r.SPECIFIC_NAME where routine_type = @p0", "PROCEDURE");
TemplateBinder <DBStoredProcedureParamater> _binder = new TemplateBinder <DBStoredProcedureParamater>();
List <DBStoredProcedureParamater> _r = new List <DBStoredProcedureParamater>();
_binder.OnBind = delegate(DBStoredProcedureParamater aT)
{
_r.Add(aT);
};
DBHelper.Execute(delegate(DbSession aDBSession)
{
aDBSession.Execute(q, _binder);
});
return(_r);
}
public IEnumerable <TableStructure> GetSchemaTables()
{
SqlQuery _query = NativeQuery.SimpleQueryHelper(
"select col.TABLE_SCHEMA, col.TABLE_CATALOG, col.TABLE_NAME, col.COLUMN_NAME, col.DATA_TYPE, const.CONSTRAINT_TYPE " +
"from INFORMATION_SCHEMA.COLUMNS as col " +
"LEFT JOIN " +
"(SELECT cu.TABLE_NAME, cu.COLUMN_NAME, cu.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE " +
"From INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu INNER JOIN " +
"INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc ON " +
"(cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME) where tc.CONSTRAINT_TYPE = 'PRIMARY KEY') AS const " +
"ON " +
"(col.TABLE_NAME = const.TABLE_NAME AND col.COLUMN_NAME = const.COLUMN_NAME) " +
"Order by col.TABLE_NAME ASC, col.COLUMN_NAME ASC");
var _dbColumns = ExecuteQuery(_query);
return(_dbColumns.Convert());
}