protected StoredProcedureSchema GetStoredProcedureSchema(string connectionString, SysObjectRecord storedProcedure, bool useTransactions)
{
StoredProcedureSchema spReturnValue = new StoredProcedureSchema();
spReturnValue.Name = storedProcedure.ObjectName;
spReturnValue.Owner = storedProcedure.OwnerName;
Log("Generating with transactions set to: " + useTransactions);
using (SqlConnection conn = new SqlConnection(connectionString))
{
// Get all the input parameters for the stored procedure.
conn.Open();
SqlTransaction trans = null;
if (useTransactions)
{
trans = conn.BeginTransaction();
}
string query = string.Format(@"
SELECT
spr.name,
spr.precision,
spr.scale,
spr.max_length,
spr.is_output,
st.name AS type,
st.is_table_type,
(select COUNT(*) from sys.columns c
inner join sys.table_types t
on t.type_table_object_id =c.object_id
where t.name=st.name
) as table_type_column_count
FROM
sys.parameters spr,
sys.procedures sp,
sys.types st,
sys.schemas ss,
sys.objects so
WHERE
so.object_id=sp.object_id
AND sp.name = '{0}'
AND ss.name = '{1}'
AND spr.object_id = sp.object_id
AND ss.schema_id = sp.schema_id
AND st.system_type_id = spr.system_type_id
AND (
(st.is_user_defined = 0 AND st.system_type_id = st.user_type_id)
OR ( st.is_table_type = 1 and spr.user_type_id=st.user_type_id)
)
ORDER BY spr.parameter_id
" ,
storedProcedure.ObjectName,
storedProcedure.OwnerName
);
Log("Running query: \r\n" + query);
SqlCommand comm = new SqlCommand(query, conn);
comm.CommandType = CommandType.Text;
if (useTransactions)
{
comm.Transaction = trans;
}
List <Parameter> parameters = new List <Parameter>();
// try
// {
SqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{
Parameter newParam = new Parameter();
//newParam.eDbType = DbConvert.ToDbType(dr["type"].ToString());
newParam.Length = Convert.IsDBNull(dr["max_length"]) == true ? null : Convert.ToInt32(dr["max_length"]) as int?;
newParam.Precision = Convert.IsDBNull(dr["precision"]) == true ? null : Convert.ToInt32(dr["precision"]) as int?;
newParam.Scale = Convert.IsDBNull(dr["scale"]) == true ? null : Convert.ToInt32(dr["scale"]) as int?;
newParam.ParameterName = dr["name"].ToString();
newParam.IsOutput = Convert.ToBoolean(dr["is_output"]);
newParam.IsTableType = Convert.ToBoolean(dr["is_table_type"]);
newParam.TableTypeColumnCount = Convert.IsDBNull(dr["table_type_column_count"]) == true ? null : Convert.ToInt32(dr["table_type_column_count"]) as int?;
newParam.DataType = newParam.IsTableType == true ? "structured" : dr["type"].ToString();
newParam.Type = Utility.ToSystemType(dr["type"].ToString(), newParam.IsTableType);
parameters.Add(newParam);
Log("Parameter added: " + newParam.ParameterName);
}
spReturnValue.Parameters = parameters;
dr.Close();
// }
// catch(SqlException ex)
// {
// // A temp table was in the stored procedure, so try again, this time
// // using transactions instead of 'WITH FMT ONLY'
// if(!useTransactions)
// {
// return(GetStoredProcedureSchema(targetServer, database, storedProcedure, true));
// }
// else
// {
// throw(ex);
// }
// }
// Get all the table schemas of any tables that this sp may be
// depending on.
//List<SysObjectRecord> tableObjects = GetDependingTablesForStoredProcedure(connectionString, "[" + storedProcedure.OwnerName + "].[" + storedProcedure.ObjectName + "]");
//List<TableSchema> tableSchemas = new List<TableSchema>();
//foreach (SysObjectRecord tableObject in tableObjects)
//{
// //Database.SysObjectRecord tableObject = new SysObjectRecord();
// //tableObject.sOwnerName =
// tableSchemas.Add(GetTableSchema(connectionString, tableObject));
// Log("Table schema added: " + tableObject.ObjectName);
//}
//spReturnValue.TableSchemas = tableSchemas;
if (!useTransactions)
{
comm.CommandText = "SET FMTONLY ON";
comm.CommandType = CommandType.Text;
comm.ExecuteNonQuery();
}
// Get columns for any returned rows.
comm.CommandText = spReturnValue.Owner + "." + spReturnValue.Name;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandTimeout = 600;
foreach (Parameter currParam in parameters)
{
SqlParameter p = new SqlParameter(); //, currParam.eDbType, currParam.nLength, ParameterDirection.Input, true, currParam.nPrecision, currParam.nScale, null, DataRowVersion.Default, DbUtility.GetSampleParameterValue(currParam));
p.ParameterName = currParam.ParameterName;
p.Value = GetSampleParameterValue(currParam);
p.Size = currParam.Length.GetValueOrDefault(-1);
if (currParam.IsOutput == true)
{
p.Direction = ParameterDirection.InputOutput;
}
else
{
p.Direction = ParameterDirection.Input;
}
comm.Parameters.Add(p);
Log("Command Parameter added: " + p.ParameterName);
// comm.Parameters.Add(currParam.sParameterName, DbUtility.GetSampleParameterValue(currParam));
//else
// comm.Parameters.Add(currParam.sParameterName);
}
List <Column> columns = new List <Column>();
try
{
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(comm);
da.Fill(dt);
int i = 0;
foreach (DataColumn c in dt.Columns)
{
Column newColumn = new Column();
newColumn.IsIdentity = c.AutoIncrement;
newColumn.Length = c.MaxLength;
newColumn.Ordinal = i;
newColumn.Precision = null;
newColumn.Scale = null;
newColumn.ColumnName = c.ColumnName;
newColumn.DataType = c.DataType.Name; //Utility.ReturnSqlDBType(c.DataType.Name, newColumn.Length, false);
newColumn.Type = c.DataType; //Utility.ToSystemType(c.DataType.Name);
newColumn.IsNullable = c.AllowDBNull;
columns.Add(newColumn);
i++;
}
}
catch (SqlException ex)
{
Log("SQL Exception, useTransactions: " + useTransactions);
Log(ex.ToString());
// A temp table was in the stored procedure, so try again, this time
// using transactions instead of 'WITH FMT ONLY'
if (!useTransactions)
{
return(GetStoredProcedureSchema(connectionString, storedProcedure, true));
}
else
{
trans.Rollback();
if (_dalTemplate != null)
{
_dalTemplate.Error(ex.ToString());
}
throw;
}
}
spReturnValue.ResultSetColumns = columns;
if (useTransactions == true)
{
trans.Rollback();
}
conn.Close();
}
return(spReturnValue);
}
public StoredProcedureSchema GetStoredProcedureSchema(string connectionString, SysObjectRecord storedProcedure)
{
try
{
if (File.Exists(LogFile) == true)
{
File.Delete(LogFile);
}
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
Log("Starting generation for: {0}", storedProcedure.ObjectName);
StoredProcedureSchema spReturnValue = new StoredProcedureSchema
{
Name = storedProcedure.ObjectName,
Owner = storedProcedure.OwnerName
};
//Log("Generating with transactions set to: " + useTransactions);
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
// Get all the input parameters for the stored procedure.
conn.Open();
MySqlTransaction trans = null;
//if (useTransactions)
trans = conn.BeginTransaction();
string query = string.Format(@"
SELECT
PARAMETER_NAME as 'name',
NUMERIC_PRECISION as 'precision',
NUMERIC_SCALE as scale,
CHARACTER_MAXIMUM_LENGTH as max_length,
CASE WHEN PARAMETER_MODE = 'OUT' THEN true ELSE false END as is_output,
DATA_TYPE as type,
false as is_table_type, -- Not supported in MySQL
0 as table_type_column_count -- Not supported in MySQL
FROM information_schema.parameters
WHERE SPECIFIC_NAME = '{0}'
AND SPECIFIC_SCHEMA = '{1}'
" ,
storedProcedure.ObjectName,
GetDatabaseName(connectionString)
);
Log("Running query: \r\n" + query);
MySqlCommand comm = new MySqlCommand(query, conn)
{
CommandType = CommandType.Text,
Transaction = trans
};
List <Parameter> parameters = new List <Parameter>();
// try
// {
MySqlDataReader dr = comm.ExecuteReader();
bool containsOutputParameter = false;
while (dr.Read())
{
Parameter newParam = new Parameter
{
Length = Convert.IsDBNull(dr["max_length"]) == true ? null : Convert.ToInt32(dr["max_length"]) as int?,
Precision = Convert.IsDBNull(dr["precision"]) == true ? null : Convert.ToInt32(dr["precision"]) as int?,
Scale = Convert.IsDBNull(dr["scale"]) == true ? null : Convert.ToInt32(dr["scale"]) as int?,
ParameterName = dr["name"].ToString(),
IsOutput = Convert.ToBoolean(dr["is_output"]),
IsTableType = Convert.ToBoolean(dr["is_table_type"]),
TableTypeColumnCount = Convert.IsDBNull(dr["table_type_column_count"]) == true ? null : Convert.ToInt32(dr["table_type_column_count"]) as int?
};
newParam.DataType = newParam.IsTableType == true ? "structured" : dr["type"].ToString();
newParam.Type = Utility.ToSystemType(dr["type"].ToString(), newParam.IsTableType);
// In MySQL, all INTs are actually LONGLONG, so override those to UINT32.
if (newParam.Type == typeof(System.Int32))
{
newParam.Type = typeof(System.UInt32);
}
if (newParam.IsOutput == true)
{
containsOutputParameter = true;
}
parameters.Add(newParam);
Log("Parameter added: " + newParam.ParameterName);
}
spReturnValue.Parameters = parameters;
dr.Close();
// }
// catch(SqlException ex)
// {
// // A temp table was in the stored procedure, so try again, this time
// // using transactions instead of 'WITH FMT ONLY'
// if(!useTransactions)
// {
// return(GetStoredProcedureSchema(targetServer, database, storedProcedure, true));
// }
// else
// {
// throw(ex);
// }
// }
// Get all the table schemas of any tables that this sp may be
// depending on.
//List<SysObjectRecord> tableObjects = GetDependingTablesForStoredProcedure(connectionString, "[" + storedProcedure.OwnerName + "].[" + storedProcedure.ObjectName + "]");
//List<TableSchema> tableSchemas = new List<TableSchema>();
//foreach (SysObjectRecord tableObject in tableObjects)
//{
// //Database.SysObjectRecord tableObject = new SysObjectRecord();
// //tableObject.sOwnerName =
// tableSchemas.Add(GetTableSchema(connectionString, tableObject));
// Log("Table schema added: " + tableObject.ObjectName);
//}
//spReturnValue.TableSchemas = tableSchemas;
//if (!useTransactions)
//{
// comm.CommandText = "SET FMTONLY ON";
// comm.CommandType = CommandType.Text;
// comm.ExecuteNonQuery();
//}
// Get columns for any returned rows.
comm.CommandText = GetDatabaseName(connectionString) + "." + spReturnValue.Name;
comm.CommandType = CommandType.StoredProcedure;
comm.CommandTimeout = 600;
List <Column> columns = new List <Column>();
if (containsOutputParameter == false)
{
foreach (Parameter currParam in parameters)
{
MySqlParameter p = new MySqlParameter
{
ParameterName = currParam.ParameterName,
Value = GetSampleParameterValue(currParam),
Size = currParam.Length.GetValueOrDefault(-1)
}; //, currParam.eDbType, currParam.nLength, ParameterDirection.Input, true, currParam.nPrecision, currParam.nScale, null, DataRowVersion.Default, DbUtility.GetSampleParameterValue(currParam));
if (currParam.IsOutput == true)
{
p.Direction = ParameterDirection.InputOutput;
}
else
{
p.Direction = ParameterDirection.Input;
}
comm.Parameters.Add(p);
Log("Command Parameter added: " + p.ParameterName);
// comm.Parameters.Add(currParam.sParameterName, DbUtility.GetSampleParameterValue(currParam));
//else
// comm.Parameters.Add(currParam.sParameterName);
}
try
{
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(comm);
da.Fill(dt);
int i = 0;
foreach (DataColumn c in dt.Columns)
{
Column newColumn = new Column
{
IsIdentity = c.AutoIncrement,
Length = c.MaxLength,
Ordinal = i,
Precision = null,
Scale = null,
ColumnName = c.ColumnName,
DataType = c.DataType.Name, //Utility.ReturnSqlDBType(c.DataType.Name, newColumn.Length, false);
Type = c.DataType, //Utility.ToSystemType(c.DataType.Name);
IsNullable = c.AllowDBNull
};
columns.Add(newColumn);
i++;
}
}
catch (MySqlException ex)
{
//Log("SQL Exception, useTransactions: " + useTransactions);
Log(ex.ToString());
// A temp table was in the stored procedure, so try again, this time
// using transactions instead of 'WITH FMT ONLY'
//if (!useTransactions)
//{
// return (GetStoredProcedureSchema(connectionString, storedProcedure, true));
//}
//else
//{
trans.Rollback();
if (_dalTemplate != null)
{
_dalTemplate.Error(ex.ToString());
}
throw;
//}
}
}
spReturnValue.ResultSetColumns = columns;
//if(useTransactions == true)
trans.Rollback();
conn.Close();
}
Log("\n\n" + JsonConvert.SerializeObject(spReturnValue, Formatting.Indented) + "\n\n");
return(spReturnValue);
}