Пример #1
0
        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);
        }
Пример #2
0
        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);
        }