/// <summary> /// Gets a list of tables that the passed stored procedure is dependant on. /// </summary> /// <param name="storedProcedureName">The name of the stored procedure, can be in format of [dbo].sp_MySP, dbo.sp_MySP, or sp_MySP</param> /// <returns>An ArrayList of string table names.</returns> public List <SysObjectRecord> GetDependingTablesForStoredProcedure(string connectionString, string storedProcedureName) { List <SysObjectRecord> returnValue = new List <SysObjectRecord>(); using (SqlConnection conn = new SqlConnection(connectionString)) { // Get all the input parameters for the stored procedure. conn.Open(); SqlCommand comm = new SqlCommand("sp_depends", conn); comm.CommandType = CommandType.StoredProcedure; comm.Parameters.AddWithValue("@objname", storedProcedureName); SqlDataReader dr = comm.ExecuteReader(); while (dr.Read()) { if (dr["type"].ToString() == "user table") { SysObjectRecord sysObjectRecord = new SysObjectRecord(dr["name"].ToString().Split('.')[1], dr["name"].ToString().Split('.')[0]); if (returnValue.Contains(sysObjectRecord) == false) { returnValue.Add(sysObjectRecord); } } } string query = string.Format("sp_depends"); conn.Close(); } return(returnValue); }
public static List <IndexSchema> GetUniqueIndexesForTable(string connectionString, SysObjectRecord tableObject) { string tableName = string.Format("[{0}].[{1}]", tableObject.OwnerName, tableObject.ObjectName); List <IndexSchema> returnValue; using (SqlConnection conn = new SqlConnection(connectionString)) { // Get all the unique indexes that are associated with the // passed table name conn.Open(); string query = string.Format(@" select indid, name from sysindexes where id = object_id('{0}') and indid > 0 and indid < 255 and (status & 64) = 0 -- Not a statistic and (status & 2) = 2 -- Unique Index order by indid " , tableName ); SqlCommand comm = new SqlCommand(query, conn); comm.CommandType = CommandType.Text; SqlDataReader dr = comm.ExecuteReader(); List <IndexSchema> indexes = new List <IndexSchema>(); while (dr.Read()) { IndexSchema ix = new IndexSchema(); ix.IndexName = dr["name"].ToString(); ix.IndexID = Convert.ToInt32(dr["indid"]); indexes.Add(ix); } dr.Close(); // Get the columns for each index. foreach (IndexSchema ix in indexes) { query = string.Format(@" select name from sysindexkeys ik join syscolumns c on c.colid = ik.colid where ik.id = object_id('{0}') and c.id = object_id('{0}') and indid = {1} " , tableName, ix.IndexID ); comm = new SqlCommand(query, conn); comm.CommandType = CommandType.Text; dr = comm.ExecuteReader(); List <String> indexNames = new List <String>(); while (dr.Read()) { indexNames.Add(dr["name"].ToString()); } dr.Close(); ix.ColumnNames = indexNames; } conn.Close(); returnValue = indexes; } return(returnValue); }
public static TableSchema GetTableSchema(string connectionString, SysObjectRecord tableObject) { //DataSet dsReturnValue = new DataSet(); TableSchema tsReturnValue = new TableSchema(); tsReturnValue.TableName = tableObject.ObjectName; string tableName = string.Format("[{0}].[{1}]", tableObject.OwnerName, tableObject.ObjectName); try { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand comm = new SqlCommand("sp_helpconstraint", conn); comm.CommandType = CommandType.StoredProcedure; comm.Parameters.AddWithValue("@objname", tableName); DataSet dsConstraints = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(comm); da.Fill(dsConstraints); DataTable pkTable; if (dsConstraints.Tables.Count > 1) { pkTable = dsConstraints.Tables[1]; } else { pkTable = dsConstraints.Tables[0]; } List <String> primaryKeys = new List <String>(); foreach (DataRow currRow in pkTable.Rows) { if (currRow[0].ToString().IndexOf("PRIMARY KEY") != -1) { foreach (string currPK in currRow[6].ToString().Split(',')) { primaryKeys.Add(currPK.Trim()); } } } // Get the column info. comm = new SqlCommand("sp_columns", conn); comm.CommandType = CommandType.StoredProcedure; comm.Parameters.AddWithValue("@table_name", tableObject.ObjectName); SqlDataReader dr = comm.ExecuteReader(); List <Column> columns = new List <Column>(); while (dr.Read()) { Column c = new Column(); c.ColumnName = dr["COLUMN_NAME"].ToString(); c.IsIdentity = dr["TYPE_NAME"].ToString().IndexOf(" identity") != -1; c.Length = Convert.ToInt32(dr["LENGTH"]); c.Precision = Convert.IsDBNull(dr["PRECISION"]) == true ? null : Convert.ToInt32(dr["PRECISION"]) as int?; c.Scale = Convert.IsDBNull(dr["SCALE"]) == true ? null : Convert.ToInt32(dr["SCALE"]) as int?; c.Ordinal = Convert.ToInt32(dr["ORDINAL_POSITION"]); if (c.IsIdentity) { c.DataType = Utility.ReturnSqlDBType(dr["TYPE_NAME"].ToString().Substring(0, dr["TYPE_NAME"].ToString().IndexOf(" identity")).Trim(), c.Length, false); } else { c.DataType = Utility.ReturnSqlDBType(dr["TYPE_NAME"].ToString(), c.Length, false); } c.SQLDataType = dr["TYPE_NAME"].ToString(); //throw new NotSupportedException("Figure out how to handle table types before using this code."); c.Type = Utility.ToSystemType(dr["TYPE_NAME"].ToString(), false); columns.Add(c); } conn.Close(); tsReturnValue.PrimaryKeyColumns = primaryKeys; tsReturnValue.TableColumns = columns; } } catch (SqlException sqlEx) { throw new Exception(String.Format("tableName is {0}", tableName), sqlEx); } tsReturnValue.UniqueIndexes = GetUniqueIndexesForTable(connectionString, tableObject); return(tsReturnValue); }
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); return(GetStoredProcedureSchema(connectionString, storedProcedure, false)); }
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); }
public override bool Equals(object obj) { SysObjectRecord compObj = (SysObjectRecord)obj; return(this.ObjectName == compObj.ObjectName && this.OwnerName == compObj.OwnerName); }