예제 #1
0
        public void FillStoredProcedureColumns(Model.StoredProcedure storedProcedure)
        {
            string sql = "exec [" + storedProcedure.Name + "] ";

            for (int i = 0; i < storedProcedure.Parameters.Length; i++)
            {
                Model.StoredProcedure.Parameter parameter = storedProcedure.Parameters[i];
                sql += parameter.Name + "=NULL";
                if (i < storedProcedure.Parameters.Length - 1)
                {
                    sql += ", ";
                }
            }

            DataTable dataTable = RunQueryDataTable(sql);

            while (storedProcedure.Columns.Length > 0)
            {
                storedProcedure.RemoveColumn(storedProcedure.Columns[0]);
            }

            foreach (DataColumn dataColumn in dataTable.Columns)
            {
                Column column = new Column(
                    dataColumn.ColumnName,
                    false,
                    storedProcedure,
                    dataColumn.Ordinal,
                    dataColumn.AllowDBNull,
                    GetSqlDataType(dataColumn.DataType),
                    0,
                    false,
                    dataColumn.AutoIncrement,
                    dataColumn.DefaultValue.ToString(),
                    false);

                if (IsSupported(column))
                {
                    storedProcedure.AddColumn(column);
                }
            }
        }
예제 #2
0
        public void FillStoredProcedureColumns(Model.StoredProcedure storedProcedure)
        {
            string sql = string.Format("SET FMTONLY ON EXEC [{0}].[{1}] ", storedProcedure.Schema, storedProcedure.Name);

            for (int i = 0; i < storedProcedure.Parameters.Length; i++)
            {
                if (Providers.Database.Helper.SQLServer.IsDataTypeText(storedProcedure.Parameters[i].DataType))
                {
                    sql += "\"0\", ";
                }
                else
                {
                    sql += "0, ";
                }
            }
            sql = sql.TrimEnd(EndChars);
            DataTable dataTable;

            try
            {
                dataTable = RunQueryDataTable(sql);
            }
            catch (Exception ex)
            {
                //ResetConnection();
                sql = sql.Replace("SET FMTONLY ON", "SET FMTONLY OFF");

                try
                {
                    using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.RequiresNew))
                    {
                        dataTable = RunQueryDataTable(sql);
                        // We must ALWAYS rollback because we are executing stored procs with FMTONLY = OFF, so just don't call scope.Complete()
                    }
                }
                catch
                {
                    storedProcedure.Errors.Add(string.Format("Error getting returned columns: {0}. SQL: {1}", ex.Message, sql));
                    return;
                }
            }
            while (storedProcedure.Columns.Length > 0)
            {
                storedProcedure.RemoveColumn(storedProcedure.Columns[0]);
            }
            foreach (DataColumn dataColumn in dataTable.Columns)
            {
                Column column = new Column(
                    dataColumn.ColumnName,
                    false,
                    storedProcedure,
                    dataColumn.Ordinal,
                    dataColumn.AllowDBNull,
                    GetSqlDataType(dataColumn.DataType),
                    0,
                    false,
                    dataColumn.AutoIncrement,
                    dataColumn.DefaultValue.ToString(),
                    false,
                    false,
                    0,
                    0);

                if (IsSupported(column))
                {
                    storedProcedure.AddColumn(column);
                }
            }
        }